请问这个动态sql应该怎么写,

sexroute 2006-03-16 11:06:52
下面这个sql执行的结果总是不对,似乎因为pk的值不对,但是如果我将下面的pk=?写成死的pk='xxxxxxx'就可以,pk在数据库中的类型是字符串
Dim strSql As String
Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
mName = Me.mName
Set conn = getConnection
conn.BeginTrans
Set cmd.ActiveConnection = conn

strSql = "update Loop set name=? ,modifiedTime=?,createTime=?,pk_Project=?,description=? where pk = ?"
cmd.CommandText = strSql
Set prm = createCMDParameter(cmd, "@pk", Me.pk)
cmd.Parameters.Append prm
Set prm = createCMDParameter(cmd, "@name", Me.mName)
cmd.Parameters.Append prm

Call setParameter(cmd, "@modifiedTime", Now, adDBTime)
Call setParameter(cmd, "@createTime", Me.createTime, adDBTime)
Call setParameter(cmd, "@pk_Project", Me.pk_Project)

Set prm = createCMDParameter(cmd, "@description", Me.description)
cmd.Parameters.Append prm
cmd.Execute
conn.CommitTrans
conn.Close
Set conn = Nothing
Set cmd = Nothing
Set prm = Nothing
...全文
80 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
sexroute 2006-03-18
我把你说的那个方法封装(针对不同的数据库的考虑)了一下,因该是一样的,奇怪的就是delete可以,select可以,update就不行,如果改成直接拼接字符串就可以,不知道vb的access字符串的preparestatement中要怎么写
回复
of123 2006-03-17
直接拼装 SQL 字符串,不要用参数。这样还方便调试,因为可以看到实际得到的 SQL 语句。

strSql = "update Loop set [name]='" & Me.mName & "',modifiedTime=#" & FORMAT(Now, "yyyy-mm-dd") & "#,createTime=#" & FORMAT(Me.createTime,"yyyy-mm-dd") & "#, pk_Project='" & Me.pk_Project & "',description='" & Me.Description & "' where pk ='" & Me.pk & "'"
回复
faysky2 2006-03-17
用这种方式试试,看结果是否一样:

Dim strSql As String
Dim conn As ADODB.Connection

Set conn = getConnection
conn.BeginTrans

strSql = "update Loop set name='" & Me.mName & "',modifiedTime=#" & Now & "#,createTime=#" & Me.createTime & "#,pk_Project='" & Me.pk_Project & "',description='" & Me.Description & "' where pk ='" & Me.pk & "'"

conn.Execute strSql
conn.CommitTrans
conn.Close
Set conn = Nothing
回复
vansoft 2006-03-17
Dim strSql As String
Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
mName = Me.mName
Set conn = getConnection
conn.BeginTrans
Set cmd.ActiveConnection = conn

strSql = "update Loop set name=? ,modifiedTime=?,createTime=?,pk_Project=?,description=? where pk = ?"
cmd.CommandText = strSql

'有幾個問號,寫幾行,括號內數字從0開始
cmd.Parameters(0)=值
......

cmd.Execute
conn.CommitTrans
conn.Close
Set conn = Nothing
Set cmd = Nothing
回复
wxrwan 2006-03-17
为了方便调试,我一直都是没有用参数
回复
sexroute 2006-03-17
你们说得当然可以,但是数据库来说效率低,我以前做c++,java的时候都要preparestatement的
回复
sexroute 2006-03-16
忘了说了,数据库是access
回复
相关推荐
发帖

1188

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
帖子事件
创建了帖子
2006-03-16 11:06
社区公告
暂无公告