how to write insert query in c#?

Question

I am working on a c# web application...I want to insert data from textbox to database ....I have written the query but it's not working:

string insertquery = 
 @"INSERT INTO [LocationInfo]([LocationIP], [LocationName]) 
 VALUES ('" + TextBox2.Text + "','" + TextBox3.Text + "') 
 WHERE LocationID=null";
SqlCommand cmd = new SqlCommand(insertquery, sqlcon_QOEMetrices);
cmd.ExecuteNonQuery();

sqlcon_QOEMetrices is my database connection object...

Please tell me if there is any syntax error or any statement missing.........

• Baz1nga: hello sql injection!!
• Jon Skeet: First problem: your code is vulnerable to SQL injection attacks. Don't include the values directly into your SQL. Use a parameterized SQL statement instead. Second problem: "it's not working" is a lousy description of what's happening. Tell us what's happening. If there's an exception, give us the full details. If it doesn't compile, give us the compile-time error. If it's inserting the wrong values, tell us about that.
• cichy: Remove "Where LocationID=null" from query
• gout: use sqlparam to insert values into tables in order to survive sql injection
• Mr Lister: Also, "it's not working", is that the error you get?

Answer 1

you can write query like following command

SqlCommand comm = new SqlCommand("INSERT INTO desg VALUES (@txtsno, @txtdesg, @txtbasic)", connection);
your_db.Open();
try {
 comm.Parameters.AddWithValue("@txtsno", txtsno.Text.Trim());
 comm.Parameters.AddWithValue("@txtdesg", txtdesg.Text.Trim());
 comm.Parameters.AddWithValue("@txtbasic", txtbasic.Text.Trim());
 comm.ExecuteNonQuery();
 comm.Dispose();
 comm = null;
}
catch(Exception ex)
{
 throw new Exception(ex.ToString(), ex);
}
finally
{
 your_db.Close();
}

• pero: 1. name your columns after "INSERT INTO desg" if somebody reorder your table columns you will be inserting into wrong columns 2. use using for opening a SqlConnection (you can then remove finally part), 3. remove your catch because it doesnt add any value

Answer 2

You should NOT concatenate SQL query with direct values from forms.

Your ADO.NET code can look like this:

 string query = "INSERT INTO [LocationInfo]([LocationIP], [LocationName])" +
 "VALUES (@locIP, @locName)";
 SqlCommand cmd = new SqlCommand(query, sqlcon_QOEMetrices);
 cmd.Parameters.AddWithValue("@locIP", TextBox2.Text);
 cmd.Parameters.AddWithValue("@locName", TextBox3.Text); 
 try
 {
 sqlcon_QOEMetrices.Open();
 cmd.ExecuteNonQuery();
 }
 catch (Exception)
 {
 throw;
 }
 finally
 {
 sqlcon_QOEMetrices.Close();
 }

• swati sharma: i want to count no of users corresponding to location name, i am writing this query : string q = "SELECT COUNT(DISTINCT [UserURI]) FROM [UserLocation] WHERE LocationName='Powai'"; SqlCommand cmd1 = new SqlCommand(q,sqlcon_QOEMetrices); TextBox6.Text = cmd1.ExecuteScalar().ToString(); but its not working even not showing any error
• Gondy: First of all, have you already tried to execute query? If it's not showing any error it is most likely that your query return no result. You can connect to your database in Visual Studio and use Query Builder [aquafold.com/images/screenshots/… screenshot) to build and test your query. You will see if query works or not.
• Gondy: But, your code does not work because you forget to open connection. ( sqlcon_QOEMetrices.Open(); ) However, this definitely should throw an exception.

Answer 3

omit

 WHERE LocationID=null

And prefer SQLCommand and Parameters. Not build strings with UI control values.

Answer 4

use LocationID IS NULL instead of LocationID = NULL and yes. your query is prone to SQL injection attacks as other people mentioned.