Error in INSERT INTO statement

G

Guest

When I run the simple code below I get the following
error message:

Type = System.Data.OleDb.OleDbException
Message = Syntax error in INSERT INTO statement

I've tweaked the insert to statement every which way.
All the other ways create earlier error messages. I've
compared this insert to statement to many I've seen in
the book ado.net core reference, as well as other books.
For the life of me I can't see what's wrong with it.
Perhaps one of you can help me.

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form
Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component
list.
Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the
Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnBuild As
System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.btnBuild = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'btnBuild
'
Me.btnBuild.Location = New System.Drawing.Point
(16, 24)
Me.btnBuild.Name = "btnBuild"
Me.btnBuild.Size = New System.Drawing.Size(72, 24)
Me.btnBuild.TabIndex = 0
Me.btnBuild.Text = "Build"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5,
13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.btnBuild)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub btnBuild_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnBuild.Click

Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\DateTypes.mdb;"
Dim cn As New OleDbConnection(strConn)
strSQL = "SELECT * FROM DateType ORDER BY
DateType;"
Dim da As New OleDbDataAdapter(strSQL, cn)

Dim ds As New DataSet
ds.DataSetName = "ds1"
cn.Open()
da.FillSchema(ds, SchemaType.Source, "DateType")
'cn.Close()
ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")



Dim dsA As New ds1

da.Fill(dsA, dsA.Tables(0).TableName)
Dim tblDateType As ds1.DateTypeDataTable =
dsA.Tables(0)
Dim rowDateType As ds1.DateTypeRow
rowDateType = tblDateType.NewDateTypeRow
rowDateType.DateType = "xxx"
rowDateType.CreateDate = Now
rowDateType.ChangeDate = Now
rowDateType.Active = True

rowDateType.ID = Integer.MaxValue
tblDateType.AddDateTypeRow(rowDateType)
da.InsertCommand = New OleDb.OleDbCommand("INSERT
INTO datetype(ID,DateType,CreateDate,ChangeDate,Active)
(?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer,4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@DateType",
OleDb.OleDbType.VarChar, 255, "DateType")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "DateType")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
Finally
cn.Close()
End Try

End Sub

End Class
 
W

William Ryan

Dennist:

I'm not sure which insert is bombing, but this looks like the likely
culprit. <<da.InsertCommand = New OleDb.OleDbCommand("INSERT
INTO datetype(ID,DateType,CreateDate,ChangeDate,Active)
(?,?,?,?,?)", cn)>> ... I think you need to include 'Values' . I also seem
to recall a problem with Access wherein if you use reserved words (It
definitely blows up) but if you use Field names that are the same as the
table name, it fails as well. I'm pretty sure that I remember reading this
but I can't find it so I may well be wrong.

Try adding the values word first and see if that doesn't fix it.

HTH,

Bill

When I run the simple code below I get the following
error message:

Type = System.Data.OleDb.OleDbException
Message = Syntax error in INSERT INTO statement

I've tweaked the insert to statement every which way.
All the other ways create earlier error messages. I've
compared this insert to statement to many I've seen in
the book ado.net core reference, as well as other books.
For the life of me I can't see what's wrong with it.
Perhaps one of you can help me.

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form
Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component
list.
Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the
Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnBuild As
System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.btnBuild = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'btnBuild
'
Me.btnBuild.Location = New System.Drawing.Point
(16, 24)
Me.btnBuild.Name = "btnBuild"
Me.btnBuild.Size = New System.Drawing.Size(72, 24)
Me.btnBuild.TabIndex = 0
Me.btnBuild.Text = "Build"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5,
13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.btnBuild)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub btnBuild_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnBuild.Click

Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\DateTypes.mdb;"
Dim cn As New OleDbConnection(strConn)
strSQL = "SELECT * FROM DateType ORDER BY
DateType;"
Dim da As New OleDbDataAdapter(strSQL, cn)

