本人在用VB6.0写一程序时遇到问题,后台数据库用的是oracle8.0.3,具体如下:表名:sys.aaa
xm,char(8),not null
py,char(3),not null
sj,date
现在要用vb程序向该表写入一条记录,其中的sj字段就取当前的时间,源代码如下:
----------------------
Option ExplicitPublic LoginSucceeded As Boolean
Public CN As New ADODB.Connection, RS As New ADODB.Recordset
Public SQL As StringConst ConnString = "PROVIDER=MSDASQL;dsn=oracle;uid=system;pwd=***;database=***;"Private Sub cmdCancel_Click()
'设定全局变量为 false 来代表
'失败的登录
LoginSucceeded = False
Me.Hide
End SubPrivate Sub cmdOK_Click()
CN.Open ConnString
SQL = "SELECT * FROM sys.aaa"
SQL = SQL & "WHERE py = '" & txtUserName & "' "
RS.Open SQL, ConnString, , , adCmdText
If RS.EOF = False Then
'检查密码的正确性
If txtPassword = RTrim(RS("kl")) Then
FMMAIN.USERNAME = RS("xm")
SQL = SQL & " VALUES ( "
SQL = SQL & " '" & RS("xm") & "', "
SQL = SQL & " '" & txtUserName & "', "
SQL = SQL & " to_date('" & CStr(Date) & "','dd-mon-yy') )" CN.Execute SQL
LoginSucceeded = True
Me.Hide
Else
MsgBox "密码错误,请重新输入!", , "登录"
txtPassword.SetFocus
SendKeys "{Home}+{End}"
End If Else
MsgBox "无此用户代号!"
txtUserName.SetFocus
SendKeys "{Home}+{End}"
End If RS.Close
CN.Close
End Sub
----------------------------
程序一运行,当做到"CN.Execute SQL"这一条时,总是说"实时错误,-2147217900 (80040e14) ORA-00913 值过多"或“缺少右括号,括起来的串没有正常结束”,但是将sj这个字段去除后(即不写入时间记录),则完全正常,看来是这个时间格式有问题,有高手知道该怎么写吗?谢谢!
xm,char(8),not null
py,char(3),not null
sj,date
现在要用vb程序向该表写入一条记录,其中的sj字段就取当前的时间,源代码如下:
----------------------
Option ExplicitPublic LoginSucceeded As Boolean
Public CN As New ADODB.Connection, RS As New ADODB.Recordset
Public SQL As StringConst ConnString = "PROVIDER=MSDASQL;dsn=oracle;uid=system;pwd=***;database=***;"Private Sub cmdCancel_Click()
'设定全局变量为 false 来代表
'失败的登录
LoginSucceeded = False
Me.Hide
End SubPrivate Sub cmdOK_Click()
CN.Open ConnString
SQL = "SELECT * FROM sys.aaa"
SQL = SQL & "WHERE py = '" & txtUserName & "' "
RS.Open SQL, ConnString, , , adCmdText
If RS.EOF = False Then
'检查密码的正确性
If txtPassword = RTrim(RS("kl")) Then
FMMAIN.USERNAME = RS("xm")
SQL = SQL & " VALUES ( "
SQL = SQL & " '" & RS("xm") & "', "
SQL = SQL & " '" & txtUserName & "', "
SQL = SQL & " to_date('" & CStr(Date) & "','dd-mon-yy') )" CN.Execute SQL
LoginSucceeded = True
Me.Hide
Else
MsgBox "密码错误,请重新输入!", , "登录"
txtPassword.SetFocus
SendKeys "{Home}+{End}"
End If Else
MsgBox "无此用户代号!"
txtUserName.SetFocus
SendKeys "{Home}+{End}"
End If RS.Close
CN.Close
End Sub
----------------------------
程序一运行,当做到"CN.Execute SQL"这一条时,总是说"实时错误,-2147217900 (80040e14) ORA-00913 值过多"或“缺少右括号,括起来的串没有正常结束”,但是将sj这个字段去除后(即不写入时间记录),则完全正常,看来是这个时间格式有问题,有高手知道该怎么写吗?谢谢!
注意变量最好转换成字符串型
我现在将表中一个无关紧要的列删去,结果一切正常,源码如下:
-------------------- SQL = "insert into ykgl.aad values ( "
SQL = SQL & " to_date('" & CStr(Date) & "','yy-mm-dd') ,"
SQL = SQL & " '" & txtUserName & "')"
CN.Execute SQL
--------------------不过,还有一问题很奇怪,就是在SQL*plus中,用原来的语句写入记录却是可以的,换到VB6.0中却不行,怪!