robinmiller
IS-IT--Management
I am attempting to return a record set from a stored procedure to a VB app.
Question is :
1) What must I have in the stored procedure to return a recordset.
2) What must I have in VB to accept it ?
Procedure is as follows
CREATE PROCEDURE .sp_Order(@ID as int)
AS
SELECT dbo.Customers.cusName, dbo.Customers.cusContact, dbo.Customers.cusAddressL1, dbo.Customers.cusAddressL2, dbo.Customers.cusAddressL3,
dbo.Customers.cusAddressL4, dbo.Customers.cusPostCode, dbo.Customers.cusCountry, dbo.Customers.cusTel, dbo.Customers.cusFax,
dbo.Customers.cusDelContact, dbo.lkupTerms.trmDescription, dbo.Employees.empSName, dbrders.ordID, dbrders.ordDate, dbrders.ordPO,
dbrders.ordOurRef, dbrders.ordGrossCost, dbrders.ordDiscount, dbrders.ordVAT, dbrders.ordDeliveryCost, dbrders.ordNettCost,
dbrders.ordNotes, dbo.Delivery.delAddressL1, dbo.Delivery.delAddressL2, dbo.Delivery.delAddressL3, dbo.Delivery.delAddressL4,
dbo.Delivery.delPostCode, dbo.Delivery.delCountry, dbo.Delivery.delTel, dbo.Delivery.delFax, dbo.Stock.stkCode, dbo.Stock.stkName,
dbo.Stock.stkUnitPrice, dbo.Stock.stkEAN8Barcode, dbrderLines.orddetAmount, dbrderLines.orddetCost, dbrderLines.orddetDiscount,
dbrderLines.orddetTotal
FROM dbo.Delivery INNER JOIN
dbrders ON dbo.Delivery.delordID = dbrders.ordID LEFT OUTER JOIN
dbo.Employees ON dbrders.ordempID = dbo.Employees.empID RIGHT OUTER JOIN
dbrderLines ON dbrders.ordID = dbrderLines.orddetordID RIGHT OUTER JOIN
dbo.Customers ON dbrders.ordcusID = dbo.Customers.cusID LEFT OUTER JOIN
dbo.Stock ON dbrderLines.orddetstkID = dbo.Stock.stkID LEFT OUTER JOIN
dbo.lkupTerms ON dbo.Customers.cusTerms = dbo.lkupTerms.trmID
Where dbrders.ordID = @ID
Return
Question is :
1) What must I have in the stored procedure to return a recordset.
2) What must I have in VB to accept it ?
Procedure is as follows
CREATE PROCEDURE .sp_Order(@ID as int)
AS
SELECT dbo.Customers.cusName, dbo.Customers.cusContact, dbo.Customers.cusAddressL1, dbo.Customers.cusAddressL2, dbo.Customers.cusAddressL3,
dbo.Customers.cusAddressL4, dbo.Customers.cusPostCode, dbo.Customers.cusCountry, dbo.Customers.cusTel, dbo.Customers.cusFax,
dbo.Customers.cusDelContact, dbo.lkupTerms.trmDescription, dbo.Employees.empSName, dbrders.ordID, dbrders.ordDate, dbrders.ordPO,
dbrders.ordOurRef, dbrders.ordGrossCost, dbrders.ordDiscount, dbrders.ordVAT, dbrders.ordDeliveryCost, dbrders.ordNettCost,
dbrders.ordNotes, dbo.Delivery.delAddressL1, dbo.Delivery.delAddressL2, dbo.Delivery.delAddressL3, dbo.Delivery.delAddressL4,
dbo.Delivery.delPostCode, dbo.Delivery.delCountry, dbo.Delivery.delTel, dbo.Delivery.delFax, dbo.Stock.stkCode, dbo.Stock.stkName,
dbo.Stock.stkUnitPrice, dbo.Stock.stkEAN8Barcode, dbrderLines.orddetAmount, dbrderLines.orddetCost, dbrderLines.orddetDiscount,
dbrderLines.orddetTotal
FROM dbo.Delivery INNER JOIN
dbrders ON dbo.Delivery.delordID = dbrders.ordID LEFT OUTER JOIN
dbo.Employees ON dbrders.ordempID = dbo.Employees.empID RIGHT OUTER JOIN
dbrderLines ON dbrders.ordID = dbrderLines.orddetordID RIGHT OUTER JOIN
dbo.Customers ON dbrders.ordcusID = dbo.Customers.cusID LEFT OUTER JOIN
dbo.Stock ON dbrderLines.orddetstkID = dbo.Stock.stkID LEFT OUTER JOIN
dbo.lkupTerms ON dbo.Customers.cusTerms = dbo.lkupTerms.trmID
Where dbrders.ordID = @ID
Return