MARS and SQL 2000

[pcigreg]

I have a client that insists on staying with SQL 2000 and wants his app converted to VS 2008. I am running into the Multiple Active Record Sets or MARS problem with 2008. Below is how I am getting to my data.

Do I have any options here? Can I get the data another way? I know the deeper I get into the app I am going to need nested recordsets for master detail type stuff.

Help!


Here is my connection string

ConnectionString = "Server=" & strDataSource & ";" & _
"DataBase=" & strSQLDB & ";" & _
"Integrated Security=SSPI;Connect Timeout=5"
cnSQL = New SqlConnection(ConnectionString)

Here is how I am getting data for forms

Dim cmSQL As SqlCommand
Dim rsCLT As SqlDataReader
cmSQL = New SqlCommand("Select * from tblclient where cltid = " & cltID, cnSQL)
rsCLT = cmSQL.ExecuteReader()
rsCLT.Read()

Here is how I am populating DexExpress xtraGRID
Dim DBCommandE As SqlDataAdapter
Dim DSPageDataE As New DataTable
DBCommandE = New SqlDataAdapter _
("select id, from tblenroll where cltid = " & cltID, cnSQL)


DBCommandE.Fill(DSPageDataE)

 

[jmcilhinney]

MARS is not supported prior to SQL Server 2005. In earlier versions, and other providers, you must either complete one query on a connection before starting the next or else use different connections for different queries.

 

[pcigreg]

I know that it is a problem with 2000...but I must stay with that backend. Is opening multiple connections in an app (inside each occurance) that is used by 400+ people a good idea? Are there other ways I can get to the data other then the ado.net way i am using to get around this?

Thansk

 

[jmcilhinney]

All data access methods will use ADO.NET to perform the actual data access. You can use NHibernate, Entity Framework or whatever; they will still use ADO.NET internally. If you could ditch your own ADO.NET altogether then why can't you just change your ADO.NET code?

 

[pcigreg]

If you could ditch your own ADO.NET altogether then why can't you just change your ADO.NET code?

Can you explain what you mean by that statement, iI am sorry I do not understand?

 

[jmcilhinney]

First you say that you must stick with your current backend, implying that you cannot change the ADO.NET code, than you ask if there's a way to perform data access without ADO.NET, implying that you can discard the ADO.NET code. If you can ditch the code, i.e. throw it away, then why can't you change it?

By the way, in future, when you're quoting someone else's words, please use the Quote button. That's what it's for. It's a bit confusing otherwise because it implies that they are your words. E.g.

Can you explain what you mean by that statement, iI am sorry I do not understand?

 

[pcigreg]

Forgive me, I am new to this forum and version of VB

The only thing I have to keep is SQL 2000. Can you I use other data access methods then ADO to get around this, or is ADO the only access method I can use with VB.net and SQL? for example in vb6, there were ado,rdo,dao and etc.... If there are those options in .net, do any of them support MARS with SQL 2000 because it sure was supported in VB6 and SQL 2000.

Thanks