| Author |
 Topic  |
|
Shaggy
Support Moderator
Ireland
6780 Posts |
 Posted - 31 March 2005 : 06:09:03
|
Just starting to play around with VB.NET and I've already hit my first hurdle in trying to connect our MySQL database. I've been googling around for the past 15 minutes without much luck so any help would be appreciated. Here's my classic VB connection which needs to be "converted" to .NET:set objConn=server.createobject("ADODB.Connection")
objConn.open "driver=MySQL;server=localhost;uid=username;pwd=password;database=database"
objConn.close()
set objConn=nothingActually, I already foresee my next problem - if database connections are handled differntly in .NET, it's fair to assume recordsets will be as well so any help in converting the following would also be greatly appreciated:set rs=objConn.execute("SELECT FIELDS FROM TABLE WHERE FIELD=VALUE")
rs.close
set rs=nothingCheers 
|
 Search is your friend
I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again. |
Edited by - Shaggy on 14 April 2005 06:52:09 |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 31 March 2005 : 11:51:04
|
Thanks, Podge Will give them a whirl tomorrow, see if I can't get one of 'em to work.
|
Search is your friend
I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again. |
 |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 01 April 2005 : 08:23:44
|
Once I have my connection string, how do I actually go about opening that connection to the database? Been Googling around a good bit more today, still with no joy 
|
Search is your friend
I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again. |
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 01 April 2005 : 09:22:13
|
Thanks, Podge Managed to get an ODBC connection set up using the first link but I had to use <%@import namespace="system.data.odbc"%> instead of imports system.data.odbc, reckon that was my problem all along.
Just need to figure out how to create a recordset, now. According to my Googles dim rs=objConn.execute(strSql) should work, but it ain't working for me. Actually took me a good while to figure that one out as there seems to be a different method for every driver.
|
Search is your friend
I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again. |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 11 April 2005 : 07:02:55
|
Still can't figure out how to open the recordset - anyone able to help with what I need to replace the line in green with below?dim objConn as new odbcconnection("driver=MySQL;server=localhost;uid=username;pwd=password;database=database")
objConn.open()
dim rs=objConn.execute("SELECT FIELDS FROM TABLE WHERE FIELD=VALUE")
dim strString as string=rs.fields(0).value
rs.close()
objConn.close() |
Search is your friend
I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again. |
|
|
|
Hamlin
Advanced Member
United Kingdom
2386 Posts |
Posted - 11 April 2005 : 07:06:06
|
| Dont you need to do, dim rs as [SomeThing]? = ... |
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 11 April 2005 : 07:28:08
|
Ay, tried dimensioning it as a recordset with no joy, though. Unfortunately I can't get the exact error messages as the server we're on isn't set up to return .NET 500s.
|
Search is your friend
I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again. |
|
|
|
Ghostnetworks
New Member
95 Posts |
Posted - 13 April 2005 : 20:08:53
|
Make sure you have the propper libraries imported
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.OleDB" %>
Now the script..
Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=Yourdb.MDB"
Dim strSQL As String = "SELECT [tbl_YourTable].[fld_Yourfield1], [tbl_YourTable].[fld_Yourfield2] FROM [tbl_YourTable]"& _
"WHERE [tbl_YourTable].[tbl_Yourfield1] = 2"
Dim objConn As New OleDbConnection(strConnection)
Dim objCommand As New OleDbCommand(strSQL, objConn)
Dim strYourValue As String
'Open the connection
objConn.Open()
'Set and execute the reader
Dim objDataReader As OleDbDataReader = objCommand.ExecuteReader()
While objDataReader.Read()
strYourValue = objDataReader("fld_Yourfield2") 'While reading, store the value
End While
Return strYourValue 'Do whatever here
As a safety measure, you may want to put the above inside a Try/Catch set
Try
'The datareader or connection
Catch ex As Exception
Response.Write(ex)
End Try
That way you can figure out if there's an error in the connection string or the SQL.
Hope that helps.
BTW..
I don't know what you plan to do with this exactly, but it may be easier if you just fill the whole thing into a Dataset and move on from there.
Datasets are easier to deal with than datareaders, since you can easily plug it into a datagrid or some other web control.
Dim objConn As New OleDbConnection(strConnection)
Dim objDataAdapter As New OleDbDataAdapter(strSQL, objConn)
Dim objDataSet As DataSet = New DataSet("YourDataSet")
objDataAdapter.Fill(objDataSet, "SomeTableName")
It's a lot less code too.
edit_
I see you use ODBC for the connection...
Try this OleDb string instead of your ODBC connection string.
The way you approached this problem is good if you're writing in ASP.
ASP.NET needs a bit of a change in prespective.
Think modules, not code bits. Everything is an object ( with an expandable set of properties ) that does a specific job. Use one to complement the other. |
 |
Edited by - Ghostnetworks on 13 April 2005 20:23:05 |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 14 April 2005 : 06:51:48
|
Thanks for all that info, GN Unfortunately, I tried before and couldn't get an OleDB connection to work, nor this time with your help.
The problem, however, doesn't lie with the database connection itself; I sorted that one and can connect to the database with ODBC but I just can't figure out the proper syntax for creating a recordset to retrieve records from the database.
|
Search is your friend
I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again. |
|
|
|
Ghostnetworks
New Member
95 Posts |
Posted - 14 April 2005 : 09:57:52
|
May I see a sample of your code?
There really isn't a static recordset option in ASP.NET, since all that functionality has moved to the DataReader object.
So maybe we can adapt your code to use a reader instead of a recordset and cursor. |
|
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 14 April 2005 : 10:12:18
|
This whole .NET thing is sounding more & more daunting to this classic ASP programmer! Anywee, here's the pared down code I'm using, with the line in green being the one I need to replace:dim objConn as new odbcconnection("driver=MySQL;server=localhost;uid=username;pwd=password;database=database")
objConn.open()
dim rs=objConn.execute("SELECT FIELDS FROM TABLE WHERE FIELD=VALUE")
dim strString as string=rs.fields(0).value
rs.close()
objConn.close() |
Search is your friend
I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again. |
|
|
|
Ghostnetworks
New Member
95 Posts |
Posted - 14 April 2005 : 10:32:17
|
Instead of a direct execute of SQL, you may need to create an OdbcCommand first.
Go down to the [Visual Basic] code there on the OdbcCommand example, and you should see something you'll be able to use in your application.
You see again, there's no direct execute of SQL. It's all done via DataReader.
Think modules not code bits  |
|
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 14 April 2005 : 11:21:35
|
Thanks Ghost  One step closer, now - managed to execute my SQL query but now I'm having trouble reading from the datareader to a string. Below is my updated code with the problem line again highlighted in green. I'm using getstring as it's a string I'm trying to assign to my variable but getint32 didn't work either when I tried that.dim objConn as new odbcconnection("driver=MySQL;server=localhost;uid=username;pwd=password;database=database;")
dim objComm as new odbccommand("SELECT FIELDS FROM TABLE WHERE FIELD=VALUE",objConn)
objConn.open()
dim rs as odbcdatareader=objComm.executereader()
dim strString as string=rs.getstring(0)
rs.close()
objConn.close() |
Search is your friend
I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again. |
|
|
|
Topic |
|
Topic Locked
