Syntax Error in insert into statement

[yousuf42]

Dear All,

I have a problem with following code . Insert into statement syntax error message appears whenever insert a new record. Following is the code.

Private Sub cmdAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddNew.Click
'we will need to create a stream from which to save this image
'we saving the image to the DB from memory and no longer from the file.
'the reason we do this is (could have lost link e.g. Floppy drive)
Dim str As New MemoryStream
'save the image to the stream in .Jpeg format
pic.Image.Save(str, Imaging.ImageFormat.Jpeg)

'inside the database the image is represented in binary format
'create the binary buffer to receive the image
Dim buffer(CInt(str.Length - 1)) As Byte 'I use option strict so I have to convert to an integer

'be sure to reset the buffer's position other wise you will get an error on readin the image
str.Position = 0
'read the contents of the memory stream into the buffer
str.Read(buffer, 0, CInt(str.Length))
'we are now ready to place it in the database

'create a connection to the images database in the root folder or our machine
Dim con As New OleDbConnection(conString)
'create a command object based on this command
Dim cmd As New OleDbCommand("INSERT INTO Pictures (picID,ThePicture,Description,PartNo) VALUES ('" & Me.txtPicID.Text & "',@ThePicture,'" & Me.txtDescription.Text & "','" & Me.txtPartNo.Text & "'", con)
cmd.CommandType = CommandType.Text

'to make my database code easier to maintain and less buggy I use parameters instead of creating the query inline
Dim prmPic As New OleDbParameter 'done step by step here
With prmPic
.ParameterName = "@MyPicture" 'the name used in the query for the parameter
.OleDbType = OleDbType.Binary 'set the database type
.Value = buffer 'assign the contents of the buffer to the value of the parameter
End With
cmd.Parameters.Add(prmPic) 'add the parameter to the command

Try
'open the connection
con.Open()
'execute the command
cmd.ExecuteNonQuery()

'report success and clear the image to allow the user to select another image
MessageBox.Show("The image was saved successfully.")
pic.Image = Nothing
Catch ex As Exception
MessageBox.Show("There was a problem saving the image." & ControlChars.CrLf & ex.Message)
Finally
'close the connection and free the memory we were using
If con.State = ConnectionState.Open Then con.Close()
con.Dispose() : cmd.Dispose()
con = Nothing : cmd = Nothing : prmPic = Nothing
End Try
End Sub
End Class


Can anyone help please?

Thanks in advance

 

[vis781]

As a general rule syntax errors will occur of you have incorrectly spelled the name of a field, however it can also happen if you are trying to use a reserved word, though i can see any evidence of that here. I would check your spellng. In addition i would set up parameters for all your entry fields, it makes it easier to debug

 

[yousuf42]

Thanks for giving your valuable time.

But I don't have idea, how to assign multiple parameters and execute it. Do you a sample code please.

Thanks in Advance

 

[vis781]

Dim cmd As New OleDbCommand("INSERT INTO Pictures (picID,ThePicture,Description,PartNo) VALUES (?, ?, ?, ?)", con)
cmd.parameters.add("@txtpicId",oledbtype.varwchar,255,"PicId").value = txtpicid.text

Add the rest of the parameters according to the datatype being sure to add the original field (column) name in the final argument, and in the order that they appear in the query.

 

[yousuf42]

Dim cmd As New OleDbCommand("INSERT INTO Pictures (picID,ThePicture,Description,PartNo) VALUES (?, ?, ?, ?)", con)
cmd.parameters.add("@txtpicId",oledbtype.varwchar,255,"PicId").value = txtpicid.text

Add the rest of the parameters according to the datatype being sure to add the original field (column) name in the final argument, and in the order that they appear in the query.

Thanks a Lot !.. It is working now

 

[yousuf42]

Thanks a Lot for giving your time !.. It is working now