Connection String not working (but one does)

[mrbungle]

I have a hard coded connection string that DOES work just fine-

Public urlMySQLDatabase1 As String = "Server=170.200.80.90;port=3300;Database=DatabaseName;Uid=UserID;Pwd=Password;"

However, I want the server and port to be dependent on the values in a textbox. I thought something like this would work but it won't connect-

Public urlMySQLDatabase1 As String = "Server=" & frmLogin.txtServerIP.Text & ";" _
        & "Port=" & frmLogin.txtServerPort.Text & ";" _
        & "Database=DatabaseName"";" _
        & "Uid=UserID"";" _
        & "Pwd=Password"";"

Does anyone have any suggestions for me?

[Smith5646]

You are missing some &s for joining strings. "" such as "string1""string2" is translated as string1"string2 and not string1string2

Public urlMySQLDatabase1 As String = "Server=" & frmLogin.txtServerIP.Text & ";" _
        & "Port=" & frmLogin.txtServerPort.Text & ";" _
        & "Database=DatabaseName" & ";" _
        & "Uid=UserID" & ";" _
        & "Pwd=Password" & ";"

[mrbungle]

Thanks for your quick reply Smith- I tried it and it's still unable to connect. Your suggestion made sense to me though.

The error is:

Unable to connect to any of the specified MySQL hosts.

[Reverend Jim]

I find it useful to type these lines into a non auto-formatting window (Notepad, for example) then copy and paste into VB once it looks right. I suggest adding a line to display the connection string before you connect so you can verify that the string you see is the same as the one in your first example. You can set Consts DATABASENAME, USERID and PASSWORD.

Public urlMySQLDatabase1 As String =                  _
      "Server="   & frmLogin.txtServerIP.Text   & ";" _
    & "Port="     & frmLogin.txtServerPort.Text & ";" _
    & "Database=" & DATABASENAME                & ";" _
    & "Uid="      & USERID                      & ";" _
    & "Pwd="      & PASSWORD                    & ";"

[mrbungle]

Thanks Reverend- great suggestion.

Both strings work, when I hard code the IP and port. I'm finding that the problem is the frmLogin.txtServerIP.Text part- it doesn't want to pick up the values from the textbox.

I'll keep working on this and try to figure out what the deal is.

[Reverend Jim]

Can you post what the connection string looks like when you hard code it, then also post what it looks like when you build it? Perhaps seeing them next to each other would provide a clue.

When I say "post" I don't mean "type in what you think it is". I want you to actually display the string in a msgbox or debug output, then copy and paste.

[mrbungle]

Ok, here is a messagebox with what the error is giving me. The line it is referring to (line 43) is MySqlConnection.Open()- Unable to conenct to any specified hosts.

With this connection string, I am able to connect-

Public urlMySQLDatabase1 As String = _
  "Server=" & "173.201.80.90" & ";" _
& "Port=" & "3300" & ";" _
& "Database=" & "Database" & ";" _
& "Uid=" & "UserID" & ";" _
& "Pwd=" & "Password" & ";"

This one does not.

Public urlMySQLDatabase1 As String = _
    "Server=" & frmLogin.txtServerIP.Text & ";" _
& "Port=" & frmLogin.txtServerPort.Text & ";" _
& "Database=" & "Database" & ";" _
& "Uid=" & "UserID" & ";" _
& "Pwd=" & "Password" & ";"

I hope this information helps.

[Reverend Jim]

You are posting the code with the variables. That's not what I need to see. After line 6 in the above example, please add the line

MsgBox(urlMySQLDatabase1)

and post the result

[mrbungle]

Public urlMySQLDatabase1 As String = _
    "Server=" & frmLogin.txtServerIP.Text & ";" _
& "Port=" & frmLogin.txtServerPort.Text & ";" _
& "Database=" & "Database" & ";" _
& "Uid=" & "UserID" & ";" _
& "Pwd=" & "Password" & ";"

Gives the first message box.

Public urlMySQLDatabase1 As String = _
  "Server=" & "173.201.80.90" & ";" _
& "Port=" & "3300" & ";" _
& "Database=" & "Database" & ";" _
& "Uid=" & "UserID" & ";" _
& "Pwd=" & "Password" & ";"

Gives the second message box with the IP and port

[Reverend Jim]

You are getting txtServerIP.Text and txtServerPort.Text from a different form and it is possible that by the time you try to get these values the form has entered a state where that data is not available. Perhaps it is in the way you are calling frmLogin. Can you post that part of the code, and perhaps the FormClosing part from frmLogin? One of my apps has an edit form that I call repeatedly and I have no problem accessing the fields on the edit form from main after the form is closed.

[mrbungle]

1: Form loads:

Public Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        x.loadSettingsAndDataFile()

    End Sub

2: Next, it loads the x.loadSettingsAndDataFile() that puts the server and port into the text boxes on the frmLogin:

