SQL Server - SQLCommand - Must Declare Scalar Variable @Lastname

In the below code I checked  that values are coming in.
The m_sql string looks like this

INSERT INTO dbo.Collaborator
(Lastname,Firstname,Degrees,Title,Department,Research)
Values(@Lastname,@Firstname,@Degrees,@Title,@Department,@Research)


yet I'm getting error "Must Declare Scalar Variable @Lastname, do i
need to put these in quotes?



Public Function UpdateTable(ByVal PassCol As WH.Collaborator,
ByVal m_sql As String, ByRef oConn As
System.Data.SqlClient.SqlConnection) As Boolean

MyBase.oCmd.CommandText = m_sql

MyBase.oCmd.CommandType = CommandType.Text

MyBase.oCmd.Parameters.Add("@ColId", SqlDbType.Int).Value
= PassCol.ColId
MyBase.oCmd.Parameters.Add("@Lastname", SqlDbType.VarChar,
50).Value = PassCol.Lastname
MyBase.oCmd.Parameters.Add("@Firstname",
SqlDbType.VarChar).Value = PassCol.Firstname
MyBase.oCmd.Parameters.Add("@Degrees",
SqlDbType.VarChar).Value = PassCol.Degrees
MyBase.oCmd.Parameters.Add("@Title",
SqlDbType.VarChar).Value = PassCol.Title
MyBase.oCmd.Parameters.Add("@Department",
SqlDbType.VarChar).Value = PassCol.Department
MyBase.oCmd.Parameters.Add("@Research",
SqlDbType.VarChar).Value = PassCol.Research


UpdateTable = False
Try
If MyBase.CheckDBConnected(oConn) Then
If MyBase.oCmd.ExecuteNonQuery() <> 0 Then
UpdateTable = True
m_MessageResult = "DBok"
Else
m_MessageResult = "Execute command
failed(dbu)..."
End If
End If
Catch ex As Exception
m_MessageResult = "DBmessage:" & ex.Message
End Try
MyBase.oCmd.Dispose()
End Function

anybody?

I'm starting to wonder if I can pass parameters to CommandType.Text
and if there might be a distinction between parameters and variables.
I have the above code working with a stored procedure elsewhere.

Thanks for any help or information!

neglected to mention, what  provider and DB type.

it is a sql 2005 db.

I set oCmd is set as follows from inherited class.
Protected oCmd As System.Data.SqlClient.SqlCommand

Are you calling a stored procedure, or are you just sending SQL?  From
your description, it looks like you're sending SQL only and expecting
to be able to add parameters on the fly.

Is the SQL you included the entirety of what is being sent to SQL
Server?  Is there a defined stored procedure that you're calling?

You should have something like this in SQL defining the stored
procedure...

CREATE PROCEDURE dbo.spr_Collaborator_Insert
(
@Lastname VARCHAR(50),
@Firstname VARCHAR(50),
@Degrees VARCHAR(50),
@Title VARCHAR(50),
@Department VARCHAR(50),
@Research VARCHAR(50)
)
AS
BEGIN --Procedure
SET NOCOUNT ON

INSERT INTO
dbo.Collaborator
(
Lastname,
Firstname,
Degrees,
Title,
Department,
Research
)
Values
(
@Lastname,
@Firstname,
@Degrees,
@Title,
@Department,
@Research)

END --Procedure


This article might help you:
http://www.developer.com/db/article.php/3438221


-Eric Isaacs

I know for sure it's possible to pass parameters to a single sql line
in the sqlcommand property of the datasource control in the markup of
asp.net pages without it being a stored procedure...

But it's not possible in code.. and i'll be very supprised..  is the
conscensus that the best way to avoid issues with sql injection and
handling quote or double quotes is to  use sql command parameters and
then apparently a db stored procedure?

thanks.

Probably. But why lose control of security and integrity, risk
injection, and violate the idea of a tiered architecture?  Keep all
the "data stuff" in the database and the rest of the system in the
front end.

