Re: Any Recordset stays empty when using SQL Native Client


"Ralph" <nt_consulting64@xxxxxxxxx> schrieb im Newsbeitrag
news:L-OdnfLPIeXvis7YnZ2dnUVZ_vadnZ2d@xxxxxxxxxxxxxxx

"Predius" <Predius@xxxxxxxxxxxxxx> wrote in message
news:uux4QM%23AHHA.3604@xxxxxxxxxxxxxxxxxxxxxxx
Hello,
I am trying to switch a VB6 application from SQLOLEDB to SQLNCLI (SQL
Native
Client) in order to use the new features of SLQ Server 2005. An
ADODB.Connection seems to work so far, but as soon as I try to retrieve
any
data my Recordsets stay emtpy although the queries arrive correctly at
the
Server and *should* return data (veryfied with SQL Profiler).

'Example code:
Dim con As New ADODB.Connection
Dim recordset1 As New ADODB.Recordset
Dim recordset2 As New ADODB.Recordset
Dim recordsaffected As Integer

con.ConnectionString = "Provider=SQLNCLI.;" _
& "Server=someserver;" _
& "Database=somedb;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open

Set recordset1 = con.Execute("SELECT * FROM sometable",
recordsaffected,
adCmdText)
Set recordset2 = con.Execute("SELECT * FROM sometable2",
recordsaffected, adCmdText)

con.Close
Set con = Nothing
'End of example

Has anybody got a clue for me, I'm rather stumped. According to MS this
code
segment should work with ADO.
My ADO Version referenced by VB6 is 2.81.1117.0 whis is the actual
Version
of XP (xpsp_sp2_rtm.040803-2158).

I have the odd feeling that my Connectionstring is missing something
essential.


SQL Server 2005
SQL Native Client OLE DB Provider
http://www.connectionstrings.com/

Parameter names shouldn't have spaces.

Enumerate the complete ADODB.Connection Errors Collection.

Abandon the "As New" declares. This construct can hide non-instancing.

Take a look at the article "Using Data Link" (Index on "Data Link") in the
Windows Help. [Start->Help]. Using a UDL file is a quick way to
build/assemble a connection string and test it within your problem domain.
As opposed to peck 'n test in code.

hth
-ralph



I got it! The essential part missing in the connection string was the cursor
location. I had to indicate a client side cursor and then it worked.


.

Relevant Pages

  • Re: Any Recordset stays empty when using SQL Native Client
    ... I am trying to switch a VB6 application from SQLOLEDB to SQLNCLI (SQL ... Server and *should* return data (veryfied with SQL Profiler). ... Dim recordset1 As New ADODB.Recordset ... I have the odd feeling that my Connectionstring is missing something ...
    (microsoft.public.vb.database.ado)
  • Re: Any Recordset stays empty when using SQL Native Client
    ... Server and *should* return data (veryfied with SQL Profiler). ... Dim recordset1 As New ADODB.Recordset ... My ADO Version referenced by VB6 is 2.81.1117.0 whis is the actual Version ... build/assemble a connection string and test it within your problem domain. ...
    (microsoft.public.vb.database.ado)
  • CONNECTION PROBLEM USING VB6 TO SQL SEVER 2000 PERSONAL EDITON
    ... I am having a problem in my vb6 application when I try to connect to sql ... server 2000 personal editon with the following connection string.(I am ...
    (microsoft.public.vb.enterprise)
  • Re: Pass Through Query Question
    ... "Joe Williams" wrote in message ... > when you mentioned setting the connection string in the properties of the ... It runs with no problem using SQL query anlayzer. ...
    (microsoft.public.access.queries)
  • Re: Cant get SQL INSERT code to work
    ... I'm not sure you can use SQL to add data to an Excel file from another ... From Access Help file (covers JET SQL): ... ' Create the connection string. ... > 'Excel file and modify the SQL statement to fit your data. ...
    (microsoft.public.access.queries)