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,Degree Required,Compensation,JobStatus,JobShift,JobCatego ry,JobDescription,Location,ContactName,ContactEmai l,ContactAddress,ContactPhone,ContactFax,Reference Code,WorkExperience,DatePosted)
VALUES (
@client,@jobTitle,@careerLevel,@educationLevel,@de greeRequired,@compensation,@jobStatus,@jobShift,@j obCategory,@jobDescription,@location,@contactName, @contactEmail,@contactAddress,@contactPhone,@conta ctFax,@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.VarCha r,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\addPositio ns2.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,Degree Required,Compensation,

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

@jobDescription,@location,@contactName,@contactEma il,@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


"tshad" <ts**********@ftsolutions.com> wrote in message
news:e4**************@TK2MSFTNGP11.phx.gbl...

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,Degree Required,Compensation,JobS
tatus,JobShift,JobCategory,JobDescription,Location ,ContactName,ContactEmail,
ContactAddress,ContactPhone,ContactFax,ReferenceCo de,WorkExperience,DatePost
ed) VALUES (
@client,@jobTitle,@careerLevel,@educationLevel,@de greeRequired,@compensation
,@jobStatus,@jobShift,@jobCategory,@jobDescription ,@location,@contactName,@c
ontactEmail,@contactAddress,@contactPhone,@contact Fax,@referenceCode,@workEx
perience,@datePosted)"
Dim objCmd as New SqlCommand(CommandText,objConn)

objCmd.Parameters.Add("@client",SqlDbType.Char,50) .value = client.text
objCmd.Parameters.Add("@jobTitle",SqlDbType.VarCha r,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\addPositio ns2.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.