Solved

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

Posted on 2016-09-14
4
48 Views
Last Modified: 2016-09-15
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.
0
Comment
Question by:Steve Williams
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
it_saige earned 400 total points
ID: 41798896
You don't need to re-execute your command query as the fill method on the dataadapter fills the datatable; e.g. -
Imports System.Data.SqlClient

Module Module1
	Sub Main()
		Dim connection As SqlConnection = Nothing
		Dim adapter As SqlDataAdapter = Nothing
		Dim command As SqlCommand = Nothing
		Dim table As DataTable = Nothing
		Try
			connection = New SqlConnection("Data Source=.;Initial Catalog=EE_Q28969899;Integrated Security=True")
			connection.Open()
			command = New SqlCommand("SELECT * FROM USERS WHERE USERNAME LIKE @USERNAME AND COMPUTERNAME LIKE @COMPUTERNAME", connection)
			command.Parameters.AddRange(New SqlParameter() _
								   { _
									   New SqlParameter("@USERNAME", SqlDbType.NVarChar) With {.Value = "%I%"}, _
									   New SqlParameter("@COMPUTERNAME", SqlDbType.NVarChar) With {.Value = "%E%"} _
								   })
			adapter = New SqlDataAdapter(command)
			table = New DataTable()
			table.Columns.AddRange(New DataColumn() {New DataColumn("ID"), New DataColumn("UserName"), New DataColumn("ComputerName")})
			adapter.Fill(table)
		Catch ex As Exception
		Finally
			If command IsNot Nothing Then command.Dispose()
			If adapter IsNot Nothing Then adapter.Dispose()
			If connection IsNot Nothing Then connection.Dispose()
		End Try
		For Each row As DataRow In table.Rows
			Console.WriteLine("ID = {0}; UserName = {1}; ComputerName = {2}", row("ID"), row("UserName"), row("ComputerName"))
		Next
		Console.ReadLine()
	End Sub
End Module

Open in new window

For the following table:Capture.JPGProduces the following output -Capture.JPG
-saige-
0
 
LVL 10

Assisted Solution

by:Duy Pham
Duy Pham earned 100 total points
ID: 41799032
That error is generated when you called 'da.Fill(dt)'. You create SqlDataAdapter from plain select statement but forgot to add necessary parameters (@UserName, @ComputerName).

Just change this line
Dim da As New SqlDataAdapter(SQL_Str, DBCon)

Open in new window

to
Dim da As New SqlDataAdapter(cmd)

Open in new window


And as @saige has pointed out, you don't need two lines
cmd.CommandText = SQL_Str
cmd.ExecuteNonQuery()

Open in new window

1
 
LVL 1

Author Closing Comment

by:Steve Williams
ID: 41799479
@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.
0
 
LVL 32

Expert Comment

by:it_saige
ID: 41799719
@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-
1

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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ā€¦
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 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

17 Experts available now in Live!

Get 1:1 Help Now