【新手求助】用SQL语句添加新记录时“语法错误(操作符丢失)”
小弟初学VB.net + 数据库,写个小程序,向Access数据库添加一条记录时,提示““语法错误(操作符丢失)”。因初涉此道,不知如何纠错,请各位不吝指教!!'数据定义
Dim Tester As String
Dim InputDate As Date
Dim InputTime As Date
Dim SN As String
Dim NextStep As String
Dim SQL As String
’数据赋值
If txtTester.Text = "" Then
lblStatus.Text = "请输入测试员工号"
MsgBox("测试员工号不可为空!")
txtTester.Focus()
Exit Sub
End If
SN = txtInputSN.Text
If SN = "" Then
lblStatus.Text = "未读取到SN,请输入"
MsgBox("未读取到SN")
ckbInputSN.Checked = True
txtInputSN.Enabled = True
txtInputSN.Focus()
Exit Sub
End If
Tester = txtTester.Text.Trim()
InputDate = Format(Now, "yyyy-MM-dd")
InputTime = Format(Now, "HH:mm:ss")
If ckbPass.Checked = True Then
NextStep = "F"
Else
NextStep = "R"
End If
'数据库连接及数据添加
objConnection = New OleDbConnection(strConnectionString)
Try
objConnection.Open()
SQL = "Insert into UploadResult(SN,Inputdate,InputTime,NextStation,Tester) Values('" + SN.Trim() + "'," + InputDate + "," + InputTime + ",'" + NextStep.Trim() + "','" + Tester.Trim() + "')"
'SQL = "Insert into UploadResult(SN,Inputdate,InputTime,NextStation,Tester)Values(,InputDate,InputTime,NextStep,Tester)"
objCommand = New OleDbCommand(SQL, objConnection)
objCommand.ExecuteNonQuery() --------------------编程问答-------------------- sql=" insert into 表(字符字段,数值字段,日期字段) values('"& 字符型变量 &"',"& 数值型变量 &",#"& 日期型变量 &"#)"
--------------------编程问答-------------------- .net 沒學過,路過! --------------------编程问答-------------------- 小弟初学VB.net + 数据库,写个小程序,向Access数据库添加一条记录时,提示““语法错误(操作符丢失)”。因初涉此道,不知如何纠错,请各位不吝指教!!
'数据定义--------------------编程问答-------------------- 这要看你的 ACCESS 里面 Inputdate 和 InputTime 是什么类型的数据字段了;
Dim Tester As String
Dim InputDate As Date
Dim InputTime As Date
Dim SN As String
Dim NextStep As String
Dim SQL As String
’数据赋值
If txtTester.Text = "" Then
lblStatus.Text = "请输入测试员工号"
MsgBox("测试员工号不可为空!")
txtTester.Focus()
Exit Sub
End If
SN = txtInputSN.Text
If SN = "" Then
lblStatus.Text = "未读取到SN,请输入"
MsgBox("未读取到SN")
ckbInputSN.Checked = True
txtInputSN.Enabled = True
txtInputSN.Focus()
Exit Sub
End If
Tester = txtTester.Text.Trim()
InputDate = Format(Now, "yyyy-MM-dd")
InputTime = Format(Now, "HH:mm:ss")
If ckbPass.Checked = True Then
NextStep = "F"
Else
NextStep = "R"
End If
'数据库连接及数据添加
objConnection = New OleDbConnection(strConnectionString)
Try
objConnection.Open()
SQL = "Insert into UploadResult(SN,Inputdate,InputTime,NextStation,Tester) Values('" + SN.Trim() + "'," + InputDate + "," + InputTime + ",'" + NextStep.Trim() + "','" + Tester.Trim() + "')"
'SQL = "Insert into UploadResult(SN,Inputdate,InputTime,NextStation,Tester)Values(,InputDate,InputTime,NextStep,Tester)"
objCommand = New OleDbCommand(SQL, objConnection)
objCommand.ExecuteNonQuery()
'如果都是字符型,那么如下:
SQL = "Insert into UploadResult(SN,Inputdate,InputTime,NextStation,Tester) Values('" + SN.Trim() + "','" + InputDate + "','" + InputTime + "','" + NextStep.Trim() + "','" + Tester.Trim() + "')"
'也就是在 InputDate 和 InputTime 两个变量的两边均得加“单引号”
但如果只有 InputTime 是字符型,而 InputDate 则是日期时间型,那么:
仅在 InputTime 变量的两边加“单引号”就行了。
--------------------编程问答-------------------- 这要看你的 ACCESS 里面 Inputdate 和 InputTime 是什么类型的数据字段了;
'如果都是字符型,那么如下:
SQL = "Insert into UploadResult(SN,Inputdate,InputTime,NextStation,Tester) Values('" + SN.Trim() + "','" + InputDate + "','" + InputTime + "','" + NextStep.Trim() + "','" + Tester.Trim() + "')"
'也就是在 InputDate 和 InputTime 两个变量的两边均得加“单引号”
但如果只有 InputTime 是字符型,而 InputDate 则是日期时间型,那么:
仅在 InputTime 变量的两边加“单引号”就行了。
--------------------编程问答-------------------- 跟踪调试一下,看看传给数据库系统的指令字符串对不对 --------------------编程问答-------------------- 看完楼主的代码,我感觉奇怪,代码是怎么执行的,我好像没看到有拼接字符串的“&”符号啊,楼主请参看1楼的提示吧。 --------------------编程问答--------------------
--------------------编程问答-------------------- 查到原因:
SN.Trim '不知道是怎么回事,可以把它单独立出来吗 SN=trim(text1.text) NextStep.Trim()同样 Tester.Trim() 同样
Sql = "Insert into UploadResult"
Sql = Sql & "(SN,Inputdate,InputTime,NextStation,Tester)"
Sql = Sql & "Values('" & SN & "', '"& InputDate &"' , '"& InputTime &"' ,'" & NextStep & "','" & Tester & "')"
InputTime 在Access中是日期型;在VB中date型
并将其格式改为:
InputTime = Format(Now, "HH:mm:ss")
暂时的解决方法式在Access和VB中都把其定义为String型
还是有些困惑,如果要在Access的字段中只写入时间(不显示日期),该怎么做呢? --------------------编程问答-------------------- 采用参数赋值的形式,就不会出现这个问题!
补充:VB , 数据库(包含打印,安装,报表)