I have an Access database for company policies. I'm now creating ASP.NET administrative pages so someone can update the policies online. I'm having a problem with the "Add a new policy" command.
It's set up as a datagrid that creates a new row at the end with the UPDATE/CANCEL commandNames in the first 2 columns. Everything works and allows me to enter data for a new policy. But when I click on UPDATE, I get the following error:
[red]System.FormatException: String was not recognized as a valid DateTime. at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) at System.DateTime.Parse(String s, IFormatProvider provider, DateTimeStyles styles) at System.DateTime.Parse(String s, IFormatProvider provider) at System.Convert.ToDateTime(String value, IFormatProvider provider) at System.String.System.IConvertible.ToDateTime(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.OleDb.OleDbParameter.GetParameterValue() at System.Data.OleDb.OleDbParameter.GetParameterScale() at System.Data.OleDb.OleDbParameter.BindParameter(Int32 i, DBBindings bindings, tagDBPARAMBINDINFO[] bindInfo) at System.Data.OleDb.OleDbCommand.CreateAccessor() at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior behavior, Boolean throwifnotsupported) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at ASP.PFFpolicies_add_aspx.DataGrid_Update(Object sender, DataGridCommandEventArgs e) in K:\Edserver develop\AdminPages\PFFpolicies_add.aspx:line 96[/red]
It clearly has a problem with how I've set up the date, but i don't see anything wrong with it. The database LastUpdated field is set to SHORT DATE DateTime format. So this should work. The second two columns (PolicyID and Manual) are invisible on the page because the user doesn't add them, they are automatically generated.
And on this line of the code, it wouldn't allow me to have a row added with blank spaces. I had to add something to avoid errors:
[red]object[] rowValues = { "0", "96", "0", "xx", "xx", "xx", "01/01/00" };
ds.Tables[0].Rows.Add(rowValues);[/red]
Here is the code that (I think) you need to look at:
// get the edit text boxes
string id = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
string manual = ((TextBox)e.Item.Cells[3].Controls[0]).Text;
string pageNumber = ((TextBox)e.Item.Cells[4].Controls[0]).Text;
string title = ((TextBox)e.Item.Cells[5].Controls[0]).Text;
string notes = ((TextBox)e.Item.Cells[6].Controls[0]).Text;
string link = ((TextBox)e.Item.Cells[7].Controls[0]).Text;
string lastDate = ((TextBox)e.Item.Cells[8].Controls[0]).Text;
// TODO: update the Command value for your application
OleDbConnection myConnection = new OleDbConnection(ConnectionString);
OleDbCommand UpdateCommand = new OleDbCommand();
UpdateCommand.Connection = myConnection;
if (AddingNew)
UpdateCommand.CommandText = "INSERT INTO PFFPolicies(PolicyID, Manual, PageNumber, Title, Notes, Link, LastUpdated) VALUES (@PolicyID, @Manual, @PageNumber, @Title, @Notes, @Link, @LastUpdated)";
else
UpdateCommand.CommandText = "UPDATE PFFPolicies SET Manual = @Manual, Title = @Title, Notes = @Notes, Link = @Link, LastUpdated = @LastUpdated WHERE PolicyID = @PolicyID";
UpdateCommand.Parameters.Add("@PolicyID", OleDbType.Char, 6).Value = id;
UpdateCommand.Parameters.Add("@Manual", OleDbType.VarChar, 3).Value = "96";
UpdateCommand.Parameters.Add("@PageNumber", OleDbType.Double, 6).Value = pageNumber;
UpdateCommand.Parameters.Add("@Title", OleDbType.VarChar, 200).Value = title;
UpdateCommand.Parameters.Add("@Notes", OleDbType.VarWChar, 500).Value = notes;
UpdateCommand.Parameters.Add("@Link", OleDbType.VarChar, 500).Value = link;
[red]UpdateCommand.Parameters.Add("@LastUpdated", OleDbType.DBTimeStamp, 10).Value = lastDate;[/red]
CheckIsEditing("");
if (!isEditing) {
// set the flag so we know to do an insert at Update time
AddingNew = true;
// add new row to the end of the dataset after binding
// first get the data
OleDbConnection myConnection = new OleDbConnection(ConnectionString);
OleDbDataAdapter myCommand = new OleDbDataAdapter(SelectCommand, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds);
// add a new blank row to the end of the data
[red]object[] rowValues = { "0", "96", "0", "xx", "xx", "xx", "01/01/00" };
ds.Tables[0].Rows.Add(rowValues);[/red]
// figure out the EditItemIndex, last record on last page
int recordCount = ds.Tables[0].Rows.Count;
if (recordCount > 1)
recordCount--;
DataGrid1.CurrentPageIndex = recordCount/DataGrid1.PageSize;
DataGrid1.EditItemIndex = recordCount%DataGrid1.PageSize;
// databind
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
}
Dawn
It's set up as a datagrid that creates a new row at the end with the UPDATE/CANCEL commandNames in the first 2 columns. Everything works and allows me to enter data for a new policy. But when I click on UPDATE, I get the following error:
[red]System.FormatException: String was not recognized as a valid DateTime. at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) at System.DateTime.Parse(String s, IFormatProvider provider, DateTimeStyles styles) at System.DateTime.Parse(String s, IFormatProvider provider) at System.Convert.ToDateTime(String value, IFormatProvider provider) at System.String.System.IConvertible.ToDateTime(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.OleDb.OleDbParameter.GetParameterValue() at System.Data.OleDb.OleDbParameter.GetParameterScale() at System.Data.OleDb.OleDbParameter.BindParameter(Int32 i, DBBindings bindings, tagDBPARAMBINDINFO[] bindInfo) at System.Data.OleDb.OleDbCommand.CreateAccessor() at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior behavior, Boolean throwifnotsupported) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at ASP.PFFpolicies_add_aspx.DataGrid_Update(Object sender, DataGridCommandEventArgs e) in K:\Edserver develop\AdminPages\PFFpolicies_add.aspx:line 96[/red]
It clearly has a problem with how I've set up the date, but i don't see anything wrong with it. The database LastUpdated field is set to SHORT DATE DateTime format. So this should work. The second two columns (PolicyID and Manual) are invisible on the page because the user doesn't add them, they are automatically generated.
And on this line of the code, it wouldn't allow me to have a row added with blank spaces. I had to add something to avoid errors:
[red]object[] rowValues = { "0", "96", "0", "xx", "xx", "xx", "01/01/00" };
ds.Tables[0].Rows.Add(rowValues);[/red]
Here is the code that (I think) you need to look at:
// get the edit text boxes
string id = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
string manual = ((TextBox)e.Item.Cells[3].Controls[0]).Text;
string pageNumber = ((TextBox)e.Item.Cells[4].Controls[0]).Text;
string title = ((TextBox)e.Item.Cells[5].Controls[0]).Text;
string notes = ((TextBox)e.Item.Cells[6].Controls[0]).Text;
string link = ((TextBox)e.Item.Cells[7].Controls[0]).Text;
string lastDate = ((TextBox)e.Item.Cells[8].Controls[0]).Text;
// TODO: update the Command value for your application
OleDbConnection myConnection = new OleDbConnection(ConnectionString);
OleDbCommand UpdateCommand = new OleDbCommand();
UpdateCommand.Connection = myConnection;
if (AddingNew)
UpdateCommand.CommandText = "INSERT INTO PFFPolicies(PolicyID, Manual, PageNumber, Title, Notes, Link, LastUpdated) VALUES (@PolicyID, @Manual, @PageNumber, @Title, @Notes, @Link, @LastUpdated)";
else
UpdateCommand.CommandText = "UPDATE PFFPolicies SET Manual = @Manual, Title = @Title, Notes = @Notes, Link = @Link, LastUpdated = @LastUpdated WHERE PolicyID = @PolicyID";
UpdateCommand.Parameters.Add("@PolicyID", OleDbType.Char, 6).Value = id;
UpdateCommand.Parameters.Add("@Manual", OleDbType.VarChar, 3).Value = "96";
UpdateCommand.Parameters.Add("@PageNumber", OleDbType.Double, 6).Value = pageNumber;
UpdateCommand.Parameters.Add("@Title", OleDbType.VarChar, 200).Value = title;
UpdateCommand.Parameters.Add("@Notes", OleDbType.VarWChar, 500).Value = notes;
UpdateCommand.Parameters.Add("@Link", OleDbType.VarChar, 500).Value = link;
[red]UpdateCommand.Parameters.Add("@LastUpdated", OleDbType.DBTimeStamp, 10).Value = lastDate;[/red]
CheckIsEditing("");
if (!isEditing) {
// set the flag so we know to do an insert at Update time
AddingNew = true;
// add new row to the end of the dataset after binding
// first get the data
OleDbConnection myConnection = new OleDbConnection(ConnectionString);
OleDbDataAdapter myCommand = new OleDbDataAdapter(SelectCommand, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds);
// add a new blank row to the end of the data
[red]object[] rowValues = { "0", "96", "0", "xx", "xx", "xx", "01/01/00" };
ds.Tables[0].Rows.Add(rowValues);[/red]
// figure out the EditItemIndex, last record on last page
int recordCount = ds.Tables[0].Rows.Count;
if (recordCount > 1)
recordCount--;
DataGrid1.CurrentPageIndex = recordCount/DataGrid1.PageSize;
DataGrid1.EditItemIndex = recordCount%DataGrid1.PageSize;
// databind
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
}
Dawn