Sam Nasr: September 2020

Listed below is an example of using Sorting and Filtering on ADO objects. This examples utilizes an Oracle DB, but ADO objects can be used with various databases. In this situation, the entire data table is retrieved in memory. When needed, that data table can be sorted using a specific column or filtered using the SELECT method with the specified predicate.

public void ProcesssData()

{

//Retrieve entire data table into memory

gPOLines = RGNdm.GetLineNumsByPO(PO);

//Create a VIEW from the in-memory data table

var newView = gPOLines.DefaultView.ToTable(false, "PART_ID", "PO_NUMBER", "CREATE_DATE", "VENDOR_NAME", "INITIATED_BY");

//SORT by desired field

gPOLines.DefaultView.Sort = " PART_ID ASC ";

//SELECT (filter) for specific criteria

DataRow[] filteredRows = gPOLines.Select($" PART_ID = '{cbPartID1.Text}' ");

if (filteredRows.Count() >= 1)

{

decimal userOrderQty = (decimal)filteredRows[0].Field<double>("USER_ORDER_QTY");

}

public DataTable GetLineNumsByPO(string PO)

{

string sql = $" SELECT LINE_NO, PART_ID, USER_ORDER_QTY FROM PO_TABLE ";

OracleDataAdapter da = new OracleDataAdapter(sql, User.connString);

DataTable tbl = new DataTable();

da.Fill(tbl);

return tbl;

}

Wednesday, September 23, 2020

Capturing the file name and line number of an Exception

Handling exceptions is a critical task in any application. When an exception is logged, it needs to have sufficient detail so it can be researched at a later time. In addition, as a developer, you want to extract and log as much detail about it as possible. Some of those details include the file name and line number where the exception occurred. Listed below is a method that handles exceptions, extracts the message, file name, and line number for logging.

private void ProcessEx(Exception ex, string details = "")

{

int lineNumber = 0;

const string lineNumberMarker = ":line ";

string traceText = ex.StackTrace + ""; //Adding empty space will ensure final value is never null

int lineIndex = traceText.LastIndexOf(lineNumberMarker);

if (lineIndex >= 0)

{

string lineNumberText = traceText.Substring(lineIndex + lineNumberMarker.Length);

int.TryParse(lineNumberText, out lineNumber);

}

string fileName = string.Empty;

const string fileNameMarker = " in ";

int fileNameIndex = traceText.LastIndexOf(fileNameMarker);

if (fileNameIndex >= 0)

{

int fileNameStart = fileNameIndex + fileNameMarker.Length;

int fileNameLength = lineIndex - fileNameIndex - fileNameMarker.Length;

fileName = traceText.Substring(fileNameStart, fileNameLength);

}

//Log error message + file/line info

string msg = $"Error: {ex.Message}. \r\n{details}";

string fileInfo = $"\r\nFile {fileName}, Line {lineNumber} ";

Logging lg = new Logging();

lg.WriteLog(msg + fileInfo);

}

Friday, September 18, 2020

Changing DB Password in Oracle SQL Developer

While working with Oracle SQL Developer, you may encounter the following message when the password is near expiration.

A database warning was encountered performing the requested operation:

ORA-28002: the password will expire within 5 days

28002. 00000 - "the password will expire within %s days"

*Cause: The user's account is about to expire and the password

needs to be changed

*Action: change the password or contact the DBA

Vendor code 28002

To change the password:

Open the Connections tab (View > Connections)
Right click the database where the password needs to be changed
Select “Reset Password…”

A pop-up form (see below) will appear prompting for the old and new passwords.

That’s it. Seems simple but if you’re not familiar with SQL Developer, this can be a frustrating.

Wednesday, September 2, 2020

Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Overview: I have a Windows Form application, built in .Net 4.7.2 using Visual Studio 2019. The form has 3 PartID combo boxes, allowing the user to select 3 distinct part IDs from a finite list of parts in the drop down.

Problem: When the combo box was being filled with values in the drop down list, the following exception was thrown.

Exception:

System.AccessViolationException

HResult=0x80004003

Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Source=<Cannot evaluate the exception source>

StackTrace:

Code:

private void cbPO_LostFocus(object sender, EventArgs e)

{

FillPartID1(poLines);

}

private void cbPO_SelectedIndexChanged(object sender, EventArgs e)

{

FillPartID1(poLines);

}

private void FillPartID1(DataTable poLines)

{

AutoCompleteStringCollection acs1 = new AutoCompleteStringCollection();

foreach (var rec in poLines.AsEnumerable())

acs1.Add(rec.ItemArray[1].ToString());

cbPartID1.AutoCompleteCustomSource = acs1;

cbPartID1.AutoCompleteMode = AutoCompleteMode.SuggestAppend; //Exception thrown here during 2^nd execution

cbPartID1.AutoCompleteSource = AutoCompleteSource.CustomSource;

cbPartID1.DataSource = poLines;

cbPartID1.DisplayMember = "PART_ID";

cbPartID1.ValueMember = "PART_ID";

cbPartID1.SelectedIndex = -1;

}

Cause: The FillPartID1() was being called twice due to event handlers cbPO_SelectedIndexChanged() and cbPO_LostFocus().

Solution: Refactor the code so FillPartID1() is called only once.

ComboBox selection changes selection in other comboBox controls

Problem: Once the user selects a PartID in any combo box, the other 2 combo boxes are automatically set to that value.

private void Start()

{

DataTable POLines = GetLineNumsByPO(PO);

FillPartID1(POLines);

FillPartID2(POLines);

FillPartID3(POLines);

}

private void FillPartID1(DataTable poLines)

{

AutoCompleteStringCollection acs1 = new AutoCompleteStringCollection();

foreach (var rec in poLines.AsEnumerable())

acs1.Add(rec.ItemArray[1].ToString());

cbPartID1.AutoCompleteCustomSource = acs1;

cbPartID1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;

cbPartID1.AutoCompleteSource = AutoCompleteSource.CustomSource;

cbPartID1.DataSource = poLines;

cbPartID1.DisplayMember = "PART_ID";

cbPartID1.ValueMember = "PART_ID";

cbPartID1.SelectedIndex = -1;

}

private void FillPartID2(DataTable poLines)

{

AutoCompleteStringCollection acs2 = new AutoCompleteStringCollection();

foreach (var rec in poLines.AsEnumerable())

acs2.Add(rec.ItemArray[1].ToString());

cbPartID2.AutoCompleteCustomSource = acs2;

cbPartID2.AutoCompleteMode = AutoCompleteMode.SuggestAppend;

cbPartID2.AutoCompleteSource = AutoCompleteSource.CustomSource;

cbPartID2.DataSource = poLines;

cbPartID2.DisplayMember = "PART_ID";

cbPartID2.ValueMember = "PART_ID";

cbPartID2.SelectedIndex = -1;

}

private void FillPartID3(DataTable poLines)

{

AutoCompleteStringCollection acs3 = new AutoCompleteStringCollection();

foreach (var rec in poLines.AsEnumerable())

acs3.Add(rec.ItemArray[1].ToString());

cbPartID3.AutoCompleteCustomSource = acs3;

cbPartID3.AutoCompleteMode = AutoCompleteMode.SuggestAppend;

cbPartID3.AutoCompleteSource = AutoCompleteSource.CustomSource;

cbPartID3.DataSource = poLines;

cbPartID3.DisplayMember = "PART_ID";

cbPartID3.ValueMember = "PART_ID";

cbPartID3.SelectedIndex = -1;

}

Solution: The Start() method was modified to have 3 separate datatables, each one passed to a partID comboBox. This resolved one combo box selection updating the other combo boxes

private void Start()

{

//3 separate datatables were required to avoid having one PartID selection update another PartID control.

DataTable POLines1 = GetLineNumsByPO(PO);

DataTable POLines2 = GetLineNumsByPO(PO);

DataTable POLines3 = GetLineNumsByPO(PO);

FillPartID1(POLines1);

FillPartID2(POLines2);

FillPartID3(POLines3);

}