sending parameters from VB 6 to SQL 6.5 stored procedure

sending parameters from VB 6 to SQL 6.5 stored procedure

Post by murat uygu » Sat, 19 Dec 1998 04:00:00



I'm trying to send parameters from a VB 6 code to a SQL server 6.5
stored procedure.
sp simply retrieves a list of rows.

here it is:

Begin

')')
end
return

Now, this works fine when I call the procedure in SQL Query Tool as:
exec Vendors_Fetch "2653,3733"

In VB side, I created a dataenvironment and dragged the sp into it.
Code that's supposed to retrieve some rows is this:

temp = "2653,3733"
de1.Vendors_Fetch (temp)

I always get only the first row but not the other(s) i.e. row with key
value = 2653
In other words (de1.rsVendors_Fetch.recordcount) is equal to 1.
In other case where variable (Vendor_ID) is  a character but not an
integer, I call the procedure in SQL Query Tool as:
exec Vendors_Fetch "'2653','3733'"
and this works fine.

Back to VB application:

temp = "'2653','3733'"
de1.Vendors_Fetch (temp)
this time no rows retrieved not even the first row.

Anyone has any idea what's going on here???

Thanks,
Murat

 
 
 

sending parameters from VB 6 to SQL 6.5 stored procedure

Post by Trevor Dento » Tue, 22 Dec 1998 04:00:00



> I'm trying to send parameters from a VB 6 code to a SQL server 6.5
> stored procedure.
> sp simply retrieves a list of rows.

> here it is:

> Begin

> ')')
> end
> return

> Now, this works fine when I call the procedure in SQL Query Tool as:
> exec Vendors_Fetch "2653,3733"

> In VB side, I created a dataenvironment and dragged the sp into it.
> Code that's supposed to retrieve some rows is this:

> temp = "2653,3733"
> de1.Vendors_Fetch (temp)

> I always get only the first row but not the other(s) i.e. row with key
> value = 2653
> In other words (de1.rsVendors_Fetch.recordcount) is equal to 1.
> In other case where variable (Vendor_ID) is  a character but not an
> integer, I call the procedure in SQL Query Tool as:
> exec Vendors_Fetch "'2653','3733'"
> and this works fine.

> Back to VB application:

> temp = "'2653','3733'"
> de1.Vendors_Fetch (temp)
> this time no rows retrieved not even the first row.

> Anyone has any idea what's going on here???

> Thanks,
> Murat

Hi,

Dim Cmd as ADODB.Command, Rec as ADODB.Recordset
Set Cmd.ActiveConnection = <established adodb connection>
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "Vendors_Fetch"

value to feed to stored Proc.
Set Rec = cmd.Execute

PS: after the cmd has executed use the debug window to check out the
cmd.Parameters.count and the associated values to look at the RETURN
value that SQL Server automatically generates (OR You can use in the
stored procedure to report your own flag conditions)

Merry Christmas

Trevor

 
 
 

1. Need help on retrieving OUTPUT parameter in stored procedure and Visual Interdev 6.0 - SQL Server 6.5

Hi there.  Can anyone show me a way to execute a stored procedure with input
parameters and retrieving the output parameter(s)?  The following is the
code I have in my Interdev.

/**** begin
*****************************************************************/

  set cmd = server.CreateObject("ADODB.Command")
  cmd.ActiveConnection=dataconn
  cmd.commandtype=&H0004
  cmd.commandtext="sp_check_room_availability"

  set parm=cmd.createparameter("ProductID", 3, &H0001,,1)
  cmd.parameters.append parm

  set parm=cmd.createparameter("DateFrom", 7, &H0001,, "1/1/1999")
  cmd.parameters.append parm

  set parm=cmd.createparameter("DateTo", 7, &H0001,, "1/5/1999")
  cmd.parameters.append parm

  set parm=cmd.createparameter("RoomStatus", 200, &H0002, 20)
  cmd.parameters.append parm

  set rs = server.CreateObject("ADODB.Recordset")
  rs.Open cmd

  while not rs is nothing
    set rs = rs.NextRecordset()
  wend

  response.write(cmd.parameters("RoomStatus").value)

/***** end
******************************************************************/

When the application executes this, I get the following error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

[Microsoft][ODBC SQL Server Driver]Optional feature not implemented

Can anyone help me?  What is the problem with my code?

Thank you.

Antonio Yu

2. Insufficient memory error with 32 megs RAM

3. Passing parameters from Excel to SQL 6.5 stored procedures

4. xp_cmdshell error

5. ADO and ORACLE

6. Stampa in VB

7. SQL 6.5 stored procedure - parameter not returning

8. Error sending SQL Text parameter to ADO parameter obj in VB

9. executing a stored procedure in SQL Server 6.5 using VB

10. S10008 Error From SQL Server 6.5 Running VB 5.0 Stored Procedures