troubleshooting Question

Are SQL Parameters compatible with .Net SQLDataAdapters?

Avatar of mpdillon
mpdillon asked on
.NET ProgrammingVisual Basic.NET
5 Comments2 Solutions312 ViewsLast Modified:
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()

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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros