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

[sexroute]

下面这个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

[sexroute]

忘了说了，数据库是access

• 打赏
• 举报

回复 赞

[sexroute]

你们说得当然可以，但是数据库来说效率低，我以前做c++,java的时候都要preparestatement的

• 打赏
• 举报

回复 赞

[wxrwan]

为了方便调试,我一直都是没有用参数

• 打赏
• 举报

回复 赞

[vansoft]

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

• 打赏
• 举报

回复 赞

[faysky2]

用这种方式试试，看结果是否一样：

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

• 打赏
• 举报

回复 赞

[of123]

直接拼装 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 & "'"

• 打赏
• 举报

回复 赞

[sexroute]

我把你说的那个方法封装（针对不同的数据库的考虑）了一下，因该是一样的，奇怪的就是delete可以，select可以，update就不行，如果改成直接拼接字符串就可以，不知道vb的access字符串的preparestatement中要怎么写

• 打赏
• 举报

回复 赞