MySQL Connection String Question[RESOLVED]

[Polariss]

Is there any reason at all that this code shouldnt work for a connection string? I cannot seem to get my web app to connect to a mySQL server.

Option Explicit On 
Public Class WebForm1
    Inherits System.Web.UI.Page
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents txtUsername As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtPassword As System.Web.UI.WebControls.TextBox
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents Button2 As System.Web.UI.WebControls.Button
    Dim mySQL As ADODB.Connection
    Public Username As String
    Public perms As String
 
#Region " Web Form Designer Generated Code "
 
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
 
    End Sub
 
    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub
 
#End Region
 
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        mySQL = New ADODB.Connection()
        mySQL.ConnectionString = ("Provider=MSDASQL;Driver={MySQL ODBC 3.51 Driver};Database=workorder;Server=IP;UID=user;PWD=password;OPTION=147458")
        mySQL.Open()
    End Sub
 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Response.Redirect("index.htm")
    End Sub
    Public Function UserCheck(ByVal strUserName As String, ByVal strPassword As String) As Boolean
 
        Dim rs As New ADODB.Recordset()
 
        rs.Open("SELECT username, password FROM users WHERE username = '" & strUserName & "'", mySQL, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
 
        If Not rs.EOF Then
            If rs.Fields("password") Is strPassword Then
                rs.Close()
                rs = Nothing
                UserCheck = True
                Exit Function
            Else
                rs.Close()
                rs = Nothing
                UserCheck = False
                Exit Function
 
            End If
        Else
            rs.Close()
            rs = Nothing
            UserCheck = False
            Exit Function
        End If
 
    End Function
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim mrs As ADODB.Recordset
        Dim permQuery As String
        Dim writeQuery As String
 
        Username = txtUsername.Text
 
        permQuery = "SELECT permissions FROM users WHERE username = '" & Username & "'"
 
        mrs = New ADODB.Recordset()
        mrs.Open(permQuery, mySQL, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
        If Not mrs.EOF Then
            perms = mrs.Fields("permissions").Value
            mrs.Close()
            mrs = Nothing
        End If
        writeQuery = "INSERT INTO verify(username, permissions) VALUES ('" & Username & "', '" & perms & "')"
        If UserCheck(txtUsername.Text, txtPassword.Text) = True Then
            mySQL.Execute(writeQuery)
            mySQL.Close()
            mySQL = Nothing
            Response.Redirect("menu.aspx")
 
        Else
            Response.Redirect("Login.aspx")
 
 
        End If
    End Sub
 
 
End Class

New to ASP.NET by the way. Came over from VB6.0 and trying to migrate an application to the web. Any help is appreciated.

[Polariss]

Anyone got anything at all? Ive done searches but no help. This code wont check the database it just keeps on redirecting me to login.aspx. Any help is appreciated. I tried following the link here

http://dev.mysql.com/tech-resources/...tnet/#ODBC.NET

When I try to do the Imports statement it isnt recognizing the System.data.Odbc item.

[techgnome]

In short Server=IP isn't a valid entry.... What the fork is IP?

Check http://www.connectionstrings.com/ for help on building connectionstrings of all kinds...

Tg

[Polariss]

Im going to say thanks for the reply however, I know what an IP is and im not going to give anyone server name nor ip address. I know what all of that information means. I know that part of the connection string and I know what to fill that out with. Basically i guess what Im asking is this: Is the syntax for the correction string correct with ASP.NET?

And I did look at connectionstrings.com.

[Polariss]

Here is a question though. I am using Visual Studio .NET 2002. I just checked my application in IIS. My app is currently using the wrong .NET Framework. How do I repair this problem?

[mendhak]

You want to use System.Data.Odbc, but you're still creating an ADODB.Connection object. Your approach is incorrect.

Scroll down, about halfway down that page and you'll find an example on how to connect to MYSQL.

I'll copy paste it here:

Demo Example - Establishing a Connection

In the demo example, we will look at how to connect to MySQL server through MyODBC using ODBC.NET.

1. Import the System.Data.Odbc namespace (ODBC.NET) to your application using the following statement:

using System.Data.Odbc;


In case of VB, it should be:

Imports System.Data.Odbc;


2. Once the namespace is imported in your application, you can create a simple class and establish a connection to MySQL server through MyODBC using an OdbcConnection object.

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=test;" +
"UID=venu;" +
"PASSWORD=venu;" +
"OPTION=3";

OdbcConnection MyConnection = new OdbcConnection(MyConString);
MyConnection.Open();


The above one uses DSN-less connection, if you have a MyODBC DSN defined already then you can just use "DSN=dsn_name" as the connection string i.e.

OdbcConnection MyConnection = new OdbcConnection("DSN=myodbc3-test");
MyConnection.Open();


In case of VB, it should be

Dim MyConnection As New OdbcConnection(MyConString)
MyConnection.Open()


3. Once connected, you can execute the SQL statements using the interfaces provided by ODBC.NET.

[Polariss]

For some reason I cannot Import ODBC.

The only one that shows is OleDb. I tried to switch the version of the .NET Framework im using. I installed the Microsoft .NET ODBC driver. How do you change Visual Studio to use .NET Framework 1.1 instead of 1.0?

[mendhak]

Did you get MDAC 2.6/2.8?

Have you added a reference to the ODBC dll that you downloaded?

(I did not know you were on 1.0)

[Polariss]

Yes I added a reference to odbc.dll however it is still not working.

[Polariss]

Ok sucessfully got the page to connect to the database. I had to use

Imports Microsoft.Data.Odbc

No idea. Anyhow the page is connecting to the database but now recordsets arent working. I need to translate my above recordsets into I guess OdbcCommands. I have no idea. Im trying something out in hopes of this actually working. Will keep ya posted.

[Polariss]

Ok this thing is solved. I do need to open another thread though. I had to rewrite the code. Thanks guys for pointing me in the right direction.

Imports Microsoft.Data.Odbc 'couldnt use System.Data.Odbc for some reason but this works.
 
Dim myConn As String
        myConn = "Driver={MySQL ODBC 3.51 Driver};SERVER=SERVER;DATABASE=workorder;UID=Username;PASSWORD=Password;OPTION=147458;"
Dim mySQL As New OdbcConnection(myConn)
        mySQL.Open()

Well on to other things!!