Connecting to Remote MySQL Server

[Skitchen8]

I have done some database programming with VB before, but up until now I have either created the DB locally with access or a similair program and changed it with VB or if I needed online interaction then I just programmed in PHP and kept it online. Neither of those are viable solutions for this project, I need to combine the two together. The database will be hosted on a server along with a website, and the program needs to be able to connect to that DB, retrieve information, and then write information to it. As of now for ease of programming all testing will be done locally, but will still use 127.0.0.1 to make it easier to change when it needs to be chagned. If anyone can give me a way to get started I would be greatful. I need to be able to connect to the DB (can I do that with the normal data control??) and then send and recieve SQL statements (I just need to know what command I need to send and recieve, the SQL I can work out on my own)

Thank you,
Eric



Edit: I should mention I am using VB6

[randem]

Skitchen8

Not sure what you are doing, but if you are connecting to the SQL server locally, you can use the same connection type and just change the IP address and password info to logon to the SQL Server on the web.

[Skitchen8]

I know that, but I don't even know how to get it connected to the SQL database locally.... I don't know where to put the IP address in what code to make it connect.

[randem]

Skitchen8


What is you connection string that you want to use? Need more info on how you plan to connect.

[Skitchen8]

Thats what Im asking, I don't know how I should connect. The only way I know how to connect to a MySQL server is in PHP, which does not help me at all here.

I downloaded and installed the MySQL connector/ODBC thing, but still have no idea what the download was for or how to use it. Basically I am starting off with a blank form and an idea in my mind to connect to a MySQL server, Im sorry Im providing so little information, but I have no idea whatsoever how to do this.

I thought maybe I could use the data control that comes with VB, but I haven't been able to get that to work even through quite a bit of trying.

[frigginjerk]

vbmysql.com

[Skitchen8]

Thanks for the link, looking through there right now. They don't seem to have any real basic tutorials on just how to begin, but Im still reading, maybe I'll see something that will help. There is a tutorial on there, but it mentions nothing about the actual VB portion.

[randem]

Skitchen8

OK, now we have a place to start. Being that you mentioned data control, I thought you had already started.

Check here for connection strings (you will need this a lot): http://www.able-consulting.com/ADO_Conn.htm

You should start with ADO. This will be a benefit down the road. There are a lot of examples on the forum (sarch for ADO). Here is something you could look at also to get some ideas:

http://www.***********/cgi-bin/countd...aseControl.zip

[Skitchen8]

All right so far I have

Private Sub Form_Load()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DRIVER={MySQL ODBC 3.51 Driver};SERVER=127.0.0.1;DATABASE=thugbuilder;UID=;OPTION=16427"""
conn.CursorLocation = adUseClient
conn.Open
 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
Dim mystream As ADODB.Stream
Set mystream = New ADODB.Stream
mystream.Type = adTypeText
rs.Open("SELECT * FROM users", conn, adOpenStatic, adLockOptimistic)
End Sub

Right up until the second to last line it works, which is confusing the hell out of me. I get "Compile error: Expected: =". So I figured maybe its running as a function and dimmed a new variable and put "testvar = rs.op...." and it said expected function or variable. I am thoroughly confused.

[randem]

Skitchen8

Why are you mixing streams with recordsets?

[Skitchen8]

I don't know what I was doing, mostly copying and pasting though. I now have working code though, hopefully this will work as I need it to

'ADODB Connection Variables
Dim conn As ADODB.Connection
Dim rstusers As ADODB.Recordset
Dim strconn As String

'Variables for storing information
'id uname email link hitget hitgive datee
Dim uid As Integer
Dim id As Integer
Dim uuname As String
Dim uname As String
Dim uemail As String
Dim email As String
Dim ulink As Long
Dim link As Long
Dim uhitget As Integer
Dim hitget As Integer
Dim uhitgive As Integer
Dim hitgive As Integer

'Open the connection to the Database
strconn = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DRIVER={MySQL ODBC 3.51 Driver};SERVER=127.0.0.1;DATABASE=thugbuilder;UID=;OPTION=16427"""
Set conn = New ADODB.Connection
conn.Open strconn

'Open the table, tell it which one is needed (ADO open BTW)
Set rstusers = New ADODB.Recordset
rstusers.CursorType = adOpenKeyset
rstusers.LockType = adLockOptimistic
rstusers.Open "users", conn, , , adCmdTable


I had it working, just rewrote all the code to fit how I like my code to look, testing in a few minutes when I tidy a few more things up.

[Skitchen8]

ran into a big problem here

I can use

MsgBox rstusers!email

to display the first users e-mail address, now how would I tell it what ID to use?? Its not using SQL which was good at first till I realized that I couldn't actually navigate around without SQL

[frigginjerk]

*** are you talking about?

[Skitchen8]

that code... I don't know how to retrieve anything but the first row in the database.


rstusers!email will return the first e-mail address in the database, I was wondering if there is a way to retrieve the second and if there is a way to match it to another value like the WHERE statement does in SQL.

[randem]

Skitchen8


Don't just open the table, use an SQL statement to retrieve all the records you need.

ie.

Select * from users

[Skitchen8]

I got it, amazing


Thank you so very much