oledb date/time insert command format

[mabond]

Hi

VB.NET 2005 Express edition
Microsoft Access 2000 (SP-3)

Having trouble writing an "insert into" command for a Microsoft table I'm
accessing through oledb.

I've tried to follow the same principle I'd use if it was an sql database
but I'm getting an error telling me the syntax of the "insert into" command
is incorrect.

Can anyone tell looking at my code what I'm doing wrong or suggest the
correct syntax.

Here is the code I'm using

Imports System.Data.OleDb
Imports System.Data

Public Class myForm

Dim myConnection As OleDbConnection
Dim myCommand As OleDb.OleDbCommand
Dim insertCMD, deleteCMD, updateCMD, insertCMDLogIn, insertCMDLogOut,
selectCMD As String

Private Sub insertRecord()

myConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source='C:\myDbase.mdb'")
insertCMD = "INSERT INTO myTable (field1, field2, field3) VALUES
(@fone, @ftwo, @fthree);"
myCommand = New OleDbCommand(insertCMD, myConnection)
myCommand.Parameters.Add(New OleDbParameter("@fone",
OleDbType.Integer))
myCommand.Parameters.Add(New OleDbParameter("@ftwo",
OleDbType.VarChar))
myCommand.Parameters.Add(New OleDbParameter("@fthree",
OleDbType.Date)) '? not sure if this is the correct object type
myCommand.Parameters(0).Value = Me.fonelabel.Text
myCommand.Parameters(1).Value = Me.ftwolabel.Text
myCommand.Parameters(2).Value = Now() 'must insert both date and
time, not just date

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

End Sub

End class
Any help appreciated

Thanks

Michael Bond

[SvenC]

Hi mabond

"mabond" <ma****@discussions.microsoft.comwrote in message
news:9A**********************************@microsof t.com...

Hi

VB.NET 2005 Express edition
Microsoft Access 2000 (SP-3)

Having trouble writing an "insert into" command for a Microsoft table I'm
accessing through oledb.

I've tried to follow the same principle I'd use if it was an sql database
but I'm getting an error telling me the syntax of the "insert into"
command
is incorrect.

<snip/>

insertCMD = "INSERT INTO myTable (field1, field2, field3) VALUES
(@fone, @ftwo, @fthree);"

Try this INSERT command without the trailing ;

--
SvenC

myCommand = New OleDbCommand(insertCMD, myConnection)
myCommand.Parameters.Add(New OleDbParameter("@fone",
OleDbType.Integer))
myCommand.Parameters.Add(New OleDbParameter("@ftwo",
OleDbType.VarChar))
myCommand.Parameters.Add(New OleDbParameter("@fthree",
OleDbType.Date)) '? not sure if this is the correct object type
myCommand.Parameters(0).Value = Me.fonelabel.Text
myCommand.Parameters(1).Value = Me.ftwolabel.Text
myCommand.Parameters(2).Value = Now() 'must insert both date and
time, not just date

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

End Sub

End class
Any help appreciated

Thanks

Michael Bond

[mabond]

SvenC

thanks for quicke response, however your suggestion has not resolved the
issue. I'm still receiving a syntax error in "insert into" command

Regards

Michael

"SvenC" wrote:

Hi mabond

"mabond" <ma****@discussions.microsoft.comwrote in message
news:9A**********************************@microsof t.com...

Hi

VB.NET 2005 Express edition
Microsoft Access 2000 (SP-3)

Having trouble writing an "insert into" command for a Microsoft table I'm
accessing through oledb.

I've tried to follow the same principle I'd use if it was an sql database
but I'm getting an error telling me the syntax of the "insert into"
command
is incorrect.

<snip/>

insertCMD = "INSERT INTO myTable (field1, field2, field3) VALUES
(@fone, @ftwo, @fthree);"

Try this INSERT command without the trailing ;

--
SvenC

