Are SQL Parameters compatible with .Net SQLDataAdapters?

mpdillon
mpdillon used Ask the Experts™
on
I cannot get the following code to execute without this error. "Must declare the Scalar variable @CusNo".
           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()

Open in new window


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()

Open in new window


Is there a way to use Parameters with a SQLDataAdapter?

Thanks,
pat
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
igordevelop

Commented:
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.AddWithValue("@CusNo", CusNoString)
             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)
            CoConn.Close()


Let me know if anything.

Regards,
Igor
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015
Commented:
@igordevelop

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)

...

Open in new window

Most Valuable Expert 2012
Top Expert 2014
Commented:
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

Open in new window

igordevelop

Commented:
Hi,

@kaufmed
The command is assigned in this line:
Dim dataadapter As New SqlDataAdapter(ParmString, MacConn)

It is not necessary to do it again.
mpdillon

Author

Commented:
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, MacConn)
            Dim dt As New DataTable
            With dataadapter
                .SelectCommand.Parameters.Clear()
                .SelectCommand.Parameters.AddWithValue("@CusNo", CusNoString)
            End With
            '
            Application.DoEvents()
            dataadapter.Fill(dt)  '<== Code no longer fails here

Thank you for your assistance.
pat

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial