System.FormatException: Input string was not in a correct format.
I have narrowed that error down to that error down to the int fields, how do I convert the string from the form into an int (I tried Convert.ToInt32() without success)?
I can get the insert the data successfully if use an explicit value (Value = 30) and not (Value = Formfield.Text)
Also how do I get the DB KeyID Field to autogenerate a number, currently if I do not manually enter a value throws a no Null value allowed error?
-------------------------------
this.sqlInsertCommand1.CommandText = @dotnet.itags.org."INSERT INTO Events(KeyID, EventDateTime, EventName, StartLocation, EndLocation, AvailableSeats, Description, RateAdult, RateChild, RateSenior) VALUES (@dotnet.itags.org.KeyID, @dotnet.itags.org.EventDateTime, @dotnet.itags.org.EventName, @dotnet.itags.org.StartLocation, @dotnet.itags.org.EndLocation, @dotnet.itags.org.AvailableSeats, @dotnet.itags.org.Description, @dotnet.itags.org.RateAdult, @dotnet.itags.org.RateChild, @dotnet.itags.org.RateSenior); SELECT KeyID, EventDateTime, EventName, StartLocation, EndLocation, AvailableSeats, Description, RateAdult, RateChild, RateSenior, EventID FROM Events WHERE KeyID = @dotnet.itags.org.KeyID";
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.KeyID", System.Data.SqlDbType.Int, 4);
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.EventDateTime", System.Data.SqlDbType.SmallDateTime, 4).Value = System.DateTime.Now;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.AvailableSeats", System.Data.SqlDbType.Int, 4).Value = 21;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.EndLocation", System.Data.SqlDbType.NVarChar, 50).Value = EndLocation.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.EventName", System.Data.SqlDbType.NVarChar, 50).Value = EventName.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.RateAdult", System.Data.SqlDbType.Int, 4).Value = RateAdult.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.RateChild", System.Data.SqlDbType.Int, 4).Value = RateChild.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.RateSenior", System.Data.SqlDbType.Int, 4).Value = RateSenior.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.StartLocation", System.Data.SqlDbType.NVarChar, 50).Value = StartLocation.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.Description", System.Data.SqlDbType.Text, 16).Value = Description.Text;
Thanks, Justin.Example on how to parse a text (must of course be number only entry, and cannot be an empty string) into an Integer:
sqlInsertCommand1.Parameters.Add("@.RateAdult", SqlDbType.Int, 4).Value = int.Parse(RateAdult.Text);
You should of course not set the KeyID field manually. This is taken care of by the DataBase when designing your Table:
Identity = Yes
Identity Seed = 1
Identity Increment = 1
Thanks Andre,
I have set the key ID field to "Identity = Yes" but I am not not sure what to do in the code with the KeyID Value. If I do not declare a value I get a non-null error.
Any ideas?
Can you recommend some good articles/tutorials for adding data to an MS SQL DB?
Thanks, Justin.
When inserting you need not worry about the KeyID value as it is created when the data is inserted into the DataBase. When updating or deleting (or selecting a certain record based on an ID) you need to get the ID value from Database when you process your query to get the Data you need to display.
If you are using a DataGrid or a DataList to present the Data, you'll use the the DataKeyField property of the DataGrid/DataList (the Repeater Control does not have this Property) to store the id:
DataKeyField = "KeyID"
When you update (or delete), you'll get the necessary ID like this:
int keyID = (int)myDataGrid.DataKeys[e.Item.ItemIndex];
Now you have the correct id for updating or deleting the correct record.
Thanks for the help.
As instructed by your first reply I tried using int.Parse() but received the same error I added an if statement to make sure the form variable was not empty.
Also, I removed all traces of keyID from the insert statment but I am still getting no nulls allowed error.
Thank you for your patience as I am still learning. I appreciate the guidance.
Thanks, Justin.
You will need to show us your Table design. What are the fields of the Table you are trying to insert into? What Type of data do they accept, do they accept 'null' (can they be empty) values or not? Remember that a 'null' value is NOT the same thing as an empty string ("").
"I can get the insert the data successfully if use an explicit value (Value = 30) and not (Value = Formfield.Text)"
If 'Formfield.Text' is empty, you cannot parse it using 'int.Parse(Formfield.Text)'. That will give you an exception. What you can do is e.g. use:
int formfield = 0;if (Formfield.Text.Trim().Length > 0)
formfield = int.Parse(Formfield.Text.Trim());sqlInsertCommand1.Parameters.Add("@.someValue", System.Data.SqlDbType.Int, 4).Value = formfield;
This way you ensure that if the particular formfield has not been assigned a value by the user, the value will be '0'.
If there are fields in your Table that by design do not accept 'null' values, you must insert some kind of value. Either by setting a default value in your table design, or by making sure that a valid value is entered by the User, using .Net Validator Controls.
Well, I finally fixed the problem.
VS.NET puts the insert statement in the InitializeComponent() class and I thought if I put the execute command in submit's event handeler it would execute the insert statement from the InitializeComponent() but it wasn't.
I put the insert command in the event handeler and now it's working.
Thank you very much for your patience.
Thanks, Justin.
