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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

samopalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.