Dim ds As New DataSet
ds.DataSetName = "ds1"
cn.Open()
da.FillSchema(ds, SchemaType.Source, "DateType")
'cn.Close()
ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")



Dim dsA As New ds1

da.Fill(dsA, dsA.Tables(0).TableName)
Dim tblDateType As ds1.DateTypeDataTable =
dsA.Tables(0)
Dim rowDateType As ds1.DateTypeRow
rowDateType = tblDateType.NewDateTypeRow
rowDateType.DateType = "xxx"
rowDateType.CreateDate = Now
rowDateType.ChangeDate = Now
rowDateType.Active = True

rowDateType.ID = Integer.MaxValue
tblDateType.AddDateTypeRow(rowDateType)
da.InsertCommand = New OleDb.OleDbCommand("INSERT
INTO datetype(ID,DateType,CreateDate,ChangeDate,Active)
(?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer,4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@DateType",
OleDb.OleDbType.VarChar, 255, "DateType")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "DateType")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
Finally
cn.Close()
End Try

End Sub

End Class
Click to expand...
 
Y

Yan-Hong Huang[MSFT]

Hello Dennist,

Thanks for posting in the group.

Did Bill's suggestion help resolve the problem? If the problem still
exists, please feel free to reply here. We will follow up you here.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
G

Guest

Yup, it worked. Thanks. But it only shows a fundamental
flaw in ado.net's dealings with access databases with
autoincrementing integer keys.

I updated the database and the id was 2 billion or so.
So I deleted it. I tried again with 12. It worked. I
then tried it with 13 and it worked. I thought I'd try
it with 11. This should be absolutely forbidden. But it
wasn't. It succeeded. I might add that I changed the
DateType field each time because that's a unique field as
well.

More importantly, I shouldn't be chosing the value. How
would I normally get it? Well here I have my database
open. The value is supposed to be determined by Access.
I shouldn't put a value in.

dennist
-----Original Message-----
When I run the simple code below I get the following
error message:

Type = System.Data.OleDb.OleDbException
Message = Syntax error in INSERT INTO statement

I've tweaked the insert to statement every which way.
All the other ways create earlier error messages. I've
compared this insert to statement to many I've seen in
the book ado.net core reference, as well as other books.
For the life of me I can't see what's wrong with it.
Perhaps one of you can help me.

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form
Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component
list.
Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the
Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnBuild As
System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.btnBuild = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'btnBuild
'
Me.btnBuild.Location = New System.Drawing.Point
(16, 24)
Me.btnBuild.Name = "btnBuild"
Me.btnBuild.Size = New System.Drawing.Size(72, 24)
Me.btnBuild.TabIndex = 0
Me.btnBuild.Text = "Build"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size (5,
13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.btnBuild)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub btnBuild_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnBuild.Click

Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\DateTypes.mdb;"
Dim cn As New OleDbConnection(strConn)
strSQL = "SELECT * FROM DateType ORDER BY
DateType;"
Dim da As New OleDbDataAdapter(strSQL, cn)

Dim ds As New DataSet
ds.DataSetName = "ds1"
cn.Open()
da.FillSchema(ds, SchemaType.Source, "DateType")
'cn.Close()
ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")



Dim dsA As New ds1

da.Fill(dsA, dsA.Tables(0).TableName)
Dim tblDateType As ds1.DateTypeDataTable =
dsA.Tables(0)
Dim rowDateType As ds1.DateTypeRow
rowDateType = tblDateType.NewDateTypeRow
rowDateType.DateType = "xxx"
rowDateType.CreateDate = Now
rowDateType.ChangeDate = Now
rowDateType.Active = True

rowDateType.ID = Integer.MaxValue
tblDateType.AddDateTypeRow(rowDateType)
da.InsertCommand = New OleDb.OleDbCommand ("INSERT
INTO datetype(ID,DateType,CreateDate,ChangeDate,Active)
(?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer,4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@DateType",
OleDb.OleDbType.VarChar, 255, "DateType")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "DateType")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
Finally
cn.Close()
End Try

End Sub

End Class

