Access 97 and VB.net INSERT INTO problem

[MacAttack]

Hi All

I am using an access 97 database (I have no control of the database type as it is accessed by several applications)

I am using VB.net 2003 to insert data using a query (it is later in the
post) and I am also most likely updating the database via a 28k link so I
want full control over the data I send.

I can paste the query into the SQL pane in Access and it works fine.
I use the same connection method for updating and deleting in the database
When I use the query in code, I get an error message indicating that the
Insert Into query has a fault in it.

I have tried the dates with and without #'s as well. I have also tried square brackets around username, password and first.

Any help is very much appreciated.

Cheers,


Query

INSERT INTO [User] (Username, Password, First, Surname, Phone, Fax,
PersonalEmailAddress, EmailDropBoxAddress, [Group], Position, BusinessUnit,
[Date], ReviewDate, Manager, ByWho) Values ('joe.bloggs', 'passwd', 'Joe',
'Bloggs', '124', '345', 'email@address', 'drop@address', 'small', 'member',
'one', '16/04/2003', '16/10/2003', 'Barney Rubble', 'Fred Nurks');

Code (I apologise if it is a little rough, but I am working on the fly)

Dim ODTcommand As New OleDb.OleDbCommand(strQuery, ODTConnection)

'open the connection
ODTConnection.Open()

'make a datareader
Dim ODTdatareader As OleDb.OleDbDataReader

'datareader command
ODTdatareader = ODTcommand.ExecuteReader


'get the data

If ODTdatareader.HasRows() Then
MessageBox.Show("The selected User Name already exists. Please
select another", "Duplicate User", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation)
ODTConnection.Close()
ODTcommand.Dispose()
Exit Sub
End If
ODTConnection.Close()
ODTcommand.Dispose()

'build the query string
Dim strsep As String = ", "

strQuery = "INSERT INTO [User] " & _
"(Username, Password, First, Surname, Phone, Fax,
PersonalEmailAddress, " & _
"EmailDropBoxAddress, [Group], Position, BusinessUnit, [Date], "
& _
"ReviewDate, Manager, ByWho) " & _
"Values ('" & cboUsername.Text & "', '" & txtPassword.Text & "',
'" & _
txtFirstName.Text & "', '" & txtSurname.Text & "', '" & _
txtPhone.Text & "', '" & txtFax.Text & "', '" & _
txtEmail.Text & "', '" & txtDropBox.Text & "', '" & _
txtGroup.Text & "', '" & txtPosition.Text & "', '" & _
cboBusinessUnit.Text & "', '" & txtStartdate.Text & "', '" & _
txtReviewdate.Text & "', '" & txtManager.Text & "', '" & _
txtbywho.Text & "');"
'strQuery = "User.username = """ & cboUsername.Text & """" & strsep
& _
' "User.Password = """ & txtPassword.Text & """" & strsep & _
' "User.First = """ & txtFirstName.Text & """" & strsep & _
' "User.Surname = """ & txtSurname.Text & """" & strsep & _
'"User.Phone = """ & txtPhone.Text & """" & strsep & _
'"User.Fax = """ & txtFax.Text & """" & strsep & _
'"User.PersonalEmailAddress = """ & txtEmail.Text & """" & strsep &
_
'"User.EmailDropBoxAddress = """ & txtDropBox.Text & """" & strsep &
_
'"User.Group = """ & txtGroup.Text & """" & strsep & _
'"User.Position = """ & txtPosition.Text & """" & strsep & _
'"User.BusinessUnit = """ & cboBusinessUnit.Text & """" & strsep & _
'"User.Date = """ & (txtStartdate.Text) & """" & strsep & _
'"User.ReviewDate = """ & (txtReviewdate.Text) & """" & strsep & _
'"User.Manager = """ & txtManager.Text & """" & strsep & _
'"User.ByWho = """ & txtbywho.Text & """"

MessageBox.Show(strQuery)
Debug.WriteLine(strQuery)
'make an oledb connection to the database (allows single fields to
be updated)
Dim ODTConnectionString As New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\test.mdb")

ODTcommand.CommandText = strQuery
ODTcommand.Connection = ODTConnectionString
'open the connection
ODTCommand.Connection.Open()

ODTCommand.ExecuteNonQuery()

'close the connection
ODTCommand.Connection.Close()

[r937]

dates will require the #s

maybe there's an apostrophe in one of the variables

you have to code two in a row, e.g.

insert ..  values ( 'O''Toole' )

[MacAttack]

The query string that I am using appears near the top of my post.

I am aware of the double apostrophe issue, but I am testing things without complications like this first.

I believe (at the moment) that the original author inserts a dummy record and then does an update query on it...


Cheers,

Macca