ADO & SQL SERVER

[kieran_rush]

I've been reading almost all the posts trying to find some helpful info about how to use ADO with SQL, but everyone uses ADO with ACCESS, is there a way to connect to SQL Server using ADO????, how can you send the strings to SQL, right now I'm using RDO to connect with SQL and works fine, except when I have to get a big resultset (over 5,000 records). Everyone is telling me that ADO is faster.
Can you send me an example in how to connect to SQL using ADO, and how to send strings

Thanks for your time

[Lothar Haensler]

here is a sample for calling an SP on SQL server.


dim cn as adodb.connection
set cn = new adodb.connection
cn.open "yourconnectstring goes here"
dim cmd as adodb.command
set cmd = new adodb.command
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_helpdb"
set cmd.activeconnection = cn
dim rs as adodb.recordset
set rs = cmd.Open
do while not rs.eof
list1.additem rs.fields(0).value
rs.movenext
Loop
rs.close
set rs = nothing
set cmd = nothing
set cn = nothing

[kieran_rush]

Thank you, by the way, just another question, does ADO works with Oracle???

Regards
RSV

[Lothar Haensler]

although I haven't used it, I am pretty sure that it does work with oracle.
You can use the native Oracle OLEDB provider or always fall back to the OLEDB provider for ODBC.

[Chris Eastwood]

Hi Lothar

I noticed you returning a RecordSet from a SQL-Server stored Procedure - I've only just started playing with ADO, is it possible to return a disconnected recordset when using the ADO command object ? I seem to be having trouble and have hacked round it for the time being by just using the RecordSet object itself to execute the SP.

Thanks

Chris Eastwood

CodeGuru - the website for developers
http://www.codeguru.com/vb

[Lothar Haensler]

I'm not sure what you mean by "disconnected recordset". You can't exec an SP without being connected, right?

or do you mean something like that?

Dim rs as new adodb.Recordset
'set rs = cmd.Open
rs.Open "sp_helpdb", "DSN=T0010;uid=sa;pwd=:-);database=einzeltest"
Do While Not rs.EOF
list1.AddItem rs.Fields(0).Value
rs.MoveNext
Loop
rs.Close
set rs = nothing

[Chris Eastwood]

When I call a normal select statement using ADO to return a Disconnected Recordset, I usually use something like :


private Function GetRecordSet(byval sSQLString as string) as ADOR.Recordset

Dim oRS as ADOR.Recordset

set oRS = new ADOR.Recordset

oRS.CursorLocation = adUseClient
oRS.CursorType = adOpenStatic

oRS.Open sSQLString, "filedsn=whatever.dsn", adOpenStatic

set oRS.ActiveConnection = nothing

set GetRecordSet = oRS
set oRS = nothing
'
' Etc..
'




The 'CursorLocation' and 'CursorType' allow the RecordSet to be 'disconnected' from the dataset and passed around various VB routines without having to remain 'connected' to the database. The above method works fine, but if I try it with an ADODB.Command object then I get all kinds of errors once I try and reference the RecordSet.

Any ideas ? (I know that can call the above routine with 'exec spWhatever')


Chris Eastwood

CodeGuru - the website for developers
http://www.codeguru.com/vb

Do you have any C++ examples ??

[al_paso]

ADO 2.1 had a limitation of returning resultsets from Oracle stored procs but this has been rectified with ADO 2.5 and ADO+.

But ADO can be used with OLEDB against Oracle quite easily.I have worked with it and it is quite painless.
Just me two cents