Connecting to a MySQL database through VB .NET

[Ed]

What is wrong with this code?


Try

Dim connectionString As String =
"Driver={MySQL};SERVER=localhost;DATABASE=Jags;USER=ed;PASSWORD=sioux"

Dim conn As New OleDb.OleDbConnection(connectionString)

'conn.Open()

Dim daAllModels As New OleDb.OleDbDataAdapter("Select * from
ModelTable", conn)

dgModels.DataSource = dsAllModels.Tables("ModelTable")

dgModels.DataBind()

Catch ex As Exception

Trace.Write("Exception was thrown")

End Try

The form in which datagrid dtModels appears empty except for the fixed
text.



thanks,



Ed

 

[Nobody]

What is wrong with this code?


Try

Dim connectionString As String =
"Driver={MySQL};SERVER=localhost;DATABASE=Jags;USER=ed;PASSWORD=sioux"

Dim conn As New OleDb.OleDbConnection(connectionString)

'conn.Open()

Dim daAllModels As New OleDb.OleDbDataAdapter("Select * from ModelTable",
conn)

dgModels.DataSource = dsAllModels.Tables("ModelTable")

dgModels.DataBind()

Catch ex As Exception

Trace.Write("Exception was thrown")

End Try

The form in which datagrid dtModels appears empty except for the fixed
text.

It seems that you are using ODBC connection string(See the last item at the
following link). Try the suggestion under ".NET Framework Data Provider for
OLE DB" below, use "Provider=MySQLProv" instead of "Driver={MySQL}".

http://www.connectionstrings.com/mysql
http://www.connectionstrings.com

 

[Cor Ligthert[MVP]]

Ed,

See inline
'I assume that here was a try

Dim connectionString As String =
"Driver={MySQL};SERVER=localhost;DATABASE=Jags;USER=ed;PASSWORD=sioux"

Dim conn As New OleDb.OleDbConnection(connectionString)

'conn.Open()

Dim daAllModels As New OleDb.OleDbDataAdapter("Select * from
ModelTable", conn)

try
daAllModels.Fill(dsAllModels,"ModelTable")
catch
Catch ex as exception
response.Write(ex.ToString)
end Catch

dgModels.DataSource = dsAllModels.Tables("ModelTable")

dgModels.DataBind()

' Catch ex As Exception

' Trace.Write("Exception was thrown")

' End Try

Cor

 

[Cor Ligthert[MVP]]

Watch typos I typed it here in the message, I see at least on, I left a
catch which I had removed, there should be only one catch

Cor

 

[Ed]

Nobody,
This is what I have after clipping the suggested strings out of the
link and changing it as you suggested:

Try

Dim connectionString As String =
"Provider=MySQLProv;Server=localhost;Database=Jags;
User=ed;Password=sioux;Option=3;"

Dim conn As New OleDb.OleDbConnection(connectionString)

Dim daAllModels As New OleDb.OleDbDataAdapter("Select * from
ModelTable", conn)

daAllModels.Fill(dsAllModels, "ModelTable")

Catch ex As Exception

Response.Write(ex.ToString)

End Try

It doesn't work, I get the following results in the form, but no data.

Error report follows. Obviously, it doesn't like the provider name.

System.InvalidOperationException: The 'MySQLProv' provider is not
registered on the local machine. at
System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString
constr, DataSourceWrapper& datasrcWrapper) at
System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString
constr, OleDbConnection connection) at
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningObject) at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup) at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection) at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory) at
System.Data.OleDb.OleDbConnection.Open() at
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable) at JagModelsWeb.WebForm1.Page_Load(Object sender, EventArgs
e) in c:\inetpub\wwwroot\JagModelsWeb\SelectModelsForm.aspx.vb:line 61

Note that I have installed mysql-connector-odbc-5.1.5-win32.msi on my
Windows machine. I also remind everyone I'm running MS Visual Studio
2002. I can see mysqld.exe running in Task manager, but also
sqlserver.exe. No evidence of a mysqlodbc or anything like that. Don't
what that inplies.

Ed

 

[Ed]

Cor,
I believe your note is suggesting two things: using fill instead of
setting dgModels.DataSource, and the response.Write to get error
reports. The latter is helpful, even though I'm still in the dark. The
first thing I learned from the error reports was the connection string
wants something like Provider=SQLOLEDB instead of Driver ={MySQL}. So
making that change I have the code:

Try

Dim connectionString As String =
"Provider=SQLOLEDB;Server=localhost;Database=Jags; Uid=ed;Pwd=sioux;"

Dim conn As New OleDb.OleDbConnection(connectionString)

Dim daAllModels As New OleDb.OleDbDataAdapter("Select * from
ModelTable", conn)

daAllModels.Fill(dsAllModels, "ModelTable")

Catch ex As Exception

Response.Write(ex.ToString)

End Try

This gives the following error reports:

System.Data.OleDb.OleDbException: [DBNETLIB][ConnectionOpen
(Connect()).]SQL Server does not exist or access denied. at
System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString
constr, OleDbConnection connection) at
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningObject) at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup) at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection) at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory) at
System.Data.OleDb.OleDbConnection.Open() at
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable) at JagModelsWeb.WebForm1.Page_Load(Object sender, EventArgs
e) in c:\inetpub\wwwroot\JagModelsWeb\SelectModelsForm.aspx.vb:line 62

So it looks like it now connects but says the SQL Server doesn't exist
or is being denied. It must be the latter since Task mamager shows
both mysqld.exe and sqlserver.exe running, and mysql runs in a Windows
console. On the other hand, I am not at all confident that
Provider=SQLOLEDB is correct.

Ed