jc,

I do not really speak your client language, but it is not a quoting error.
What it the error is saying is that with the command stream SQL Server sees,
it would expect the following for syntactical correctness.

DECLARE @Lastname NVARCHAR(30)
--  and declare all the other variables as well.
SET @LastName = 'Johnson'
-- and set all the other variables as well

INSERT INTO dbo.Collaborator
(Lastname,Firstname,Degrees,Title,Department,Research)
Values(@Lastname,@Firstname,@Degrees,@Title,@Department,@Research)

This obviously is not what you want to do.

After looking at a few sites it appears that you are not successfully
replacing the @Lastname, etc, with the value that you intend to be inserted.
You might command your syntax with that used here:
http://www.knebel.net/pages/tutorials/db/view_tutorial.php?id=169

This is his query:
sSql = "UPDATE mkTest SET Notes=@Notes WHERE pKey=@pKey";

As you will see, his code apparently parameterizes his pKey value in the
place of the "@pKey" string.
oCmd.Parameters.Add("@pKey", SqlDbType.Int, 4, "pKey");
oCmd.Parameters["@pKey"].Value = pKey;

He uses two calls to do what you are doing in one, but I don't know if that
is the problem.  It does not seem that it should be.  It looks more like
your statements are not actually updating your Command object and replacing
the targeted strings with the parameterized values.  If that is the case, is
that a useful clue?  Is there any way that you do not actually have a
Command object?  (I do not see you create one here, but I assume that you
expect it to be created elsewhere.)

FWIW,
RLF




I know for sure it's possible to pass parameters to a single sql line
in the sqlcommand property of the datasource control in the markup of
asp.net pages without it being a stored procedure...

But it's not possible in code.. and i'll be very supprised..  is the
conscensus that the best way to avoid issues with sql injection and
handling quote or double quotes is to  use sql command parameters and
then apparently a db stored procedure?

thanks.

Thanks..

I tried:

MyBase.oCmd.Parameters.Add("@Lastname", SqlDbType.VarChar,
50, "Lastname")
MyBase.oCmd.Parameters("@Lastname").Value =
PassCol.Lastname


but no luck -  same problem. And i did confirm that a value is being
passed.

I do think the issue is that it's not declared as the error is clearly
stating. A distinction between a scalar variable and a parameter. I
did see a post on a similar problem with OLE and Oracle datasource
which ofcourse use a different parameter identifier and other cases
where you have to use ? instead of @, but this is sql 2005...


BTW, If I remove the parameter all together I get the error :

An SqlParameter with ParameterName '@Lastname' is not contained by
this SqlParameterCollection.

So it appears to miss it, but refused to use it or expects that to be
a variable and not a parameter .. if there is a distinction.

what's somewhat inconsistent is that I've used parameters in asp.net's
datasource control where the sqlcommand with just sql with no
declares.

this is going to be something right under my nose.

Yes, you can pass parameters to parameterized query. See the following
articles:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx
http://aspnet101.com/aspnet101/tutorials.aspx?id=1
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx

Check that you add the parameters to the Parameters collection in the same
order as they appear in the query. Also, seems you are passing additional
parameters not used in the query (like @ColId), those should be removed.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Did you try AddWithValue?

Thanks.. I thought about the order and unused params...

and tested it exact, and it still fails.

MyBase.oCmd.Parameters.Add("@Lastname", SqlDbType.VarChar).Value =
PassCol.Lastname
MyBase.oCmd.Parameters.Add("@Firstname",
SqlDbType.VarChar).Value = PassCol.Firstname
MyBase.oCmd.Parameters.Add("@Degrees",
SqlDbType.VarChar).Value = PassCol.Degrees
MyBase.oCmd.Parameters.Add("@Title",
SqlDbType.VarChar).Value = PassCol.Title
MyBase.oCmd.Parameters.Add("@Department",
SqlDbType.VarChar).Value = PassCol.Department
MyBase.oCmd.Parameters.Add("@wResearch",
SqlDbType.VarChar).Value = PassCol.Research


