1

I have a bunch of code. When I add the details, i dont know why I am getting error as Input string was not in a correct format.

at this line

cmd1.ExecuteNonQuery();

Please see the code for your reference:-

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultCSRConnection"].ConnectionString); using (SqlCommand cmd = conn.CreateCommand()) { conn.Open(); SqlCommand cmd1 = new SqlCommand("Insert into tbl_expense_category (NgoId,expense_category_name,expense_category_description,active) values(@NgoId,@expense_category_name, @expense_category_description,@active)", conn); cmd1.Parameters.Add("@NgoId", SqlDbType.Int).Value = ddlNgoName.SelectedValue; cmd1.Parameters.Add("@expense_category_name", SqlDbType.Int).Value = txtExpenseCat.Text; cmd1.Parameters.Add("@expense_category_description", SqlDbType.NVarChar).Value = txtEspenseDesc.Text; cmd1.Parameters.Add("@active", SqlDbType.Bit).Value = chkActive.Checked; cmd1.ExecuteNonQuery(); conn.Close(); ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Expenses added sucessfully');window.location ='csrexpensecategorylist.aspx';", true); }

Please suggest where I am going wrong.

Improve this question
7
  • Do your SqlDbType's match the type's in the DB? Commented Jan 23, 2015 at 14:03
  • you need to parse the values to their correct types, not use the text values etc Commented Jan 23, 2015 at 14:04
  • Run it in the debugger and see what the value of ddlNgoName.SelectedValue and txtExpenseCat.Text are. Is expense_category_name really an int? Commented Jan 23, 2015 at 14:05
  • @Aaron: I had written wrong DB type for expense_category_name it was nvarchar Commented Jan 23, 2015 at 14:10
  • @DStanley: Yes, I checked it and it was the only problem, Now it is working for me Commented Jan 23, 2015 at 14:12

2 Answers 2

Reset to default
1

In these two lines you are saying that your parameter are Integers, but the first line tries to assign an object value and the second line tries to assign a string value. Something need to be fixed there

cmd1.Parameters.Add("@NgoId", SqlDbType.Int).Value = ddlNgoName.SelectedValue; cmd1.Parameters.Add("@expense_category_name", SqlDbType.Int).Value = txtExpenseCat.Text;

For example these seems more appropriate given the field names

cmd1.Parameters.Add("@NgoId", SqlDbType.Int).Value = Convert.ToInt32(ddlNgoName.SelectedValue); cmd1.Parameters.Add("@expense_category_name", SqlDbType.NVarChar, 255).Value = txtExpenseCat.Text;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Actually I need to change the DB type for @expense_category_name as in the table it is mentioned as nvarchar.
Well, if you need an integer then you return the SqlDbType.Int and add a conversion on the TextBox.
Yes you are right, but I dont need integer. My logic was to add text for that column. Thanks a lot.
0

First of all it seems that you use two SqlCommand. You don't need them both; try using one, like this:

conn.Open(); using (SqlCommand command = new SqlCommand( "Insert into tbl_expense_category (NgoId, expense_category_name, expense_category_description, active) values (@NgoId, @expense_category_name, @expense_category_description, @active)", conn)) { command.Parameters.Add("@NgoId", SqlDbType.Int).Value = Convert.ToInt32(ddlNgoName.SelectedValue); command.Parameters.Add("@expense_category_name", SqlDbType.Int).Value = Convert.ToInt32(txtExpenseCat.Text); command.Parameters.Add("@expense_category_description", SqlDbType.NVarChar).Value = txtEspenseDesc.Text; command.Parameters.Add("@active", SqlDbType.Bit).Value = chkActive.Checked; command.ExecuteNonQuery(); } conn.Close();

Second, you have to know that it is a good practice open the connection before the using directive, especially when you are sending more than 1 command to the database.

Third, you must use the value types correctly. Where you are using integers, assign integer values to the parameters.

I have edited the code for you.

I hope this answers your question.

Improve this answer

5 Comments

Good catch on the extra command, but you don't have to open the connection before using.
You are right, I think it's a good practice. I will replace "must" with "it is nice to" :)
Why do you think it's good practice? The best practices I've seen (and what the examples on MSDN do) is to open the connection immediately before the command is executed.
Because if you use more commands against the data source, you open the connection, use the connection through 1 or more commands, and afterwards you close the connection.
Also I think that the best practice is to use using on the SqlConnection too. It is a lot easier to follow the code, operationally speaking.