Insert Into Error

[mackyboy07]

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

[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 & "#, "

[mackyboy07]

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

[mackyboy07]

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

[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?

[mackyboy07]

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")]

[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

[mackyboy07]

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")]

[gupex]

Change ....
" #" & dte & "#, ")
to
" #" & dte & "# ")

[mackyboy07]

Change ....
" #" & dte & "#, ")
to
" #" & dte & "# ")

i tried it dude.. but an error states that " expected end of statement" the pound(#) is highlighted?

[gupex]

Change ....
treatment & "'," #" & dte & "#, ")
to
treatment & "', #" & dte & "# ")

[mackyboy07]

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..

[gupex]

Surely, if you removed the extra " between the , and the # it gave you a different error??
If so, what was it?

[pclement]

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.

[mackyboy07]

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..