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 !
 
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.
 
VB.NET:
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
 
VB.NET:
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.
 
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.
 
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.
 
Back
Top