Passing multiple parameters to stored procedure

[SDecou]

I'm having trouble with my Infopath command to execute a stored procuedure
that will insert values into a table. The table has an identity field that
will increment when a record is inserted.

This is the command line I'm trying to execute:

XDocument.QueryAdapter.Command = "execute sp_insert_Overtime " + "'" + id +
"'," + "'" + CLEmpID + "'"

id is = 0, using to set the identity in the table
clempid is = 2294 and is defined as a varchar field in the table

I keep getting type mismatch errors. I was able to successfully run the
above command line in query analyzer so that a record was inserted into the
table. Can anyone help me with the syntax above?

 

[S.Y.M. Wong-A-Ton]

A type mismatch error means that you are passing a type that is incompatible
with the type being expected.

I saw in another one of your posts that the "id" in your stored procedure is
of type "int", while in your code you are passing it as a string. Try
removing the single quotes around "id" and see if it works.

Before:
XDocument.QueryAdapter.Command = "execute sp_insert_Overtime " + "'" + id +
"'," + "'" + CLEmpID + "'"

After:
XDocument.QueryAdapter.Command = "execute sp_insert_Overtime " + id +
"," + "'" + CLEmpID + "'"

Or change the "id" parameter in your stored procedure to "varchar". Note:
You may have to convert the @@identity being returned to varchar too if you
do this.

S.Y.M. Wong-A-Ton

 

[SDecou]

I tried both of your suggestions that you list below and unfortunately
neither of them work. Any other suggestions?

 

[S.Y.M. Wong-A-Ton]

No, but I do have two questions: Why did you set id as an output parameter in
your stored procedure? And is a row inserted if you change your stored
procedure to only accept empid, so only the input parameter?

S.Y.M. Wong-A-Ton

 

[S.Y.M. Wong-A-Ton]

FWIW, I was able to successfully insert and update records by using a stored
procedure as described in the knowledge base article (both with script and
with C# code). Adding an output parameter to the stored procedure did not
impede the update. Passing a string while an int was expected by the stored
procedure, produced an error, but no explicit "type mismatch" error as you
mentioned.

I didn't find a way to retrieve the value of the output parameter by using
the same syntax as in your code. According to me this is not the correct way
to retrieve the value of an output parameter. The common way is to set
parameters on a command object. However, the query adapter does not offer
this possibility or I missed it, somehow.

S.Y.M. Wong-A-Ton