asked on

What is causing the "Must declare the scalar variable" Error when debugging code?

I'm having an issue trying to paramatize my code. When I run the following code I get this error:

"An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll. Additional information: Must declare the scalar variable "@UserName"."

Option Explicit On
Imports System.Management
Imports CarterOpenAPrint.GVar 'Global Variables Stored here.
Imports System.Data.SqlClient


Public Class frmPS
    Dim DBCon As SqlConnection

    Private Sub frmPrintSettings_Load(sender As Object, e As EventArgs) Handles Me.Load
        Show()
        BringToFront()
        Focus()

        'Runs sub to populate the combobox for cbPrinterName
        PrinterList()

        '# Populate the labels with Username 
        lblUName.Text = Gvar._UserName.ToString
        lblCName.Text = GVar._ComputerName.ToString

        '########################################################################
        '# This section creates a connection to the SQL Table "PrintSettings"   #
        '#  and then verifies if a ComputerName and UserName have Pre-existing  #
        '#  settings stored in the sql table.                                   #
        '########################################################################
        Dim cnStr As String
        cnStr = "Server=FMSERVER\EXTRAS;Database=eco;User ID=ForestGump;Password=Jenny"
        DBCon = New SqlConnection(cnStr)
        DBCon.Open()

        Try
            Dim SQL_Str As String = "SELECT * FROM PrintSettings WHERE UserName = @UserName AND ComputerName = @ComputerName"
            Dim dt As New DataTable
            Dim cmd As New SqlCommand(SQL_Str, DBCon)
            Dim da As New SqlDataAdapter(SQL_Str, DBCon)
            Dim unSearch As String = lblUName.Text
            Dim cnSearch As String = lblCName.Text

            dt.Columns.Add("Id")
            dt.Columns.Add("UserName")
            dt.Columns.Add("PrinterName")
            dt.Columns.Add("Rotation")
            dt.Columns.Add("NumOfCopies")
            dt.Columns.Add("RangeMode")
            dt.Columns.Add("RangeTo")
            dt.Columns.Add("RangeFrom")
            dt.Columns.Add("ComputerName")

            cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = unSearch
            cmd.Parameters.Add("@ComputerName", SqlDbType.NVarChar).Value = cnSearch

            da.Fill(dt)
            cmd.CommandText = SQL_Str
            cmd.ExecuteNonQuery()

            'Dim cnFound As String = dt.Rows(8)("ComputerName").ToString
            'If cnFound = Nothing Then
            '    Exit Sub
            'Else
            '    lblUName.Text = dt.Rows(1)("UserName").ToString

            '    cbPrinterName.SelectedText = dt.Rows(2)("PrinterName").ToString

            '    Dim dtRotation = dt.Rows(3)("Rotation")
            '    If dtRotation = 1 Then rbPortrait.Checked = True
            '    If dtRotation = 2 Then rbLandscape.Checked = True

            '    txtNumOfCopies = dt.Rows(4)("NumOfCopies")

            '    Dim dtRangeMode = dt.Rows(5)("RangeMode")
            '    Dim dtRangeTo = dt.Rows(6)("RangeTo").ToString
            '    Dim dtRangeFrom = dt.Rows(7)("RangeFrom").ToString
            '    If dtRangeMode = 0 Then rbPrintRangeAll.Checked = True
            '    If dtRangeMode = 1 Then rbPrintRangeCV.Checked = True
            '    If dtRangeMode = 2 Then rbPrintRangeCP.Checked = True
            '    If dtRangeMode = 3 Then
            '        rbPrintRangeFromTo.Checked = True
            '        txtPrintRangeFrom.Text = dtRangeFrom
            '        txtPrintRangeTo.Text = dtRangeTo
            '    End If
            '    lblCName.Text = cnFound
            'End If
        Catch ex As System.FormatException
        End Try
    End Sub
End Class

Open in new window

I commented a bunch of the code out because I was not done debugging it. I had this issue before but it was because I misspelled the parameters name. I don't get what is causing this issue this time because everything is spelled correctly from what I can see unless I'm blind to it.

I tried to keep all my variable declarations at the top of the code but some need to be declared after the fact. Does anyone have any idea why this is Throwing the "Must declare the scalar variable."? I appreciate any help you all may give me.

