Accessing SQL Server with VB6

Question

I am trying to display data stored in SQL server in VB6.

ALTER PROCEDURE [dbo].[ledger] 
    -- Add the parameters for the stored procedure here

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
select a.StockMaster,sum1,sum2,(sum2-sum1) as TQty from
(SELECT tblStockMaster.SmName AS StockMaster, SUM(isnull(tblReceivingD.TotalQuantity,0)) AS sum2
FROM tblStockMaster LEFT JOIN tblReceivingD ON tblReceivingD.StockControlR=tblStockMaster.SmName
GROUP BY tblStockMaster.SmName) a ,
(SELECT tblStockMaster.SmName AS StockMaster, SUM(isnull(tblPurchaseOrderD.TotalQuantity,0)) AS sum1
FROM tblStockMaster LEFT JOIN tblPurchaseOrderD ON tblPurchaseOrderD.StockControl=tblStockMaster.SmName GROUP BY tblStockMaster.SmName) b
where a.StockMaster =b.StockMaster

While in VB6 the SQL cannot be found. How can I connect to the SQL database? Here's the code:

Private Sub Form_Load()
SetGrid
recdisplay "ledger"
End Sub
Function SetGrid()


With MSFlexGrid1
    .Rows = 1

    .ColWidth(0) = 0
    .ColWidth(1) = 4650: .TextMatrix(0, 1) = "Item Name"
    .ColWidth(2) = 3565: .TextMatrix(0, 2) = "Total Quantity"
    End With
End Function



Function recdisplay(sql As Variant)

The error says:

item cannot be found in the collection corresponding to the requested name or ordinal

Dim R As Integer
Set rs = New ADODB.Recordset
rs.Open sql, conn, adOpenStatic, adLockReadOnly

With MSFlexGrid1
.Rows = 1

    While Not rs.EOF
     .AddItem rs!SMControl & vbTab & rs!SmName & vbTab & rs!tqty

     rs.MoveNext
     Wend

End With
End Function

stick a break point on your 'With MSFlexGrid1' line, go to your Immediate window and type: rs.Save "c:\rs.xml",adPersistXML ...this will write your record set to xml so you can examine it outside your code — benPearce
– benPearce, Commented Mar 7, 2011 at 6:18

onedaywhen · Accepted Answer · 2011-03-07 08:56:42Z

2

You stored proc returns the following columns:

StockMaster
sum1
sum2
TQty

Your VB6 code is trying to use the following names in the rs Recordset object:

SMControl
qty

So I think the error is saying that SMControl is not a valid name for a Field in your Recordset object. I would guess you need to change SMControlto StockMaster in your VB6 code.

answered Mar 7, 2011 at 8:56

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cidtek Over a year ago

You need to accept the correct answer since those who don't give credit end up never getting further help.

Collectives™ on Stack Overflow

Accessing SQL Server with VB6

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related