myCommand = New OleDbCommand(insertCMD, myConnection)
myCommand.Parameters.Add(New OleDbParameter("@fone",
OleDbType.Integer))
myCommand.Parameters.Add(New OleDbParameter("@ftwo",
OleDbType.VarChar))
myCommand.Parameters.Add(New OleDbParameter("@fthree",
OleDbType.Date)) '? not sure if this is the correct object type
myCommand.Parameters(0).Value = Me.fonelabel.Text
myCommand.Parameters(1).Value = Me.ftwolabel.Text
myCommand.Parameters(2).Value = Now() 'must insert both date and
time, not just date

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

End Sub

End class
Any help appreciated

Thanks

Michael Bond

[SvenC]

Hi mabond,

"mabond" <ma****@discussions.microsoft.comwrote in message
news:53**********************************@microsof t.com...

SvenC

thanks for quicke response, however your suggestion has not resolved the
issue. I'm still receiving a syntax error in "insert into" command

I never used Access and Oledb, so I am just guessing. Here is the next
guess:
I seem to remember that parameters might be declared with a ? in the command
string, so you could try to replace @fone, @ftwo, @fthree with ?, ?, ?
The questionmarks are bound to parameters by position because they do not
have unique names anymore.

You might also have to explicitly state the length of strings you pass.
Though that would rather show up as a problem of data lost on the way to the
DB table and not as runtime error.

--
SvenC

Regards

Michael

"SvenC" wrote:

>Hi mabond

"mabond" <ma****@discussions.microsoft.comwrote in message
news:9A**********************************@microso ft.com...

Hi

VB.NET 2005 Express edition
Microsoft Access 2000 (SP-3)

Having trouble writing an "insert into" command for a Microsoft table
I'm
accessing through oledb.

I've tried to follow the same principle I'd use if it was an sql
database
but I'm getting an error telling me the syntax of the "insert into"
command
is incorrect.

<snip/>

insertCMD = "INSERT INTO myTable (field1, field2, field3) VALUES
(@fone, @ftwo, @fthree);"

Try this INSERT command without the trailing ;

--
SvenC

myCommand = New OleDbCommand(insertCMD, myConnection)
myCommand.Parameters.Add(New OleDbParameter("@fone",
OleDbType.Integer))
myCommand.Parameters.Add(New OleDbParameter("@ftwo",
OleDbType.VarChar))
myCommand.Parameters.Add(New OleDbParameter("@fthree",
OleDbType.Date)) '? not sure if this is the correct object type
myCommand.Parameters(0).Value = Me.fonelabel.Text
myCommand.Parameters(1).Value = Me.ftwolabel.Text
myCommand.Parameters(2).Value = Now() 'must insert both date and
time, not just date

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

End Sub

End class
Any help appreciated

Thanks

Michael Bond

[mabond]

Sven

Thanks for this ..... but I'm afraid I've realised what the answer is....and
a silly mistake on my part.....and has led to me wasting your precious time.

To protect my original code I replaced tablenames, fieldnames etc in my post
with "dummy" names. A bad mistake cos you would have spotted the problem
right away.

field3 is actually named in my Access table as DateTime. With that being an
expression for defining a field type it's no wonder that my "insert into"
command was coming back with a syntax error!!

Sorry for leading you an a wild goose chase and I've learned my lesson .....
always post the code being used.

Thanks

Michael Bond

laised the answer

"SvenC" wrote:

Hi mabond,

"mabond" <ma****@discussions.microsoft.comwrote in message
news:53**********************************@microsof t.com...

SvenC

thanks for quicke response, however your suggestion has not resolved the
issue. I'm still receiving a syntax error in "insert into" command

I never used Access and Oledb, so I am just guessing. Here is the next
guess:
I seem to remember that parameters might be declared with a ? in the command
string, so you could try to replace @fone, @ftwo, @fthree with ?, ?, ?
The questionmarks are bound to parameters by position because they do not
have unique names anymore.

You might also have to explicitly state the length of strings you pass.
Though that would rather show up as a problem of data lost on the way to the
DB table and not as runtime error.

--
SvenC

Regards

Michael

"SvenC" wrote:

Hi mabond

"mabond" <ma****@discussions.microsoft.comwrote in message
news:9A**********************************@microsof t.com...
Hi