ASKER CERTIFIED SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Duy Pham

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Williams

ASKER

@it_Saige: Thanks for the heads up on using both items. I did not realize this and it was right in front of my face. I removed these two lines of code and it works like it should.

Dim da As New SqlDataAdapter(SQL_Str, DBCon)
da.Fill(dt)

@Duy Pham: You stated "That error is generated when you called 'da.Fill(dt)'."
This is true so I removed it and the variable declaration. Just as it_Saige had suggested.

You also stated this "You create SqlDataAdapter from plain select statement but forgot to add necessary parameters (@UserName, @ComputerName)."

This is not true as I did create them here are the two lines that show this.

cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = unSearch
cmd.Parameters.Add("@ComputerName", SqlDbType.NVarChar).Value = cnSearch

I appreciate both your effort, time and help so I awarded you both points.

Thanks, fella's the help is always appreciated.

it_saige

@Steve,

Glad we were able to help, but Duy is correct. You added the parameters to the command object, not to the dataadapter object. This is why Duy stated to change the constructor used to build the dataadapter object so that you would build the dataadapter using the command object instead of using the string statement to build the dataadapter.

Here is your original code with Duy's recommendations included:

Option Explicit On
Imports System.Management
Imports CarterOpenAPrint.GVar 'Global Variables Stored here.
Imports System.Data.SqlClient


Public Class frmPS
	Dim DBCon As SqlConnection

	Private Sub frmPrintSettings_Load(sender As Object, e As EventArgs) Handles Me.Load
		Show()
		BringToFront()
		Focus()

		'Runs sub to populate the combobox for cbPrinterName
		PrinterList()

		'# Populate the labels with Username 
		lblUName.Text = Gvar._UserName.ToString
		lblCName.Text = GVar._ComputerName.ToString

		'########################################################################
		'# This section creates a connection to the SQL Table "PrintSettings"   #
		'#  and then verifies if a ComputerName and UserName have Pre-existing  #
		'#  settings stored in the sql table.                                   #
		'########################################################################
		Dim cnStr As String
		cnStr = "Server=FMSERVER\EXTRAS;Database=eco;User ID=ForestGump;Password=Jenny"
		DBCon = New SqlConnection(cnStr)
		DBCon.Open()

		Try
			Dim SQL_Str As String = "SELECT * FROM PrintSettings WHERE UserName = @UserName AND ComputerName = @ComputerName"
			Dim dt As New DataTable
			Dim cmd As New SqlCommand(SQL_Str, DBCon)
			' Construct your adapter using the command object...
			Dim da As New SqlDataAdapter(cmd)
			Dim unSearch As String = lblUName.Text
			Dim cnSearch As String = lblCName.Text

			dt.Columns.Add("Id")
			dt.Columns.Add("UserName")
			dt.Columns.Add("PrinterName")
			dt.Columns.Add("Rotation")
			dt.Columns.Add("NumOfCopies")
			dt.Columns.Add("RangeMode")
			dt.Columns.Add("RangeTo")
			dt.Columns.Add("RangeFrom")
			dt.Columns.Add("ComputerName")

			cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = unSearch
			cmd.Parameters.Add("@ComputerName", SqlDbType.NVarChar).Value = cnSearch

			da.Fill(dt)

			'Dim cnFound As String = dt.Rows(8)("ComputerName").ToString
			'If cnFound = Nothing Then
			'    Exit Sub
			'Else
			'    lblUName.Text = dt.Rows(1)("UserName").ToString

			'    cbPrinterName.SelectedText = dt.Rows(2)("PrinterName").ToString

			'    Dim dtRotation = dt.Rows(3)("Rotation")
			'    If dtRotation = 1 Then rbPortrait.Checked = True
			'    If dtRotation = 2 Then rbLandscape.Checked = True

			'    txtNumOfCopies = dt.Rows(4)("NumOfCopies")

			'    Dim dtRangeMode = dt.Rows(5)("RangeMode")
			'    Dim dtRangeTo = dt.Rows(6)("RangeTo").ToString
			'    Dim dtRangeFrom = dt.Rows(7)("RangeFrom").ToString
			'    If dtRangeMode = 0 Then rbPrintRangeAll.Checked = True
			'    If dtRangeMode = 1 Then rbPrintRangeCV.Checked = True
			'    If dtRangeMode = 2 Then rbPrintRangeCP.Checked = True
			'    If dtRangeMode = 3 Then
			'        rbPrintRangeFromTo.Checked = True
			'        txtPrintRangeFrom.Text = dtRangeFrom
			'        txtPrintRangeTo.Text = dtRangeTo
			'    End If
			'    lblCName.Text = cnFound
			'End If
		Catch ex As System.FormatException
		End Try
	End Sub
