Must declare the scalar variable

  • Thread starter Thread starter m1c
  • Start date Start date
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".
 
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".
 
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...
 
Back
Top