ADODB question

[macgyver]

Hi,

I am currently trying to convert a bunch of code from ASP to ASPx. I was hoping to simply move the code into the new system without major modifications, but with no luck.

Anyway, I am wondering how to change the following code into the new ASPx format with the smallest amount of recoding. Any help would be great, as I have a lot of code that this will need to be done with.

My biggest holdup right now is how to DIM rst in the code, or is this even the correct approach?

This code worked with ASP 1.1, but not 2.0.


Session("ecab_customer") = ""
Session("ecab_name") = ""
Session("ecab_zip_code") = ""
strQuery="SELECT ""ecab_customer""=customer_id, ""ecab_name""=customer_name, ""ecab_zip_code""=zip_code FROM [SOFCM] WHERE CUSTOMER_ID='" & cstID &"' AND ZIP_CODE='" & cstZIP &"'"
strProvider="driver={SQL Server}; server=myServer; database=Mydb; UID=username; PWD=password; "
set rst=server.CreateObject("ADODB.recordset")
rst.Open strQuery, strProvider
If (Not rst.EOF) Then
For Each fld in rst.Fields
If fld.Value="" Then
Response.Redirect "https://invalid.asp"
Else
Session(fld.Name) = RTrim(fld.Value)
End If
Next
rst.MoveNext
End If



Any help witht his conversion will save me a great amount of work. If you need me to post additional information, please ask.

thanks.

[HairyMonkeyMan]

I'll have a crack at that ;)

Imports System.Data.Sqlclient

Session("ecab_customer") = ""
Session("ecab_name") = ""
Session("ecab_zip_code") = ""
strQuery="SELECT ""ecab_customer""=customer_id, ""ecab_name""=customer_name, ""ecab_zip_code""=zip_code FROM [SOFCM] WHERE CUSTOMER_ID='" & cstID &"' AND ZIP_CODE='" & cstZIP &"'"
strProvider="driver={SQL Server}; server=myServer; database=Mydb; UID=username; PWD=password; "

' set rst=server.CreateObject("ADODB.recordset")
Dim dbConn as New SqlConnection(strProvider)
Dim dbComm as New SqlCommand(dbConn, strQuery)

' rst.Open strQuery, strProvider
dbComm.Connection.Open

' If (Not rst.EOF) Then
Dim dbRead as SqlDataReader = dbComm.ExecuteReader
While dbRead.Read

' For Each fld in rst.Fields
For i = 0 to dbRead.FieldCount

' If fld.Value="" Then
If dr.Items(i).Value="" Then
Response.Redirect "https://invalid.asp"
Else
' Session(fld.Name) = RTrim(fld.Value)
Session(dbRead.GetName(i)) = RTrim(dbRead.Items(i).Value)
End If
Next

' rst.MoveNext
' End If

Loop

Does that work?
btw, you know you can save your connection strings in the web.config file?

Good luck

[macgyver]

Almost works great. Close enough to get me going in the right direction. I do have it working now. I was getting close, but was missing a few of the basics still. Why they have to change the old stuff I will never know.

I do plan to move my connection stuff into the Web.Config file, but for debugging, it was easier to put it into my code file. Thanks for suggesting it though.

I really thank you for your reply. It saved me massive amounts of time. The hardest part of my job is the constant recoding of everyone elses.

I did have a few problems with your reply. Most of which I was able to fix pretty easily. Some was syntax, and maybe I am not using it correct, so if you feel that I should probably change it, I will.

SqlCommand(strQuery, dbConn) -- Had to reverse parameters, no biggie.
dr is actually dbRead.
dr.Items(i).Value --Does not work
I used dbRead.Item(i) instead. I made similar changes in other areas.
My strProvider connection string was way off for ASP 2.0. I found the solution on www.connectionstrings.com (http://www.connectionstrings.com)
See code below for the change.

:thumb: But as I said, your sample got me going. THANKS :thumb: :D

Session("ecab_customer") = ""
Session("ecab_name") = ""
Session("ecab_zip_code") = ""
Dim strQuery As String = "SELECT ""ecab_customer""=customer_id, ""ecab_name""=customer_name, ""ecab_zip_code""=zip_code FROM [SOFCM] WHERE CUSTOMER_ID='" & customer_id.Text & "' AND ZIP_CODE='" & zip_code.Text & "'"
Dim strProvider As String = "Server=myServer;Database=MyDb;User ID=username;Password=password;Trusted_Connection=False"
'"driver={SQL Server}; server=myServer; database=MyDb; UID=username; PWD=password; "

' Dim rst As Object = Server.CreateObject("ADODB.recordset")
' Dim fld As Object
Dim dbConn As New SqlConnection(strProvider)
Dim dbComm As New SqlCommand(strQuery, dbConn)


'rst.Open(strQuery, strProvider)
dbComm.Connection.Open()


'If (Not rst.EOF) Then
Dim dbRead As SqlDataReader = dbComm.ExecuteReader
While dbRead.Read
'For Each fld In rst.Fields
Dim i As Integer
For i = 0 To dbRead.FieldCount - 1
'If fld.Value = "" Then
If dbRead.Item(i) = "" Then
Response.Redirect("https://invalid.asp")
Else
'Session(fld.Name) = RTrim(fld.Value)
Session(dbRead.GetName(i)) = RTrim(dbRead.Item(i))
End If
Next
'End If
'Response.Redirect("https://invalid.asp")
End While


End Function

[HairyMonkeyMan]

Glad I was able to help :)