Must declare the scalar variable

M

m1c

Crap - getting the SOS. This is an old post, so I'll try to recycle it
as new with hopefully enough details.
Here's the relevant code causing this error
SQLOfficeData.ExecuteNonQuery()<-----ERROR [42000] [Microsoft][SQL
Native Client][SQL Server]Must declare the scalar variable
"@dbOfficeName".
at the end of all of this:

Dim dbConn = New
System.Data.Odbc.OdbcConnection("Dsn=Election;description=Election;trusted_­
connection=Yes;app=Microsoft®
Visual Studio® 2008;wsid=M1CVISTA1;database=Election")

Dim CString As String
Dim OfficeName = New OdbcParameter("@dbOfficeName",
OdbcType.Char)
Dim CandidateOfficeName = New
OdbcParameter("@dbCandidateOfficeName", OdbcType.Char)
Dim CandidateName = New OdbcParameter("@dbCandidateName",
OdbcType.Char)
Dim CandidateNumeric = New
OdbcParameter("@dbCandidateNumeric", OdbcType.Int)
Dim CandidateOfficeParty = New
OdbcParameter("@dbCandidateOfficeParty", OdbcType.Char)
Dim CandidatePicture = New
OdbcParameter("@dbCandidatePicture", OdbcType.Char)


Dim CCount As Integer = 0
Dim SQLReturn As Integer = 0


OfficeName.Value = "."
CandidateOfficeName.Value = "."
CandidateName.Value = "."
CandidateNumeric.Value = 0
CandidateOfficeParty.Value = "."
CandidatePicture.Value = "."


dbConn.Open()


Dim SQLOfficeData = New OdbcCommand("DECLARE OfficeData
CURSOR
FOR SELECT @dbOfficeName FROM Office ORDER BY @dbOfficeName", dbConn)
Dim SQLOfficeDataOpen = New OdbcCommand("OPEN OfficeData",
dbConn)
Dim SQLOfficeDataFetch = New OdbcCommand("FETCH OfficeData
INTO @dbOfficeName", dbConn)
Dim SQLOfficeDataClose = New OdbcCommand("CLOSE OfficeData",
dbConn)


Dim SQLCandidateData = New OdbcCommand("DECLARE CandidateData
CURSOR FOR SELECT * FROM Candidate ORDER BY CandidateOfficeName",
dbConn)
Dim SQLCandidateDataFetch = New OdbcCommand("FETCH
CandidateData INTO @dbCandidateName, @dbCandidateOfficeName,
@dbCandidateNumeric, @dbCandidateOfficeParty, @dbCandidatePicture",
dbConn)
Dim SQLCandidateDataOpen = New OdbcCommand("OPEN
CandidateData", dbConn)
Dim SQLCandidateDataClose = New OdbcCommand("CLOSE
CandidateData", dbConn)


SQLOfficeData.Parameters.Add(OfficeName)


SQLCandidateData.Parameters.Add(CandidateOfficeName)
SQLCandidateData.Parameters.Add(CandidateName)
SQLCandidateData.Parameters.Add(CandidateNumeric)
SQLCandidateData.Parameters.Add(CandidateOfficeParty)
SQLCandidateData.Parameters.Add(CandidatePicture)


SQLOfficeData.ExecuteNonQuery()<-----ERROR [42000] [Microsoft][SQL
Native Client][SQL Server]Must declare the scalar variable
"@dbOfficeName".
 
C

Cor Ligthert[MVP]

m1c,

Why are you showing us a piece of program filled with all kind of crap not
belonging to the problem and then ask us to investigate that what is your
problem.

Remove all the code that is not for the SQLOfficeData command and then
try it again, as it still does now work here, then try it again

Cor

"m1c" <[email protected]> schreef in bericht
Crap - getting the SOS. This is an old post, so I'll try to recycle it
as new with hopefully enough details.
Here's the relevant code causing this error
SQLOfficeData.ExecuteNonQuery()<-----ERROR [42000] [Microsoft][SQL
Native Client][SQL Server]Must declare the scalar variable
"@dbOfficeName".
at the end of all of this:

Dim dbConn = New
System.Data.Odbc.OdbcConnection("Dsn=Election;description=Election;trusted_­
connection=Yes;app=Microsoft®
Visual Studio® 2008;wsid=M1CVISTA1;database=Election")

Dim CString As String
Dim OfficeName = New OdbcParameter("@dbOfficeName",
OdbcType.Char)
Dim CandidateOfficeName = New
OdbcParameter("@dbCandidateOfficeName", OdbcType.Char)
Dim CandidateName = New OdbcParameter("@dbCandidateName",
OdbcType.Char)
Dim CandidateNumeric = New
OdbcParameter("@dbCandidateNumeric", OdbcType.Int)
Dim CandidateOfficeParty = New
OdbcParameter("@dbCandidateOfficeParty", OdbcType.Char)
Dim CandidatePicture = New
OdbcParameter("@dbCandidatePicture", OdbcType.Char)


Dim CCount As Integer = 0
Dim SQLReturn As Integer = 0


OfficeName.Value = "."
CandidateOfficeName.Value = "."
CandidateName.Value = "."
CandidateNumeric.Value = 0
CandidateOfficeParty.Value = "."
CandidatePicture.Value = "."


dbConn.Open()


Dim SQLOfficeData = New OdbcCommand("DECLARE OfficeData
CURSOR
FOR SELECT @dbOfficeName FROM Office ORDER BY @dbOfficeName", dbConn)
Dim SQLOfficeDataOpen = New OdbcCommand("OPEN OfficeData",
dbConn)
Dim SQLOfficeDataFetch = New OdbcCommand("FETCH OfficeData
INTO @dbOfficeName", dbConn)
Dim SQLOfficeDataClose = New OdbcCommand("CLOSE OfficeData",
dbConn)


Dim SQLCandidateData = New OdbcCommand("DECLARE CandidateData
CURSOR FOR SELECT * FROM Candidate ORDER BY CandidateOfficeName",
dbConn)
Dim SQLCandidateDataFetch = New OdbcCommand("FETCH
CandidateData INTO @dbCandidateName, @dbCandidateOfficeName,
@dbCandidateNumeric, @dbCandidateOfficeParty, @dbCandidatePicture",
dbConn)
Dim SQLCandidateDataOpen = New OdbcCommand("OPEN
CandidateData", dbConn)
Dim SQLCandidateDataClose = New OdbcCommand("CLOSE
CandidateData", dbConn)


SQLOfficeData.Parameters.Add(OfficeName)


SQLCandidateData.Parameters.Add(CandidateOfficeName)
SQLCandidateData.Parameters.Add(CandidateName)
SQLCandidateData.Parameters.Add(CandidateNumeric)
SQLCandidateData.Parameters.Add(CandidateOfficeParty)
SQLCandidateData.Parameters.Add(CandidatePicture)


SQLOfficeData.ExecuteNonQuery()<-----ERROR [42000] [Microsoft][SQL
Native Client][SQL Server]Must declare the scalar variable
"@dbOfficeName".
 
J

Jack Jackson

I have never used ODBC with .NET (why not use the native SQL driver,
it should be faster), but I think ODBC uses ? instead of @ to mark
parameters.

I'm not sure you can use parameters for anything other than WHERE
clause values. You are using them for SELECT items, INTO and ORDER
BY. Besides which the values you are specifying for those parameters
don't make any sense to me.


Crap - getting the SOS. This is an old post, so I'll try to recycle it
as new with hopefully enough details.
Here's the relevant code causing this error
SQLOfficeData.ExecuteNonQuery()<-----ERROR [42000] [Microsoft][SQL
Native Client][SQL Server]Must declare the scalar variable
"@dbOfficeName".
at the end of all of this:

Dim dbConn = New
System.Data.Odbc.OdbcConnection("Dsn=Election;description=Election;trusted_­
connection=Yes;app=Microsoft®
Visual Studio® 2008;wsid=M1CVISTA1;database=Election")

Dim CString As String
Dim OfficeName = New OdbcParameter("@dbOfficeName",
OdbcType.Char)
Dim CandidateOfficeName = New
OdbcParameter("@dbCandidateOfficeName", OdbcType.Char)
Dim CandidateName = New OdbcParameter("@dbCandidateName",
OdbcType.Char)
Dim CandidateNumeric = New
OdbcParameter("@dbCandidateNumeric", OdbcType.Int)
Dim CandidateOfficeParty = New
OdbcParameter("@dbCandidateOfficeParty", OdbcType.Char)
Dim CandidatePicture = New
OdbcParameter("@dbCandidatePicture", OdbcType.Char)


Dim CCount As Integer = 0
Dim SQLReturn As Integer = 0


OfficeName.Value = "."
CandidateOfficeName.Value = "."
CandidateName.Value = "."
CandidateNumeric.Value = 0
CandidateOfficeParty.Value = "."
CandidatePicture.Value = "."


dbConn.Open()


Dim SQLOfficeData = New OdbcCommand("DECLARE OfficeData
CURSOR
FOR SELECT @dbOfficeName FROM Office ORDER BY @dbOfficeName", dbConn)
Dim SQLOfficeDataOpen = New OdbcCommand("OPEN OfficeData",
dbConn)
Dim SQLOfficeDataFetch = New OdbcCommand("FETCH OfficeData
INTO @dbOfficeName", dbConn)
Dim SQLOfficeDataClose = New OdbcCommand("CLOSE OfficeData",
dbConn)


Dim SQLCandidateData = New OdbcCommand("DECLARE CandidateData
CURSOR FOR SELECT * FROM Candidate ORDER BY CandidateOfficeName",
dbConn)
Dim SQLCandidateDataFetch = New OdbcCommand("FETCH
CandidateData INTO @dbCandidateName, @dbCandidateOfficeName,
@dbCandidateNumeric, @dbCandidateOfficeParty, @dbCandidatePicture",
dbConn)
Dim SQLCandidateDataOpen = New OdbcCommand("OPEN
CandidateData", dbConn)
Dim SQLCandidateDataClose = New OdbcCommand("CLOSE
CandidateData", dbConn)


SQLOfficeData.Parameters.Add(OfficeName)


SQLCandidateData.Parameters.Add(CandidateOfficeName)
SQLCandidateData.Parameters.Add(CandidateName)
SQLCandidateData.Parameters.Add(CandidateNumeric)
SQLCandidateData.Parameters.Add(CandidateOfficeParty)
SQLCandidateData.Parameters.Add(CandidatePicture)


SQLOfficeData.ExecuteNonQuery()<-----ERROR [42000] [Microsoft][SQL
Native Client][SQL Server]Must declare the scalar variable
"@dbOfficeName".
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VB Scalar Declaration Error 6
Must declare the scalar variable "@CheckUser" 1
Update Command gives "Must Declare scalar variable "@P7CYCELDAY"" 2
Must Declare the Scalar Variable error 2
Must delcare the scalar variable 1
InsertCommand gives "Must declare the scalar variable "@..." 4
Incorrect syntax near '-'.Must declare the scalar variable "@UserName 0

Top