Insert Into Error


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Insert Into Error

  1. #1
    Join Date
    Jul 2005
    Posts
    29

    Insert Into Error

    hi guys! im new here... and im a newbie on vb programmin. im doin this project for our school.. and i have a simple problem...

    i cannot insert the date on my access database... here is my code

    studno = lblStno.Caption
    classno = lblClno.Caption
    fname = lblFName.Caption
    lname = lblLName.Caption
    complaint = txtComplaint.Text
    remarks = txtRemarks.Text
    treatment = txtTreatment.Text
    tme = lbltime.Caption
    dte = lbldate.Caption

    opn_sql
    sqlcmd = "insert into StudRec2005 (STUDNO,CLASSNO,NAMEFIRST,NAMELAST,COMPLAINT,REMARKS,TREATMENT) values ('" & studno & "','" & classno & "','" & _
    fname & "','" & lname & "','" & complaint & "','" & remarks & "','" & treatment & "');"
    Set rst = cnn.Execute(sqlcmd)
    cls_sql

    this code is good.. buy when im trying to input the DATE and TIME it returns an error.. insert into error... all of my variables are declared string.. but i dont know the data type of date and time.. pls. guys help me.. tnx

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    I don't see the date columns in your SQL statement, but the date values should be enclosed within pound sign characters (#) instead of single quotes if the data types of the columns into which you are inserting are Date/Time:

    " #" & dte & "#, "
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jul 2005
    Posts
    29
    Quote Originally Posted by pclement
    I don't see the date columns in your SQL statement, but the date values should be enclosed within pound sign characters (#) instead of single quotes if the data types of the columns into which you are inserting are Date/Time:

    " #" & dte & "#, "
    thanks a lot sir.. but i tried using the " #" & dte & "#, " but it returns an error. expected end of statement and the error was on the ( " ). but when i remove the quotation marks it runs but it contains the INSERT INTO error again.. please help.. tnx

  4. #4
    Join Date
    Jul 2005
    Posts
    29
    Quote Originally Posted by pclement
    I don't see the date columns in your SQL statement, but the date values should be enclosed within pound sign characters (#) instead of single quotes if the data types of the columns into which you are inserting are Date/Time:

    " #" & dte & "#, "
    thanks a lot sir.. but i tried using the " #" & dte & "#, " but it returns an error. expected end of statement and the error was on the ( # ). but when i remove the quotation marks it runs but it contains the INSERT INTO error again.. please help.. tnx

  5. #5
    Join Date
    Feb 2004
    Location
    Sydney, Australia
    Posts
    498
    As Paul suggested, there do not appear to be any Date fields in your Insert. If they are required fields in your d/b then you would get an error.

    BTW - what is the format of your lblTime.Caption and lblDate.Caption?

  6. #6
    Join Date
    Jul 2005
    Posts
    29
    Quote Originally Posted by gupex
    As Paul suggested, there do not appear to be any Date fields in your Insert. If they are required fields in your d/b then you would get an error.

    BTW - what is the format of your lblTime.Caption and lblDate.Caption?
    tnx sir.. here is my code..

    dte = lbldate.Caption
    tme = lbltime.Caption

    opn_sql
    sqlcmd = "insert into StudRec2005 (STUDNO,CLASSNO,NAMEFIRST,NAMELAST,COMPLAINT,REMARKS,TREATMENT,DATE) values ('" & studno & "','" & classno & "','" & _
    fname & "','" & lname & "','" & complaint & "','" & remarks & "','" & treatment & "'," #" & dte & "#, ");"
    Set rst = cnn.Execute(sqlcmd)
    cls_sql

    Private Sub Timer1_Timer()
    lbldate.Caption = Format(Date, "dd-mmmm-yyyy")
    lbltime.Caption = Format(Time, "hh:mm AMPM")]



  7. #7
    Join Date
    Nov 2003
    Location
    Alameda, CA
    Posts
    1,737
    in your first post you stated: but i dont know the data type of date and time
    well, working with a data base you must know the type of the record fields, otherwise how do you get around it? Do you have the specific for the data base? if this a school project the teacher should have provided you that. Otherwise there are ways to query the data base and investigate the type of the data record (it depends on the database on how to do it)
    Before messing around with your code, be sure you know the data type.
    Marco
    "There are two ways to write error-free programs. Only the third one works."
    Unknown

  8. #8
    Join Date
    Jul 2005
    Posts
    29
    Quote Originally Posted by mstraf
    in your first post you stated: but i dont know the data type of date and time
    well, working with a data base you must know the type of the record fields, otherwise how do you get around it? Do you have the specific for the data base? if this a school project the teacher should have provided you that. Otherwise there are ways to query the data base and investigate the type of the data record (it depends on the database on how to do it)
    Before messing around with your code, be sure you know the data type.
    Marco
    dte = lbldate.Caption
    tme = lbltime.Caption

    tnx for the info dude, i already know the data type of the records.. my only problem now is the syntax of the date on sql command. pclement told me to try this... " #" & dte & "#, "... so i put it.. but it still returns an error.. can u help me with this? thanks a lot

    opn_sql
    sqlcmd = "insert into StudRec2005 (STUDNO,CLASSNO,NAMEFIRST,NAMELAST,COMPLAINT,REMAR KS,TREATMENT,DATE) values ('" & studno & "','" & classno & "','" & _
    fname & "','" & lname & "','" & complaint & "','" & remarks & "','" & treatment & "'," #" & dte & "#, ");"
    Set rst = cnn.Execute(sqlcmd)
    cls_sql

    Private Sub Timer1_Timer()
    lbldate.Caption = Format(Date, "dd-mmmm-yyyy")
    lbltime.Caption = Format(Time, "hh:mm AMPM")]

  9. #9
    Join Date
    Feb 2004
    Location
    Sydney, Australia
    Posts
    498
    Change ....
    " #" & dte & "#, ")
    to
    " #" & dte & "# ")

  10. #10
    Join Date
    Jul 2005
    Posts
    29
    Quote Originally Posted by gupex
    Change ....
    " #" & dte & "#, ")
    to
    " #" & dte & "# ")
    i tried it dude.. but an error states that " expected end of statement" the pound(#) is highlighted?

  11. #11
    Join Date
    Feb 2004
    Location
    Sydney, Australia
    Posts
    498
    Change ....
    treatment & "'," #" & dte & "#, ")
    to
    treatment & "', #" & dte & "# ")

  12. #12
    Join Date
    Jul 2005
    Posts
    29
    Quote Originally Posted by gupex
    Change ....
    treatment & "'," #" & dte & "#, ")
    to
    treatment & "', #" & dte & "# ")
    thanks man.... but it still doesnt work... geesh.. i m getting frustrated with this,.. i can't figure it out..

  13. #13
    Join Date
    Feb 2004
    Location
    Sydney, Australia
    Posts
    498
    Surely, if you removed the extra " between the , and the # it gave you a different error??
    If so, what was it?

  14. #14
    Join Date
    Dec 2003
    Posts
    2,750
    Can you display the value of sqlcmd in the Immediate Window just before you execute the query and post the value. You can also place the following code after the sqlcmd assignment to display the value:

    Debug.Print sqlcmd

    I'd rather see the actual value of the SQL statement than guess what you are trying to execute.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  15. #15
    Join Date
    Jul 2005
    Posts
    29
    Quote Originally Posted by gupex
    Surely, if you removed the extra " between the , and the # it gave you a different error??
    If so, what was it?
    it's the same error.. well here is the code..

    Private Sub cmdSubmit_Click()
    Dim sqlcmd, moo, studno, classno, fname, lname, complaint, remarks, treatment As String
    Dim dte, tme As Date



    If txtComplaint.Text = "" Then
    moo = MsgBox("Please fill out the fields", vbExclamation)
    Else
    If txtRemarks.Text = "" Then
    moo = MsgBox("Please fill out the fields", vbExclamation)
    Else
    If txtTreatment.Text = "" Then
    moo = MsgBox("Please fill out the fields", vbExclamation)


    Else

    studno = lblStNo.Caption
    classno = lblClno.Caption
    fname = lblFName.Caption
    lname = lblLName.Caption
    complaint = txtComplaint.Text
    remarks = txtRemarks.Text
    treatment = txtTreatment.Text
    tme = Left(lbltime.Caption, 5)
    dte = Left(lbldate.Caption, 5)

    opn_sql
    sqlcmd = "insert into StudRec2005 (STUDNO,CLASSNO,NAMEFIRST,NAMELAST,COMPLAINT,REMARKS,TREATMENT,DATE) values ('" & studno & "','" & classno & "','" & _
    fname & "','" & lname & "','" & complaint & "','" & remarks & "','" & treatment & "',#" & dte & "# );"
    Set rst = cnn.Execute(sqlcmd)
    cls_sql

    txtComplaint.Text = ""
    txtRemarks.Text = ""
    txtTreatment.Text = ""

    Unload Me



    End If
    End If
    End If



    End Sub

    Private Sub Picture1_Click()

    End Sub

    Private Sub Command1_Click()

    End Sub

    Private Sub Timer1_Timer()
    lbldate.Caption = Format(Date, "dd-mmmm-yyyy")
    lbltime.Caption = Format(Time, "hh:mm AMPM")

    End Sub

    Private Sub submit()

    End Sub


    and here is my module..

    Public cnn As New ADODB.Connection, rst As New Recordset
    Public dte As Variant
    Public tme As Variant



    Public Sub opn_sql()
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\mac\macky\macky'sfile\docs\VB\HealthServices\Health Services.mdb;Persist Security Info=False"
    End Sub

    Public Sub cls_sql()
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
    End Sub

    help guys..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center