Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!
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)
...
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
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month12 days, 15 hours left to enroll
650 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.