jana
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey!
How do I go about this? Delete the question? Awarded? What's the next step in a situation like this?
How do I go about this? Delete the question? Awarded? What's the next step in a situation like this?
You can close the question and accept your own solution by clicking "Accept As Solution" at the bottom of a comment.
--
Alain
--
Alain
ASKER
Thanx
ASKER
I see this question in my open question. Is this closed?
ASKER
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.