0

Here is the thing I can't use insert query in my code there is an error in my SqlCommand that says the ExecuteNonQuery() not match with the values blah blah

Here is my code

Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;AttachDBFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Finals.mdf;Database=Finals;Trusted_Connection=Yes;")

Dim cmd As New SqlClient.SqlCommand
cmd.Connection = con
cmd.CommandText = "Insert Into [Finals].[dbo].[Nokia]  Values ('" & Unit.Text & "'),('" & Price.Text & " '),('" & Stack.Text & "'),('" & Processor.Text & "'),('" & Size.Text & "'),('" & RAM.Text & "'),('" & Internal.Text & "'),('" & ComboBox1.Text & "')"

con.Open()
cmd.ExecuteNonQuery()
con.Close()

The problem is the cmd.CommandText can anyone please help me?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MaouAion
  • 25
  • 1
  • 5

1 Answers1

4

You need to rewrite your query to use a parameterized query. This would avoid parsing problems if your textboxes contains single quotes and, most important, would remove any possibility of Sql Injection.

So you code could look like this

Dim cmdText = "Insert Into [Finals].[dbo].[Nokia]  Values (@unit, @price,@stack," & _ 
              "@processor,@size,@ram,@internal,@lastvalue"
Using con As New SqlConnection(......)
Using cmd As New SqlCommand(cmdText, con)
   con.Open()
   cmd.Parameters.AddWithValue("@unit",Unit.Text )
   cmd.Parameters.AddWithValue("@price",Price.Text)
   cmd.Parameters.AddWithValue("@stack",Stack.Text)
   cmd.Parameters.AddWithValue("@processor", Processor.Text)
   cmd.Parameters.AddWithValue("@size",Size.Text)
   cmd.Parameters.AddWithValue("@ram", RAM.Text)
   cmd.Parameters.AddWithValue("@internal",Internal.Text)
   cmd.Parameters.AddWithValue("@lastvalue", ComboBox1.Text)
   cmd.ExecuteNonQuery()
End Using
End Using

Said that, be aware of two more problems:

You don't specify a column list before the VALUES statement. This means that you need to pass the exact number of parameters for every column present in your table named Nokia AND in the EXACT ORDER of the underlying columns. If you forget one parameter you will receive an exception and if you swap the order of the parameters you end writing your data in the wrong column (with an exception waiting for you if the datatype doesn't match).

The second problem concerns the datatype of every parameter passed to the query. In your case you use the Text property of the textboxes and this means that you are passing a string for every column in the datatable. Of course, if a column expects a numeric value you get a mismatch error.

For example the @price parameter could be used to update a decimal column in the datatable and thus you need to convert the parameter from string to decimal before adding it using the AddWithValue method

 cmd.Parameters.AddWithValue("@price",Convert.ToDecimal(Price.Text))
Steve
  • 213,761
  • 22
  • 232
  • 286
  • i see my teacher said that i can use that but if this is much better then ill use this – MaouAion Oct 18 '13 at 14:38
  • Show your teacher this http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work – Steve Oct 18 '13 at 15:27
  • @user2882523 I was going to post exactly the same thing, it's always good advice to go parameterized query, I would even take this a step further and say try using a stored procedure rather than inline SQL, generally they are considered the better choice. – Purplegoldfish Oct 18 '13 at 16:04
  • @Purplegoldfish I agree with you about SP, but you should consider also that basing your code on SP for every simple CRUD op could become a maintenance nightmare on big db. Unless you could quickly change your hat from developer to DBA and do the maintenance yourself on the spot. – Steve Oct 18 '13 at 16:19