Organize all the SQL Reads & Writes within VB6

jana
jana used Ask the Experts™
on
We need assistance in organizing our codes, specifically the SQL commands for sole purpose to help us troubleshoot code when needed.

In any VB apps we may have 10 to 20 SQL execute statements.  And each of those statements may have 20 to 100 lines of setting up the SQL commands (see SQL code sample attached).

The thing is that when we need to read thru the code and analysis the algorithm and logistics, it gets suffocating viewing thru all these SQL statements that we loose track and restart form whatever point we were.

I just came up with an option for this and would like to place it so EE can offer their observations and experiences.  

This is what I plan to do:

Per every SQL statement have a call where I would place all the SQL executions and have them in an "IF" with literal or constant so I can identify them in a routine or function; we'll call this SQL rouine ExecSql().

EXAMPLE:
     ::
     ::
    Call ExecSql(GetNegotiation)
    If Not (rst.EOF And rst.BOF) Then
       ::
       EndIF
     ::
    Call ExecSql(SaveNewNegData)
    If Not (rst.EOF And rst.BOF) Then
       ::
       EndIF
     ::
     ::
Private Function ExecSql(Asking As String, sSql As String) As String
 If Asking = "GetNegotiation" Then
    ::
    sSql  = " select..."
    End If
 If Asking = "SaveNewNegData" Then
    ::
    sSql  = " select..."
    End If

 cmd.CommandText = sSql  
 Set rst = cmd.Execute

End Function


Please advice on this concept

Thanx in advance!
::
::
::
::
 
If MixMatch > "" Then 'Only if theres is a Group (category Value)
       'Add a new additional lines to the HISTORY of negotiation
        sSql = "Insert into Negociacion "
        sSql = sSql & "(VendorNumber,VendorName, ItemID, ItemDescription, LastCost, NewCost,"
        sSql = sSql & "Discount,PerCase,PerX,PerY,Buyer,Vendor_contact,WholeSale,WholeSaleMargen,"
        sSql = sSql & "Detail,DetailSaleMargen,BillPercase1,BillPercase2,datefrom,dateto,NewBaseCost,"
        sSql = sSql & "LastChange,MasterUPC,itemDescription2,CategoryNumber,Pack,PSize,WholeSaleMargenN,"
        sSql = sSql & "DetailSaleMargenN,NewBaseCost2,pickupallowance) "
        sSql = sSql & " select "
        sSql = sSql & "'" & sq(txtVendorID.Text) & "'"
        sSql = sSql & ",'" & sq(lblVendorName.Caption) & "'"
       
       'Include the actual Vendors Item Number
        sSql = sSql & ", IV00103.vnditnum"
       'Include the actual Vendors Item Description
        sSql = sSql & ", IV00103.vnditdsc"
        
        sSql = sSql & "," & Trim(lblLastVendorCost.Caption) & ""
        sSql = sSql & "," & Trim(lblFinalCost.Caption) & ""
        If txtDiscount.Text = "" Then
            txtDiscount.Text = "0.00"
        End If
        If txtPerCase.Text = "" Then
            txtPerCase.Text = "0.00"
        End If
        If txtPurchBuyX.Text = "" Then
            txtPurchBuyX.Text = "0.00"
        End If
        If txtPurchBuyY.Text = "" Then
            txtPurchBuyY.Text = "0.00"
        End If
        sSql = sSql & "," & Trim(txtDiscount.Text) & ""
        sSql = sSql & "," & Trim(txtPerCase.Text) & ""
        sSql = sSql & "," & Trim(txtPurchBuyX.Text) & ""
        sSql = sSql & "," & Trim(txtPurchBuyY.Text) & ""
        sSql = sSql & ",'" & sq(lblBuyer.Caption) & "'"
        sSql = sSql & ",'" & sq(lblVendorContact.Caption) & "'"
        sSql = sSql & "," & Format$(txtPriceCaseCI, "######0.00") & ""
        sSql = sSql & "," & Format$(txtNewPricingWhoesalePercectl, "######0.00") & ""
        sSql = sSql & "," & Format$(txtPriceDetailCI, "######0.00") & ""
        sSql = sSql & "," & Format$(txtNewPricingRetaiPercectl, "######0.00") & ""
        If txtdiscount1.Text = "" Then
            txtdiscount1.Text = "0.00"
        End If
        If txtdiscount2.Text = "" Then
            txtdiscount2.Text = "0.00"
        End If
        sSql = sSql & "," & Format$(txtdiscount1, "######0.00") & ""
        sSql = sSql & "," & Format$(txtdiscount2, "######0.00") & ""
        Dim t1 As Double
        Dim t2 As Double
        t1 = txtdiscount1
        t2 = txtdiscount2
        If t1 + t2 < 1 Then
            datefrom.Value = "01/01/2001"
            dateto.Value = "01/01/2001"
        End If
        sSql = sSql & ",'" & Format(datefrom.Value, "yyyyMMdd") & "'"
        sSql = sSql & ",'" & Format(dateto.Value, "yyyyMMdd") & "'"
        sSql = sSql & "," & lblFinalCost.Caption & ""
        sSql = sSql & ",'" & lblLastChange.Caption & "'"
       
       'Include the actual Master Item Number
        sSql = sSql & ", IV00101.ITEMNMBR"
       'Include the actual Master Item Number
        sSql = sSql & ", iv00101.ITEMDESC"
        
        sSql = sSql & ",'" & sq(lblCategoryNumber.Caption) & "'"
        sSql = sSql & ",'" & sq(lblPack.Caption) & "'"
        sSql = sSql & ",'" & sq(lblSize.Caption) & "'"
        sSql = sSql & "," & txtNewPricingWhoesalePercectl2.Caption & ""
        sSql = sSql & "," & txtNewPricingRetaiPercectl2.Caption & ""
        sSql = sSql & "," & txtNewBaseCost.Text & ""
        sSql = sSql & "," & Me.txtPickupAllowance.Text
        sSql = sSql & " From IV00101 JOIN IV00103 ON IV00103.itemnmbr=IV00101.itemnmbr"
        sSql = sSql & " WHERE uscatvls_4 = '" & MixMatch & "'"
        cnn.Execute sSqlHistorico
        End If
 
