SQL statement in VB 
Author Message
 SQL statement in VB

To all my seniors :

I am currently in a situation where :
I have to select, sum and group a number of fields from a table, thru a SQL
statement in VB,
before passing the sum or group of values to Crystal Report thru formula /
parameter.
Here's the source code look like :
============================================================================
==============
        Dim sSQL54 As String
        Dim str_54_Pass0         As String ' formula(0) to CR
        Dim str_54_Pass1         As String ' formula(1) to CR
        Dim str_54_Pass2         As String ' formual(2) to CR
        Dim str_54_Pass3         As String ' formula(3) to CR
        Dim str_54_Pass4         As String ' formula(4) to CR
        Dim str_54_Pass5         As String ' formula(5) to CR
        Dim str_54_Pass6         As String ' formula(6) to CR

    sSQL54 = "SELECT FORMAT(BILL_DATE, 'YYYY-MM'),'str_54_Pass0',
SUM(AMT_PREV_BAL),'str_54_Pass1', SUM(AMT_CURR_PAYMENT),'str_54_Pass2',
SUM(AMT_CURR_ADJUST),'str_54_Pass3', SUM(AMT_BAL_FORWARD),'str_54_Pass4',
SUM(AMT_TOT_CURR_CHGS),'str_54_Pass5', SUM(AMT_NOW_DUE),'str_54_Pass6' FROM
RINVOICE GROUP BY str_54_Pass0 ORDER BY str_54_Pass0"
    Set RECSET54 = TCMSDB.OpenRecordset(sSQL54, dbOpenSnapshot)

    If RECSET54.RecordCount <> 0 Then
        RECSET54.MoveFirst
        Do While Not RECSET54.EOF
            'pass to CR
            CrystalReport1.Formula(0) = "54_Pass0 = '" & str_54_Pass0 & "'"
            CrystalReport1.Formula(1) = "54_Pass1 = '" & str_54_Pass1 & "'"
            CrystalReport1.Formula(2) = "54_Pass2 = '" & str_54_Pass2 & "'"
            CrystalReport1.Formula(3) = "54_Pass3 = '" & str_54_Pass3 & "'"
            CrystalReport1.Formula(4) = "54_Pass4 = '" & str_54_Pass4 & "'"
            CrystalReport1.Formula(5) = "54_Pass5 = '" & str_54_Pass5 & "'"
            CrystalReport1.Formula(6) = "54_Pass6 = '" & str_54_Pass6 & "'"
            'end passing to CR
            RECSET54.MoveNext
        Loop
    End If
    RECSET54.Close
============================================================================
==============
The most important part is at "sSQL54".  I tried the code above but VB told
me that the code contains some error.
Basically in PL/SQL (Oracle 7) the SQL select statement (which I tested and
proven ok) will look like this :
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-
SQL> LIST
  1  SELECT COUNT(*),
  2  TO_CHAR(BILL_DATE, 'YYYY-MM'),
  3  SUM(AMT_PREV_BAL), SUM(AMT_CURR_PAYMENT),
  4  SUM(AMT_CURR_ADJUST),
  5  SUM(AMT_BAL_FORWARD), SUM(AMT_TOT_CURR_CHGS),
  6  SUM(AMT_NOW_DUE) FROM RINVOICE
  7  GROUP BY TO_CHAR(BILL_DATE, 'YYYY-MM')
  8* ORDER BY TO_CHAR(BILL_DATE, 'YYYY-MM')
SQL> /
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-
I would like to convert this SQL select statement into VB format so that I
can get the same result thru VB.
Need your help !

Looking forward on your reply, million thanks!

Keep in good touch,
Warmest Regards from :
Sunny ^_^



Tue, 15 May 2001 03:00:00 GMT  
 SQL statement in VB
Dear Sunny:

I have slightly changed  the SQL statement you posted to match the VB/Access
pattern. I think it will work properly now.

sSQL54 = "SELECT Format(BILL_DATE, 'yyyy-mm') As 'str_54_Pass0', " _
                                & "Sum(AMT_PREV_BAL) As 'str_54_Pass1', " _
                                & "Sum(AMT_CURR_PAYMENT) As 'str_54_Pass2',
" _
                                & "Sum(AMT_CURR_ADJUST) As 'str_54_Pass3', "
_
                                & "Sum(AMT_BAL_FORWARD) As 'str_54_Pass4', "
_
                                & "Sum(AMT_TOT_CURR_CHGS) As 'str_54_Pass5',
" _
                                & "Sum(AMT_NOW_DUE) As 'str_54_Pass6'" _
                & "FROM RINVOICE " _
                & "GROUP BY Format(BILL_DATE, 'yyyy-mm') " _
                & "ORDER BY Format(BILL_DATE, 'yyyy-mm') "

