Parameter problem adding

[tshad]

I have changed my code from a straight string for my Sql to Parameters and
can't seem to get it to work.

I have the following excerpts from my code:

***************************************************************
Dim objConn as New SqlConnection (ConnectionString)
Dim CommandText as String = "INSERT INTO ftsolutions.dbo.position
(client,JobTitle,CareerLevel,EducationLevel,DegreeRequired,Compensation,JobStatus,JobShift,JobCategory,JobDescription,Location,ContactName,ContactEmail,ContactAddress,ContactPhone,ContactFax,ReferenceCode,WorkExperience,DatePosted)
VALUES (
@client,@jobTitle,@careerLevel,@educationLevel,@degreeRequired,@compensation,@jobStatus,@jobShift,@jobCategory,@jobDescription,@location,@contactName,@contactEmail,@contactAddress,@contactPhone,@contactFax,@referenceCode,@workExperience,@datePosted)"

Dim objCmd as New SqlCommand(CommandText,objConn)

objCmd.Parameters.Add("@client",SqlDbType.Char,50).value = client.text
objCmd.Parameters.Add("@jobTitle",SqlDbType.VarChar,50).value =
jobTitle.text

*****************************************************************

I have also see it as:

objCmd.Parameters.Add(New SqlParameter("@client",SqlDbType.Char,50)).value =
client.text

But this doesn't work either.

I get the following error:

**********************************************************************
Must declare the variable '@client'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the
variable '@client'.

Source Error:

Line 71: ' objCommand.Prepare()
Line 72:
Line 73: Dim objDataReader as SqlDataReader = objCommand.ExecuteReader( _
Line 74: CommandBehavior.CloseConnection)
Line 75:


Source File: c:\inetpub\wwwroot\development\staffing\addPositions2.aspx
Line: 73
**********************************************************************

As far as I can tell, @client is being defined.

What am I missing?

Also, in some places I see:

objCmd.Parameters.Add("@client",SqlDbType.VarChar,50).value = client.text

and other places

objCmd.Parameters.Add("@client",SqlDbType.VarChar).value = client.text

Why do I need the size of the VarChar (and do I)?

Thanks,

Tom.

 

[James]

Suggestion: Take the SQL out of your code and create a stored procedure with
your DBMS. I would create the following stored proc based on your sql
statements


CREATE PROCEDURE dbo.spSomeProcedure
@client as char(50),
@jobtitle as varchar(50),
etc etc etc....
@lastparameter as varchar(50)
AS
INSERT INTO ftsolutions.dbo.position
(client,JobTitle,CareerLevel,EducationLevel,DegreeRequired,Compensation,

JobStatus,JobShift,JobCategory,JobDescription,Location,ContactName,ContactEm
ail,ContactAddress,ContactPhone,
ContactFax,ReferenceCode,WorkExperience,DatePosted)
VALUES
(@client,@jobTitle,@careerLevel,@educationLevel,@degreeRequired,@compensatio
n,@jobStatus,@jobShift,@jobCategory,

@jobDescription,@location,@contactName,@contactEmail,@contactAddress,@contac
tPhone,@contactFax,@referenceCode,
@workExperience,@datePosted)
GO

Then from code set up your connection (objCon below).

(VB)
dim objCom as new sqlclient.sqlcommand("spSomeProcedure", objCon)
objCom.CommandType=CommandType.StoredProcedure

'repeat this for all parameters
dim prmClient as new sqlclient.sqlparameter("@client", sqldbtype.char, 50)
prmClient.value=client.text
objcom.parameters.add(prmClient)

'once you've added all the parameters to the command object you execute (for
insert you shouldn't need a return so a execute nonquery will work)
objcon.open()
objcom.executenonquery()
objcon.close()

It may not be the best way, but it's a solution.....
Hope it helps