|
 |
aspx_beginners thread: Error on Update of an Access database with a DataSet
Message #1 by "Roger Finks" <rfinks01@e...> on Fri, 14 Mar 2003 03:18:31
|
|
I'm getting an error message "System.Data.OleDb.OleDbException: Syntax
error in INSERT INTO statement on an Update of my Access database with a
DataSet object. Could someone please tell me how to get a more definitive
error message so I can tell what's going on. I am running in debut mode.
Also if you happen to see the problem I'd be extremely happy.
Code preceeding and leading upto the Update:
' Construct Insert SQL command
Dim objCommand As New OleDbCommand
objCommand.Connection = objConnection
strSQL = "INSERT INTO Track "
strSQL += "(CDKey, TrackNumber, Composition, "
strSQL += "Description, Time) "
strSQL += "VALUES (?, ?, ?, ?, ?)"
objCommand.CommandText = strSQL
objDataAdapter.InsertCommand = objCommand
Dim param As OleDbParameter
param = objDataAdapter.InsertCommand.Parameters.Add
("@CDKey", OleDbType.BigInt, 16, "CDKey")
param.SourceColumn = "CDKey"
param = objDataAdapter.InsertCommand.Parameters.Add
("@TrackNumber", OleDbType.Integer, 8, "TrackNumber")
param.SourceColumn = "TrackNumber"
param = objDataAdapter.InsertCommand.Parameters.Add
("@Composition", OleDbType.VarWChar, 50, "Composition")
param.SourceColumn = "Composition"
param = objDataAdapter.InsertCommand.Parameters.Add
("@Description", OleDbType.VarWChar, 50, "Description")
param.SourceColumn = "Description"
param = objDataAdapter.InsertCommand.Parameters.Add
("@Time", OleDbType.DBTimeStamp, 32, "Time")
param.SourceColumn = "Time"
objDataAdapter.Update(DataSet1, "Track")
Access Table layout:
CDKey Long Integer
TrackNumber Integer
Composition Text(50)
Description Text(50)
Time Date/Time (Short Time)
Function LoadDataSet() As DataSet
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
strConnection += "Data
Source=D:\Roger\DotNetProjects\CDCatalog\CDCatalog.mdb"
objConnection = New OledbConnection(strConnection)
strSQL = "SELECT Track.CDKey, Track.TrackNumber, Track.Composition,
Track.Description, Track.Time"
strSQL += " FROM Track WHERE Track.CDKey = " & CLng(Request.QueryString
("txtCDKey"))
strSQL += " ORDER BY TrackNumber;"
objDataAdapter = New OledbDataAdapter(strSQL, objConnection)
objDataAdapter.Fill(DataSet1, "Track")
Return DataSet1
End Function
Message #2 by "Jerry Lanphear" <jerrylan@q...> on Thu, 13 Mar 2003 20:57:59 -0700
|
|
Roger,
This looks good, except after you set up the INSERT command you need to use
your objCommand Command object and an ExecuteNonQuery.
Regards
The DataAdapter can be used
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 3:18 AM
Subject: [aspx_beginners] Error on Update of an Access database with a
DataSet
> I'm getting an error message "System.Data.OleDb.OleDbException: Syntax
> error in INSERT INTO statement on an Update of my Access database with a
> DataSet object. Could someone please tell me how to get a more definitive
> error message so I can tell what's going on. I am running in debut mode.
> Also if you happen to see the problem I'd be extremely happy.
>
> Code preceeding and leading upto the Update:
>
> ' Construct Insert SQL command
> Dim objCommand As New OleDbCommand
> objCommand.Connection = objConnection
> strSQL = "INSERT INTO Track "
> strSQL += "(CDKey, TrackNumber, Composition, "
> strSQL += "Description, Time) "
> strSQL += "VALUES (?, ?, ?, ?, ?)"
> objCommand.CommandText = strSQL
> objDataAdapter.InsertCommand = objCommand
>
> Dim param As OleDbParameter
> param = objDataAdapter.InsertCommand.Parameters.Add
> ("@CDKey", OleDbType.BigInt, 16, "CDKey")
> param.SourceColumn = "CDKey"
> param = objDataAdapter.InsertCommand.Parameters.Add
> ("@TrackNumber", OleDbType.Integer, 8, "TrackNumber")
> param.SourceColumn = "TrackNumber"
> param = objDataAdapter.InsertCommand.Parameters.Add
> ("@Composition", OleDbType.VarWChar, 50, "Composition")
> param.SourceColumn = "Composition"
> param = objDataAdapter.InsertCommand.Parameters.Add
> ("@Description", OleDbType.VarWChar, 50, "Description")
> param.SourceColumn = "Description"
> param = objDataAdapter.InsertCommand.Parameters.Add
> ("@Time", OleDbType.DBTimeStamp, 32, "Time")
> param.SourceColumn = "Time"
>
> objDataAdapter.Update(DataSet1, "Track")
>
> Access Table layout:
> CDKey Long Integer
> TrackNumber Integer
> Composition Text(50)
> Description Text(50)
> Time Date/Time (Short Time)
>
> Function LoadDataSet() As DataSet
>
> strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
> strConnection += "Data
> Source=D:\Roger\DotNetProjects\CDCatalog\CDCatalog.mdb"
>
> objConnection = New OledbConnection(strConnection)
> strSQL = "SELECT Track.CDKey, Track.TrackNumber, Track.Composition,
> Track.Description, Track.Time"
> strSQL += " FROM Track WHERE Track.CDKey = " & CLng(Request.QueryString
> ("txtCDKey"))
> strSQL += " ORDER BY TrackNumber;"
> objDataAdapter = New OledbDataAdapter(strSQL, objConnection)
> objDataAdapter.Fill(DataSet1, "Track")
> Return DataSet1
> End Function
>
>
Message #3 by "Roger Finks" <rfinks01@e...> on Fri, 14 Mar 2003 06:11:44 -0600
|
|
>I'm getting an error message "System.Data.OleDb.OleDbException: Syntax
>error in INSERT INTO statement on an Update of my Access database with a
>DataSet object. Could someone please tell me how to get a more definitive
>error message so I can tell what's going on. I am running in debut mode.
>Also if you happen to see the problem I'd be extremely happy.
>
>Code preceeding and leading upto the Update:
>
>' Construct Insert SQL command
>Dim objCommand As New OleDbCommand
>objCommand.Connection = objConnection
>strSQL = "INSERT INTO Track "
>strSQL += "(CDKey, TrackNumber, Composition, "
>strSQL += "Description, Time) "
>strSQL += "VALUES (?, ?, ?, ?, ?)"
>objCommand.CommandText = strSQL
>objDataAdapter.InsertCommand = objCommand
>
>Dim param As OleDbParameter
>param = objDataAdapter.InsertCommand.Parameters.Add
> ("@CDKey", OleDbType.BigInt, 16, "CDKey")
>param.SourceColumn = "CDKey"
>param = objDataAdapter.InsertCommand.Parameters.Add
> ("@TrackNumber", OleDbType.Integer, 8, "TrackNumber")
>param.SourceColumn = "TrackNumber"
>param = objDataAdapter.InsertCommand.Parameters.Add
> ("@Composition", OleDbType.VarWChar, 50, "Composition")
>param.SourceColumn = "Composition"
>param = objDataAdapter.InsertCommand.Parameters.Add
> ("@Description", OleDbType.VarWChar, 50, "Description")
>param.SourceColumn = "Description"
>param = objDataAdapter.InsertCommand.Parameters.Add
> ("@Time", OleDbType.DBTimeStamp, 32, "Time")
>param.SourceColumn = "Time"
>
>objDataAdapter.Update(DataSet1, "Track")
>
>Access Table layout:
>CDKey Long Integer
>TrackNumber Integer
>Composition Text(50)
>Description Text(50)
>Time Date/Time (Short Time)
>
>Function LoadDataSet() As DataSet
>
> strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
> strConnection += "Data
> Source=D:\Roger\DotNetProjects\CDCatalog\CDCatalog.mdb"
>
> objConnection = New OledbConnection(strConnection)
> strSQL = "SELECT Track.CDKey, Track.TrackNumber, Track.Composition,
> Track.Description, Track.Time"
> strSQL += " FROM Track WHERE Track.CDKey = " & CLng(Request.QueryString
> ("txtCDKey"))
> strSQL += " ORDER BY TrackNumber;"
> objDataAdapter = New OledbDataAdapter(strSQL, objConnection)
> objDataAdapter.Fill(DataSet1, "Track")
> Return DataSet1
>End Function
----------------------------------------------------------------------------
-----------------Roger,
This looks good, except after you set up the INSERT command you need to use
your objCommand Command object and an ExecuteNonQuery.
Regards
----- Original Message -----
From: "aspx_beginners digest" <aspx_beginners@p...>
To: "aspx_beginners digest recipients" <aspx_beginners@p...>
Sent: Thursday, March 13, 2003 6:05 PM
Subject: aspx_beginners digest: March 13, 2003
----------------------------------------------------------------------------
---------------------------------------------------
I added:
objConnection.Open()
bjCommand.ExecuteNonQuery()
Prior to the Update command but got the same result, Syntax error in Insert
Into statement.
Roger
Message #4 by "Jerry Lanphear" <jerrylan@q...> on Fri, 14 Mar 2003 08:10:50 -0700
|
|
Sorry Roger
I had a knee-jerk reaction to your post and gave you bad info.
Did you write this by hand? I use the wizards to do stuff like this. I
know it's less manly... Oh Well!
Regarding your question about a better message, you could put a try/catch
block around the update command and see if that helps. Use a "Catch e As
Exception" on your catch and you will have your message in "e"
Now for the unfortunate news... Unfortunate because its confusing. OleDb
Commands and SQL Commands use a different syntax for adding parameters.
Here, you have used the SQL version. OleDb does not support named
parameters with the "@" in front. It uses them in the order received. I
think if you take off the "@" in front of your names it will be OK.
Regards
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 5:11 AM
Subject: [aspx_beginners] Re: Error on Update of an Access database with a
DataSet
> >I'm getting an error message "System.Data.OleDb.OleDbException: Syntax
> >error in INSERT INTO statement on an Update of my Access database with a
> >DataSet object. Could someone please tell me how to get a more definitive
> >error message so I can tell what's going on. I am running in debut mode.
> >Also if you happen to see the problem I'd be extremely happy.
> >
> >Code preceeding and leading upto the Update:
> >
> >' Construct Insert SQL command
> >Dim objCommand As New OleDbCommand
> >objCommand.Connection = objConnection
> >strSQL = "INSERT INTO Track "
> >strSQL += "(CDKey, TrackNumber, Composition, "
> >strSQL += "Description, Time) "
> >strSQL += "VALUES (?, ?, ?, ?, ?)"
> >objCommand.CommandText = strSQL
> >objDataAdapter.InsertCommand = objCommand
> >
> >Dim param As OleDbParameter
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@CDKey", OleDbType.BigInt, 16, "CDKey")
> >param.SourceColumn = "CDKey"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@TrackNumber", OleDbType.Integer, 8, "TrackNumber")
> >param.SourceColumn = "TrackNumber"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Composition", OleDbType.VarWChar, 50, "Composition")
> >param.SourceColumn = "Composition"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Description", OleDbType.VarWChar, 50, "Description")
> >param.SourceColumn = "Description"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Time", OleDbType.DBTimeStamp, 32, "Time")
> >param.SourceColumn = "Time"
> >
> >objDataAdapter.Update(DataSet1, "Track")
> >
> >Access Table layout:
> >CDKey Long Integer
> >TrackNumber Integer
> >Composition Text(50)
> >Description Text(50)
> >Time Date/Time (Short Time)
> >
> >Function LoadDataSet() As DataSet
> >
> > strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
> > strConnection += "Data
> > Source=D:\Roger\DotNetProjects\CDCatalog\CDCatalog.mdb"
> >
> > objConnection = New OledbConnection(strConnection)
> > strSQL = "SELECT Track.CDKey, Track.TrackNumber, Track.Composition,
> > Track.Description, Track.Time"
> > strSQL += " FROM Track WHERE Track.CDKey = " &
CLng(Request.QueryString
> > ("txtCDKey"))
> > strSQL += " ORDER BY TrackNumber;"
> > objDataAdapter = New OledbDataAdapter(strSQL, objConnection)
> > objDataAdapter.Fill(DataSet1, "Track")
> > Return DataSet1
> >End Function
> --------------------------------------------------------------------------
--
> -----------------Roger,
>
> This looks good, except after you set up the INSERT command you need to
use
> your objCommand Command object and an ExecuteNonQuery.
>
> Regards
> ----- Original Message -----
> From: "aspx_beginners digest" <aspx_beginners@p...>
> To: "aspx_beginners digest recipients" <aspx_beginners@p...>
> Sent: Thursday, March 13, 2003 6:05 PM
> Subject: aspx_beginners digest: March 13, 2003
> --------------------------------------------------------------------------
--
> ---------------------------------------------------
> I added:
>
> objConnection.Open()
> bjCommand.ExecuteNonQuery()
>
> Prior to the Update command but got the same result, Syntax error in
Insert
> Into statement.
>
> Roger
>
>
>
>
Message #5 by "Roger Finks" <rfinks01@e...> on Sun, 16 Mar 2003 10:27:05 -0600
|
|
Jerry, thanks for your suggestions. They sure have given me a lot of ideas
to investigate but I still have the same problem. I tried your suggestion of
taking the @ out of the parameters but I looked it up in the .NET Framework
documentation and there's an example in the article "Using Parameters with a
DataAdapter" that shows OleDb using the @ sign. In either case it still
doesn't work with or without the @ sign. I still get the "Syntax error in
INSERT INTO statement" error message. I also went back and recovered the
Debugging and Error Handling chapter in Beginning ASP.NET 1.0 with VB .NET
including doing a trace, the Try, Catch structured error checking. I still
get the same error message with an error code of 5. I also added a watch of
the row I added to the dataset. I spent probably an hour going through that
entire object but was unable to find anything that pointed to what I'm doing
wrong. Any other suggestions would be most appreciated.
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 6:11 AM
Subject: Re: Error on Update of an Access database with a DataSet
Sorry Roger
I had a knee-jerk reaction to your post and gave you bad info.
Did you write this by hand? I use the wizards to do stuff like this. I
know it's less manly... Oh Well!
Regarding your question about a better message, you could put a try/catch
block around the update command and see if that helps. Use a "Catch e As
Exception" on your catch and you will have your message in "e"
Now for the unfortunate news... Unfortunate because its confusing. OleDb
Commands and SQL Commands use a different syntax for adding parameters.
Here, you have used the SQL version. OleDb does not support named
parameters with the "@" in front. It uses them in the order received. I
think if you take off the "@" in front of your names it will be OK.
Regards
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 5:11 AM
Subject: [aspx_beginners] Re: Error on Update of an Access database with a
DataSet
> >I'm getting an error message "System.Data.OleDb.OleDbException: Syntax
> >error in INSERT INTO statement on an Update of my Access database with a
> >DataSet object. Could someone please tell me how to get a more definitive
> >error message so I can tell what's going on. I am running in debut mode.
> >Also if you happen to see the problem I'd be extremely happy.
> >
> >Code preceeding and leading upto the Update:
> >
> >' Construct Insert SQL command
> >Dim objCommand As New OleDbCommand
> >objCommand.Connection = objConnection
> >strSQL = "INSERT INTO Track "
> >strSQL += "(CDKey, TrackNumber, Composition, "
> >strSQL += "Description, Time) "
> >strSQL += "VALUES (?, ?, ?, ?, ?)"
> >objCommand.CommandText = strSQL
> >objDataAdapter.InsertCommand = objCommand
> >
> >Dim param As OleDbParameter
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@CDKey", OleDbType.BigInt, 16, "CDKey")
> >param.SourceColumn = "CDKey"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@TrackNumber", OleDbType.Integer, 8, "TrackNumber")
> >param.SourceColumn = "TrackNumber"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Composition", OleDbType.VarWChar, 50, "Composition")
> >param.SourceColumn = "Composition"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Description", OleDbType.VarWChar, 50, "Description")
> >param.SourceColumn = "Description"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Time", OleDbType.DBTimeStamp, 32, "Time")
> >param.SourceColumn = "Time"
> >
> >objDataAdapter.Update(DataSet1, "Track")
> >
> >Access Table layout:
> >CDKey Long Integer
> >TrackNumber Integer
> >Composition Text(50)
> >Description Text(50)
> >Time Date/Time (Short Time)
> >
> >Function LoadDataSet() As DataSet
> >
> > strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
> > strConnection += "Data
> > Source=D:\Roger\DotNetProjects\CDCatalog\CDCatalog.mdb"
> >
> > objConnection = New OledbConnection(strConnection)
> > strSQL = "SELECT Track.CDKey, Track.TrackNumber, Track.Composition,
> > Track.Description, Track.Time"
> > strSQL += " FROM Track WHERE Track.CDKey = " &
CLng(Request.QueryString
> > ("txtCDKey"))
> > strSQL += " ORDER BY TrackNumber;"
> > objDataAdapter = New OledbDataAdapter(strSQL, objConnection)
> > objDataAdapter.Fill(DataSet1, "Track")
> > Return DataSet1
> >End Function
> --------------------------------------------------------------------------
--
> -----------------Roger,
>
> This looks good, except after you set up the INSERT command you need to
use
> your objCommand Command object and an ExecuteNonQuery.
>
> Regards
> ----- Original Message -----
> From: "aspx_beginners digest" <aspx_beginners@p...>
> To: "aspx_beginners digest recipients" <aspx_beginners@p...>
> Sent: Thursday, March 13, 2003 6:05 PM
> Subject: aspx_beginners digest: March 13, 2003
> --------------------------------------------------------------------------
--
> ---------------------------------------------------
> I added:
>
> objConnection.Open()
> bjCommand.ExecuteNonQuery()
>
> Prior to the Update command but got the same result, Syntax error in
Insert
> Into statement.
>
> Roger
>
>
>
>
Message #6 by "Jerry Lanphear" <jerrylan@q...> on Sun, 16 Mar 2003 13:51:39 -0700
|
|
Ok Roger, you may wish to try this then.
I am assuming you are using some version of Visual Studio .NET. If you
can generate the equivalent connection and data adapter using automated
tools you may figure it out.
Start a new project. View the Server Explorer. If your CDCatalog.mdb
has no entry in the server explorer then create a new connection by
right clicking in the white space and selecting "Add Connection". Create
and test the connection. Click on the plus sign next to the new
connection and expand the tables folder. Drag your track table onto
your blank form1 and a new connection object and DataAdapter object will
be created for your track table. View the code and compare the
generated code with your existing code.
If you do not have Visual Studio and are coding by hand, I would be
willing to accept an email to my qwest account from you containing the
.mdb file. I will perform these steps for you and email you back the
generated code. You may gain insight as to where your error is. The .mdb
file you send does not need to contain the data, just the empty track
table.
Regards
Jerry
-----Original Message-----
From: Roger Finks [mailto:rfinks01@e...]
Sent: Sunday, March 16, 2003 9:27 AM
To: aspx_beginners
Subject: [aspx_beginners] Re: Error on Update of an Access database with
a DataSet
Jerry, thanks for your suggestions. They sure have given me a lot of
ideas
to investigate but I still have the same problem. I tried your
suggestion of
taking the @ out of the parameters but I looked it up in the .NET
Framework
documentation and there's an example in the article "Using Parameters
with a
DataAdapter" that shows OleDb using the @ sign. In either case it still
doesn't work with or without the @ sign. I still get the "Syntax error
in
INSERT INTO statement" error message. I also went back and recovered the
Debugging and Error Handling chapter in Beginning ASP.NET 1.0 with VB
.NET
including doing a trace, the Try, Catch structured error checking. I
still
get the same error message with an error code of 5. I also added a watch
of
the row I added to the dataset. I spent probably an hour going through
that
entire object but was unable to find anything that pointed to what I'm
doing
wrong. Any other suggestions would be most appreciated.
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 6:11 AM
Subject: Re: Error on Update of an Access database with a DataSet
Sorry Roger
I had a knee-jerk reaction to your post and gave you bad info.
Did you write this by hand? I use the wizards to do stuff like this. I
know it's less manly... Oh Well!
Regarding your question about a better message, you could put a
try/catch
block around the update command and see if that helps. Use a "Catch e
As
Exception" on your catch and you will have your message in "e"
Now for the unfortunate news... Unfortunate because its confusing.
OleDb
Commands and SQL Commands use a different syntax for adding parameters.
Here, you have used the SQL version. OleDb does not support named
parameters with the "@" in front. It uses them in the order received.
I
think if you take off the "@" in front of your names it will be OK.
Regards
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 5:11 AM
Subject: [aspx_beginners] Re: Error on Update of an Access database with
a
DataSet
> >I'm getting an error message "System.Data.OleDb.OleDbException:
Syntax
> >error in INSERT INTO statement on an Update of my Access database
with a
> >DataSet object. Could someone please tell me how to get a more
definitive
> >error message so I can tell what's going on. I am running in debut
mode.
> >Also if you happen to see the problem I'd be extremely happy.
> >
> >Code preceeding and leading upto the Update:
> >
> >' Construct Insert SQL command
> >Dim objCommand As New OleDbCommand
> >objCommand.Connection = objConnection
> >strSQL = "INSERT INTO Track "
> >strSQL += "(CDKey, TrackNumber, Composition, "
> >strSQL += "Description, Time) "
> >strSQL += "VALUES (?, ?, ?, ?, ?)"
> >objCommand.CommandText = strSQL
> >objDataAdapter.InsertCommand = objCommand
> >
> >Dim param As OleDbParameter
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@CDKey", OleDbType.BigInt, 16, "CDKey")
> >param.SourceColumn = "CDKey"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@TrackNumber", OleDbType.Integer, 8, "TrackNumber")
> >param.SourceColumn = "TrackNumber"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Composition", OleDbType.VarWChar, 50, "Composition")
> >param.SourceColumn = "Composition"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Description", OleDbType.VarWChar, 50, "Description")
> >param.SourceColumn = "Description"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Time", OleDbType.DBTimeStamp, 32, "Time")
> >param.SourceColumn = "Time"
> >
> >objDataAdapter.Update(DataSet1, "Track")
> >
> >Access Table layout:
> >CDKey Long Integer
> >TrackNumber Integer
> >Composition Text(50)
> >Description Text(50)
> >Time Date/Time (Short Time)
> >
> >Function LoadDataSet() As DataSet
> >
> > strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
> > strConnection += "Data
> > Source=D:\Roger\DotNetProjects\CDCatalog\CDCatalog.mdb"
> >
> > objConnection = New OledbConnection(strConnection)
> > strSQL = "SELECT Track.CDKey, Track.TrackNumber,
Track.Composition,
> > Track.Description, Track.Time"
> > strSQL += " FROM Track WHERE Track.CDKey = " &
CLng(Request.QueryString
> > ("txtCDKey"))
> > strSQL += " ORDER BY TrackNumber;"
> > objDataAdapter = New OledbDataAdapter(strSQL, objConnection)
> > objDataAdapter.Fill(DataSet1, "Track")
> > Return DataSet1
> >End Function
>
------------------------------------------------------------------------
--
--
> -----------------Roger,
>
> This looks good, except after you set up the INSERT command you need
to
use
> your objCommand Command object and an ExecuteNonQuery.
>
> Regards
> ----- Original Message -----
> From: "aspx_beginners digest" <aspx_beginners@p...>
> To: "aspx_beginners digest recipients" <aspx_beginners@p...>
> Sent: Thursday, March 13, 2003 6:05 PM
> Subject: aspx_beginners digest: March 13, 2003
>
------------------------------------------------------------------------
--
--
> ---------------------------------------------------
> I added:
>
> objConnection.Open()
> bjCommand.ExecuteNonQuery()
>
> Prior to the Update command but got the same result, Syntax error in
Insert
> Into statement.
>
> Roger
>
>
>
>
Message #7 by "Jerry Lanphear" <jerrylan@q...> on Sun, 16 Mar 2003 15:39:26 -0700
|
|
Roger, here's an excerpt from one of my OleDb programs. As you can see,
When adding parameters the Add is used as a method with no equality such
as "Param =" And there is no "@" anywhere.
Regards
Me.OleDbInsertCommand1.CommandText = "INSERT INTO
Customers(BillCity, BillName, BillPlus4, BillState, BillStreet, BillZ" &
_
"ip, City, CustID, Directions, Fax, FirstName, LastName,
Location, Phone, Plus4, " & _
"Price, ProvID, SCode, State, Street, Zip) VALUES (?, ?, ?, ?,
?, ?, ?, ?, ?, ?, " & _
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillCity",
System.Data.OleDb.OleDbType.VarWChar, 50, "BillCity"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillName",
System.Data.OleDb.OleDbType.VarWChar, 50, "BillName"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillPlus4",
System.Data.OleDb.OleDbType.VarWChar, 4, "BillPlus4"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillState",
System.Data.OleDb.OleDbType.VarWChar, 2, "BillState"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillStreet",
System.Data.OleDb.OleDbType.VarWChar, 50, "BillStreet"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillZip",
System.Data.OleDb.OleDbType.VarWChar, 5, "BillZip"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("City",
System.Data.OleDb.OleDbType.VarWChar, 50, "City"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("CustID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "CustID", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Directions",
System.Data.OleDb.OleDbType.VarWChar, 250, "Directions"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Fax",
System.Data.OleDb.OleDbType.VarWChar, 10, "Fax"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("FirstName",
System.Data.OleDb.OleDbType.VarWChar, 50, "FirstName"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LastName",
System.Data.OleDb.OleDbType.VarWChar, 50, "LastName"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Location",
System.Data.OleDb.OleDbType.VarWChar, 100, "Location"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Phone",
System.Data.OleDb.OleDbType.VarWChar, 15, "Phone"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Plus4",
System.Data.OleDb.OleDbType.VarWChar, 4, "Plus4"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Price",
System.Data.OleDb.OleDbType.Currency, 0,
System.Data.ParameterDirection.Input, False, CType(19, Byte), CType(0,
Byte), "Price", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("ProvID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "ProvID", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("SCode",
System.Data.OleDb.OleDbType.VarWChar, 2, "SCode"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("State",
System.Data.OleDb.OleDbType.VarWChar, 2, "State"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Street",
System.Data.OleDb.OleDbType.VarWChar, 200, "Street"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Zip",
System.Data.OleDb.OleDbType.VarWChar, 5, "Zip"))
'
-----Original Message-----
From: Roger Finks [mailto:rfinks01@e...]
Sent: Sunday, March 16, 2003 9:27 AM
To: aspx_beginners
Subject: [aspx_beginners] Re: Error on Update of an Access database with
a DataSet
Jerry, thanks for your suggestions. They sure have given me a lot of
ideas
to investigate but I still have the same problem. I tried your
suggestion of
taking the @ out of the parameters but I looked it up in the .NET
Framework
documentation and there's an example in the article "Using Parameters
with a
DataAdapter" that shows OleDb using the @ sign. In either case it still
doesn't work with or without the @ sign. I still get the "Syntax error
in
INSERT INTO statement" error message. I also went back and recovered the
Debugging and Error Handling chapter in Beginning ASP.NET 1.0 with VB
.NET
including doing a trace, the Try, Catch structured error checking. I
still
get the same error message with an error code of 5. I also added a watch
of
the row I added to the dataset. I spent probably an hour going through
that
entire object but was unable to find anything that pointed to what I'm
doing
wrong. Any other suggestions would be most appreciated.
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 6:11 AM
Subject: Re: Error on Update of an Access database with a DataSet
Sorry Roger
I had a knee-jerk reaction to your post and gave you bad info.
Did you write this by hand? I use the wizards to do stuff like this. I
know it's less manly... Oh Well!
Regarding your question about a better message, you could put a
try/catch
block around the update command and see if that helps. Use a "Catch e
As
Exception" on your catch and you will have your message in "e"
Now for the unfortunate news... Unfortunate because its confusing.
OleDb
Commands and SQL Commands use a different syntax for adding parameters.
Here, you have used the SQL version. OleDb does not support named
parameters with the "@" in front. It uses them in the order received.
I
think if you take off the "@" in front of your names it will be OK.
Regards
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 5:11 AM
Subject: [aspx_beginners] Re: Error on Update of an Access database with
a
DataSet
> >I'm getting an error message "System.Data.OleDb.OleDbException:
Syntax
> >error in INSERT INTO statement on an Update of my Access database
with a
> >DataSet object. Could someone please tell me how to get a more
definitive
> >error message so I can tell what's going on. I am running in debut
mode.
> >Also if you happen to see the problem I'd be extremely happy.
> >
> >Code preceeding and leading upto the Update:
> >
> >' Construct Insert SQL command
> >Dim objCommand As New OleDbCommand
> >objCommand.Connection = objConnection
> >strSQL = "INSERT INTO Track "
> >strSQL += "(CDKey, TrackNumber, Composition, "
> >strSQL += "Description, Time) "
> >strSQL += "VALUES (?, ?, ?, ?, ?)"
> >objCommand.CommandText = strSQL
> >objDataAdapter.InsertCommand = objCommand
> >
> >Dim param As OleDbParameter
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@CDKey", OleDbType.BigInt, 16, "CDKey")
> >param.SourceColumn = "CDKey"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@TrackNumber", OleDbType.Integer, 8, "TrackNumber")
> >param.SourceColumn = "TrackNumber"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Composition", OleDbType.VarWChar, 50, "Composition")
> >param.SourceColumn = "Composition"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Description", OleDbType.VarWChar, 50, "Description")
> >param.SourceColumn = "Description"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Time", OleDbType.DBTimeStamp, 32, "Time")
> >param.SourceColumn = "Time"
> >
> >objDataAdapter.Update(DataSet1, "Track")
> >
> >Access Table layout:
> >CDKey Long Integer
> >TrackNumber Integer
> >Composition Text(50)
> >Description Text(50)
> >Time Date/Time (Short Time)
> >
> >Function LoadDataSet() As DataSet
> >
> > strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
> > strConnection += "Data
> > Source=D:\Roger\DotNetProjects\CDCatalog\CDCatalog.mdb"
> >
> > objConnection = New OledbConnection(strConnection)
> > strSQL = "SELECT Track.CDKey, Track.TrackNumber,
Track.Composition,
> > Track.Description, Track.Time"
> > strSQL += " FROM Track WHERE Track.CDKey = " &
CLng(Request.QueryString
> > ("txtCDKey"))
> > strSQL += " ORDER BY TrackNumber;"
> > objDataAdapter = New OledbDataAdapter(strSQL, objConnection)
> > objDataAdapter.Fill(DataSet1, "Track")
> > Return DataSet1
> >End Function
>
------------------------------------------------------------------------
--
--
> -----------------Roger,
>
> This looks good, except after you set up the INSERT command you need
to
use
> your objCommand Command object and an ExecuteNonQuery.
>
> Regards
> ----- Original Message -----
> From: "aspx_beginners digest" <aspx_beginners@p...>
> To: "aspx_beginners digest recipients" <aspx_beginners@p...>
> Sent: Thursday, March 13, 2003 6:05 PM
> Subject: aspx_beginners digest: March 13, 2003
>
------------------------------------------------------------------------
--
--
> ---------------------------------------------------
> I added:
>
> objConnection.Open()
> bjCommand.ExecuteNonQuery()
>
> Prior to the Update command but got the same result, Syntax error in
Insert
> Into statement.
>
> Roger
>
>
>
>
Message #8 by "Roger Finks" <rfinks01@e...> on Sat, 22 Mar 2003 22:57:14
|
|
Jerry, thanks for your suggestions. I've finally solved this problem. As
you would expect it was easier than I could have imagined. In my SQL
Insert command I had a field "Time". I needed brackets around this field
name as such [Time] and that solved the problem.
> Roger, here's an excerpt from one of my OleDb programs. As you can see,
When adding parameters the Add is used as a method with no equality such
as "Param =" And there is no "@" anywhere.
Regards
Me.OleDbInsertCommand1.CommandText = "INSERT INTO
Customers(BillCity, BillName, BillPlus4, BillState, BillStreet, BillZ" &
_
"ip, City, CustID, Directions, Fax, FirstName, LastName,
Location, Phone, Plus4, " & _
"Price, ProvID, SCode, State, Street, Zip) VALUES (?, ?, ?, ?,
?, ?, ?, ?, ?, ?, " & _
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillCity",
System.Data.OleDb.OleDbType.VarWChar, 50, "BillCity"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillName",
System.Data.OleDb.OleDbType.VarWChar, 50, "BillName"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillPlus4",
System.Data.OleDb.OleDbType.VarWChar, 4, "BillPlus4"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillState",
System.Data.OleDb.OleDbType.VarWChar, 2, "BillState"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillStreet",
System.Data.OleDb.OleDbType.VarWChar, 50, "BillStreet"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("BillZip",
System.Data.OleDb.OleDbType.VarWChar, 5, "BillZip"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("City",
System.Data.OleDb.OleDbType.VarWChar, 50, "City"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("CustID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "CustID", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Directions",
System.Data.OleDb.OleDbType.VarWChar, 250, "Directions"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Fax",
System.Data.OleDb.OleDbType.VarWChar, 10, "Fax"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("FirstName",
System.Data.OleDb.OleDbType.VarWChar, 50, "FirstName"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LastName",
System.Data.OleDb.OleDbType.VarWChar, 50, "LastName"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Location",
System.Data.OleDb.OleDbType.VarWChar, 100, "Location"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Phone",
System.Data.OleDb.OleDbType.VarWChar, 15, "Phone"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Plus4",
System.Data.OleDb.OleDbType.VarWChar, 4, "Plus4"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Price",
System.Data.OleDb.OleDbType.Currency, 0,
System.Data.ParameterDirection.Input, False, CType(19, Byte), CType(0,
Byte), "Price", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("ProvID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "ProvID", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("SCode",
System.Data.OleDb.OleDbType.VarWChar, 2, "SCode"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("State",
System.Data.OleDb.OleDbType.VarWChar, 2, "State"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Street",
System.Data.OleDb.OleDbType.VarWChar, 200, "Street"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Zip",
System.Data.OleDb.OleDbType.VarWChar, 5, "Zip"))
'
-----Original Message-----
From: Roger Finks [mailto:rfinks01@e...]
Sent: Sunday, March 16, 2003 9:27 AM
To: aspx_beginners
Subject: [aspx_beginners] Re: Error on Update of an Access database with
a DataSet
Jerry, thanks for your suggestions. They sure have given me a lot of
ideas
to investigate but I still have the same problem. I tried your
suggestion of
taking the @ out of the parameters but I looked it up in the .NET
Framework
documentation and there's an example in the article "Using Parameters
with a
DataAdapter" that shows OleDb using the @ sign. In either case it still
doesn't work with or without the @ sign. I still get the "Syntax error
in
INSERT INTO statement" error message. I also went back and recovered the
Debugging and Error Handling chapter in Beginning ASP.NET 1.0 with VB
.NET
including doing a trace, the Try, Catch structured error checking. I
still
get the same error message with an error code of 5. I also added a watch
of
the row I added to the dataset. I spent probably an hour going through
that
entire object but was unable to find anything that pointed to what I'm
doing
wrong. Any other suggestions would be most appreciated.
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 6:11 AM
Subject: Re: Error on Update of an Access database with a DataSet
Sorry Roger
I had a knee-jerk reaction to your post and gave you bad info.
Did you write this by hand? I use the wizards to do stuff like this. I
know it's less manly... Oh Well!
Regarding your question about a better message, you could put a
try/catch
block around the update command and see if that helps. Use a "Catch e
As
Exception" on your catch and you will have your message in "e"
Now for the unfortunate news... Unfortunate because its confusing.
OleDb
Commands and SQL Commands use a different syntax for adding parameters.
Here, you have used the SQL version. OleDb does not support named
parameters with the "@" in front. It uses them in the order received.
I
think if you take off the "@" in front of your names it will be OK.
Regards
----- Original Message -----
From: "Roger Finks" <rfinks01@e...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, March 14, 2003 5:11 AM
Subject: [aspx_beginners] Re: Error on Update of an Access database with
a
DataSet
> >I'm getting an error message "System.Data.OleDb.OleDbException:
Syntax
> >error in INSERT INTO statement on an Update of my Access database
with a
> >DataSet object. Could someone please tell me how to get a more
definitive
> >error message so I can tell what's going on. I am running in debut
mode.
> >Also if you happen to see the problem I'd be extremely happy.
> >
> >Code preceeding and leading upto the Update:
> >
> >' Construct Insert SQL command
> >Dim objCommand As New OleDbCommand
> >objCommand.Connection = objConnection
> >strSQL = "INSERT INTO Track "
> >strSQL += "(CDKey, TrackNumber, Composition, "
> >strSQL += "Description, Time) "
> >strSQL += "VALUES (?, ?, ?, ?, ?)"
> >objCommand.CommandText = strSQL
> >objDataAdapter.InsertCommand = objCommand
> >
> >Dim param As OleDbParameter
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@CDKey", OleDbType.BigInt, 16, "CDKey")
> >param.SourceColumn = "CDKey"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@TrackNumber", OleDbType.Integer, 8, "TrackNumber")
> >param.SourceColumn = "TrackNumber"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Composition", OleDbType.VarWChar, 50, "Composition")
> >param.SourceColumn = "Composition"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Description", OleDbType.VarWChar, 50, "Description")
> >param.SourceColumn = "Description"
> >param = objDataAdapter.InsertCommand.Parameters.Add
> > ("@Time", OleDbType.DBTimeStamp, 32, "Time")
> >param.SourceColumn = "Time"
> >
> >objDataAdapter.Update(DataSet1, "Track")
> >
> >Access Table layout:
> >CDKey Long Integer
> >TrackNumber Integer
> >Composition Text(50)
> >Description Text(50)
> >Time Date/Time (Short Time)
> >
> >Function LoadDataSet() As DataSet
> >
> > strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
> > strConnection += "Data
> > Source=D:\Roger\DotNetProjects\CDCatalog\CDCatalog.mdb"
> >
> > objConnection = New OledbConnection(strConnection)
> > strSQL = "SELECT Track.CDKey, Track.TrackNumber,
Track.Composition,
> > Track.Description, Track.Time"
> > strSQL += " FROM Track WHERE Track.CDKey = " &
CLng(Request.QueryString
> > ("txtCDKey"))
> > strSQL += " ORDER BY TrackNumber;"
> > objDataAdapter = New OledbDataAdapter(strSQL, objConnection)
> > objDataAdapter.Fill(DataSet1, "Track")
> > Return DataSet1
> >End Function
>
------------------------------------------------------------------------
--
--
> -----------------Roger,
>
> This looks good, except after you set up the INSERT command you need
to
use
> your objCommand Command object and an ExecuteNonQuery.
>
> Regards
> ----- Original Message -----
> From: "aspx_beginners digest" <aspx_beginners@p...>
> To: "aspx_beginners digest recipients" <aspx_beginners@p...>
> Sent: Thursday, March 13, 2003 6:05 PM
> Subject: aspx_beginners digest: March 13, 2003
>
------------------------------------------------------------------------
--
--
> ---------------------------------------------------
> I added:
>
> objConnection.Open()
> bjCommand.ExecuteNonQuery()
>
> Prior to the Update command but got the same result, Syntax error in
Insert
> Into statement.
>
> Roger
>
>
>
>
|
|
|
View Cart