Tips: 1) Always use the actual formula in the WHERE, GROUP BY, ORDER BY,
etc., instead of its alias;
          2) To alias formulas, use the "As" clause instead of commas;
          3) You have to use this pattern even when using ODBC connections.
The only exception is when you use the SQLPASSTHROUGH option, which requires
native database (Sybase, Oracle, etc...) SQL pattern;

Sincerely yours,

Luciano Evaristo Guerche

S?o Paulo, Brazil

Quote:
>To all my seniors :

>I am currently in a situation where :
>I have to select, sum and group a number of fields from a table, thru a SQL
>statement in VB,
>before passing the sum or group of values to Crystal Report thru formula /
>parameter.
>Here's the source code look like :
>===========================================================================
=
>==============
>        Dim sSQL54 As String
>        Dim str_54_Pass0         As String ' formula(0) to CR
>        Dim str_54_Pass1         As String ' formula(1) to CR
>        Dim str_54_Pass2         As String ' formual(2) to CR
>        Dim str_54_Pass3         As String ' formula(3) to CR
>        Dim str_54_Pass4         As String ' formula(4) to CR
>        Dim str_54_Pass5         As String ' formula(5) to CR
>        Dim str_54_Pass6         As String ' formula(6) to CR

>    sSQL54 = "SELECT FORMAT(BILL_DATE, 'YYYY-MM'),'str_54_Pass0',
>SUM(AMT_PREV_BAL),'str_54_Pass1', SUM(AMT_CURR_PAYMENT),'str_54_Pass2',
>SUM(AMT_CURR_ADJUST),'str_54_Pass3', SUM(AMT_BAL_FORWARD),'str_54_Pass4',
>SUM(AMT_TOT_CURR_CHGS),'str_54_Pass5', SUM(AMT_NOW_DUE),'str_54_Pass6' FROM
>RINVOICE GROUP BY str_54_Pass0 ORDER BY str_54_Pass0"
>    Set RECSET54 = TCMSDB.OpenRecordset(sSQL54, dbOpenSnapshot)

>    If RECSET54.RecordCount <> 0 Then
>        RECSET54.MoveFirst
>        Do While Not RECSET54.EOF
>            'pass to CR
>            CrystalReport1.Formula(0) = "54_Pass0 = '" & str_54_Pass0 & "'"
>            CrystalReport1.Formula(1) = "54_Pass1 = '" & str_54_Pass1 & "'"
>            CrystalReport1.Formula(2) = "54_Pass2 = '" & str_54_Pass2 & "'"
>            CrystalReport1.Formula(3) = "54_Pass3 = '" & str_54_Pass3 & "'"
>            CrystalReport1.Formula(4) = "54_Pass4 = '" & str_54_Pass4 & "'"
>            CrystalReport1.Formula(5) = "54_Pass5 = '" & str_54_Pass5 & "'"
>            CrystalReport1.Formula(6) = "54_Pass6 = '" & str_54_Pass6 & "'"
>            'end passing to CR
>            RECSET54.MoveNext
>        Loop
>    End If
>    RECSET54.Close
>===========================================================================
=
>==============
>The most important part is at "sSQL54".  I tried the code above but VB told
>me that the code contains some error.
>Basically in PL/SQL (Oracle 7) the SQL select statement (which I tested and
>proven ok) will look like this :
>---------------------------------------------------------------------------
-
>---------------------------------------------------------------------------
-
>-
>SQL> LIST
>  1  SELECT COUNT(*),
>  2  TO_CHAR(BILL_DATE, 'YYYY-MM'),
>  3  SUM(AMT_PREV_BAL), SUM(AMT_CURR_PAYMENT),
>  4  SUM(AMT_CURR_ADJUST),
>  5  SUM(AMT_BAL_FORWARD), SUM(AMT_TOT_CURR_CHGS),
>  6  SUM(AMT_NOW_DUE) FROM RINVOICE
>  7  GROUP BY TO_CHAR(BILL_DATE, 'YYYY-MM')
>  8* ORDER BY TO_CHAR(BILL_DATE, 'YYYY-MM')
>SQL> /
>---------------------------------------------------------------------------
-
>---------------------------------------------------------------------------
-
>-
>I would like to convert this SQL select statement into VB format so that I
>can get the same result thru VB.
>Need your help !

>Looking forward on your reply, million thanks!

>Keep in good touch,
>Warmest Regards from :
>Sunny ^_^



Thu, 17 May 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. SQL Statement in vb.net app

2. sql statement in vb using access database

3. Make-table SQL statement in VB Code

4. I need SQL statement in VB help

5. Great Big SQL statement in VB

6. Make-Table SQL statement in VB Code

7. How to use WHERE in SQL statement in VB 4.0

8. convert SQL statement to VB

9. Populate a Crystal Report with a SQL statement from VB

10. Executing Multiple SQL Statements from VB

11. Wildcards in SQL statement in VB

12. Error of SQL statement in VB

 

 
Powered by phpBB® Forum Software