VB.NET 2005 Express edition
Microsoft Access 2000 (SP-3)

Having trouble writing an "insert into" command for a Microsoft table
I'm
accessing through oledb.

I've tried to follow the same principle I'd use if it was an sql
database
but I'm getting an error telling me the syntax of the "insert into"
command
is incorrect.
<snip/>
insertCMD = "INSERT INTO myTable (field1, field2, field3) VALUES
(@fone, @ftwo, @fthree);"

Try this INSERT command without the trailing ;

--
SvenC

myCommand = New OleDbCommand(insertCMD, myConnection)
myCommand.Parameters.Add(New OleDbParameter("@fone",
OleDbType.Integer))
myCommand.Parameters.Add(New OleDbParameter("@ftwo",
OleDbType.VarChar))
myCommand.Parameters.Add(New OleDbParameter("@fthree",
OleDbType.Date)) '? not sure if this is the correct object type
myCommand.Parameters(0).Value = Me.fonelabel.Text
myCommand.Parameters(1).Value = Me.ftwolabel.Text
myCommand.Parameters(2).Value = Now() 'must insert both date and
time, not just date

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

End Sub

End class
Any help appreciated

Thanks

Michael Bond

[SvenC]

Hi Michael,

"mabond" <ma****@discussions.microsoft.comwrote in message
news:1A**********************************@microsof t.com...

Sven

Thanks for this ..... but I'm afraid I've realised what the answer
is....and
a silly mistake on my part.....and has led to me wasting your precious
time.

To protect my original code I replaced tablenames, fieldnames etc in my
post
with "dummy" names. A bad mistake cos you would have spotted the problem
right away.

field3 is actually named in my Access table as DateTime. With that being
an
expression for defining a field type it's no wonder that my "insert into"
command was coming back with a syntax error!!

Sorry for leading you an a wild goose chase and I've learned my lesson
.....
always post the code being used.

No problem ;)

--
SvenC

Thanks

Michael Bond

laised the answer

"SvenC" wrote:

>Hi mabond,

"mabond" <ma****@discussions.microsoft.comwrote in message
news:53**********************************@microso ft.com...

SvenC

thanks for quicke response, however your suggestion has not resolved
the
issue. I'm still receiving a syntax error in "insert into" command

I never used Access and Oledb, so I am just guessing. Here is the next
guess:
I seem to remember that parameters might be declared with a ? in the
command
string, so you could try to replace @fone, @ftwo, @fthree with ?, ?, ?
The questionmarks are bound to parameters by position because they do not
have unique names anymore.

You might also have to explicitly state the length of strings you pass.
Though that would rather show up as a problem of data lost on the way to
the
DB table and not as runtime error.

--
SvenC

Regards

Michael

"SvenC" wrote:

Hi mabond

"mabond" <ma****@discussions.microsoft.comwrote in message
news:9A**********************************@microso ft.com...
Hi

VB.NET 2005 Express edition
Microsoft Access 2000 (SP-3)

Having trouble writing an "insert into" command for a Microsoft
table
I'm
accessing through oledb.

I've tried to follow the same principle I'd use if it was an sql
database
but I'm getting an error telling me the syntax of the "insert into"
command
is incorrect.
<snip/>
insertCMD = "INSERT INTO myTable (field1, field2, field3)
VALUES
(@fone, @ftwo, @fthree);"

Try this INSERT command without the trailing ;

--
SvenC

myCommand = New OleDbCommand(insertCMD, myConnection)
myCommand.Parameters.Add(New OleDbParameter("@fone",
OleDbType.Integer))
myCommand.Parameters.Add(New OleDbParameter("@ftwo",
OleDbType.VarChar))
myCommand.Parameters.Add(New OleDbParameter("@fthree",
OleDbType.Date)) '? not sure if this is the correct object type
myCommand.Parameters(0).Value = Me.fonelabel.Text
myCommand.Parameters(1).Value = Me.ftwolabel.Text
myCommand.Parameters(2).Value = Now() 'must insert both date
and
time, not just date

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

End Sub

End class
Any help appreciated

Thanks

Michael Bond