stored Procedure Parameter issues

[checkai]

ERROR: Parameter1 is not a parameter for procedure Workflow_SPWipTransfer

cmd = New SqlCommand()
cmd.Connection = objConn
cmd.CommandText = "Workflow_SPWipTransfer"
cmd.CommandType = CommandType.StoredProcedure

'Parameters
parDate = cmd.CreateParameter
parDate.ParameterName = "@tDate"
parDate.Direction = ParameterDirection.Input
parDate.SqlDbType = SqlDbType.DateTime
parDate.Value = transDate
cmd.Parameters.Add(parDate)

parItems = cmd.CreateParameter
parItems.ParameterName = "@tItems"
parItems = cmd.CreateParameter
parItems.Direction = ParameterDirection.Input
parItems.SqlDbType = SqlDbType.VarChar
parItems.Value = list
cmd.Parameters.Add(parItems)

parDesc = cmd.CreateParameter
parDesc.ParameterName = "@tDesc"
parDesc = cmd.CreateParameter
parDesc.Direction = ParameterDirection.Input
parDesc.SqlDbType = SqlDbType.VarChar
parDesc.Value = transDesc
cmd.Parameters.Add(parDesc)

parToClient = cmd.CreateParameter
parToClient.ParameterName = "@tClientID"
parToClient = cmd.CreateParameter
parToClient.Direction = ParameterDirection.Input
parToClient.SqlDbType = SqlDbType.Int
parToClient.Value = toClientID
cmd.Parameters.Add(parToClient)

parToJob = cmd.CreateParameter
parToJob.ParameterName = "@tJobID"
parToJob = cmd.CreateParameter
parToJob.Direction = ParameterDirection.Input
parToJob.SqlDbType = SqlDbType.Int
parToJob.Value = toJobID
cmd.Parameters.Add(parToJob)

cmd.ExecuteNonQuery()
objConn.Close()

HERE IS THE SP
=============================
CREATE procedure Workflow_SPWipTransfer @tDate datetime, @tItems varchar(8000), @tdesc varchar(8000), @tClientID int, @tJobID int
as
--------------------------------------------------------------------------------------------------------
insert into tblwip (approved,date,clientid,jobid,staffid,chargeacid,chargetypeid,controlchargeacid,
costrate,rate,units,amount,billed,postedbystaffid,apprbystaffid,partnerid,managerid,jobpartnerid,
jobmanagerid,detail,profitloss,dotransfer,stafftypeid,ratetype)

select approved,@tDate,clientid,jobid,staffid,135,chargetypeid,controlchargeacid,costrate,rate,units,
(amount * -1) as amount,billed,postedbystaffid,apprbystaffid,partnerid,managerid,jobpartnerid,
jobmanagerid,'['+ str(wipID) +'] '+ @tdesc,profitloss,0 as doTransfer,stafftypeid,ratetype

from tblwip where wipid in (@tItems)
--------------------------------------------------------------------------------------------------------


--This is to add TRANSFER records
--------------------------------------------------------------------------------------------------------
insert into tblwip (approved,date,clientid,jobid,staffid,chargeacid,chargetypeid,controlchargeacid,
costrate,rate,units,amount,billed,postedbystaffid,apprbystaffid,partnerid,managerid,jobpartnerid,
jobmanagerid,detail,profitloss,dotransfer,stafftypeid,ratetype)

select approved,@tDate,@tClientID,@tJobID,staffid,135,chargetypeid,controlchargeacid,costrate,rate,units,
(amount * -1) as amount,billed,postedbystaffid,apprbystaffid,partnerid,managerid,jobpartnerid,
jobmanagerid,'['+ str(wipID) +'] '+ @tdesc,profitloss,dotransfer,stafftypeid,ratetype

from tblwip where wipid in (@tItems)
--------------------------------------------------------------------------------------------------------



--This updates the job partner id and manager id
--------------------------------------------------------------------------------------------------------
update tblwip
set jobpartnerid = b.partnerid,
jobmanagerid = b.managerid
from tblJob b
where tblwip.jobid = b.jobid and tblwip.date = @tdate and tblwip.chargeacid = 135
--------------------------------------------------------------------------------------------------------


--This updates the doTransfer field
--------------------------------------------------------------------------------------------------------
update tblwip
set doTransfer = 0
where wipid in (@tItems)
--------------------------------------------------------------------------------------------------------
GO



Don't understand why this error is occurring?

DLC

 

[NoCoolHandle]

I was having all sorts of nice (being facetious here) expreiences here myself just a day or so ago..

Here is what I found to work....

Dim cn As New SqlClient.SqlConnection("server=myserver;trusted connection=yes;database=miscprojects&quot
Dim cm As New SqlClient.SqlCommand
cn.Open()
cm.Connection = cn
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "InsertProject"
cm.Parameters.Add("@ProjectName", SqlDbType.VarChar, 300).Value = txtName.Text
cm.Parameters.Add("@ProjectDescription", SqlDbType.VarChar, 7000).Value = txtDescription.Text
cm.Parameters.Add("@ProjectLocation", SqlDbType.VarChar, 500).Value = txtLocation.Text
cm.Parameters.Add("@InSourceSafe", SqlDbType.Bit).Value = chkSourceSafe.Checked
cm.Parameters.Add("@DateCreated", SqlDbType.SmallDateTime).Value = txtDate.Text
cm.Parameters.Add("@Developer", SqlDbType.VarChar, 50).Value = txtDeveloper.Text
cm.ExecuteNonQuery()
cm.Dispose()
cn.Close()
cn.Dispose()

It seems that the less you do sometimes the better it works..
But adding the parameter directly to the command and defining its datatype and value in one move seems to work flawlesssly..

It also seems like you MUST have an open connection and have it bound to the Command before you define it's type and parameters..

HTH


Rob

 

[Zarcom]

if you check my post in this thread thread855-579833 you can see the method I use to create my command objects.

It also seems like you MUST have an open connection and have it bound to the Command before you define it's type and parameters..

This is untrue. It probably isn't a huge deal at considering the length of time that it actually takes to create the parameters but as good programming practices I try to keep my open and close statments for the connection within 4 or 5 lines of each other in the code. This way the connection is open for as short a time as possible and provides a very small door for errors to happen.

That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[jemminger]

i do it this way in asp:

cmd.parameters(1) = "foo"
cmd.parameters(2) = "bar"

etc...

not sure if you can do it like so in .net though



=========================================================
try { succeed(); } catch(E) { tryAgain(); }
-jeff

http://www.jeffemminger.com