G
Guest
Can someone please tell me how I build an array with all my SQLParameters!
I want to do something like the example shown below... (" I know it doesn't
work!")
I would like to generate all the sql parameters on the fly if it is possible
as I have a long form with a load of textboxes that I want to input the
values of into a database!
Thanks for any help!
....example 1...
Sub UploadData(ByVal sender As Object, ByVal e As EventArgs)
Dim MyConn As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("AddMenu", MyConn)
cmd.CommandType = CommandType.StoredProcedure
Dim i As Integer = 10
Do
Dim Itemparam(i) As SqlParameter("@mItem" & (i),
SqlDbType.NVarChar, 255)
Dim Priceparam(i) As SqlParameter("@mPrice" & (i),
SqlDbType.NVarChar, 255)
Itemparam(i).Value = tbMenuItem1.Text
Priceparam(i).Value = tbMenuPrice1.Text
cmd.Parameters.Add(Itemparam(i))
cmd.Parameters.Add(Priceparam(i))
i = i + 1
Loop While i < 10
Dim Dateparam As New SqlParameter("@Date", SqlDbType.NVarChar, 255)
Dateparam.Value = tbDate.Text
cmd.Parameters.Add(Dateparam)
MyConn.Open()
cmd.ExecuteNonQuery()
MyConn.Close()
End Sub
....example 2...
Private Shared Function BuildSqlParamArray(ByVal ParmData() As ParmData)
As SqlParameter()
Dim arParms() As SqlParameter = New SqlParameter(ParmData.Length -
1) {}
Dim parm As ParmData
Dim i As Integer = 0
Dim myParam As SqlParameter
For Each parm In ParmData
myParam = New SqlParameter(parm.ParmName, parm.ParmType,
parm.ParmSize)
Select Case parm.ParmDirection
Case Direction.Input
myParam.Direction = ParameterDirection.Input
Case Direction.InputOutput
myParam.Direction = ParameterDirection.InputOutput
Case Direction.Output
myParam.Direction = ParameterDirection.Output
Case Direction.ReturnValue
myParam.Direction = ParameterDirection.ReturnValue
End Select
myParam.Value = parm.ParmValue
arParms(i) = myParam
i += 1
Next
Return arParms
End Function
I want to do something like the example shown below... (" I know it doesn't
work!")
I would like to generate all the sql parameters on the fly if it is possible
as I have a long form with a load of textboxes that I want to input the
values of into a database!
Thanks for any help!
....example 1...
Sub UploadData(ByVal sender As Object, ByVal e As EventArgs)
Dim MyConn As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("AddMenu", MyConn)
cmd.CommandType = CommandType.StoredProcedure
Dim i As Integer = 10
Do
Dim Itemparam(i) As SqlParameter("@mItem" & (i),
SqlDbType.NVarChar, 255)
Dim Priceparam(i) As SqlParameter("@mPrice" & (i),
SqlDbType.NVarChar, 255)
Itemparam(i).Value = tbMenuItem1.Text
Priceparam(i).Value = tbMenuPrice1.Text
cmd.Parameters.Add(Itemparam(i))
cmd.Parameters.Add(Priceparam(i))
i = i + 1
Loop While i < 10
Dim Dateparam As New SqlParameter("@Date", SqlDbType.NVarChar, 255)
Dateparam.Value = tbDate.Text
cmd.Parameters.Add(Dateparam)
MyConn.Open()
cmd.ExecuteNonQuery()
MyConn.Close()
End Sub
....example 2...
Private Shared Function BuildSqlParamArray(ByVal ParmData() As ParmData)
As SqlParameter()
Dim arParms() As SqlParameter = New SqlParameter(ParmData.Length -
1) {}
Dim parm As ParmData
Dim i As Integer = 0
Dim myParam As SqlParameter
For Each parm In ParmData
myParam = New SqlParameter(parm.ParmName, parm.ParmType,
parm.ParmSize)
Select Case parm.ParmDirection
Case Direction.Input
myParam.Direction = ParameterDirection.Input
Case Direction.InputOutput
myParam.Direction = ParameterDirection.InputOutput
Case Direction.Output
myParam.Direction = ParameterDirection.Output
Case Direction.ReturnValue
myParam.Direction = ParameterDirection.ReturnValue
End Select
myParam.Value = parm.ParmValue
arParms(i) = myParam
i += 1
Next
Return arParms
End Function