Add data type handling in /include/library/db.asp, with source code

[GoogleCodeExporter]

Function Update(Table,Params,Where)
        Dim sqlCmd, parameteres, oParams
        Dim iName
        sqlCmd = "Set nocount on" & vbCrlf
        sqlCmd = sqlCmd & "Update "&Table&" set "
        parameteres = " "
        set objRec=ExecuteRecordSet("select top 0 * from " & table 
, null)
        Set oParams = Server.CreateObject("Scripting.Dictionary")
        If Not IsNull(params) Then
            For Each iName in params
                If InStr(iName,"#") > 0 Then
                    params.Key(iName) = 
Replace(iName,"#","")
                    iName = Replace(iName,"#","")
                    sqlCmd = sqlCmd & iName & "=" & 
iName & " + @" & iName & ","
                Else
                    sqlCmd = sqlCmd & iName & "=@" & 
iName & ","
                End If

                select case objRec(replace(iName, "@", 
"")).type
                case 3, 20, 17,2
                dtype = " int "
                case 4, 5, 6, 14, 131
                dtype = " decimal(20,15) "
                case 135
                dtype = " datetime "
                case 204, 128,205
                dtype = " binary "
                case 11

                dtype = " bit "
                case 129, 130, 202, 200, 201, 203
                dtype = " varchar(8000) "
                case else
                dtype = " varchar(8000) "
                end select

                parameteres = parameteres & "@" & iName & 
dtype & ","
            Next
        End If
        sqlCmd = Left(sqlCmd,Len(sqlCmd)-1)
        If Trim(Where) <> "" Then sqlCmd=sqlCmd&" Where "&Where&""
        sqlCmd = sqlCmd & vbCrlf & "select CAST(IsNull(@@ROWCOUNT,-
100) as int)"
        parameteres = Left(parameteres,Len(parameteres)-1)
        oParams.Add "@stmt",sqlCmd
        oParams.Add "@parameters",parameteres
        If Not IsNull(Params) Then
            For Each iName in Params
                oParams.Add "@"&iName,Params(iName)&""
            Next
        End If
        Update = Me.ExecuteScalar("sp_executesql",oParams)
        Set oParams = Nothing
    End Function

Original issue reported on code.google.com by mannch...@gmail.com on 21 Oct 2009 at 10:55

[GoogleCodeExporter]

Function Update(Table,Params,Where)
Dim sqlCmd, parameteres, oParams
Dim iName
sqlCmd = "Set nocount on" & vbCrlf
sqlCmd = sqlCmd & "Update "&Table&" set "
parameteres = " "
set objRec=ExecuteRecordSet("select top 0 * from " & table , null)
Set oParams = Server.CreateObject("Scripting.Dictionary")
If Not IsNull(params) Then
For Each iName in params
If InStr(iName,"#") > 0 Then
params.Key(iName) = Replace(iName,"#","")
iName = Replace(iName,"#","")
sqlCmd = sqlCmd & iName & "=" & iName & " + @" & iName & ","
Else
sqlCmd = sqlCmd & iName & "=@" & iName & ","
End If

select case objRec(replace(iName, "@", "")).type
case 3, 20, 17,2
dtype = " int "
case 4, 5, 6, 14, 131
dtype = " decimal(20,15) "
case 135
dtype = " datetime "
case 204, 128,205
dtype = " binary "
case 11

dtype = " bit "
case 129, 130, 202, 200, 201, 203
dtype = " varchar(8000) "
case else
dtype = " varchar(8000) "
end select

parameteres = parameteres & "@" & iName & dtype & ","
Next
End If
sqlCmd = Left(sqlCmd,Len(sqlCmd)-1)
If Trim(Where) <> "" Then sqlCmd=sqlCmd&" Where "&Where&""
sqlCmd = sqlCmd & vbCrlf & "select CAST(IsNull(@@ROWCOUNT,-100) as int)"
parameteres = Left(parameteres,Len(parameteres)-1)
oParams.Add "@stmt",sqlCmd
oParams.Add "@parameters",parameteres
If Not IsNull(Params) Then
For Each iName in Params
oParams.Add "@"&iName,Params(iName)&""
Next
End If
Update = Me.ExecuteScalar("sp_executesql",oParams)
Set oParams = Nothing
End Function

Original comment by mannch...@gmail.com on 21 Oct 2009 at 10:55

[GoogleCodeExporter]

Must add?
Why？

Original comment by wuchaoboy on 27 Oct 2009 at 2:51

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I love your framework very much , but error happens when I update a datetime 
field
and decimal field....

btw, 你的框架最易抄, 關於update, 個人認為EASP造的較準, 
因為考慮到datatype.

Original comment by mannch...@gmail.com on 27 Oct 2009 at 11:31

[GoogleCodeExporter]

datetime和decimal的话也不会出错呀，当然如你那样的处理在内��
�上更省。

确实这样的框架很容易抄袭，其实我觉得都还不算是框架，��
�在开发的时候我的想法是每个类都可以独立出来
单独使用，不需要依靠这个框架，使用更灵活。

Original comment by wuchaoboy on 28 Oct 2009 at 1:07

[GoogleCodeExporter]

对了，如果您是28号前下载的3.0版本，麻烦重新下载一下，之
前打包时，里面有些错误。

Original comment by wuchaoboy on 28 Oct 2009 at 1:08

[GoogleCodeExporter]

我最想用的其實是dynamic stored procedure. 
例如 

因為工作需要, 我將會加一個executeStoredProc 在db.asp. 
用法大概是這樣的.
set dbParam = newParam("@x" 1)
dbParam.add "@y" 2
dbParam.add "@z" 3
set objDB = new cls_Db
objDB.ExecuteSP("select * from table where columna in (@x, @y) and columnc=@z", 
dbParam)
我試過用.

objDB.ExecuteRecordSet("select * from table where columna in (?, ?) and 
columnc=?", 
dbParam)
不行.

Original comment by mannch...@gmail.com on 28 Oct 2009 at 2:04

[GoogleCodeExporter]

这个直接用obj.db.Exec就可以了
如果你需要对@x和@y做类型限定的话，可以在之前用obj.rq(0,@x,0
,0)来做处理。
rq的方法是对传入参数进行类型转换及默认值设定的操作，在
Common.asp里定义的。

Original comment by wuchaoboy on 29 Oct 2009 at 3:29