::
::
::
::
::

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
use stored procedures.
that way, all you have to do in your code is, in short:
use that procedure (by name), pass these values for arguments, and execute...

Author

Commented:
I'm not too familiar with store procedures; but willing to anything.

Anyways, can you give me an example maybe?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
quick example: starting with a stored proc
create procedure do_insert_table
 @col1 int, @co2 varchar(100)
as
 insert into yourtable ( col1, col2)  values (@col1, @col2 )

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
vb code:
dim cmd as adodb.command
set cmd = new adodb.command
set cmd.activeconnection = your_ado_connection
cmd.commandtext = "do_insert_table"
cmd.commandtype = adCmdStoredProc
cmd.parameters.apppend cmd.createparameter("@col1", adParamInput, adInteger,, 123)
cmd.parameters.apppend cmd.createparameter("@col2", adParamInput, advarchar, len("test"), "test")
cmd.execute

Open in new window

Author

Commented:
Ok., understood on the process of creating.  Then whats the next step?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
no next step, actually.
you can organize only more by putting the stored procedure setup+call into a vb method/function you call...

Author

Commented:
Ok....so where I do place my SQL statements (Insert, Select, Delete, etc)?

For example, I have SQL statements similar to the one attached.  Where do I place those lines? In this example I will pass the 'RCT1287' value so it can be executed correctly.
set rowcount 1  
SELECT sy03300.dscpctam as TermsPaid, sy03300.pymtrmid AS Terms
  FROM POP10310
  INNER JOIN POP10100 
   on POP10310.ponumber = POP10100.ponumber
  INNER JOIN sy03300 
  on POP10100.pymtrmid = sy03300.pymtrmid
  where POP10310.poprctnm = 'RCT1287'
  UNION ALL SELECT sy03300.dscpctam, sy03300.pymtrmid
  FROM POP10310
  INNER JOIN POP10100 
  on POP10310.ponumber = POP10100.ponumber
   INNER JOIN sy03300 
   on POP10100.pymtrmid = sy03300.pymtrmid
  where POP10310.poprctnm = 'RCT1287'
set rowcount 0  

Open in new window

Author

Commented:
(I have SQL statements woth long calculations, joins, etc.)
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
into the relevant stored procedure.

for selects, the vb code changes to:
dim cmd as adodb.command
set cmd = new adodb.command
set cmd.activeconnection = your_ado_connection
cmd.commandtext = "do_select_table"
cmd.commandtype = adCmdStoredProc
cmd.parameters.apppend cmd.createparameter("@col1", adParamInput, adInteger,, 123)
cmd.parameters.apppend cmd.createparameter("@col2", adParamInput, advarchar, len("test"), "test")
 
