oledb insert error vb .net

[erin.sebastian]

Hi all,
I have a really silly problem that i can't find the answer too. I am
working with VB.NET and i am trying to insert a new record into my
access database (pretty easy right?) well i am getting a syntax error
in my SQL statement and nothing i do will correct it. It looks FINE to
me, can anyone help me out??
Thanks so much in advance, here is the code i am using

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim strSQL As String
Dim icount As Integer

Try
cn = New OleDbConnection(constants.CONNECTIONSTRING)
'provider to be used when working with access database
cn.Open()

cmd = New OleDbCommand
cmd.Connection = cn

cmd.CommandText = "INSERT into original
values(?Name,?Processor,?Ram,?HDD,?Monitor,?Device,?Office,?OS,?Serial)"

cmd.Parameters.Add("?Name", txtName.Text)
cmd.Parameters.Add("?Processor", txtProc.Text)
cmd.Parameters.Add("?Ram", Me.txtRAM.Text)
cmd.Parameters.Add("?HDD", Me.txthdd.Text)
cmd.Parameters.Add("?Monitor", Me.txtMonitor.Text)
cmd.Parameters.Add("?Device", Me.txtDevice.Text)
cmd.Parameters.Add("?Office", Me.txtOffice.Text)
cmd.Parameters.Add("?OS", Me.txtOS.Text)
cmd.Parameters.Add("?Serial", Me.txtserial.Text)


icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally

cn.Close()
End Try

 

[W.G. Ryan - MVP]

I don't think your named parameters are working here and I think the ? Name
is giving you the trouble. Try removing the names after the ? marks and
when you add the parameters, just add them in the order which they appear in
the statement. If you have a column called Name though,
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335, it's a
reserved word and the OleDb provider doesn't handle it well, even though
within access you won't encounter any problems. You can use [] around the
column names but since you're not specifying individual columns that
probably won't help since there's no place to reference it. Also, if you
are using Name, you probably want to get rid of it, even if you escape it,
someone else will probably forget it in the future and you'll run through
this again.

HTH,

Bill

 

[erin.sebastian]

Hello,
Thanks SO MUCH for your prompt reply. I've made the changes you
suggested however i still get the error. I have no idea what's going
wrong. This is what the code looks like with the changes made. Do you
have any more suggestions?
Thanks so much!!

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim strSQL As String
Dim icount As Integer

Try
cn = New OleDbConnection(constants.CONNECTIONSTRING)
'provider to be used when working with access database
cn.Open()

strSQL = "insert into
original(Operator,Processor,RAM,hdd,Monitor,Device,Office,O/S,serial)
values('" & txtName.Text & "'," & System.Convert.ToInt32(txtProc.Text)
& "," & System.Convert.ToInt32(txtRAM.Text) & "," &
System.Convert.ToInt32(txthdd.Text) & ",'" & txtMonitor.Text & "','" &
txtDevice.Text & "','" & txtOffice.Text & "','" & txtOS.Text & "','" &
txtserial.Text & "');"

cmd = New OleDbCommand
cmd.Connection = cn

cmd.CommandText = strSQL

icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally

cn.Close()
End Try

 

[m.posseth]

why don`t you just do it like this ?


Copied and pasted from one of my projects

Dim sql As String = "INSERT INTO ARTPIC ( ArtMaId, FabId, BildName,
DokumentenArt, SortNr ) " _

& "VALUES (" & artmaid & "," & fabid & ",'" & bildname & "'," & docart & ","
& sortnr & " ) ;"

cdata.fExecuteScalar(sql)



in my data class



Friend Function fExecuteScalar(ByVal SQL As String) As String

Try

If _objCon.State <> ConnectionState.Open Then _objCon.Open()

Dim cmd As New OleDbCommand(SQL, _objCon)

fExecuteScalar = CStr(cmd.ExecuteScalar)

Catch

Return ""

Finally

If _objCon.State <> ConnectionState.Closed Then _objCon.Close()

End Try

End Function





a lot less code and works guaranteed



Michel Posseth

I don't think your named parameters are working here and I think the ? Name
is giving you the trouble. Try removing the names after the ? marks and
when you add the parameters, just add them in the order which they appear
in the statement. If you have a column called Name though,
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335, it's a
reserved word and the OleDb provider doesn't handle it well, even though
within access you won't encounter any problems. You can use [] around the
column names but since you're not specifying individual columns that
probably won't help since there's no place to reference it. Also, if you
are using Name, you probably want to get rid of it, even if you escape it,
someone else will probably forget it in the future and you'll run through
this again.

HTH,

Bill

Hi all,
I have a really silly problem that i can't find the answer too. I am
working with VB.NET and i am trying to insert a new record into my
access database (pretty easy right?) well i am getting a syntax error
in my SQL statement and nothing i do will correct it. It looks FINE to
me, can anyone help me out??
Thanks so much in advance, here is the code i am using

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim strSQL As String
Dim icount As Integer

Try
cn = New OleDbConnection(constants.CONNECTIONSTRING)
'provider to be used when working with access database
cn.Open()

cmd = New OleDbCommand
cmd.Connection = cn

cmd.CommandText = "INSERT into original
values(?Name,?Processor,?Ram,?HDD,?Monitor,?Device,?Office,?OS,?Serial)"

cmd.Parameters.Add("?Name", txtName.Text)
cmd.Parameters.Add("?Processor", txtProc.Text)
cmd.Parameters.Add("?Ram", Me.txtRAM.Text)
cmd.Parameters.Add("?HDD", Me.txthdd.Text)
cmd.Parameters.Add("?Monitor", Me.txtMonitor.Text)
cmd.Parameters.Add("?Device", Me.txtDevice.Text)
cmd.Parameters.Add("?Office", Me.txtOffice.Text)
cmd.Parameters.Add("?OS", Me.txtOS.Text)
cmd.Parameters.Add("?Serial", Me.txtserial.Text)


icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally

cn.Close()
End Try

 

[Cor Ligthert [MVP]]

Erin,

What is the error, because whithout the error it is hard to say what it is.
I once had with OleDb and an access databate the error that there was
missing a parameter. By adding a dummy one at the end I was ready. Not the
answer on the problem, however it did work.

Therefore what is the error that is showed.

Cor

 

[m.posseth]

Hello Erin


add a debug.writeline( strSQL ) to your code now run it in debug and check
the sql string for errors if it is allright it should work in access , so
open access select new query open design view , select sql
( in the left corner ) paste the sql from your program in the sql window
and see what access tells you , if it isn`t obvious paste the SQL string in
this newsgroup so we can have a look at it ( it might be a missing space or
a single quote ' in the string to insert that messes things up


hth

Michel Posseth [MCP]

 

[erin.sebastian]

Hi All,
Thanks for all of the many replys.
I did as Michel asked and pasted my sql statement into access all the
error says is "Syntax error in INSERT INTO statement" Here is the sql
statement i use

insert into
original(Operator,Processor,RAM,hdd,Monitor,Device,Office,O/S,serial)
values('test',1400,512,60,'test','test','test','test','test');

I will continue trying to edit this until it works, if anyone else has
any idea's please let me know.
YOu've all been SO HELPFUL!!! thanks so much!
Erin

 

[m.posseth]

it is the / in O/S

remove it from the column name and change it in the sql and it will work

regards

Michel Posseth [MCP]

----- Original Message -----
From: <[email protected]>
Newsgroups: microsoft.public.dotnet.languages.vb
Sent: Monday, October 24, 2005 7:41 PM
Subject: Re: oledb insert error vb .net

 

[erin.sebastian]

Thanks Michel,
That did it!!!!! so silly, i should have known!!
Erin