Weird Access INSERT INTO Query error

  • Thread starter Thread starter Macca
  • Start date Start date
M

Macca

Hi all,

This should probably go into the .net newsgroup as well.

I am using an Access 97 database (I have no control over this - it is part
of a package written for my company and is part of a web, customer and
administration package)

I am writing a tool to access this database and modify some of the contents
using oledb. I am going the path I have as it will often be updated via a
28k link.

I can cut and paste the following query into the access SQL window and
execute it, without a problem.

When I run the query in code from VN.net, I get an error (error in Insert
into) I have also attached the VB code.

Any suggestions please reply to the newsgroup (I have not supplied a
personal email for spam reasons)

Thanks for reading...

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');

VB Code

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()
 
Macca,

I am not entirely familiar with the type of environment you are working
in here. But I think there may be a problem with the dates. If [Date]
and ReviewDate are Date data type, I don't think it will work to try to
insert string values into them. Either enclose the dates in #
delimiters (and probably they should be in American date format as
well), or else convert to integers for example using a CLng() function.

--
Steve Schapel, Microsoft Access MVP
Hi all,

This should probably go into the .net newsgroup as well.

I am using an Access 97 database (I have no control over this - it is part
of a package written for my company and is part of a web, customer and
administration package)

I am writing a tool to access this database and modify some of the contents
using oledb. I am going the path I have as it will often be updated via a
28k link.

I can cut and paste the following query into the access SQL window and
execute it, without a problem.

When I run the query in code from VN.net, I get an error (error in Insert
into) I have also attached the VB code.

Any suggestions please reply to the newsgroup (I have not supplied a
personal email for spam reasons)

Thanks for reading...

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');

VB Code

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()
Click to expand...
 
I'd also recommend putting square brackets around the field names Password
and First, as I believe both of them are reserved words as well.

