Question Syntax serror? (insert)

[black zero]

cmd4 = New SqlCommand("insert into kelancaran " & _
            "(no_transaction, transaction_date, ID_sopir, Nama_sopir, Shift, ATM_rp, " & _
            " Kantor_rp, Dropping_rp, nama_details) values('" & labeltransaction.Text & "'," & _
            " '" & datetimepckr.Text & "' , '" & txtIdSopir.Text & "' , '" & ComboShift.Text & "'," & _
            " '" & textFromATM.Text & "' , '" & textFromKantor.Text & "'" & _
            " '" & TextDropping.Text & "','" & CmbNamaDetails.Text & "')", con)

When I execute that code, my try catch trap captures this 'incorrect syntax near "'

 

[InertiaM]

1. Look at cjard's excellent thread about parameterised queries - it will help you dramatically.

2. Rewrite your insert query

3. Appreciating that the above might be slightly long-winded, change

textFromKantor.Text & "'"

to

textFromKantor.Text & "', "

 

[black zero]

And it's hardly surprising, because though it might work, after you spend ages debugging which comma is in the wrong place.. It looks truly awful, doesnt it?

Sorry for that...

Thanks InertiaM, really appreciate it. Btw, how did you sort out my mistake? If you don't mind, please tell me your method debugging my code, so I can improve. Thank you.

 

[InertiaM]

Before I start this, please note - I am not advocating writing SQL queries in the archaic way. You should still change them over to parameterised queries (PQs).

The following are only my opinions - everyone has their own way of doing it, and this used to be mine until cjard converted me to PQs

Dont build your SQL within the SQLCommand line - build it beforehand. Separate the SQL into readable parts, and it will make it far easier to debug. It will also make it far easier to modify it into a PQ. Use uppercase for reserved keywords - they show up better.

  dim sSQL as string = ""
  sSQL &= " INSERT INTO"
  sSQL &= "   kelancaran"
  sSQL &= "   ("
  sSQL &= "     no_transaction,"
  sSQL &= "     transaction_date,"
  sSQL &= "     ID_sopir,"
  sSQL &= "     Nama_sopir,"
  sSQL &= "     Shift,"
  sSQL &= "     ATM_rp,"
  sSQL &= "     Kantor_rp,"
  sSQL &= "     Dropping_rp,"
  sSQL &= "     nama_details"
  sSQL &= "   )"
  sSQL &= "   VALUES"
  sSQL &= "   ("
  sSQL &= "   )"

Now copy the field name lines and paste them inside the commas for the value section. Comment out the field name and add some dummy value to be replaced later. The code should now look something like this :-

  dim sSQL as string = ""
  sSQL &= " INSERT INTO"
  sSQL &= "   kelancaran"
  sSQL &= "   ("
  sSQL &= "     no_transaction,"
  sSQL &= "     transaction_date,"
  sSQL &= "     ID_sopir,"
  sSQL &= "     Nama_sopir,"
  sSQL &= "     Shift,"
  sSQL &= "     ATM_rp,"
  sSQL &= "     Kantor_rp,"
  sSQL &= "     Dropping_rp,"
  sSQL &= "     nama_details"
  sSQL &= "   )"
  sSQL &= "   VALUES"
  sSQL &= "   ("
  sSQL &= "     '" & 0 & "', "  'no_transaction
  sSQL &= "     '" & 0 & "', "  'transaction_date
  sSQL &= "     '" & 0 & "', "  'ID_sopir
  sSQL &= "     '" & 0 & "', "  'Nama_sopir
  sSQL &= "     '" & 0 & "', "  'Shift
  sSQL &= "     '" & 0 & "', "  'ATM_rp
  sSQL &= "     '" & 0 & "', "  'Kantor_rp
  sSQL &= "     '" & 0 & "', "  'Dropping_rp
  sSQL &= "     '" & 0 & "'"  'nama_details
  sSQL &= "   )"

You then replace the 0 with where you obtain the data from.

I'll let you figure out the rest - you'll see why

 

[black zero]

Wheeeeee...

That's clever indeed. Thank you veryyyy much!

PS: I still don't understand anything about parameterised queries.... I haven't had any lesson regarding that before this.

 

[cjard]

