DataReport with Record lines and a Total using VB6 and SQL 2000

I have a report (DataReport) that should read all the records of a particular Vendor and then totals the cost.

Here's is the table contens:
VendorID VendorReceiptNo DifPrecio DiscOtrasNegoc
----------- --------------------- ----------- ---------------------
AA01      AA01RCT1205            654.00     0.00
AA01      AA01RCT1255            123.00     100.0000
AA01      AA01RCT1263      10.00        2.5000

I have a form that asks for the Vendor id (Variable is 'VendorId').

On Form Load, I have the connection:
     Set cnn = Nothing
     Set rstRecord = Nothing
     Set cnn = New ADODB.Connection
     Set rstRecord = New ADODB.Recordset
     Dim lCompanyDB, SqlServerName As String
      SqlServerName = "(local)"
      lCompanyDB = "TWO"
      cnn.Open "Provider=SQLOLEDB; " & _
               "Initial Catalog=" & lCompanyDB & "; " & _
               "Data Source=" & SqlServerName & "; " & _
               "integrated security=SSPI; persist security info=True;"

On the "OK" button on the Select Vendor Screen, I have the both query that first reads recordset of all the Vendors records and another that totals (SUM).  At the end I have a SHOW for the DataReport form:

 Set rstRecord = New ADODB.Recordset

'Read all record of the Vendor
 ssql = "select VendorID, VendorReceiptNo, DifPrecio as Cost1,
                    DiscOtrasNegoc as Cost2 from ces00rp01"
 ssql = ssql & " WHERE vendorid='" & VendorID & "'"
 rstRecord.Open ssql, cnn, adOpenKeyset, adLockOptimistic
 If Not rstRecord.EOF Then
    rstRecord.Close
   Else
    rstRecord.Close
    End If
         
'Get totals
 Dim Cost1, Cost2 As Currency
 Cost1 = 0
 Cost2 = 0
 Set rstRecord = New ADODB.Recordset
 ssql = "select sum(DifPrecio) as Cost1, sum(DiscOtrasNegoc) as Cost2 from ces00rp01"
 ssql = ssql & " WHERE vendorid='" & VendorID & "'"
 rstRecord.Open ssql, cnn, adOpenKeyset, adLockOptimistic
 If Not rstRecord.EOF Then
    Cost1 = rstRecord("Cost1").Value
    Cost2 = rstRecord("Cost2").Value
    rstRecord.Close
   Else
    rstRecord.Close
    End If

'Show Report
 DataReport1.Show

When running it I get this error:
"Invalid Data Source"

2 Questions:

1. How can I fix my code for nolt displaying the error?

2. I didn't create a DataEnvironment1 contents since I place all the connections in the Form Load and the command in the Ok button; is this ok?

3. I can't seem to place the fields in the DataReport1 form (that is, place the record contents fields in Detail Section and place the Totals in the Footer Section).

We are kind'a new to VB.

Thanx
rayluvsAsked:
Who is Participating?
 
rayluvsConnect With a Mentor Author Commented:
0
 
rayluvsAuthor Commented:
We have resolve the "Invalid Data Source" and the "DataReport with Record lines" thru assistance of EE in another question.

We are still working with displaying totals in the report.  How can we place the totals in the report?

We are using:
    ssql = "select sum(DifPrecio) as Cost1, sum(DiscOtrasNegoc) as Cost2 from ces00rp01"
    ssql = ssql & " WHERE vendorid='" & VendorID & "'"
    rstRecord.Open ssql, cnn, adOpenKeyset, adLockOptimistic

Please advice.
0
 
rayluvsAuthor Commented:
Hey!

How do I go about this? Delete the question? Awarded? What's the next step in a situation like this?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
alainbrydenCommented:
You can close the question and accept your own solution by clicking "Accept As Solution" at the bottom of a comment.

--
Alain
0
 
rayluvsAuthor Commented:
Thanx
0
 
rayluvsAuthor Commented:
I see this question in my open question.  Is this closed?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.