| 以文本方式查看主题 - Foxtable(狐表) (http://foxtable.net/bbs/index.asp) -- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2) ---- SQLCommand 的参数化设置 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=170641) |
| -- 作者：qq252476275 -- 发布时间：2021/8/3 8:19:00 -- SQLCommand 的参数化设置 请教下，如何使用 Parameters.add 增加空值， 如字符串， 日期 确认数:0 确认时间: 主管人员: 修改者:admin 修改时间:2021-08-03 08:00:23 以下内容为程序代码: 1 ElseIf dt.DataCols(strName).IsDate Then 2 If strValue = "" Then 3 cmd.Parameters.Add("@" & strName , "null") 4 Else 5 If cmd.C Then 6 cmd.Parameters.Add("@" & strName , "#" & strValue & "#") 7 Else 8 cmd.Parameters.Add("@" & strName , strValue) 9 End If 10 End If 11 12 \' output.show(strName) 13 Functions.Execute("showMsg2Form1", strName & ":" & strValue ) 14 cmd.CommandText = cmd.CommandText & strName & " = ? ," 15 其中确认时间修改为 \'2022-02-02 02:02:02\' 主管人员修改为 \'xxxx\' 可以修改成功， 如果是最上面的空白， 则修改不成功。也没有错误提示 |
| -- 作者：qq252476275 -- 发布时间：2021/8/3 8:20:00 -- 参数化的更新语句 |
| -- 作者：有点蓝 -- 发布时间：2021/8/3 8:47:00 -- 贴出完整代码，另外请不要以这种带行号的方式发代码，和普通文字一样发上来即可。 另外看看：http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&Id=69632
|
| -- 作者：qq252476275 -- 发布时间：2021/8/3 10:19:00 -- 后台代码： Case "update" \' 修改记录 Dim strName As String Dim strValue As String If strCcode") = "401" obj("ErrInfo") = "update未指定content参数" Else Dim jt As jToken = jObject.Parse(strContent) \' 更新内容转为对象 \'生成datatable以便处理日期列 Dim cns As String \'更新用到的列名 \' output.Show("ok0") For Each jp As jproperty In jt \' Output.Show(jp.ToString()) strName = jp.name.ToString If strName = "id" And cmd.ConnectionName <> "dd" Then cns = cns & "_identify" & "," Else cns = cns & strName & "," End If Next cmd.CommandText = "sel ect " & cns.TrimEnd(",") & " from " & strTableName \' 不需要数据的空表 If cmd.C Then cmd.CommandText = cmd.CommandText & " where false" Else cmd.CommandText = cmd.CommandText & " where _identify = 0" End If \'output.show(cmd.CommandText) Dim dt As DataTable = cmd.ExecuteReader \'output.Show("ok1") Dim bHaveModifyTime As Boolean bHaveModifyTime = False \' content中是否包含修改时间字段 |
| -- 作者：qq252476275 -- 发布时间：2021/8/3 10:19:00 -- If nVersion = 2 Then cmd.CommandText = "Upda te {" & strTableName & "} set " For Each jp As JProperty In jt strName = jp.name.ToString strValue = jp.value.ToString If strName = "id" Then If cmd.C Then cn = "id=" & strValue Else cn = "_identify = " & strValue End If ElseIf dt.DataCols(strName).IsDate Then If strValue = "" Then cmd.Parameters.Add("@" & strName , "null") Else If cmd.C Then cmd.Parameters.Add("@" & strName , "#" & strValue & "#") Else cmd.Parameters.Add("@" & strName , strValue) End If End If \' output.show(strName) Functions.Execute("showMsg2Form1", strName & ":" & strValue ) cmd.CommandText = cmd.CommandText & strName & " = ? ," ElseIf strName = "修改时间" Then cmd.Parameters.Add("@" & strName , strValue) bHaveModifyTime = True \' output.show(strName) Functions.Execute("showMsg2Form1", strName & ":" & strValue ) cmd.CommandText = cmd.CommandText & strName & " = ? ," Else cmd.Parameters.Add("@" & strName , strValue) \' output.show(strName) Functions.Execute("showMsg2Form1", strName & ":" & strValue ) cmd.CommandText = cmd.CommandText & strName & " = ? ," End If Next
|
| -- 作者：qq252476275 -- 发布时间：2021/8/3 10:20:00 -- If strTableName = "消息" And not e.Values("content").Contains("已读人") Then cmd.CommandText = cmd.CommandText & " time = ?" cmd.Parameters.Add("@time" , "#" & strValue & "#") output.show("time") End If If bHaveModifyTime Then cmd.CommandText = cmd.CommandText.trimEnd(",") Else cmd.CommandText = cmd.CommandText & "[修改时间] = ?" cmd.Parameters.Add("@修改时间",time) Functions.Execute("showMsg2Form1", "修改时间" & ":" & time) End If If cn <> "" Then cmd.CommandText = cmd.CommandText & " where " & cn Else If strTj <> "" Then cmd.CommandText = cmd.CommandText & " where " & strTj End If End If strSQL = cmd.CommandText \'Output.Show(strSQL) Functions.AsyncExecute("showMsg2Form1", strSQL) cmd.ExecuteNonQuery
|
| -- 作者：qq252476275 -- 发布时间：2021/8/3 10:20:00 -- Else \'再次遍历jt对象以生成更新内容 For Each jp As jproperty In jt strName = jp.name.ToString strValue = jp.value.ToString If strName = "id" Then \'如果是id列,就拼接成更新条件 If cmd.C Then cn = "id=" & strValue Else cn = "_identify = " & strValue End If ElseIf dt.DataCols(strName ).IsDate Then \'如果是日期类型的列 If strValue = "" Then \'日期为空时写入null cv = cv & strName & "=null," Else \'日期不为空时,用#号连接 If cmd.C Then cv = cv & strName & "=#" & strValue & "#," Else cv = cv & strName & "=\'" & strValue & "\'," End If End If ElseIf strName = "修改时间" Then cv = cv & "[修改时间]=\'" & time & "\'," bHaveModifyTime = True Else cv = cv & "[" & strName & "]=\'" & strValue.Replace("\'","\'\'") & "\'," End If Next \'Output.show("ok2") If strTableName= "消息" And Not e.Values("content").Contains("已读人") Then cv = cv & "[time]=#" & time & "#," \'记录消息发布或更新时间 End If If bHaveModifyTime Then cmd.CommandText = "up date " & strTableName & " set " & cv.TrimEnd(",") Else cmd.CommandText = "up date " & strTableName & " set " & cv & "[修改时间] = \'" & time & "\'" End If If cn <> "" Then cmd.CommandText = cmd.CommandText & " where " & cn Else If strTj <> "" Then cmd.CommandText = cmd.CommandText & " where " & strTj End If End If strSQL = cmd.CommandText \'Output.Show(strSQL) Functions.AsyncExecute("showMsg2Form1", strSQL) cmd.ExecuteNonQuery End If End If
|
| -- 作者：qq252476275 -- 发布时间：2021/8/3 10:20:00 -- 前台代码： // 取消确认 $(\'#unConfirm\').click(function(){
// 获取哪些被选中
var cbs = $(\'input[id^=checkbox]\');
var partIDs = "";
$.each(cbs , function(index, item){
if ( item.checked ){
// 找出技术物量的ID值
var sID = item.getAttribute("id");
sID = parseInt(sID.replace(\'checkbox\',\'\'));
partIDs += (sID + ",");
}
});
// partIDs = partIDs.substring(0,partIDs.length - 1);
// console.log("_identify in (" + partIDs + " 0 )");
admin.ajax({
url : \'aud\',
data:{
dataSource : \'foxData\',
tablename : \'PGGL_workDis\',
reqType : \'update\',
content : JSON.stringify({
确认数 : 0,
确认时间: \'2022-02-02 02:02:02\',
主管人员: \'\',
修改者 : strUser
}),
tj : "_identify in (" + partIDs + " 0 )",
},
dataType : \'json\',
success : function(res){
console.log(res);
if( res.code == 0 ){
layer.msg("取消完成！");
}
$(\'#search\').click();
}
}); });
|
| -- 作者：有点蓝 -- 发布时间：2021/8/3 10:31:00 -- 空值使用nothing If strValue = "" Then cmd.Parameters.Add("@" & strName , nothing) |