Parameter problem adding

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,EducationLe
vel,DegreeRequired,Compensation,JobS
tatus,JobShift,JobCategory,JobDescriptio
n,Location,ContactName,ContactEmail,
ContactAddress,ContactPhone,ContactFax,R
eferenceCode,WorkExperience,DatePost
ed)
VALUES (
@dotnet.itags.org.client,@dotnet.itags.org.jobTitle,@dotnet.itags.org.careerLevel,@dotnet.itags.org.educatio
nLevel,@dotnet.itags.org.degreeRequired,@dotnet.itags.org.compensation
,@dotnet.itags.org.jobStatus,@dotnet.itags.org.jobShift,@dotnet.itags.org.jobCategory,@dotnet.itags.org.jobD
escription,@dotnet.itags.org.location,@dotnet.itags.org.contactName,@dotnet.itags.org.c
ontactEmail,@dotnet.itags.org.contactAddress,@dotnet.itags.org.contactPhon
e,@dotnet.itags.org.contactFax,@dotnet.itags.org.referenceCode,@dotnet.itags.org.workEx
perience,@dotnet.itags.org.datePosted)"
Dim objCmd as New SqlCommand(CommandText,objConn)
objCmd.Parameters.Add("@dotnet.itags.org.client",SqlDbType.Char,50).value = client.text
objCmd.Parameters.Add("@dotnet.itags.org.jobTitle",SqlDbType.VarChar,50).value =
jobTitle.text
****************************************
*************************
I have also see it as:
objCmd.Parameters.Add(New SqlParameter("@dotnet.itags.org.client",SqlDbType.Char,50)).value =
client.text
But this doesn't work either.
I get the following error:
****************************************
******************************
Must declare the variable '@dotnet.itags.org.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 '@dotnet.itags.org.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, @dotnet.itags.org.client is being defined.
What am I missing?
Also, in some places I see:
objCmd.Parameters.Add("@dotnet.itags.org.client",SqlDbType.VarChar,50).value = client.text
and other places
objCmd.Parameters.Add("@dotnet.itags.org.client",SqlDbType.VarChar).value = client.text
Why do I need the size of the VarChar (and do I)?
Thanks,
Tom.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,EducationLe
vel,DegreeRequired,Compensation,
JobStatus,JobShift,JobCategory,JobDescri
ption,Location,ContactName,ContactEm
ail,ContactAddress,ContactPhone,
ContactFax,ReferenceCode,WorkExperience,
DatePosted)
VALUES
(@.client,@.jobTitle,@.careerLevel,@.educati
onLevel,@.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
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e4mLaHArEHA.1232@.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,EducationLe
vel,DegreeRequired,Compensation,JobS
tatus,JobShift,JobCategory,JobDescriptio
n,Location,ContactName,ContactEmail,
ContactAddress,ContactPhone,ContactFax,R
eferenceCode,WorkExperience,DatePost
ed)
> VALUES (
>
@.client,@.jobTitle,@.careerLevel,@.educatio
nLevel,@.degreeRequired,@.compensation
,@.jobStatus,@.jobShift,@.jobCategory,@.jobD
escription,@.location,@.contactName,@.c
ontactEmail,@.contactAddress,@.contactPhon
e,@.contactFax,@.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.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.
>
>