.
Click to expand...
 
W

William \(Bill\) Vaughn

First, take a look at my article on handling Identity problems.
http://www.betav.com/msdn_magazine.htm
Consider that JET supports Identity processing so you should not pass the ID
value to the INSERT for the Identity column.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

When I run the simple code below I get the following
error message:

Type = System.Data.OleDb.OleDbException
Message = Syntax error in INSERT INTO statement

I've tweaked the insert to statement every which way.
All the other ways create earlier error messages. I've
compared this insert to statement to many I've seen in
the book ado.net core reference, as well as other books.
For the life of me I can't see what's wrong with it.
Perhaps one of you can help me.

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form
Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component
list.
Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the
Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnBuild As
System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.btnBuild = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'btnBuild
'
Me.btnBuild.Location = New System.Drawing.Point
(16, 24)
Me.btnBuild.Name = "btnBuild"
Me.btnBuild.Size = New System.Drawing.Size(72, 24)
Me.btnBuild.TabIndex = 0
Me.btnBuild.Text = "Build"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5,
13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.btnBuild)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub btnBuild_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnBuild.Click

Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\DateTypes.mdb;"
Dim cn As New OleDbConnection(strConn)
strSQL = "SELECT * FROM DateType ORDER BY
DateType;"
Dim da As New OleDbDataAdapter(strSQL, cn)

Dim ds As New DataSet
ds.DataSetName = "ds1"
cn.Open()
da.FillSchema(ds, SchemaType.Source, "DateType")
'cn.Close()
ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")



Dim dsA As New ds1

da.Fill(dsA, dsA.Tables(0).TableName)
Dim tblDateType As ds1.DateTypeDataTable =
dsA.Tables(0)
Dim rowDateType As ds1.DateTypeRow
rowDateType = tblDateType.NewDateTypeRow
rowDateType.DateType = "xxx"
rowDateType.CreateDate = Now
rowDateType.ChangeDate = Now
rowDateType.Active = True

rowDateType.ID = Integer.MaxValue
tblDateType.AddDateTypeRow(rowDateType)
da.InsertCommand = New OleDb.OleDbCommand("INSERT
INTO datetype(ID,DateType,CreateDate,ChangeDate,Active)
(?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer,4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@DateType",
OleDb.OleDbType.VarChar, 255, "DateType")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "DateType")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
Finally
cn.Close()
End Try

End Sub

End Class
Click to expand...
 
G

Guest

Thank you for your reply, but it's too brief. Do you
also mean I shouldn't assign a value to the ID column?
How would you change my code? I read your article but I
can't quickly digest it.

thanks again.

dennist
-----Original Message-----
First, take a look at my article on handling Identity problems.
http://www.betav.com/msdn_magazine.htm
Consider that JET supports Identity processing so you should not pass the ID
value to the INSERT for the Identity column.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

When I run the simple code below I get the following
error message:

Type = System.Data.OleDb.OleDbException
Message = Syntax error in INSERT INTO statement

I've tweaked the insert to statement every which way.
All the other ways create earlier error messages. I've
compared this insert to statement to many I've seen in
the book ado.net core reference, as well as other books.
For the life of me I can't see what's wrong with it.
Perhaps one of you can help me.

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form
Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component
list.
Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the
Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnBuild As
System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.btnBuild = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'btnBuild
'
Me.btnBuild.Location = New System.Drawing.Point
(16, 24)
Me.btnBuild.Name = "btnBuild"
Me.btnBuild.Size = New System.Drawing.Size(72, 24)
Me.btnBuild.TabIndex = 0
Me.btnBuild.Text = "Build"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size (5,
13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.btnBuild)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub btnBuild_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnBuild.Click

Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\DateTypes.mdb;"
Dim cn As New OleDbConnection(strConn)
strSQL = "SELECT * FROM DateType ORDER BY
DateType;"
Dim da As New OleDbDataAdapter(strSQL, cn)

