Question syntax ERROR in the INSERT INTO instruction

medattes

New member
Joined
Sep 6, 2011
Messages
1
Programming Experience
Beginner
Hello,
I am using VB.NET 2008 and I can't create a button. I recieve the message shown in the title !
Here is my code :

dtr = dts.Tables("28071").NewRow
'dtr("datee") = TextBox1.Text
'dtr("index") = TextBox2.Text
'dtr("hmj") = TextBox3.Text
'dtr("hmc") = TextBox4.Text
'dtr("CumulJours") = calcul.TextBox5.Text
'dts.Tables("28071").Rows.Add(dtr)
'cmbd = New OleDbCommandBuilder(dta)
'dta.Update(dts, "28071")
'dts.Clear()
'dta.Fill(dts, "28071")
'dtt = dts.Tables("28071")
'cnx.Close()


or I use this code


cnx = New OleDbConnection("provider = microsoft.jet.oledb.4.0; data source= D:\Nouveau dossier (2)\hm.mdb")
cnx.Open()
cmd.CommandText = "insert into 28071 (datee,index,hmj,hmc,CumulJours) values('" & TextBox1.Text & "'," & TextBox2.Text & "'," & TextBox3.Text & "'," & TextBox4.Text & "'," & TextBox5.Text & "')"
cmd.Connection = cnx
cmd.ExecuteNonQuery()
cnx.Close()

Same problem !
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
Don't use String concatenation to insert variables into SQL code. This is just one of the issues that can create, and it's not the most serious. Always use parameters where possible. Follow the Blog link in my signature and check out my post on ADO.NET Parameters for the why and how.

Just note that the values you assign to the parameters must the correct type, so if you can't use Strings where Dates or numbers are expected. If appropriate, you wil need to convert text values to the appropriate type first. Where possible, avoid that by not using Strings in the first place. For instance, if you want the user to enter a date then you should generally use a DateTimePicker rather than a TextBox.
 

s1ckOh

Well-known member
Joined
Aug 1, 2011
Messages
68
Location
San Diego, Ca
Programming Experience
Beginner
Code:
Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" D:\Nouveau dossier (2)\hm.mdb;")
            Try
                cn.Open()
               Dim dat(2) AsString 
               Dim str AsString
               Dim cmd AsOleDbCommand

                dat(1) = "'" & TextBox1.Text & "'"
                dat(2) = "'" & TextBox2.Text & "'"
                'etc...
                Str = "INSERT INTO [Table] VALUES(" & dat(1) & "," & dat(2) & ")"
                cmd = NewOleDbCommand(Str, cn)
                cmd.ExecuteNonQuery()
                cn.Close()
            Catch
                MsgBox(ErrorToString)
                cn.Close()
            End Try
Hope this helps
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
Code:
Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" D:\Nouveau dossier (2)\hm.mdb;")
            Try
                cn.Open()
               Dim dat(2) AsString 
               Dim str AsString
               Dim cmd AsOleDbCommand

                dat(1) = "'" & TextBox1.Text & "'"
                dat(2) = "'" & TextBox2.Text & "'"
                'etc...
                Str = "INSERT INTO [Table] VALUES(" & dat(1) & "," & dat(2) & ")"
                cmd = NewOleDbCommand(Str, cn)
                cmd.ExecuteNonQuery()
                cn.Close()
            Catch
                MsgBox(ErrorToString)
                cn.Close()
            End Try
Hope this helps
It doesn't really. It solves one issue but leaves a host of others. If you just do it the proper way in the first place, i.e. using parameters, then all the issues are taken care of. We pretty much all start out not using parameters because we don't know they exist. As soon as you do though, there's no justification for not using them.
 

s1ckOh

Well-known member
Joined
Aug 1, 2011
Messages
68
Location
San Diego, Ca
Programming Experience
Beginner
hey there, could you give a small example of using the parameters? you are correct about not knowing about them and just gave an example of what has worked for me but i am very interested in either an example or maybe a link to one so i can improve my style. i know this sounds a little sarcastic, i dont mean it to be.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
hey there, could you give a small example of using the parameters? you are correct about not knowing about them and just gave an example of what has worked for me but i am very interested in either an example or maybe a link to one so i can improve my style. i know this sounds a little sarcastic, i dont mean it to be.
It doesn't sound sarcastic and I wouldn't take it that way, but it does sound like you haven't read my previous post.
jmcilhinney said:
Follow the Blog link in my signature and check out my post on ADO.NET Parameters for the why and how.
 

s1ckOh

Well-known member
Joined
Aug 1, 2011
Messages
68
Location
San Diego, Ca
Programming Experience
Beginner
Thank you for the info, it was a great tutorial and yes, it is much cleaner
 
Top Bottom