(To be honest, I'd actually recommend renaming the table, plus any of the
fields in it that are reserved words, rather than relying on [] to keep
things "honest"!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve Schapel said:
Macca,

I am not entirely familiar with the type of environment you are working
in here. But I think there may be a problem with the dates. If [Date]
and ReviewDate are Date data type, I don't think it will work to try to
insert string values into them. Either enclose the dates in #
delimiters (and probably they should be in American date format as
well), or else convert to integers for example using a CLng() function.

--
Steve Schapel, Microsoft Access MVP
Hi all,

This should probably go into the .net newsgroup as well.

I am using an Access 97 database (I have no control over this - it is part
of a package written for my company and is part of a web, customer and
administration package)

I am writing a tool to access this database and modify some of the contents
using oledb. I am going the path I have as it will often be updated via a
28k link.

I can cut and paste the following query into the access SQL window and
execute it, without a problem.

When I run the query in code from VN.net, I get an error (error in Insert
into) I have also attached the VB code.

Any suggestions please reply to the newsgroup (I have not supplied a
personal email for spam reasons)

Thanks for reading...

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');

VB Code

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()
Click to expand...
Click to expand...
 
Thanks for the replies so far Guys,

As far as the dates go, I have used #'s as well. The query string is the one
that I used. Yes I have also put square brackets around the columns first,
password and username to no avail.

I have no control over the format of the database as there is a website,
this tool and another two programs that manipulate it (It is also located on
a machine remote form me, but i am using local connections while I test)

Cheers,

Macca

Douglas J. Steele said:
I'd also recommend putting square brackets around the field names Password
and First, as I believe both of them are reserved words as well.

(To be honest, I'd actually recommend renaming the table, plus any of the
fields in it that are reserved words, rather than relying on [] to keep
things "honest"!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve Schapel said:
Macca,

I am not entirely familiar with the type of environment you are working
in here. But I think there may be a problem with the dates. If [Date]
and ReviewDate are Date data type, I don't think it will work to try to
insert string values into them. Either enclose the dates in #
delimiters (and probably they should be in American date format as
well), or else convert to integers for example using a CLng() function.

--
Steve Schapel, Microsoft Access MVP
Hi all,

This should probably go into the .net newsgroup as well.

I am using an Access 97 database (I have no control over this - it is part
of a package written for my company and is part of a web, customer and
administration package)

I am writing a tool to access this database and modify some of the contents
using oledb. I am going the path I have as it will often be updated
Click to expand...
Click to expand...
via
a
28k link.

I can cut and paste the following query into the access SQL window and
execute it, without a problem.

When I run the query in code from VN.net, I get an error (error in Insert
into) I have also attached the VB code.

Any suggestions please reply to the newsgroup (I have not supplied a
personal email for spam reasons)

Thanks for reading...

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');

VB Code

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
Click to expand...
Click to expand...
&
"',
Click to expand...
&
_
Click to expand...
strsep
& _ &
_
Click to expand...
fields
to
Click to expand...
Click to expand...
 
Macca,

I don't quite understand. The "query string" you gave shows the dates
in universal format (i.e. non-US) and as text. If the [Date] and
ReviewDate fields are Date data type (I still don't know whether this is
the case), the query string needs to be either of these:

....VALUES ('joe.bloggs', 'passwd', 'Joe', 'Bloggs', '124', '345',
'email@address', 'drop@address', 'small', 'member', 'one', #4/16/2003#,
#10/16/2003#, 'Barney Rubble', 'Fred Nurks')

....VALUES ('joe.bloggs', 'passwd', 'Joe', 'Bloggs', '124', '345',
'email@address', 'drop@address', 'small', 'member', 'one', 37727, 37910,
'Barney Rubble', 'Fred Nurks')

This would mean your code needs to be like one of these (at lease this
is what one would do in Access VBA, in which case using the .Text
property of the controls in incorrect):

strQuery = "INSERT INTO [User] " & _
"(Username, [Password], [First], Surname, Phone, Fax,
PersonalEmailAddress, " & _
"EmailDropBoxAddress, [Group], Position, BusinessUnit, [Date], " & _
"ReviewDate, Manager, ByWho) " & _
"VALUES ('" & cboUsername & "', '" & txtPassword & "', '" & _
txtFirstName & "', '" & txtSurname & "', '" & _
txtPhone & "', '" & txtFax & "', '" & _
txtEmail & "', '" & txtDropBox & "', '" & _
txtGroup & "', '" & txtPosition & "', '" & _
cboBusinessUnit & "', #" & Format(txtStartdate, 'mm/dd/yyyy') &
"#, #" & _
Format(txtReviewdate, 'mm/dd/yyyy') & #", '" & txtManager & "', '" & _
txtbywho & "')"

strQuery = "INSERT INTO [User] " & _
"(Username, [Password], [First], Surname, Phone, Fax,
PersonalEmailAddress, " & _
"EmailDropBoxAddress, [Group], Position, BusinessUnit, [Date], " & _
"ReviewDate, Manager, ByWho) " & _
"VALUES ('" & cboUsername & "', '" & txtPassword & "', '" & _
txtFirstName & "', '" & txtSurname & "', '" & _
txtPhone & "', '" & txtFax & "', '" & _
txtEmail & "', '" & txtDropBox & "', '" & _
txtGroup & "', '" & txtPosition & "', '" & _
cboBusinessUnit & "', " & CLng(txtStartdate) & ", " & _
CLng(txtReviewdate) & ", '" & txtManager & "', '" & _
txtbywho & "')"
 
I'll give that a try once I have cleared my in tray (I have been off sick
for a week)

Thanks.


Steve Schapel said:
Macca,

I don't quite understand. The "query string" you gave shows the dates
in universal format (i.e. non-US) and as text. If the [Date] and
ReviewDate fields are Date data type (I still don't know whether this is
the case), the query string needs to be either of these:

...VALUES ('joe.bloggs', 'passwd', 'Joe', 'Bloggs', '124', '345',
'email@address', 'drop@address', 'small', 'member', 'one', #4/16/2003#,
#10/16/2003#, 'Barney Rubble', 'Fred Nurks')

...VALUES ('joe.bloggs', 'passwd', 'Joe', 'Bloggs', '124', '345',
'email@address', 'drop@address', 'small', 'member', 'one', 37727, 37910,
'Barney Rubble', 'Fred Nurks')

This would mean your code needs to be like one of these (at lease this
is what one would do in Access VBA, in which case using the .Text
property of the controls in incorrect):

strQuery = "INSERT INTO [User] " & _
"(Username, [Password], [First], Surname, Phone, Fax,
PersonalEmailAddress, " & _
"EmailDropBoxAddress, [Group], Position, BusinessUnit, [Date], " & _
"ReviewDate, Manager, ByWho) " & _
"VALUES ('" & cboUsername & "', '" & txtPassword & "', '" & _
txtFirstName & "', '" & txtSurname & "', '" & _
txtPhone & "', '" & txtFax & "', '" & _
txtEmail & "', '" & txtDropBox & "', '" & _
txtGroup & "', '" & txtPosition & "', '" & _
cboBusinessUnit & "', #" & Format(txtStartdate, 'mm/dd/yyyy') &
"#, #" & _
Format(txtReviewdate, 'mm/dd/yyyy') & #", '" & txtManager & "', '" & _
txtbywho & "')"

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

--
Steve Schapel, Microsoft Access MVP

Thanks for the replies so far Guys,

As far as the dates go, I have used #'s as well. The query string is the one
that I used. Yes I have also put square brackets around the columns first,
password and username to no avail.

I have no control over the format of the database as there is a website,
this tool and another two programs that manipulate it (It is also located on
a machine remote form me, but i am using local connections while I test)

Cheers,

Macca
Click to expand...
Click to expand...
 
Back
Top