Dim ds As New DataSet
ds.DataSetName = "ds1"
cn.Open()
da.FillSchema(ds, SchemaType.Source, "DateType")
'cn.Close()
ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")



Dim dsA As New ds1

da.Fill(dsA, dsA.Tables(0).TableName)
Dim tblDateType As ds1.DateTypeDataTable =
dsA.Tables(0)
Dim rowDateType As ds1.DateTypeRow
rowDateType = tblDateType.NewDateTypeRow
rowDateType.DateType = "xxx"
rowDateType.CreateDate = Now
rowDateType.ChangeDate = Now
rowDateType.Active = True

rowDateType.ID = Integer.MaxValue
tblDateType.AddDateTypeRow(rowDateType)
da.InsertCommand = New OleDb.OleDbCommand ("INSERT
INTO datetype(ID,DateType,CreateDate,ChangeDate,Active)
(?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer,4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add ("@DateType",
OleDb.OleDbType.VarChar, 255, "DateType")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "DateType")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
Finally
cn.Close()
End Try

End Sub

End Class
Click to expand...


.
Click to expand...
 
G

Guest

well, what do ya' know? I just tried it for myself and
it worked perfectly. I don't know how I'd manage this
with bound controls as in dataform wizards. I just
created a dataform without the ID and it didn't work.
Same problems as in my post on dataforms.

Thanks muchly.

dennist

-----Original Message-----
First, take a look at my article on handling Identity problems.
http://www.betav.com/msdn_magazine.htm
Consider that JET supports Identity processing so you should not pass the ID
value to the INSERT for the Identity column.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

When I run the simple code below I get the following
error message:

Type = System.Data.OleDb.OleDbException
Message = Syntax error in INSERT INTO statement

I've tweaked the insert to statement every which way.
All the other ways create earlier error messages. I've
compared this insert to statement to many I've seen in
the book ado.net core reference, as well as other books.
For the life of me I can't see what's wrong with it.
Perhaps one of you can help me.

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form
Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component
list.
Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the
Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnBuild As
System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.btnBuild = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'btnBuild
'
Me.btnBuild.Location = New System.Drawing.Point
(16, 24)
Me.btnBuild.Name = "btnBuild"
Me.btnBuild.Size = New System.Drawing.Size(72, 24)
Me.btnBuild.TabIndex = 0
Me.btnBuild.Text = "Build"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size (5,
13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.btnBuild)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub btnBuild_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnBuild.Click

Dim strConn, strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\DateTypes.mdb;"
Dim cn As New OleDbConnection(strConn)
strSQL = "SELECT * FROM DateType ORDER BY
DateType;"
Dim da As New OleDbDataAdapter(strSQL, cn)

Dim ds As New DataSet
ds.DataSetName = "ds1"
cn.Open()
da.FillSchema(ds, SchemaType.Source, "DateType")
'cn.Close()
ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")



Dim dsA As New ds1

da.Fill(dsA, dsA.Tables(0).TableName)
Dim tblDateType As ds1.DateTypeDataTable =
dsA.Tables(0)
Dim rowDateType As ds1.DateTypeRow
rowDateType = tblDateType.NewDateTypeRow
rowDateType.DateType = "xxx"
rowDateType.CreateDate = Now
rowDateType.ChangeDate = Now
rowDateType.Active = True

rowDateType.ID = Integer.MaxValue
tblDateType.AddDateTypeRow(rowDateType)
da.InsertCommand = New OleDb.OleDbCommand ("INSERT
INTO datetype(ID,DateType,CreateDate,ChangeDate,Active)
(?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer,4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add ("@DateType",
OleDb.OleDbType.VarChar, 255, "DateType")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "DateType")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
Finally
cn.Close()
End Try

End Sub

End Class
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

can't retrieve updated autoincremented value 5
can't transfer value from class to form 2
parameter problems 15
can't add row in code to access mdb 1
can't add row to ms access table 19
can't create new rows by code 1
I can't even KISS ! 'Syntax error in INSERT INTO statement 2
value member into listbox 10

Top