Solved

Are SQL Parameters compatible with .Net SQLDataAdapters?

Posted on 2012-12-26
5
274 Views
Last Modified: 2012-12-28
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
0
Comment
Question by:mpdillon
5 Comments
 
LVL 1

Expert Comment

by:igordevelop
Comment Utility
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
0
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 250 total points
Comment Utility
@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

0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 250 total points
Comment Utility
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

0
 
LVL 1

Expert Comment

by:igordevelop
Comment Utility
Hi,

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

It is not necessary to do it again.
0
 

Author Closing Comment

by:mpdillon
Comment Utility
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
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now