error: preoc expects param @xxxxx not supplied - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

error: preoc expects param @xxxxx not supplied

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-23 : 15:14:29

I created a stored proc with 2 insert queries. Both queries have parameters. The first query inserts a row in first table and second query inserts a row in the second query. In addition, the first query gets the scope_id from the first table and inserts this same scope_identity in the second table along with the other supplied params.
Here is the stored proc: (accountNumber gets the scope_identity)

ALTER PROCEDURE dbo.InsertAccount
@AccRisAsse bit,
@AccHldBll bit,
@accountNumber bigint output,

@prpDateBir datetime,
@prpSinNmbr char(15)

AS

insert into TblAccount(AccRisAsse,AccHldBll)
values(@AccRisAsse,@AccHldBll)

set @accountNumber=scope_identity()

insert into TblCredInf(Account,CredDatBir , CredSinNmbr)
values(@accountnumber, @prpDateBir, @prpSinNmbr)

Go

I define al the param like this:

pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@AccRisAsse", SqlDbType.bit))
pm.Value = Myvalue1
.........and the other params......
pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@prpSinNmbr", SqlDbType.Char,15))

But runing the proced gives me the error that the first param in the second query (prpDateBir) not supplied

Thanks a lot

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-23 : 15:31:07

Are you handling the output parameter in your code too?

Tara Kizer
aka tduggan

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-23 : 15:46:00

yes, i get the accountnumber (the scope_id) back to be used in my code

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-23 : 15:49:27

Could you show us that code then? You are missing something in your application code, so we need to see that.

Does the stored procedure work if you run it in Query Analyzer?

Tara Kizer
aka tduggan

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-23 : 16:26:14

Here is the VB.Net code:

oleDBInsNeAcc.CommandType = CommandType.StoredProcedure
oleDBInsNeAcc.CommandText = "InsertAccount"

Dim pm As SqlParameter
pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber", SqlDbType.BigInt))
pm.Direction = ParameterDirection.Output
pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@AccRisAsse", SqlDbType.Bit))
pm.Value = frmNacct.chkRisAsse.Checked
pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@AccHldBll", SqlDbType.Bit))
pm.Value = frmNacct.ChkHoldBill.Checked

pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@prpDateBir", SqlDbType.DateTime))
pm.Value = objcred.prpDateBir
pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@prpSinNmbr", SqlDbType.Char))
pm.Value = objcred.prpSinNmbr

oleDBInsNeAcc.Connection = pblsqlcn

oleDBInsNeAcc.ExecuteNonQuery()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-23 : 16:34:53

This part doesn't appear to be correct:
pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber", SqlDbType.BigInt))
pm.Direction = ParameterDirection.Output

Admittedly I'm not the best programmer...but here is what I have for that type of line:

cmd.Parameters.Add("@RowCount", SqlDbType.TinyInt)
cmd.Parameters("@RowCount").Direction = ParameterDirection.Output

Tara Kizer
aka tduggan

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-23 : 16:39:44

my stored proc executes perfectly in QA

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-23 : 16:48:41

that part of the code works perfectly when i have only one query in my stored proc. I start having the pb only after I put tow insert queries in the stored proc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-23 : 16:48:59

Since it works in Query Analyzer, something is wrong with your application code. Check out my last post for the Output parameter part. Here is the rest of the relevant information for the code that I posted:

Dim cmd As New SqlCommand("SomeProc", cnSql)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256)
cmd.Parameters("@UserName").Value = DataAccess.UserName
cmd.Parameters.Add("@RowCount", SqlDbType.TinyInt)
cmd.Parameters("@RowCount").Direction = ParameterDirection.Output
cnSql.Open()
cmd.ExecuteNonQuery()
cnSql.Close()

Tara Kizer
aka tduggan

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-23 : 17:10:31

I tried that. not working either. it s the same whether
pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber", SqlDbType.BigInt))
pm.Direction =....
or the way you wrote, they re the same (both supposed to work)
As I said, every thing works well whn I have only one insert query. But when I add the second query (check my stored proc above) and the extra params, I start getting the error

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-23 : 17:11:42

You'll need to crack open SQL Profiler to determine what you are sending to the SQL Server. Once you see the call to the stored procedure, you should be able to figure out the problem in your application code. The problem isn't within the stored procedure since it works fine in Query Analyzer.

Tara Kizer
aka tduggan

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-23 : 18:12:40

what is profiler and how do i use it
thanks

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-23 : 19:41:40

It turned out I was passing an empty value of objcred.prpDateBir
Thanks a lot for your help guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources