passing paramiters to sp's

[carlblanchard]

Can you help me im totally lost

I have a stored procedure simple Insert one that works fine because ive tested it with query a
------------------------------------------------------------------------------

Create Procedure InsRecord

@RecordId int Output,
@strTitle nvarchar(4),
@strFirstName nvarchar(50),
@strSurname nvarchar(50)
AS

Set NoCount On

Declare @IntErrorCode Int
Select @intErrorCode = @@error

If @intErrorCode = 0
Begin

Insert into TelesalesData(Title,firstName,Surname)Values(@strTitle,@strFirstName,@strSurname)
Select @intErrorCode = @@Error,
@RecordId = @@Identity
End
Return @IntErrorCode


GO
-----------------------------------------------------------------------------
However im having major problems

is this the standard way to pass stuff to a stored proc

Dim cn As New SqlConnection("Password=blah;Persist Security Info=True;User ID=blah;Initial Catalog=blah;Data Source=blah")
Dim cmd As New SqlCommand("insRecord")
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@strTitle", "Mr"))
cmd.Parameters.Add(New SqlParameter("@strFirstName", "wolly"))
cmd.Parameters.Add(New SqlParameter("@strSurname", "woo"))
cmd.Connection.Open()
cmd.ExecuteNonQuery()

if not then what is the standard way to deal with SP's ?

cheers in advance
-------------------------------------------------------------------------------------
ps all i get is this

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

[Edneeis]

You should inspect the exception's message and stacktrace properties for more information on the error.

[carlblanchard]

So is the code right then ?

[carlblanchard]

found this

http://www.dotnet247.com/247referenc...;EN-US;Q310368

whats your opinon im not at work no more so cant test till tomorrow

[Edneeis]

Looks fine to me or atleast nothing 'seems' out of place. I'd save myself some typing when creating the command but that is strictly personal.

Dim cmd As New SqlCommand("insRecord", _ 
New SqlConnection("Password=KJH987KJ;Persist Security Info=True;User ID=sa;Initial Catalog=MortgageFlowDev;Data Source=COSERV01\MORTGAGEFLOWLIVE"))
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@strTitle", "Mr"))
cmd.Parameters.Add(New SqlParameter("@strFirstName", "wolly"))
cmd.Parameters.Add(New SqlParameter("@strSurname", "woo"))

Are you using SQL7 and I think that articles is relative to the Prepare method. Actually if you want to make life easier then you should check out the MS Data Access Blocks.

http://msdn.microsoft.com/library/de...ml/daab-rm.asp

It is basically a set of wrapper type classes that simplify the process.

[carlblanchard]

How do u action the command

executeNonQuery dosnt allow passing of parameters to a stored proc

HAS ANYONE GOT A THE VB CODE FOR INSERTING, UPDATING, DELETING by use with stored procs

[Edneeis]

You can pass parameters to a stored procedure with ExecuteNonQuery.

Dim cmd As New SqlCommand("del_Diagnosis", _
        New SqlConnection("Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=MHC_NET;Data Source=MEPHISTO"))
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@DiagnosisID", _DiagnosisID)
        cmd.Parameters.Add("@AllParts", AllParts)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
        cmd.Connection.Dispose()
        cmd.Dispose()

[carlblanchard]

Thanks for putting me right on that msdn is confusing to read sometimes......

Why im i getting system error all the time ?

i stuck the executeNonQuery in try catch end try to get more of the error than just system.error (dam u microsoft) and this is what i got

System.Data.SqlClient.SqlException: The request for procedure 'TrustedUsers' failed because 'TrustedUsers' is a table object.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SQLexecuteNonQuery.Form1.Button1_Click(Object sender, EventArgs e) in C:\Katiya Hosting\Internal Software\SQLexecuteNonQuery\Form1.vb:line 67

any comments

[Edneeis]

Where does 'TrustedUsers' come in? Is that in your SP? I don't know it kinda sounds like your SP tries to use a User Function named TrustedUsers and there is a table named TrustedUsers and its getting confused but thats just a guess. Without knowing what TrustedUsers is I can't tell you. It could also be some sort of SQL Security issue.

[carlblanchard]

Hey THANKS LOADS MAN

at last it works Yes i struck the table name in by mistake DOH

ONCE AGAIN THANKS FOR YOUR HELP