End Class

Open in new window

And this represents another way of accomplishing the same task without using the command object:

Imports System.Management
Imports CarterOpenAPrint.GVar 'Global Variables Stored here.
Imports System.Data.SqlClient


Public Class frmPS
	Dim DBCon As SqlConnection

	Private Sub frmPrintSettings_Load(sender As Object, e As EventArgs) Handles Me.Load
		Show()
		BringToFront()
		Focus()

		'Runs sub to populate the combobox for cbPrinterName
		PrinterList()

		'# Populate the labels with Username 
		lblUName.Text = Gvar._UserName.ToString
		lblCName.Text = GVar._ComputerName.ToString

		'########################################################################
		'# This section creates a connection to the SQL Table "PrintSettings"   #
		'#  and then verifies if a ComputerName and UserName have Pre-existing  #
		'#  settings stored in the sql table.                                   #
		'########################################################################
		Dim cnStr As String
		cnStr = "Server=FMSERVER\EXTRAS;Database=eco;User ID=ForestGump;Password=Jenny"
		DBCon = New SqlConnection(cnStr)
		DBCon.Open()

		Try
			Dim SQL_Str As String = "SELECT * FROM PrintSettings WHERE UserName = @UserName AND ComputerName = @ComputerName"
			Dim dt As New DataTable
			Dim da As New SqlDataAdapter(SQL_Str, DBCon)
			Dim unSearch As String = lblUName.Text
			Dim cnSearch As String = lblCName.Text

			dt.Columns.Add("Id")
			dt.Columns.Add("UserName")
			dt.Columns.Add("PrinterName")
			dt.Columns.Add("Rotation")
			dt.Columns.Add("NumOfCopies")
			dt.Columns.Add("RangeMode")
			dt.Columns.Add("RangeTo")
			dt.Columns.Add("RangeFrom")
			dt.Columns.Add("ComputerName")

			da.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = unSearch
			da.Parameters.Add("@ComputerName", SqlDbType.NVarChar).Value = cnSearch

			da.Fill(dt)

			'Dim cnFound As String = dt.Rows(8)("ComputerName").ToString
			'If cnFound = Nothing Then
			'    Exit Sub
			'Else
			'    lblUName.Text = dt.Rows(1)("UserName").ToString

			'    cbPrinterName.SelectedText = dt.Rows(2)("PrinterName").ToString

			'    Dim dtRotation = dt.Rows(3)("Rotation")
			'    If dtRotation = 1 Then rbPortrait.Checked = True
			'    If dtRotation = 2 Then rbLandscape.Checked = True

			'    txtNumOfCopies = dt.Rows(4)("NumOfCopies")

			'    Dim dtRangeMode = dt.Rows(5)("RangeMode")
			'    Dim dtRangeTo = dt.Rows(6)("RangeTo").ToString
			'    Dim dtRangeFrom = dt.Rows(7)("RangeFrom").ToString
			'    If dtRangeMode = 0 Then rbPrintRangeAll.Checked = True
			'    If dtRangeMode = 1 Then rbPrintRangeCV.Checked = True
			'    If dtRangeMode = 2 Then rbPrintRangeCP.Checked = True
			'    If dtRangeMode = 3 Then
			'        rbPrintRangeFromTo.Checked = True
			'        txtPrintRangeFrom.Text = dtRangeFrom
			'        txtPrintRangeTo.Text = dtRangeTo
			'    End If
			'    lblCName.Text = cnFound
			'End If
		Catch ex As System.FormatException
		End Try
	End Sub
End Class

Open in new window

-saige-

What is causing the &quot;Must declare the scalar variable&quot; Error when debugging code?

What is causing the "Must declare the scalar variable" Error when debugging code?