I've also tryied AddWithValues

MyBase.oCmd.Parameters.AddWithValue("@Lastname",
PassCol.Lastname)
MyBase.oCmd.Parameters.Add("@Firstname",
SqlDbType.VarChar).Value = PassCol.Firstname
MyBase.oCmd.Parameters.Add("@Degrees",
SqlDbType.VarChar).Value = PassCol.Degrees
MyBase.oCmd.Parameters.Add("@Title",
SqlDbType.VarChar).Value = PassCol.Title
MyBase.oCmd.Parameters.Add("@Department",
SqlDbType.VarChar).Value = PassCol.Department
MyBase.oCmd.Parameters.Add("@wResearch",
SqlDbType.VarChar).Value = PassCol.Research

Same problem.

Also, if order and extra params will be a problem (and I don't think
it is once I get pasted this) I'll have to redesign my code as I use
the same  function for updates, inserts and deletes.. and it was
working fine before parameters were introduced. I like to keep the
code a compact as possible since I try to code from codesmith
templates.

Thanks.

From what you posted you still do not have all the parameters correct. In
your initial query you listed the following parameters:

@Lastname, @Firstname, @Degrees, @Title, @Department, @Research

In the code you just posted you have:

@Lastname, @Firstname, @Degrees, @Title, @Department, @wResearch

Note the past parameter name has an additional 'w'. The names of the
parameters in the Parameters collection must match exactly the names of the
parameters in the query.
http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

jc (wildman@noclient.net) writes:

Your code looks good to me. Did you try using Profiler to see what you
actually send to the server? You should include the event RPC:Starting.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks Erland .. I'm, still stuck on this.


This is what profiler shows.

INSERT INTO dbo.Collaborator
(Lastname,Firstname,Degrees,Title,Department,Research)
Values(@Lastname,@Firstname,@Degrees,@Title,@Department,@Research)


My code:

Public Function UpdateTable(ByVal PassCol As WH.Collaborator,
ByVal m_sql As String, ByRef oConn As
System.Data.SqlClient.SqlConnection) As Boolean

MyBase.oCmd.CommandText = m_sql

MyBase.oCmd.CommandType = CommandType.Text



'MyBase.oCmd.Parameters.AddWithValue("@Lastname",
PassCol.Lastname)

MyBase.oCmd.Parameters.Add("@Lastname", SqlDbType.VarChar,
50, "Lastname")
MyBase.oCmd.Parameters.Add("@Firstname",
SqlDbType.VarChar).Value = PassCol.Firstname
MyBase.oCmd.Parameters.Add("@Degrees",
SqlDbType.VarChar).Value = PassCol.Degrees
MyBase.oCmd.Parameters.Add("@Title",
SqlDbType.VarChar).Value = PassCol.Title
MyBase.oCmd.Parameters.Add("@Department",
SqlDbType.VarChar).Value = PassCol.Department
MyBase.oCmd.Parameters.Add("@Research",
SqlDbType.VarChar).Value = PassCol.Research

Dim x As String =
MyBase.oCmd.Parameters("@Lastname").Value.ToString()


UpdateTable = False
Try
If MyBase.CheckDBConnected(oConn) Then
If MyBase.oCmd.ExecuteNonQuery() <> 0 Then
UpdateTable = True
m_MessageResult = "DBok"
Else
m_MessageResult = "Execute command
failed(dbu)..."
End If
End If
Catch ex As Exception
m_MessageResult = "DBmessage:" & ex.Message
End Try
MyBase.oCmd.Dispose()
End Function


and when I check PassCol.Lastname at run time and other properties I
see what was entered via the screen.
Also x, shows the correct value.

For some reason, variables/parameters are not getting set..

I've done this identical code using an SP many times with no
problems.. only difference it's  CommandType.Text.

Thanks for any help.

Thanks Erland.. I'm still stuck on this..

