oledb insert error vb .net

[erin.sebastian@cowaninsurancegroup.com]

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.CONN ECTIONSTRING)
'provider to be used when working with access database
cn.Open()

cmd = New OleDbCommand
cmd.Connection = cn

cmd.CommandText = "INSERT into original
values(?Name,?P rocessor,?Ram,? HDD,?Monitor,?D evice,?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.T ext)
cmd.Parameters. Add("?Device", Me.txtDevice.Te xt)
cmd.Parameters. Add("?Office", Me.txtOffice.Te xt)
cmd.Parameters. Add("?OS", Me.txtOS.Text)
cmd.Parameters. Add("?Serial", Me.txtserial.Te xt)


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

cn.Close()
End Try

[W.G. Ryan - MVP]

Re: oledb insert error vb .net

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...b;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 @cowaninsurance group.com> wrote in message
news:1130172493 .110620.130290@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> 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.CONN ECTIONSTRING)
> 'provider to be used when working with access database
> cn.Open()
>
> cmd = New OleDbCommand
> cmd.Connection = cn
>
> cmd.CommandText = "INSERT into original
> values(?Name,?P rocessor,?Ram,? HDD,?Monitor,?D evice,?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.T ext)
> cmd.Parameters. Add("?Device", Me.txtDevice.Te xt)
> cmd.Parameters. Add("?Office", Me.txtOffice.Te xt)
> cmd.Parameters. Add("?OS", Me.txtOS.Text)
> cmd.Parameters. Add("?Serial", Me.txtserial.Te xt)
>
>
> icount = cmd.ExecuteNonQ uery
> MessageBox.Show (icount)
> Catch ex As Exception
> MessageBox.Show (ex.Message)
> Finally
>
> cn.Close()
> End Try
>[/color]

[erin.sebastian@cowaninsurancegroup.com]

Re: oledb insert error vb .net

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.CONN ECTIONSTRING)
'provider to be used when working with access database
cn.Open()

strSQL = "insert into
original(Operat or,Processor,RA M,hdd,Monitor,D evice,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.ExecuteNonQ uery
MessageBox.Show (icount)
Catch ex As Exception
MessageBox.Show (ex.Message)
Finally

cn.Close()
End Try

[m.posseth]

Re: oledb insert error vb .net

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.fExecuteS calar(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(SQ L, _objCon)

fExecuteScalar = CStr(cmd.Execut eScalar)

Catch

Return ""

Finally

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

End Try

End Function





a lot less code and works guaranteed :-)



Michel Posseth








"W.G. Ryan - MVP" <WilliamRyan@no spam.gmail.com> wrote in message
news:edK2NuL2FH A.2008@TK2MSFTN GP10.phx.gbl...[color=blue]
>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...b;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 @cowaninsurance group.com> wrote in message
> news:1130172493 .110620.130290@ g14g2000cwa.goo glegroups.com.. .[color=green]
>> 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.CONN ECTIONSTRING)
>> 'provider to be used when working with access database
>> cn.Open()
>>
>> cmd = New OleDbCommand
>> cmd.Connection = cn
>>
>> cmd.CommandText = "INSERT into original
>> values(?Name,?P rocessor,?Ram,? HDD,?Monitor,?D evice,?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.T ext)
>> cmd.Parameters. Add("?Device", Me.txtDevice.Te xt)
>> cmd.Parameters. Add("?Office", Me.txtOffice.Te xt)
>> cmd.Parameters. Add("?OS", Me.txtOS.Text)
>> cmd.Parameters. Add("?Serial", Me.txtserial.Te xt)
>>
>>
>> icount = cmd.ExecuteNonQ uery
>> MessageBox.Show (icount)
>> Catch ex As Exception
>> MessageBox.Show (ex.Message)
>> Finally
>>
>> cn.Close()
>> End Try
>>[/color]
>
>[/color]

[Cor Ligthert [MVP]]

Re: oledb insert error vb .net

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]

Re: oledb insert error vb .net

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 @cowaninsurance group.com> wrote in message
news:1130173671 .119702.291340@ g47g2000cwa.goo glegroups.com.. .[color=blue]
> 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.CONN ECTIONSTRING)
> 'provider to be used when working with access database
> cn.Open()
>
> strSQL = "insert into
> original(Operat or,Processor,RA M,hdd,Monitor,D evice,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.ExecuteNonQ uery
> MessageBox.Show (icount)
> Catch ex As Exception
> MessageBox.Show (ex.Message)
> Finally
>
> cn.Close()
> End Try
>[/color]

[erin.sebastian@cowaninsurancegroup.com]

Re: oledb insert error vb .net

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(Operat or,Processor,RA M,hdd,Monitor,D evice,Office,O/S,serial)
values('test',1 400,512,60,'tes t','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]

Re: oledb insert error vb .net

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: <erin.sebastian @cowaninsurance group.com>
Newsgroups: microsoft.publi c.dotnet.langua ges.vb
Sent: Monday, October 24, 2005 7:41 PM
Subject: Re: oledb insert error vb .net

[color=blue]
> 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(Operat or,Processor,RA M,hdd,Monitor,D evice,Office,O/S,serial)
> values('test',1 400,512,60,'tes t','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
>[/color]

[erin.sebastian@cowaninsurancegroup.com]

Re: oledb insert error vb .net

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