oRecordset in ASP.NET mySQL problem

Question

I have this mySQL code that connects to my server. It connects just fine:

 Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
 "SERVER=xxx.com;" & _
 "DATABASE=xxx;" & _
 "UID=xxx;" & _
 "PASSWORD=xxx;" & _
 "OPTION=3;"

 Dim conn As OdbcConnection = New OdbcConnection(MyConString)
 conn.Open()

 Dim MyCommand As New OdbcCommand
 MyCommand.Connection = conn
 MyCommand.CommandText = "select * from userinfo WHERE emailAddress = '" & theUN & "'""
 MyCommand.ExecuteNonQuery()
 conn.Close()

However, i have an old Classic ASP page that uses "oRecordset" to get the data from the mySQL server:

 Set oConnection = Server.CreateObject("ADODB.Connection")
 Set oRecordset = Server.CreateObject("ADODB.Recordset")

 oConnection.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xxx.com; PORT=3306; DATABASE=xxx; USER=xxx; PASSWORD=xxx; OPTION=3;"
 sqltemp = "select * from userinfo WHERE emailAddress = '" & theUN & "'"
 oRecordset.Open sqltemp, oConnection,3,3

And i can use oRecordset as follows:

 if oRecordset.EOF then....

or

 strValue = oRecordset("Table_Name").value

or

 oRecordset("Table_Name").value = "New Value"
 oRecordset.update

etc...

However, for the life of me, i can not find any .net code that is simular to that of my Classic ASP page!!!!!

Any help would be great! :o)

David

Answer 1

This is what you have to do:

instead of MyCommand.ExecuteNonQuery you should use MyCommand.ExecuteQuery and assign it to DataReader.

Check out this sample:

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim dr As New SqlDataReader()
'declaring the objects

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'establishing connection. you need to provide password for sql server
Try
myConnection.Open()
'opening the connection
myCommand = New SqlCommand("Select * from discounts", myConnection)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()
While dr.Read()
'reading from the datareader
MessageBox.Show("discounttype" & dr(0).ToString())
MessageBox.Show("stor_id" & dr(1).ToString())
MessageBox.Show("lowqty" & dr(2).ToString())
MessageBox.Show("highqty" & dr(3).ToString())
MessageBox.Show("discount" & dr(4).ToString())
'displaying the data from the table
End While
dr.Close()
myConnection.Close()
Catch e As Exception
End Try

HTH

Answer 2

Dim email As String = "[email protected]"
Dim stringValue As String

Using conn As OdbcConnection = New OdbcConnection(MyConString)
    conn.Open()
    Dim sql = "Select ... From userInfo Where emailAddress = @Email"
    Using cmd As OdbcCommand = New OdbcCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Email", email)
        Dim reader As OdbcDataReader = cmd.ExecuteReader()
        While reader.Read()
            stringValue = reader.GetString(0)
        End While
    End Using
    conn.Close()
End Using

'To do an Update
Using conn As OdbcConnection = New OdbcConnection(MyConString)
    conn.Open()
    Dim sql As String = "Update userInfo Set Column = @Value Where PK = @PK"
    Using cmd As OdbcCommand = New OdbcCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Email", email)
        cmd.ExecuteNonQuery()
    End Using
End Using

'To do an Insert
Using conn As OdbcConnection = New OdbcConnection(MyConString)
    conn.Open()
    Dim sql As String = "Insert userInfo(Col1,Col2,...) Values(@Value1,@Value2...)"
    Using cmd As OdbcCommand = New OdbcCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Col1", value1)
        cmd.Parameters.AddWithValue("@Col2", value2)
        ...
        cmd.ExecuteNonQuery()
    End Using
End Using

First, even in ASP Classic, it is an absolutely horrid approach to concatenate a value directly into a SQL statement. This is how SQL Injection vulnerabilities happen. You should always sanitize values that get concatenated into SQL statements. In .NET, you can use parametrized queries where you replace the values that go into your query with a variable that begins with an @ sign. You then add a parameter to the command object and set your value that way. The Command object will sanitize the value for you.

ADDITION You mentioned in a comment that your ASP Classic code is shorter. In fact, the .NET code is shorter because there are a host of things happening that you do not see and have not implemented in your ASP Classic code. I already mentioned one which is sanitizing the inputs. Another is logging. Out of the box, if an exception is thrown, it will log it in the Event Log with a call stack. To even get a call stack in ASP Classic is a chore much less any sort of decent logging. You would need to set On Error Resume Next and check for err.number <> 0 after each line. In addition, without On Error Resume Next, if an error is thrown, you have no guarantee that the connection will be closed. It should be closed, but the only way to know for sure is to use On Error Resume Next and try to close it.

Generally, I encapsulate all of my data access code into a set of methods so that I can simply pass the SQL statement and the parameter values and ensure that it is called properly each time. (This holds true for ASP Classic too).

Answer 3

 Dim conn As OdbcConnection = New OdbcConnection("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xxx.com; DATABASE=xxx; UID=xxx; PASSWORD=xxx; OPTION=3;")
    conn.Open()

    Dim MyCommand As New OdbcCommand
    MyCommand.Connection = conn
    MyCommand.CommandText = "SELECT * FROM userinfo"
    Dim rst = MyCommand.ExecuteReader()

    While rst.Read()
        response.write(rst("userID").ToString())        
    End While
    conn.Close()