profiler reports this.

SQL:BatchStarting	INSERT INTO dbo.Collaborator
(Lastname,Firstname,Degrees,Title,Department,Research)
Values(@Lastname,@Firstname,@Degrees,@Title,@Department,@Research)	.Net
SqlClient Data Provider		codesmith					2420	55	2008-06-23
10:36:45.063


The exception:

System.Data.SqlClient.SqlException was caught
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="Must declare the scalar variable "@Lastname"."
Number=137
Procedure=""
Server="xxxx"
Source=".Net SqlClient Data Provider"
State=2
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
methodName, Boolean async)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DBUTIL.DBUtility.UpdateTable(Collaborator PassCol, String
m_sql, SqlConnection& oConn) in C:\jcp\CodeSmithGrid_sqlcommand
\App_Code\DBUtility.vb:line 131




my last version of the code. It's odd but's not

Public Function UpdateTable(ByVal PassCol As WH.Collaborator, ByVal
m_sql As String, ByRef oConn As System.Data.SqlClient.SqlConnection)
As Boolean

MyBase.oCmd.CommandText = m_sql

MyBase.oCmd.CommandType = CommandType.Text


MyBase.oCmd.Parameters.Add("@Lastname",
SqlDbType.VarChar).Value = PassCol.Lastname
MyBase.oCmd.Parameters.Add("@Firstname",
SqlDbType.VarChar).Value = PassCol.Firstname
MyBase.oCmd.Parameters.Add("@Degrees",
SqlDbType.VarChar).Value = PassCol.Degrees
MyBase.oCmd.Parameters.Add("@Title",
SqlDbType.VarChar).Value = PassCol.Title
MyBase.oCmd.Parameters.Add("@Department",
SqlDbType.VarChar).Value = PassCol.Department
MyBase.oCmd.Parameters.Add("@Research",
SqlDbType.VarChar).Value = PassCol.Research

Dim x As String =
MyBase.oCmd.Parameters("@Lastname").Value.ToString()


UpdateTable = False
Try
If MyBase.CheckDBConnected(oConn) Then
If MyBase.oCmd.ExecuteNonQuery() <> 0 Then
UpdateTable = True
m_MessageResult = "DBok"
Else
m_MessageResult = "Execute command
failed(dbu)..."
End If
End If
Catch ex As Exception
m_MessageResult = "DBmessage:" & ex.Message
End Try
MyBase.oCmd.Dispose()
End Function

I've used this same code before with SP many times.. only clear
difference is CommandType.Text

Thanks for any help.

Why are these different?  You have .Value on every parameter except the one
that is causing you trouble.

Also, are you sure you are looking in the right place?  I would not expect a
function called "UpdateTable" to generate an INSERT statement.

I don't think your parameter is getting replaced correctly.

The batch that SQL Server is seeing should contain an actual value for
@Lastname, @Firstname, etc.  Not parameter names.

Why are you passing m_sql into the function as a string?  Wouldn't it make
more sense to build the statement within the function?




On 6/23/08 10:42 AM, in article
4adf545e-593f-4f3a-8ff8-1a8848fe866c@d1g2000hsg.googlegroups.com, "jc"

jc (wildman@noclient.net) writes:

That and nothing more? Absolutely nothing more?

That would indicate that your .Parameters collection has gone lost, so
that ADO thinks that this is a non-parameterised query. SQL Server then
complains about the undeclared variables of course.

As for why your parameters collection goes up in smoke, I have absolutely
no idea. But maybe this call does something you are not aware of?

MyBase.CheckDBConnected(oConn)

Run your program in the debugger, and step through it closely.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Doh. My bad. Sorry and Thanks for the help.

Protected Function CheckDBConnected(ByRef oConn As
System.Data.SqlClient.SqlConnection) As Boolean
oCmd.Parameters.Clear()
If oConn.State = Data.ConnectionState.Open Then
Return True
Else
Return False
End If
End Function