dim rst as adodb.recordset
set rst = new adodb.recordset
rst.open cmd

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
procedure example
create procedure my_proc1
@poprctnm varchar(100)
as
rowcount 1  
SELECT sy03300.dscpctam as TermsPaid, sy03300.pymtrmid AS Terms
  FROM POP10310
  INNER JOIN POP10100 
   on POP10310.ponumber = POP10100.ponumber
  INNER JOIN sy03300 
  on POP10100.pymtrmid = sy03300.pymtrmid
  where POP10310.poprctnm = @poprctnm 
  UNION ALL SELECT sy03300.dscpctam, sy03300.pymtrmid
  FROM POP10310
  INNER JOIN POP10100 
  on POP10310.ponumber = POP10100.ponumber
   INNER JOIN sy03300 
   on POP10100.pymtrmid = sy03300.pymtrmid
  where POP10310.poprctnm = @poprctnm 
set rowcount 0  

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
to clarify: you run the CREATE PROCEDURE once on the sql server (using sql management studio, for example).
as from then, you can use (aka run) the procedure as many times you want

Author

Commented:
Ok.  Clearly understood and it looks good.  However, two points:

1. While developing our VB apps, we would have to go to our sql management studio and
    create each procedures per each SQL statement used.  And each SQL statement are pretty
    much unique; there may be 1 or 2 that can be reused, but not many.  So it looks like more work

2. Protecting the viewing of SQL statements.  We need to exclude certain techs from viewing the
    SQL statement due to financial and access control / banking related.

Please Advice.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
re 1/
yes, a bit more work. but, the DBA will later have a chance to look and eventually tune the queries without having the app to be recompiled. and your (vb) code is FAR more organized.

re 2/
depending on what the techies can do (permission-wise), a simple sql profiler can show all the sql statements when started. it won't matter if the sql is in a procedure or in vb code.
using the strings function, you can even extract VERY easily the sql strings from your .exe (unless it would be obfuscated)

=> you could also encrypt the stored proc by adding WITH ENCRYPTION to the code, however, it's easy to break that encryption.

so, to really protect the calculations, the procedure should limit to return the data, the calculations then to be done in compiled code.

Author

Commented:
Very true on both observations.  On #2, you raised a real good point and we'll place a question for that: security & encrypting.  Nevertheless, its more work and also have to be viewing/maintaining two environment: VB & Sql Mgr.

Can there be something similar but within the same VB code, maybe classes, modules, etc.?



Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>Nevertheless, its more work and also have to be viewing/maintaining two environment: VB & Sql Mgr.
if you plan to deliver a high-quality program, you should really plan on getting the best from the 2 worlds.
limiting your "view" and "code" to only vb, it will be less efficient.

>Can there be something similar but within the same VB code, maybe classes, modules, etc.?
as I said, you can organize using some function in modules/classes. but it remains a solution with mixed vb+sql code.

Author

Commented:
When u can, would be ok to give us some example of those function in modules/classes?

Thanx

Author

Commented:
Hi I just want the feedback from various EE on how they organize thier coding in order to facilitate reading it for troubleshooting or updates, etc.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>When u can, would be ok to give us some example of those function in modules/classes?
I don't use functions/modules to organize my sql. I simply prefer stored procedures for that (as I can include them also in my versioning tool (svn / tortoise ))

anyhow, if I would use functions, I would create a function per SQL to be executed, with the parameters needed, which would run the sql, and eventually return the recordset required.
no classes, unless you start to develop with an object-oriented approach.

Author

Commented:
Understood and your advice has been well taken in consideration

Thanx

Author

Commented:
Nevertheless, and in no way underestimating your knowledge and your advice, is there a way to get other EE advice on this issue to broaden the scope the solution?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
use the "Request Attention" button, explaining that you want others to be notified of the question.
a moderator can then take actions so designated experts get notified of the question
a3

Author

Commented:
Thanx

Commented:
My advice would be to do what Angel advises and use stored procedures instead, which is the best solution on several levels.

If you won't do that then you should use vbNewLine on the end of each bit of the string  concatenation. you can print out the sql after it has been formed using Debug.Print or just outputting it to a log file. Using vbNewLine it will become readable, that way if you have any issues you will be able to read the sql.

