น่าจะมี Parameters ที่เป็น INT แต่คุณส่ง String เข้าไปครับ
Date :
2018-12-08 20:09:33
By :
mr.win
No. 2
Guest
บรรทัดที่ 415 CInt(txtQty.Text)
และจริงฯ ควรจะใช้แบบนี้
Code (VB.NET)
Public Shared Sub AddWithValue(ByRef cmd As DbCommand, ByVal pName As String, ByVal pValue As Object)
Dim Param = cmd.CreateParameter()
Param.ParameterName = pName
Param.Value = pValue
cmd.Parameters.Add(Param)
End Sub
ตัวอย่างการใช้งาน Code (VB.NET)
''' <summary>
''' ลบข้อมูลครั้งละหลายฯระเบียน
''' </summary>
''' <param name="lstCmd"></param>
''' <param name="values"></param>
''' <remarks></remarks>
Public Shared Sub SetDeleteCommand(ByRef lstCmd As List(Of lstDbCommand), ByVal values As List(Of msrB_S_Rate))
For Each r In values
c = New lstDbCommand
c.CmdText = "Delete From B_S_RATE Where RECORD_NUMBER = @RECORD_NUMBER"
c.CmdParameter = New List(Of DbParameter) From {BuildParameter("@RECORD_NUMBER", r.RECORD_NUMBER)}
lstCmd.Add(c)
Next
End Sub
''' <summary>
''' ลบข้อมูลครั้งละหนึ่งระเบียน
''' </summary>
''' <param name="lstCmd"></param>
''' <param name="value"></param>
''' <remarks></remarks>
Public Shared Sub SetDeleteCommand(ByRef lstCmd As List(Of lstDbCommand), ByVal value As msrB_S_Rate)
c = New lstDbCommand
c.CmdText = "Delete From B_S_RATE Where RECORD_NUMBER = @RECORD_NUMBER"
c.CmdParameter = New List(Of DbParameter) From {BuildParameter("@RECORD_NUMBER", value.RECORD_NUMBER)}
lstCmd.Add(c)
End Sub
''' <summary>
''' Build Parameter
''' </summary>
''' <param name="pName"></param>
''' <param name="pValue"></param>
''' <param name="pvdStr"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function BuildParameter(ByVal pName As String, pValue As Object, Optional ByVal pvdStr As String = "") As DbParameter
Dim dbParam As DbParameter = Nothing
If pvdStr = String.Empty Then
pvdStr = xxxProviderName
End If
Select Case pvdStr
Case "System.Data.SqlClient"
'2555-01-12 แก้ไข .Value = pValue --> If (pValue Is Nothing, DBNull.Value, pValue)
dbParam = New SqlParameter() With {.ParameterName = pName, .Value = If(pValue Is Nothing, DBNull.Value, pValue)}
Case "System.Data.OracleClient", "Oracle.DataAccess.Client", "Oracle.ManagedDataAccess.Client"
'dbParam = New OracleParameter() With {.ParameterName = pName, .Value = pValue}
'2561-02-07 แก้ไข .Value = pValue --> If (pValue Is Nothing, DBNull.Value, pValue)
dbParam = New OracleParameter() With {.ParameterName = pName, .Value = If(pValue Is Nothing, DBNull.Value, pValue)}
Case "System.Data.MySqlClient"
dbParam = New MySqlParameter() With {.ParameterName = pName, .Value = pValue}
Case "FirebirdSql.Data.FirebirdClient"
dbParam = New FbParameter() With {.ParameterName = pName, .Value = If(pValue Is Nothing, DBNull.Value, pValue)}
Case "System.Data.SQLite"
'2558-07-24 แก้ไข .Value = pValue --> If (pValue Is Nothing, DBNull.Value, pValue)
dbParam = New SQLiteParameter() With {.ParameterName = pName, .Value = pValue}
Case "System.Data.OleDb"
dbParam = New OleDbParameter With {.ParameterName = pName, .Value = pValue}
Case "System.Data.Odbc"
dbParam = New OdbcParameter With {.ParameterName = pName, .Value = pValue}
Case "System.Data.VFPClient" 'FastDBClient (www.codeplex.com)
'
Case "System.Data.DB2Client"
'
Case Else
'Not Implement yet.
End Select
Return dbParam
End Function
Date :
2018-12-08 21:38:39
By :
หน้าฮี
No. 4
Guest
แถมให้อีกอันหนึ่ง For All RDBMS
--- รู้ไว้ใช่ว่าใส่บ่าแบกหาม
Code (VB.NET)
''' <summary>
''' Fixed Parameter for All RDBMS
''' </summary>
''' <param name="strSQL"></param>
''' <param name="pvdStr"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function FixedParam(ByVal strSQL As String, Optional ByVal pvdStr As String = "") As String
If pvdStr = String.Empty Then
pvdStr = xxxProviderName
End If
'http://msdn.microsoft.com/en-us/library/30wbz966(v=vs.110).aspx
'Change (@\w*) TO (@\w+) 2010-08-20
'* = Zero or more
'+ = One or more
'? = Zero or one
'"(@\w+^')"
'^ Match the beginning of the input string.
Select Case pvdStr
Case "System.Data.SqlClient", "System.Data.MySqlClient", "System.Data.DB2Client", "FirebirdSql.Data.FirebirdClient", "System.Data.SQLite"
Return strSQL
Case "System.Data.OracleClient", "Oracle.ManagedDataAccess.Client", "Oracle.DataAccess.Client"
strSQL = Regex.Replace(strSQL, "(@\w+)", Function(m) ":" & m.Value.Replace(" ", "").Substring(1)) ':paramName
Case "System.Data.OleDb", "System.Data.Odbc", "System.Data.VFPClient"
strSQL = Regex.Replace(strSQL, "(@\w+)", Function(m) "?")
Case Else
strSQL = Regex.Replace(strSQL, "(@\w+)", Function(m) "Select/Insert/Update #Error DbHelper")
End Select
'DB2 ใช้ได้ทั้งสองแบบ @ หรือ ? (Named parameters & Positioned Parameters)
'http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.swg.im.dbclient.adonet.ref.doc/doc/DB2CommandClassParametersProperty.html
Return strSQL
End Function
Date :
2018-12-08 21:46:20
By :
หน้าฮี
No. 5
Guest
Code (C#)
/// <summary>
/// ''' Fixed Parameter All RDBMS
/// ''' </summary>
/// ''' <param name="strSQL"></param>
/// ''' <param name="pvdStr"></param>
/// ''' <returns></returns>
/// ''' <remarks></remarks>
public static string FixedParam(string strSQL, string pvdStr = "")
{
if (pvdStr == string.Empty)
pvdStr = xxxProviderName;
// http://msdn.microsoft.com/en-us/library/30wbz966(v=vs.110).aspx
// Change (@\w*) TO (@\w+) 2010-08-20
// * = Zero or more
// + = One or more
// ? = Zero or one
// "(@\w+^')"
// ^ Match the beginning of the input string.
switch (pvdStr)
{
case "System.Data.SqlClient":
case "System.Data.MySqlClient":
case "System.Data.DB2Client":
case "FirebirdSql.Data.FirebirdClient":
case "System.Data.SQLite":
{
return strSQL;
//break;
}
case "System.Data.OracleClient":
case "Oracle.ManagedDataAccess.Client":
case "Oracle.DataAccess.Client":
{
strSQL = Regex.Replace(strSQL, @"(@\w+)", m => ":" + m.Value.Replace(" ", "").Substring(1)); // :paramName
break;
}