SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
-
Jul 10, 2004, 05:13 #1
- Join Date
- Jul 2004
- Location
- Australia
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Access 97 and VB.net INSERT INTO problem
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()
-
Jul 10, 2004, 08:33 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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.Code:insert .. values ( 'O''Toole' )
-
Jul 13, 2004, 00:30 #3
- Join Date
- Jul 2004
- Location
- Australia
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Clarification on query string
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
Bookmarks