Connecting to a MySQL database through VB .NET

  • Thread starter Thread starter Ed
  • Start date Start date
E

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
 
Ed said:
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.
Click to expand...

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
 
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)
Click to expand...
try
daAllModels.Fill(dsAllModels,"ModelTable")
catch
Catch ex as exception
response.Write(ex.ToString)
end Catch
dgModels.DataSource = dsAllModels.Tables("ModelTable")

dgModels.DataBind()
Click to expand...
' Catch ex As Exception

' Trace.Write("Exception was thrown")

' End Try

Cor
 
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
 
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
 
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
 
Back
Top