SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    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()

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 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' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •