Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What is wrong with this INSERT statement? (VB6/Access 2K)

    INSERT INTO tblPasswordHist (UserID, Password, PWDDate) VALUES ('mshea', 'test', #12/14/2003 1:28:20 PM#)

    It is being exectued using this:

    dCN.Execute ("INSERT INTO " & gsPWDHistTable & " " _
    & "(UserID, Password, PWDDate) " _
    & "VALUES ('" & smUserName & "', " _
    & "'" & txtField2.Text & "', " _
    & "#" & Now() & "#)")

    The table has a UID field which is AutoNumber (tried changing it to long but didn't help), UserID and Password are both strings, and PWDDate is a Date/Time.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: What is wrong with this INSERT statement? (VB6

    What happens when you run it? Do you get an error message, or does nothing happen?

    I suggest you define a string variable as follows:

    Dim strSql as string
    strSql = ("INSERT INTO " & gsPWDHistTable & " " _
    & "(UserID, Password, PWDDate) " _
    & "VALUES ('" & smUserName & "', " _
    & "'" & txtField2.Text & "', " _
    & "#" & Now() & "#)"

    dCN.Execute (strSql)

    You can't refer to forms or form controls within DAO code.
    Regards
    John



  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: What is wrong with this INSERT statement? (VB6/Access 2K)

    In what event is this code running. The .Text property of a control is ONLY available when that control has the focus.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What is wrong with this INSERT statement? (VB6/Access 2K)

    That's true in Access, Mark, but not in VB.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What is wrong with this INSERT statement? (VB6/Access 2K)

    Mike,

    I assume you're using an ADO connection to execute the SQL, right? I've found that building a SQL string for an insert sometime gets problematic. This sounds silly, but try putting spaces between the parentheses and the lists they contain. I don't know why, but that has worked for me in the past with a Values list. Also, doesn't ADO use the standard SQL delimiters for dates, or is that only with SQL Server? I haven't worked actively in ADO for a couple of years, so my memory is rusty.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What is wrong with this INSERT statement? (VB6/Access 2K)

    Okay, Guys, here's the solution (and don't ask me why). I took the output string and pasted it into the SQL view of an Access query, went into design view, then returned it to SQL view. Didn't have a problem at all with the syntax. When it returned to SQL view, of course, it re-did the syntax to its own flavor - turning the Values statement into a Select. Another thing it did was put brackets around the field name Password, so it reads [Password]. SO. . .I did that in my SQL string in VB and it now works great.

Posting Permissions

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