INSERT INTO from GetInsertCommand() Has Null for Data

[EDN Admin]

I am trying to add a new row with data to a DataTable. The new row is successfully added to the table as is revealed by looping through the ItemArray and printing the items to the console, but my GetInsertCommad returns column names but null for data. The INSERT INTO looks like the following: INSERT INTO user (userFirstName, userLastName, ... , userLogin) VALUES (?, ?, ... , ?) , and I obviously get a syntax error.
My database is an Access accdb. My table has a composite primary unique key made of userFirstName and userLastName.
Method fromSaveForm has passed to it the form as a control (Control frm) and a datatable (DataTable dt). The table resides in an Access accdb database. Some of the controls that are contained within the form have Tag values that are equal to the name of a column in the DataTable. The goal is to iterate through controls, find those with Tag values != null, and update a column of a new DataRow whose ColumnName is equal to the Tag value.
I apologize for posting so many lines of code, but I think it is necessary to convey the whole concept.public int formSaveForm(Control frm, DataTable dt)
{
// Populate arrays with column names and corresponding data types.
string[] araColumns = new string[dt.Columns.Count];
string[] araDataTypes = new string[dt.Columns.Count];
for (int j = 0; j < dt.Columns.Count; j++)
{
araColumns[j] = dt.Columns[j].ColumnName;
araDataTypes[j] = dt.Columns[j].DataType.ToString();
}
// Create a row for the datatable
DataRow dr = dt.NewRow();
// Add data to the row with a recursive function
dr = buildDataRow(frm, dr, araColumns, araDataTypes);
dt.Rows.Add(dr);
OleDbCommandBuilder cmd = new OleDbCommandBuilder(odaAdapter);
odaAdapter.InsertCommand = cmd.GetInsertCommand();
odaAdapter.Update(dt); // Fails here
return 1;
}
private DataRow buildDataRow(Control frm, DataRow dr, string[] araColumns, string[] araDataTypes)
{
// iterate through all controls on the form
for (int j = 0; j < frm.Controls.Count; j++)
{
if (isContainer(frm.Controls[j])) // If the control is a container, recurse
buildDataRow(frm.Controls[j], dr, araColumns, araDataTypes);
else if (frm.Controls[j].Tag != null
&& frm.Controls[j].Tag.ToString() != ""
&& frm.Controls[j].GetType().ToString() != "System.Windows.Forms.Button")
{
// Determine the data type for the Tag values
// First, find the index of the coulmn name in araColumns[].
// Then, the data type has the same index number (i) in araDataTypes[].
int i = 0;
for (i = 0; i < araColumns.Length; i++)
{
if (frm.Controls[j].Tag.ToString() == araColumns) break;
}
// Get the column name from the Tag value of the control
string sColName = frm.Controls[j].Tag.ToString();
// Add column value from the control to the row using index i
switch (araDataTypes.ToString())
{
case "System.String":
dr[sColName] = "" + frm.Controls[j].Text.ToString() + "";
break;
case "System.DateTime":
dr[sColName] = "#" + frm.Controls[j].Text.ToString() + "#";
break;
default:
dr[sColName] = frm.Controls[j].Text;
break;
}
}
}
return dr;
}
private bool isContainer(Control ctrl)
{
string sControlType = ctrl.GetType().ToString();
switch (sControlType)
{
case "System.Windows.Forms.FlowLayoutPanel":
case "System.Windows.Forms.GroupBox":
case "System.Windows.Forms.Panel":
case "System.Windows.Forms.SplitContainer":
case "System.Windows.Forms.TabControl":
case "System.Windows.Forms.TableLayoutPanel":
return true;
default:
return false;
}
}

REvans

View the full article