VB6/SS6.5 Stored procedures versus commands

I have a grid that I select a record from then add/change/delete it.

Currently I am using a recordset created by a VB command in a data environment to access SQL Server6.5.
This works OK but should I be useing a stored procedure for efficiency?
If so how do I do this?
I'm looking for some sample code if anyone has any.
TIA.


schoonAsked:
Who is Participating?
 
samopalConnect With a Mentor Commented:
Hi, Schoon!

It's simple.

In start module open connection to database

Public NewConnect As new ADODB.Connection

   strCnn = "DSN=mySQLdsn;UID=" & Trim(txtUserName) & ";PWD=" & Trim(txtPassword)
   NewConnect.Open strCnn

'This example fills Combo

    Dim AdoRec as New ADODB.Recordset
    AdoRec.CursorLocation = adUseClient
    AdoRec.Open "myStoredProc(1,2,3)", NewConnect, adOpenStatic, adLockReadOnly, adCmdUnknown
   
    i = 0
    Combo1.Clear
    Do While AdoRec.EOF = False
        Combo1.AddItem Trim(AdoRec.Fields(2).Value)
        Combo1.ItemData(i) = AdoRec.Fields(0).Value
        AdoRec.MoveNext
        i = i + 1
    Loop
    Combo1.ListIndex = 0

HTH
0
 
schoonAuthor Commented:
Thanks.
How do I add/delete/update records?
Also does opening connections give a performance hit? Is it one connection per user or if they open 2 different forms with different Stored Procedures do I need other connections? I was using a data environment designer with no connection. Does it create a connection without me knowing?
Sorry to ask all this extra stuff.
0
 
samopalCommented:
If you want to see opening connections, open SQL Trace. Of course, data environment creates connection.
You may use one connection in your programm for all recordsets (then define it as Public), or may create new connection for every query (don't forget to close it!). It's up to you. As for me, i like to have one opening connection.
To manipulate with records you can
1) write stored procedures for every manipulation on server side, and call them from your programm
2) or do smth like this
AdoRec.open "MyTable",NewConnect,,,adCmdTable
AdoRec.AddNew
Adorec.Fields(1).value = 123  'or
AdoRec!myFieldName = "bla-bla"
AdoRec.Update

and so on... More examples you would find in MSDN
0
 
RichardAZCommented:
I think Schoon doesn't know what the hell he's talking about.  He probably learned ADO from Sean.
0
 
schoonAuthor Commented:
sean is my guru.
0
All Courses

From novice to tech pro — start learning today.