How do i trap sql error in storeprocedure ???

Results 1 to 2 of 2

Thread: How do i trap sql error in storeprocedure ???

  1. #1
    uttam das Guest

    Default How do i trap sql error in storeprocedure ???

    Ello you wonderful people,<BR>Its me first time in here so please help me out! :-p<BR><BR>Using ADO i&#039;m trying to pick up the return value of my store procedure. Command.parameters(0) always return 0 even if i exp-licitly return some other numbers in my sp.<BR><BR>I have attached both my store procedure and VB code here: hope somebody can tell me what i&#039;m doing wrong..<BR><BR>cheers in advance.<BR><BR>**********************<BR>CREATE PROCEDURE dbo.sp_AddNewUser <BR>-- add a new user<BR>@userid varchar(30),<BR>@password varchar(30)<BR><BR>AS<BR>declare @error int<BR><BR>insert into <BR>usertable ( userid, <BR>passwords <BR>) <BR>values(@userid,<BR>@password<BR>)<BR>select @error = @@ERROR<BR><BR>if @error != 0<BR>begin<BR>return @error<BR>end<BR>return 0<BR>GO<BR>******************<BR>and this is my VB code<BR><BR>Dim oCmd As New adodb.Command<BR>Dim oRs As New adodb.Recordset<BR>Dim RetVal As Integer<BR><BR>With oCmd<BR>.ActiveConnection = g_oConn<BR>.CommandType = adCmdStoredProc<BR>.CommandText = "sp_AddNewUser"<BR>.Parameters.Refresh<BR>.Paramet ers(1).Value = txtLoginID<BR>.Parameters(2).Value = txtPassword<BR>.Execute<BR>RetVal = .Parameters(0)<BR>End With<BR><BR>&#039;why does RetVal always return 0 ?!!!!!<BR><BR>Set oRs = Nothing<BR>Set oCmd = Nothing<BR> <BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: How do i trap sql error in storeprocedure ???

    parameters.refresh is a bit naughty as you are forcing the server to do two round trips.<BR><BR>calling a stored procedure sp_ is VERY VERY NAUGHTY, repeat after me "i am bad, i am bad"<BR><BR>sp_ tells sql server that it is a MASTER stored procedure, so it goes off and looks in the master database for it first, BEFORE it looks in your database, lots of extra work, so don&#039;t do it!<BR><BR>Try the following<BR><BR> with objCommand<BR> .parameters.append .createParameter("RETURN_VALUE", adInteger, adParamReturnValue)<BR> .parameters.append .createParameter("@userid", adVarChar, adParamInput,30,txtLoginID)<BR> .parameters.append .createParameter("@password", adVarChar, adParamInput,30,txtPassword)<BR> .commandType = adCmdStoredProc<BR> .commandText = "storedProc_AddNewUser"<BR> .ActiveConnection = g_oConn<BR> .execute<BR> RetVal = .parameters("RETURN_VALUE")<BR>end with<BR><BR>You also seem to be declaring an unnecessary recordset oRs<BR><BR>Hope this helps you out

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts