0

I know how to use Text Box value in Access query for string fields, but i am unable to understand how to use it for int fields.

I am writing the following query and receiving error messages.

ERROR MESSAGE: No value given for one or more required parameters.

OleDbCommand cmd = new OleDbCommand("Update Table1 Set Name= '" + textBox2.Text + "' where ID= " +textBox2.Text  , conn);
conn.Open();
cmd.ExecuteNonQuery();

I also tried to convert textBox2 into int, but its also given me an error message.

Input string was not in a correct format.

int Id= Convert.ToInt16(textBox2.Text);
OleDbCommand cmd = new OleDbCommand("Update Table1 Set Name= '" + textBox2.Text + "' where ID= " + Id  , conn);
conn.Open();
cmd.ExecuteNonQuery();
jruizaranguren
  • 12,679
  • 7
  • 55
  • 73
Taha Kirmani
  • 101
  • 1
  • 1
  • 9
  • Do you really use the same textbox for name and id ? – Steve Apr 18 '13 at 20:40
  • My Code is now working, but i have one more question, i didn't convert Text Box field into INT, but it is still accepting its value for an Auto Number field. – Taha Kirmani Apr 18 '13 at 20:56
  • You are not enclosing your textbox in single quotes. The engine interpret your query like this. `Update Table1 Set Name= 'Steve' where ID= 1` and this is correct syntax. However I will post a sample on how you really should write your queries – Steve Apr 18 '13 at 21:06

1 Answers1

1

This answer corrects your problem
First, the TextBox for Name is not the same Textbox used for ID
Second, do not concatenate strings to build sql commands. It is very error prone and open to a well know sql vulnerability called Sql Injection

string queryText = Update Table1 Set Name= ? where ID= ?"; 
OleDbCommand cmd = new OleDbCommand(queryText, conn);
cmd.Parameters.AddWithValue("@p1", TextBox1.Text);
cmd.Parameters.AddWithValue("@p2", Convert.ToInt32(TextBox2.Text));
conn.Open();
cmd.ExecuteNonQuery();

Here I have removed the string concatenation and inserted two parameters placeholders (?), then I have added to the OleDbCommand two parameters and their values.
When executing the query the OleDb code will replace the placeholders with the actual values checking for invalid characters and invalid sql statements

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • My Code is now working, but i have one more question, i didn't convert Text Box field into INT, but it is still accepting its value for an Auto Number field. – Taha Kirmani Apr 18 '13 at 20:55