[RESOLVED] save data to SQL server have runtime error "3704"
hi,please help.when i try to save data to SQL Server have error message.At the early time i was use access recordset to save the database, now i'm changing the recordset to SQL Server.when i try to save the data to SQL database have error message runtime error "3704"appear.
this is the current code:
Code:
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
Set conDataConnection = New Connection
conn.ConnectionString = "driver={SQL Server};Server=GRACEKUO;Uid=sa;pwd=sa;database=SPC"
conn.Open
' Dim strSQL As String
'First insert
strSQL = "INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values ('" & combopartno.Text & "','" & Txtdimension.Text & "','" & Txtinspector.Text & "',#" & DTPicker1.Value & "#,#" & Txttime.Text & "#,'" & txtdata1.Text & "')"
Debug.Print strSQL
'conDataConnection.Execute strSQL
conn.Execute strSQL error is highlight at this line
Re: save data to SQL server have runtime error "3704"
kevchadders:
hi,thanks ya reply.
this is the debug.print strSQL
INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values (' 0B21405 - DD','Parallelism 4.0','','28/02/2008','11:00','1.4')
this is the debug.print return
INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values (' 0B21405 - DD','Parallelism 4.0','','28/02/2008','11:00','1.4')
Re: save data to SQL server have runtime error "3704"
It sounds like its erroring on your [Time] field. (colon bit of the time) What is the datatype of that field in SQL? I expect it should be someting like VARCHAR.
Also, just concentrate on getting the SQL working via Query Analyser first, so that you know what data is required for the correct insert to work in code.
ps. Based on your requirements is it possible to change the Date and Time Fields to be just the one field of type timestamp?
one more question how to save the txtdata is only contain value to sql server?current have 5 textbox data want to save to the sql server database ,if the txtdata.text is empty thenstrsql will not perform save the [txtData] with Null value to sql server database,it only save the txtdata is not empty value.