e.g:
If MixMatch > "" Then 'Only if theres is a Group (category Value)
    'Add a new additional lines to the HISTORY of negotiation
     sSql = "Insert into Negociacion " & vbNewLine
     sSql = sSql & "(VendorNumber,VendorName, ItemID, ItemDescription, LastCost, NewCost," & vbNewLine
     sSql = sSql & "Discount,PerCase,PerX,PerY,Buyer,Vendor_contact,WholeSale,WholeSaleMargen," & vbNewLine
     sSql = sSql & "Detail,DetailSaleMargen,BillPercase1,BillPercase2,datefrom,dateto,NewBaseCost," & vbNewLine
     sSql = sSql & "LastChange,MasterUPC,itemDescription2,CategoryNumber,Pack,PSize,WholeSaleMargenN," & vbNewLine
     sSql = sSql & "DetailSaleMargenN,NewBaseCost2,pickupallowance) " & vbNewLine
     sSql = sSql & " select " & vbNewLine
     sSql = sSql & "'" & sq(txtVendorID.Text) & "'" & vbNewLine
     sSql = sSql & ",'" & sq(lblVendorName.Caption) & "'" & vbNewLine
    
    'Include the actual Vendors Item Number
     sSql = sSql & ", IV00103.vnditnum" & vbNewLine
    'Include the actual Vendors Item Description
     sSql = sSql & ", IV00103.vnditdsc" & vbNewLine
     
     sSql = sSql & "," & Trim(lblLastVendorCost.Caption) & vbNewLine
     sSql = sSql & "," & Trim(lblFinalCost.Caption) & vbNewLine
     If txtDiscount.Text = "" Then
         txtDiscount.Text = "0.00"
     End If
     If txtPerCase.Text = "" Then
         txtPerCase.Text = "0.00"
     End If
     If txtPurchBuyX.Text = "" Then
         txtPurchBuyX.Text = "0.00"
     End If
     If txtPurchBuyY.Text = "" Then
         txtPurchBuyY.Text = "0.00"
     End If
     sSql = sSql & "," & Trim(txtDiscount.Text) & vbNewLine
     sSql = sSql & "," & Trim(txtPerCase.Text) & vbNewLine
     sSql = sSql & "," & Trim(txtPurchBuyX.Text) & vbNewLine
     sSql = sSql & "," & Trim(txtPurchBuyY.Text) & vbNewLine
     sSql = sSql & ",'" & sq(lblBuyer.Caption) & "'" & vbNewLine
     sSql = sSql & ",'" & sq(lblVendorContact.Caption) & "'" & vbNewLine
     sSql = sSql & "," & Format$(txtPriceCaseCI, "######0.00") & vbNewLine
     sSql = sSql & "," & Format$(txtNewPricingWhoesalePercectl, "######0.00") & vbNewLine
     sSql = sSql & "," & Format$(txtPriceDetailCI, "######0.00") & vbNewLine
     sSql = sSql & "," & Format$(txtNewPricingRetaiPercectl, "######0.00") & vbNewLine
     If txtdiscount1.Text = "" Then
         txtdiscount1.Text = "0.00"
     End If
     If txtdiscount2.Text = "" Then
         txtdiscount2.Text = "0.00"
     End If
     sSql = sSql & "," & Format$(txtdiscount1, "######0.00") & "" & vbNewLine
     sSql = sSql & "," & Format$(txtdiscount2, "######0.00") & "" & vbNewLine
     Dim t1 As Double
     Dim t2 As Double
     t1 = txtdiscount1
     t2 = txtdiscount2
     If t1 + t2 < 1 Then
         datefrom.Value = "01/01/2001"
         dateto.Value = "01/01/2001"
     End If
     sSql = sSql & ",'" & Format(datefrom.Value, "yyyyMMdd") & "'" & vbNewLine
     sSql = sSql & ",'" & Format(dateto.Value, "yyyyMMdd") & "'" & vbNewLine
     sSql = sSql & "," & lblFinalCost.Caption & "" & vbNewLine
     sSql = sSql & ",'" & lblLastChange.Caption & "'" & vbNewLine
    
    'Include the actual Master Item Number
     sSql = sSql & ", IV00101.ITEMNMBR"
    'Include the actual Master Item Number
     sSql = sSql & ", iv00101.ITEMDESC"
     
     sSql = sSql & ",'" & sq(lblCategoryNumber.Caption) & "'" & vbNewLine
     sSql = sSql & ",'" & sq(lblPack.Caption) & "'" & vbNewLine
     sSql = sSql & ",'" & sq(lblSize.Caption) & "'" & vbNewLine
     sSql = sSql & "," & txtNewPricingWhoesalePercectl2.Caption & "" & vbNewLine
     sSql = sSql & "," & txtNewPricingRetaiPercectl2.Caption & "" & vbNewLine
     sSql = sSql & "," & txtNewBaseCost.Text & "" & vbNewLine
     sSql = sSql & "," & Me.txtPickupAllowance.Text & vbNewLine
     sSql = sSql & " From IV00101 JOIN IV00103 ON IV00103.itemnmbr=IV00101.itemnmbr" & vbNewLine
     sSql = sSql & " WHERE uscatvls_4 = '" & MixMatch & "'" & vbNewLine
     
     cnn.Execute sSqlHistorico
     
End If
 

Open in new window

Author

Commented:
ok.  I think what I was planning to do as stated in my question is feasable.  We'll also considere the store procedure

Thanx

Author

Commented:
Ok...I'll close the question.

Author

Commented:
I really wanted some more EEs input as working environment with VB when lots of code.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial