asked on
Are SQL Parameters compatible with .Net SQLDataAdapters?
I cannot get the following code to execute without this error. "Must declare the Scalar variable @CusNo".
When I remove the Parameter, @CusNo and directly substitute the value, the SQLDataAdapter does not fail. See below.
Is there a way to use Parameters with a SQLDataAdapter?
Thanks,
pat
With MacCmd
.Parameters.Clear()
.Parameters.AddWithValue("@CusNo", CusNoString)
End With
ParmString = "Select M.Cus_No, M.Cus_Name, A.IDNo From " & CompanyNameString & ".dbo.ARCusFil_SQL M " & _
"Inner Join Amatex.dbo.AMTransferHdr A " & _
"ON M.Cus_no = A.CusNo where CusNo = @CusNo " & _
"Order by A.IDNo Desc"
'
MacCmd.CommandText = ParmString
'
Dim dataadapter As New SqlDataAdapter(ParmString, MacConn)
Dim ds As New DataSet()
Dim dt As New DataTable
'
Application.DoEvents()
dataadapter.Fill(dt) '<== Fails here
CoConn.Close()
When I remove the Parameter, @CusNo and directly substitute the value, the SQLDataAdapter does not fail. See below.
ParmString = "Select M.Cus_No, M.Cus_Name, A.IDNo From " & CompanyNameString & ".dbo.ARCusFil_SQL M " & _
"Inner Join Amatex.dbo.AMTransferHdr A " & _
"ON M.Cus_no = A.CusNo where CusNo = '000000106800' " & _
"Order by A.IDNo Desc"
'
MacCmd.CommandText = ParmString
'
Dim dataadapter As New SqlDataAdapter(ParmString, MacConn)
Dim ds As New DataSet()
Dim dt As New DataTable
'
Application.DoEvents()
dataadapter.Fill(dt) '<== Does not fail when I use the actual variable value
CoConn.Close()
Is there a way to use Parameters with a SQLDataAdapter?
Thanks,
pat
.NET ProgrammingVisual Basic.NET
Last Comment
mpdillon
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Hi,
@kaufmed
The command is assigned in this line:
Dim dataadapter As New SqlDataAdapter(ParmString,
It is not necessary to do it again.
@kaufmed
The command is assigned in this line:
Dim dataadapter As New SqlDataAdapter(ParmString,
It is not necessary to do it again.
ASKER
Assigning the paramaters to the dataadapter works great.I thought that the Parameters were associated with the SQLCommand, MacCmd, but apparently the parameters must be assigned to the dataadapter.
Here is my final code.
MacCmd.CommandText = ParmString
'
Dim dataadapter As New SqlDataAdapter(ParmString,
Dim dt As New DataTable
With dataadapter
.SelectCommand.Parameters.
.SelectCommand.Parameters.
End With
'
Application.DoEvents()
dataadapter.Fill(dt) '<== Code no longer fails here
Thank you for your assistance.
pat
Here is my final code.
MacCmd.CommandText = ParmString
'
Dim dataadapter As New SqlDataAdapter(ParmString,
Dim dt As New DataTable
With dataadapter
.SelectCommand.Parameters.
.SelectCommand.Parameters.
End With
'
Application.DoEvents()
dataadapter.Fill(dt) '<== Code no longer fails here
Thank you for your assistance.
pat
You must first declare the SqlCommand and later pass the parameter. You were doing the reverse.
Try this, should work:
ParmString = "Select M.Cus_No, M.Cus_Name, A.IDNo From " & CompanyNameString & ".dbo.ARCusFil_SQL M " & _
"Inner Join Amatex.dbo.AMTransferHdr A " & _
"ON M.Cus_no = A.CusNo where CusNo = @CusNo " & _
"Order by A.IDNo Desc"
'
MacCmd.Parameters.Clear()
MacCmd.Parameters.AddWithV
MacCmd.CommandText = ParmString
'
Dim dataadapter As New SqlDataAdapter(ParmString,
Dim ds As New DataSet()
Dim dt As New DataTable
'
Application.DoEvents()
dataadapter.Fill(dt)
CoConn.Close()
Let me know if anything.
Regards,
Igor