Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
COURSE of the MONTH
13 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Are SQL Parameters compatible with .Net SQLDataAdapters?
Posted on 2012-12-26
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
- +1
5 Comments
Hi,
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
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
Active today
@igordevelop
@mpdillon
The problem as I see it--from the code you posted above--is that you have not associated the command with the DataAdapter. (Honestly, I don't think you even need the DataAdapter. See this article as to why.) Try assigning the command to the DataDapter:
You must first declare the SqlCommand and later pass the parameter.I don't think that is accurate. It shouldn't matter until you actually send the query to the server.
@mpdillon
The problem as I see it--from the code you posted above--is that you have not associated the command with the DataAdapter. (Honestly, I don't think you even need the DataAdapter. See this article as to why.) Try assigning the command to the DataDapter:
...
dataadapter.SelectCommand = MacCmd
dataadapter.Fill(dt)
...
Active 6 days ago
As above OR change your code to following
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"
Dim dataadapter As New SqlDataAdapter(ParmString, MacConn)
dataadapter.SelectCommand.Parameters.AddWithValue("@CusNo", CusNoString)
Dim dt As New DataTable
dataadapter.Fill(dt)
dataadapter.Dispose
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.
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month13 days, left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.