Parameter problem adding

T

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.
 
V

Val Mazur

Hi,

try next

Dim loParameter as SqlParameter

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

tshad

Miha Markic said:
Hi,

Is it possbile that client.text is a null value?
Click to expand...

That was the problem. I have to test the value first. I would have thought
that ADO would have done that, but apparently not.

Tom.
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

tshad said:
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.
Click to expand...
Click to expand...
 
Top