Public Sub loadSettingsAndDataFile()
        If File.Exists(mySettingsAndDataFile) Then
            Dim arFileLines() As String = IO.File.ReadAllLines(mySettingsAndDataFile)
            For i As Integer = 0 To arFileLines.Length - 1
                Try
                    With arFileLines(i)

                        ' ================= GLOBAL OPTIONS =================\\


                        For Each line As String In arFileLines
                            ' With frmMain
                            With frmLogin
                                'If line.StartsWith("Mileage =") Then .txtStartMiles.Text = line.Substring(line.IndexOf("=") + 2)
                                If line.StartsWith("Login ID = ") Then .txtUserID.Text = line.Substring(line.IndexOf("=") + 2)
                                If line.StartsWith("Password = ") Then .txtUserPassword.Text = line.Substring(line.IndexOf("=") + 2)
                                If line.StartsWith("ServerIP = ") Then .txtServerIP.Text = line.Substring(line.IndexOf("=") + 2)
                                If line.StartsWith("ServerPort = ") Then .txtServerPort.Text = line.Substring(line.IndexOf("=") + 2)
                            End With
                            'With frmServerIP
                            '    If line.StartsWith("ServerIP = ") Then .txtServerIP.Text = line.Substring(line.IndexOf("=") + 2)
                            'End With
                        Next

                    End With
                Catch ex As Exception
                    MsgBox("Error loading: " & mySettingsAndDataFile & vbNewLine & "Error #30", MsgBoxStyle.Critical)
                End Try
            Next
        End If
    End Sub

3: Now that the text boxes are populated, when the user clicks OK, it loads the MySQL section which included the string:

Public urlMySQLDatabase1 As String = _
      "Server=" & frmLogin.txtServerIP.Text & ";" _
    & "Port=" & frmLogin.txtServerPort.Text & ";" _
    & "Database=" & "dailylogmain" & ";" _
    & "Uid=" & "dailylogmain" & ";" _
    & "Pwd=" & "Dailylog2011" & ";"

It goes out to the MySQL, validates the user, etc. But before it can get to MySQL, it needs the server and port info which should have been loaded in step 2.

So in short:

1: load form-
2: then load in the ip and port
3: click OK, then goes to MySQL

Oh- frmClosing just saves the values that are in the text boxes.

x.saveSettingsAndDataFile()

[Reverend Jim]

I'm a little coonfused. Are you getting the parameters from a dialog (form) or froma config file? Can I suggest that instead of a config file you use application settings? That way your code can be

if My.Settings.ServerName = "" Then
    'display dialog box for user to enter server and port info
End If

The dialog that allows the user to enter the server name and port number could save these values back to My.Settings before closing. It would also eliminate the problem with parsing a config file. The values in My.Settings can be on a per user or per application basis.

[Begginnerdev]

As reverend stated. It is much easier to create a settings variables such as ServerIP that holds the value from the server ip text box.

That way, on form_exit you can call:

with My.Settings
   .ServerIP = txtServerIP.text
   .Save
end with

That way you can call it on the next form load, or even call it without needing to pull the values from a text box.

On form load.

with My.Settings
   txtServerIP = .ServerIP
end with

[mrbungle]

Thanks for the replies. Yes, the values are being saved in a config file. I was thinking that I would just use the config file to write to and save the values to. But, from your suggestions, I'll use MySettings instead. I'm having to many problems with the config file.

I'll post the code when I'm done.

[mrbungle]

UPDATE- it all works fine. Do I really have to shut the app down and restart it to get the values to work in My.Settings? I have

' Save IP and port to MySettings FIRST THING
        With My.Settings
            .ServerIP = txtServerIP.Text
            .ServerPort = txtServerPort.Text
            .Save()
        End With

on Public Sub OK_Click as the very first thing to do. I thought it would save the value, then at the end of the sub it calls the

Dim load As New clsMySQL
        load.Login()

to load the string from

Public urlMySQLDatabase1 As String = _
      "Server=" & My.Settings.ServerIP & ";" _
    & "Port=" & My.Settings.ServerPort & ";" _
    & "Database=" & "Database" & ";" _
    & "Uid=" & "UserID" & ";" _
    & "Pwd=" & "Password" & ";"

When I click OK it saves, but only on the next startup of the application.

[Reverend Jim]

You don't have to shut down and restart. I should have mentioned that while you have the project open, you have to go into the project properties and add appropriately named and typed variables to the Settings tab. For string values you DO NOT put quotes around the text.

As soon as you do My.Settings.Var = value the value is available. For example

My.Settings.Test = "splunge"
MsgBox(My.Settings.Test)

will pop up a box with the text "splunge". Because you want the settings preserved from one run to the next I thought that using My.Settings would more easily accomplish that and also avoid the problem you were having.

[mrbungle]

Thanks for your help everyone. I got busy a couple of days but got back to work on it today.

Thanks again.

[M.Waqas Aslam]

hello !
if Reverend Jim is right then why not you try to use global variable to get your values , as Reverend Jim said that 'may be by the time you try to get values the form has entered a state where that data is not available'

Regards