Results 1 to 4 of 4

Thread: Database - How can I work with dates correctly?

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2012
    Posts
    41

    Database - How can I work with dates correctly?

    please help me with this..please tell what is wrong here..here is my code;
    vb Code:
    1. Private Sub cmdSave_Click()
    2. Dim db_file As String
    3. Dim statement As String
    4. Dim conn1 As ADODB.Connection
    5. Dim ctl As Control
    6. Dim sDateString As Date
    7.  
    8. ' Date Format
    9. 'sDateString = Format(DTCalendar.value, "mm/dd/yyyy")
    10.  
    11. ' Get the data.
    12. db_file = App.Path
    13. If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
    14. db_file = db_file & "DB.mdb"
    15.  
    16. ' Open a connection.
    17. Set conn1 = New ADODB.Connection
    18. conn1.ConnectionString = _
    19. "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    20. "Data Source=" & db_file & ";" & _
    21. "Persist Security Info=False"
    22. conn1.Open
    23.  
    24. ' Compose the INSERT statement.
    25. statement = "INSERT INTO tblDPSR " & _
    26. "(LineNo, Shift, Date, PreparedBy, ItemCodes, Weight, StartingNo, LastNo, Quantity, TotalHPE1) " & _
    27. " VALUES (" & _
    28. "'" & cmbLine.Text & "', " & _
    29. "'" & cmbShift.Text & "', " & _
    30. "'" & DTCalendar.value & "', " & _
    31. "'" & cmbPrepared.Text & "', " & _
    32. "'" & cmbIC1.Text & "', " & _
    33. "'" & txtWeight1.Text & "', " & _
    34. "'" & txtStarting1.Text & "', " & _
    35. "'" & txtLast1.Text & "', " & _
    36. "'" & txtQ1.Text & "', " & _
    37. "'" & txtTotalHPE1.Text & "'," & _
    38. ")"
    39.  
    40. ' Execute the statement.
    41. conn1.Execute statement, , adCmdText
    42. 'conn1.Execute ("INSERT INTO tblDPSR(Date) VALUES (" & "'" & DTCalendar.value & "#,)")
    43.  
    44. ' Close the connection.
    45. conn1.Close
    46.  
    47. ' Clear the TextBoxes.
    48. For Each ctl In Controls
    49. If TypeOf ctl Is TextBox Then
    50. ctl.Text = ""
    51. End If
    52. Next ctl
    53.  
    54. End Sub
    But as i click my save button, this is the error message:
    Syntax error in INTO statement
    With that error, highlights the line: conn1.Execute statement, , adCmdText

    What could be my error? Please help me

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Database - How can I work with dates correctly?

    Split into its own thread and moved to VB6 And Earlier

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database - How can I work with dates correctly?

    Most of your questions can be answered here - http://www.vbforums.com/showthread.php?t=337051 including why you shouldn't use "date" as a field name (big ol' hint: it's a reserved word... which is addressed in the linked thread)... might also be work reading through the section on parameters.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Database - How can I work with dates correctly?

    Yes you should not use Date as a field name and if you do then you must surround it with [] in your sql statements as in [date]. The same is true for all reserved words as well as field names which contain spaces.

    Also Access requires you to use # as a delimiter for date fields rather than '. ' is used for text fields and nothing is used on numeric fields.


    If your fields are defined as the types the names would suggest then you have several errors in that SQL Statement

Posting Permissions

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



Click Here to Expand Forum to Full Width