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
8  5399 
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
<er************ @cowaninsurance group.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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
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
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" <Wi*********@no spam.gmail.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.phx.gbl... 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
<er************ @cowaninsurance group.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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
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
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]
<er************ @cowaninsurance group.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. . 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
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
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: <er************ @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 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
Thanks Michel,
That did it!!!!! so silly, i should have known!!
Erin
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: MP |
last post by:
ok - I have no idea what's doing on. In a nutshell I am doing a SQL
insert in a Access database from PHP but am getting an error.
Here is the code:
// PHP db connection
$db = 'C:\\Phpweb\\govDB.mdb';
$conn = new COM('ADODB.Connection');
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db");
|
by: Pravin A. Sable |
last post by:
Dear All
I am trying to execute following cod
Sub SubmitBtn_Click(ByVal Sender As Object, ByVal E As EventArgs
Dim DBConn As OleDbConnectio
Dim DBInsert As New OleDbComman
DBConn = New OleDbConnection("PROVIDER="
& "Microsoft.Jet.OLEDB.4.0;"
& "DATA SOURCE="
|
by: INSERT ERROR |
last post by:
I am trying to INSERT record in MYSQL database, but each time the code tries
to execute the command: MySQL_cmdGLAccount.ExecuteNonQuery(), it reports an
error with the message
"COLUMN COUNT DOESN'T MATCH VALUE COUNT AT ROW 1"
Am using MYSQL CONNECTOR FOR NET VERY 1.0.3 as my provider. Am wondering
what is going on in my code. Below is part of the code.
MySQL_Cnn.Open()
myTrans = MySQL_Cnn.BeginTransaction
MySQL_cmdGLAccount.Transaction...
|
by: INSERT ERROR |
last post by:
I am trying to INSERT record in MYSQL database, but each time the code tries
to execute the command: MySQL_cmdGLAccount.ExecuteNonQuery(), it reports an
error with the message
"COLUMN COUNT DOESN'T MATCH VALUE COUNT AT ROW 1"
Am using MYSQL CONNECTOR FOR NET VERY 1.0.3 as my provider. Am wondering
what is going on in my code. Below is part of the code.
MySQL_Cnn.Open()
myTrans = MySQL_Cnn.BeginTransaction
MySQL_cmdGLAccount.Transaction...
|
by: Bonzol |
last post by:
Can someone tell me why I get this error
"An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll"
when i do this
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
| | |
by: Greg Corradini |
last post by:
Hello,
I'm trying to perform a simple insert statement into a table called
Parcel_Test (see code below). Yet, I get an error message that I've never
seen before (see traceback below). I've tried to put a semicolon at the end
of the sql statement, but with no luck. Any ideas from more experienced
mx.ODBC users?
CODE
TRACEBACK
|
by: acphiri |
last post by:
Hello there,
I am please requesting for urgent help.
My program is packaged and installed on a pc.I used some data grids and reports on some forms.The problem is that they can only open on the PC I install the package( where I also test the database connections in the data environment).Whenever I install it on another computer and I try to call the forms with the data grids or reports, a message pops up that reads something like "JET OLEDB...
|
by: Greg Corradini |
last post by:
Hello,
I've never gotten this traceback error before using mx.ODBC. Any ideas about
resolving this issue? The statement and the error it generates are listed
below.
curse.execute("Insert into FHWA_StandSamp_2008(LRS_ID_NEW)
values('0402000010') where LRS_ID = '0403700010'")
Traceback (most recent call last):
File "<pyshell#37>", line 1, in ?
|
by: fbartolom |
last post by:
Hullo,
I have a quite simple piece of code that returns a syntax error in the query execution: Have you got any idea about what might be wrong?
public void SQLCommandWithParam(string name, byte image)
{
OleDbCommand sqlCommand1 = new OleDbCommand();
System.Data.OleDb.OleDbConnection myOleConnection = new System.Data.OleDb.OleDbConnection();
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