PS: I still don't understand anything about parameterised queries.... I haven't had any lesson regarding that before this.

Yes you do, because you program in VB and assign values to variables. Ergo you know what a variable is.

Now if I tell you that this SQL:

UPDATE table SET col1 = @value1, col2 = @value1 WHERE col2 = @value2


Has 2 variables:
@value1
@value2


And you can assign values to those variables, you can read and understand that it's not much different to:

Dim x1 as String
Dim x2 as String


i.e. both codes have 2 variables that can take values


As you can understand, you wouldnt hard code every value in your VB, and then rewrite the source code with new values and recompile it every time you make a change to a value.. But that is what your bad sql up there is doing!

Now you should be able to relate PQs to what you know, have another go at reading the PQ link

 

[cjard]

Before I start this, please note - I am not advocating writing SQL queries in the archaic way. You should still change them over to parameterised queries (PQs).

Most important part of IM's post


Second most important part:
Presentation is vital. Messy code is so hard to read, your chances of spotting bugs are (as you have found) very low



When building strings, doing this is neater:

  dim sSQL as string = ""
  sSQL &= " INSERT INTO"
  sSQL &= "   kelancaran"
  sSQL &= "   ("
  sSQL &= "     no_transaction,"
  sSQL &= "     transaction_date,"
  sSQL &= "     ID_sopir,"
  sSQL &= "     Nama_sopir,"
  sSQL &= "     Shift,"
  sSQL &= "     ATM_rp,"
  sSQL &= "     Kantor_rp,"
  sSQL &= "     Dropping_rp,"
  sSQL &= "     nama_details"
  sSQL &= "   )"
  sSQL &= "   VALUES"
  sSQL &= "   ("
  sSQL &= "     '{0}', "  'no_transaction
  sSQL &= "     '{1}', "  'transaction_date
  sSQL &= "     '{2}', "  'ID_sopir
  sSQL &= "     '{3}', "  'Nama_sopir
  sSQL &= "     '{4}', "  'Shift
  sSQL &= "     '{5}', "  'ATM_rp
  sSQL &= "     '{6}', "  'Kantor_rp
  sSQL &= "     '{7}', "  'Dropping_rp
  sSQL &= "     '{8}'"  'nama_details
  sSQL &= "   )"
  sSQL = String.Format(sSQL, <9 values>)

NOTE. THIS IS NOT PARAMETERIZATION OF A QUERY. This query will still be recompiled by the database every time you alter it

Just to be absolutely 100% clear: THIS IS NOT PARAMETERIZATION OF A QUERY.


This is the parameterized version:

  dim sSQL as string = ""
  sSQL &= " INSERT INTO"
  sSQL &= "   kelancaran"
  sSQL &= "   ("
  sSQL &= "     no_transaction,"
  sSQL &= "     transaction_date,"
  sSQL &= "     ID_sopir,"
  sSQL &= "     Nama_sopir,"
  sSQL &= "     Shift,"
  sSQL &= "     ATM_rp,"
  sSQL &= "     Kantor_rp,"
  sSQL &= "     Dropping_rp,"
  sSQL &= "     nama_details"
  sSQL &= "   )"
  sSQL &= "   VALUES"
  sSQL &= "   ("
  sSQL &= "     @no_transaction,"
  sSQL &= "     @transaction_date,"
  sSQL &= "     @ID_sopir,"
  sSQL &= "     @Nama_sopir,"
  sSQL &= "     @Shift,"
  sSQL &= "     @ATM_rp,"
  sSQL &= "     @Kantor_rp,"
  sSQL &= "     @Dropping_rp,"
  sSQL &= "     @nama_details"
  sSQL &= "   )"
  
  myCmd As SqlCommand(sSQL)
  myCmd.Parameters.Add("@no_transaction", SqlType.VarChar)
  myCmd.Parameters.Add("@transaction_date", SqlType.DateTime)
  ..blah..

It's boring writing these SQLs isnt it? You can get Visual Studio to write them for you (and better) by using the DataSet generator. FOr info on how it is used, see the DW2 link in my signature, section Creating a Simple Data App (involves use of the dataset designer)

 

[black zero]

I'm gonna overhaul my coding once this project completed.

Thank you for your wonderful help and insightful post, all.