Syntax error in INSERT INTO statement

[Contagion]

im getting this error when using the insert into command

code:

Dim atable(4) As dbsettings.Field
Dim idpat, idcons As String
Dim ptable, ctable As New Data.DataTable

atable(0).name = "AdmId"
atable(0).value = TbAdm.Text

findid(idpat, CbPat, "name", "IdCard", "patients", ptable)
atable(1).name = "PatId"
atable(1).value = idpat

atable(2).name = "Date"
atable(2).value = TbDate.Text

atable(3).name = "Ward"
atable(3).value = TbWard.Text

findid(idcons, CbCons, "name", "CId", "consultants", ctable)
atable(4).name = "ConsId"
atable(4).value = idcons

dbs.updatedb(dbs.sqls("Adm_Add", atable, "insert"))


Public Function sqls(ByVal table As String, ByVal datacol() As Field, ByVal sdtype As String) As String
Dim i As Integer
Dim fieldname, fieldvalue As String
Dim sqls1, sqls2 As String

If sdtype = "insert" Then
sqls1 = "INSERT INTO " + (table) + " ("
sqls2 = "VALUES ("
For i = 0 To datacol.Length - 1
If datacol(i).value <> Nothing Then
fieldname = datacol(i).name
fieldvalue = datacol(i).value
sqls1 = sqls1 + fieldname + ", "
sqls2 = sqls2 + "'" + fieldvalue + "', "
End If
Next
sqls1 = sqls1.Remove(sqls1.Length - 2, 2) + ") "
sqls2 = sqls2.Remove(sqls2.Length - 2, 2) + ") "
sqls = sqls1 + sqls2


Public Sub updatedb(ByVal sql As String)
Try
cmd.CommandText = sql
cmd.CommandType = CommandType.Text

cmd.Connection = connection

cmd.ExecuteScalar()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub


This is the coding used regards the matter. The thing is when using it with a different table, everything works fine. I checked all the properties of the current table and it seems ok as I put everything into string format, and all the table properties are of text format. Can you help pls

 

[kulrom]

hmm ... your code is very confusing but i will try to guess
Just change this line and try again:
cmd.ExecuteScalar()
to
cmd.ExecuteNonQuery()

I am not sure will this help but it was that i noticed on the 1st sight

 

[Contagion]

Yeah, i'm sorry. Its my first assigment in vb and I'm a bit green.

I tried that but still it didn't work.

 

[kulrom]

What is the exception you get now?

 

[Contagion]

Same one as the one I posted with the only difference that it states ExecuteNonQuery instead of ExecuteScalar

 

[kulrom]

Well, it could be that you are using some Reserved words on your fields ... i.e. Date ... i guess it's reserved.

 

[kulrom]

if you still want to use reserved words just enclose them in square brackets

 

[Contagion]

Ohh dat was the problem!! How silly!
10x a lot

 

[kulrom]

Actually from the beginning i missed to read this: The thing is when using it with a different table, everything works fine.
I am always checking the threads only partly well, not always ...
Now i am pretty sure that the issue comes by the using Reserved word(s) on your field(s). So, check the fields again and just rename if you find some with reserved words name ... i.e Date, Catalog, Count, Domain etc. avoid their using

 

[kulrom]

Well that's good that you solved the problem ... be more careful next time ... Just kidding wellcome to the forum

 

[Contagion]

Hehe I was starting to panic cause i need to finish the assigment by next week. Just started to learn vb.net, need loads of practice

 

[kulrom]

hey get relax for a while do not panic ... thats why we are here ... to help people before they start panic ... lol
Hey this goes off-topic ... bye and keep practicing