CODEWITHSUNDEEP
sql-servervb6
laky
laky

Reputation: 1

Accessing SQL Server with VB6

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

Upvotes: 0

Views: 676

Answers (1)

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 2

Related Questions