Anyone help with VB.NET NooB.

[pwsincd]

Im tryin to establish a connection to a gameserver sql database , i have spoken with the server provider and the connection is possible , ie allows remote connections , i can telnet the database . however im learning VB.NET and am trying to connect to for an app im trying to create , but im getting a connection error , does anyone here know VB and can assist.

Imports System.Data.SqlClient
 
Public Class Form1
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    End Sub
 
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim strConnection As String
        strConnection = "Server=IP; UID=USERNAME; PWD=PASSWORD; Database=DB NAME; pooling=false"
        Dim conn As New SqlConnection(strConnection)
        Try
            conn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        conn.Dispose()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub
End Class

Error Message :

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

the error is on the line : conn.Open()

 

[Pleng]

You need to put the correct IP address, portno username etc in the connection string:

strConnection = "Server=IP; UID=USERNAME; PWD=PASSWORD; Database=DB NAME; pooling=false"

If you've just taken them out for security reasons (well done), you might need to put the username and password in quotes if they contain any special chars:

SERVER='xyz.com';UID='myid';PASSWORD='passwith\specailchars'etc...

 

[pwsincd]

lol thanks .. it was for security . hmm i have a look at that although theres no reference to that on the MSDN

 

[Pleng]

No that'll be found in the documentation of the SQL server you're trying to connect to.

Also did you put those spaces in for readability? As if you've got them there it's possible that the server is interpreting the spaces as part of the username etc

What type of server is it you're trying to connect to?

 

[pwsincd]

Ok im not 100% sure what version the server is i just know its a SQL server i admin with myPHPadmin , maybe i can see from there somehow. I have tried with and without the spaces , that was just the latest attempt pasted here.

allthough looking on a connection strings site , it seems the same string is required for most SQL types / versions.


edit would i be right in saying its a mySQL 5.1 , thats all the info i can see on it ?

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

i have the right string .. i think lol.

 

[Pleng]

If it's managed via myPHPadmin then it'll be mySQL you're connecting to.

You'll need to find an alternative library. According to MSDN (and unsurprisingly considering it's a Microsoft product) sqlConnection is only for connecting to SQL Server databases, which is also a Microsoft product.

(Your best bet, if you don't need to use any mySql specific functionallity, is to do a search for connecting to databases via ODBC)

 

[pwsincd]

Ah , ok ill have a look into that . What would you mean by mysql fuctionality. I do intend to read and write to the DB once connected.

 

[pwsincd]

oops duplicate post.

 

[Pleng]

Ah , ok ill have a look into that . What would you mean by mysql fuctionality. I do intend to read and write to the DB once connected.

SELECTS and INSERTS will be covered by the ODBC libraries. To be honest I can't think of anything specific to MySQL that you'd need, so I'm sure the ODBC will sort you just fine.

 

[pwsincd]

Ok i have looked through the OBDC resources and upon doing so stumbled upon the mySQL.Data dll , having struggled to find the correct source for the OBDC driver i managed , (or so it seems so far ) to have succeeded with the mySQL.data resource , i have managed to connect and verify connection by reading the mysql version and returning that info . So ill approach SELECTS etc now to see how i can manipulate the info .

Thanks pleng for the help so far , ill keep this thread "on the go" for when i stumble into a new problem .. cheers fella.

 

[pwsincd]

Hey pleng , maybe you can assist here fella . I have some code here that connects to my database and verifies connection is ok or wether an exception occurs. It all works fine . Now if i preset the values of the strings when i declare them ( to save me time in debugging) it connects fine , however if i leave the preset values blank it doesnt then take my textbox entries into account and use those as my connection string . I hope you understand what im saying , heres my code :

Public Class Form1
    'common variables
    Public Shared IP As String = "ok if filled with value"
    Public Shared DBNAME As String
    Public Shared USER As String = "but not if empty like above"
    Public Shared PASS As String = "W T F HELP!!!"
    Public Shared conn_ok As Boolean = False
    Public Shared connstr As String = "Server=" & IP & ";Database=" & DBNAME & ";Uid=" & USER & ";Pwd=" & PASS & ";"
    Public Shared myConnection As New MySqlConnection(connstr)
    Public Shared command As New MySqlCommand
    Public Shared adapter As New MySqlDataAdapter
    Public Shared data As MySqlDataReader
    'connection code
    'ip address
    Private Sub KryptonTextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox1.TextChanged
        IP = KryptonTextBox1.Text
    End Sub
    'Database name
    Private Sub KryptonTextBox2_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox2.TextChanged
        DBNAME = KryptonTextBox2.Text
    End Sub
    'username
    Private Sub KryptonTextBox3_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox3.TextChanged
        USER = KryptonTextBox3.Text
    End Sub
    'password
    Private Sub KryptonTextBox4_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox4.TextChanged
        PASS = KryptonTextBox4.Text
    End Sub
    'connection test
    Private Sub KryptonButton1_Click(sender As System.Object, e As System.EventArgs) Handles KryptonButton1.Click
        ' check connection by retrieving sql version
        KryptonRichTextBox1.Text = ""
        Dim stm As String = "SELECT VERSION()"
        Dim Version As String = ""
        Try
            myConnection.Open()
            Dim cmd As MySqlCommand = New MySqlCommand(stm, myConnection)
            Version = Convert.ToString(cmd.ExecuteScalar())
 
            KryptonRichTextBox1.Text = "Successful connection : connected to PermissionsEx database version : " & Version
            conn_ok = True
 
        Catch ex As MySqlException
            KryptonRichTextBox1.Text = "Connection Error : Please verify your database info is correct and your internet connection is valid."
            conn_ok = False
        Finally
            myConnection.Close()
        End Try

 

[OrGoN3]

IP = [String].Copy(KryptonTextBox1.Text)

Well, why not do a Console.WriteLine("IP = {0}", IP) after you set the IP to the text, so you can see what it is really set to.

 

[pwsincd]

Well i populated a textbox with the connection string prior to connection (shown at the bottom of the window) as you can see the login info isnt entered into the textboxes and the subsequent string is displayed in the textbox . However it has clearly connected to my database as shown by the populated listboxes on the right so it has used the pre determined values i set in the string declaration rather than any textbox entry . now if i enter anything into the text boxes that content appears in the bottom textbox but still it uses the predetermined string allocations. If i remove the predetermined string values and enter them into my textboxes it fails to connect.. yet the info appears in the string textbox. So im confused as to wtf info it is using to connect or fail...

 

[Pleng]

In the onChanged events you're updating the IP, UID etc variables, but you don't appear to be updating the connection string

example

Private Sub KryptonTextBox4_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox4.TextChanged
    PASS = KryptonTextBox4.Text
End Sub

Should read

Private Sub KryptonTextBox4_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox4.TextChanged
    PASS = KryptonTextBox4.Text
 
    'now update the connection string!
    connstr = "Server=" & IP & ";Database=" & DBNAME & ";Uid=" & USER & ";Pwd=" & PASS & ";"
End Sub

 

[pwsincd]

I just tried that . No change , if i populate the debug textbox with the string value when i have entered values into the text box , the debug textbox displays what would be the correct conn string but it doesnt connect. If i add the predetermined values back to the string declaration it connects fine upon clicking CONNECT but the connstr displayed in my degub textbox is null apart from the text in inverted commas.

NB: the debug textbox write line is directly prior to a connection attempt , so to my mind it is displaying the current values. But logic tells me that that cant be so as when it connects the variable values dont display.... im totally overlooking something obvious here.

Essentially i cannot pass variables into my connection string .. But i can predetermine them .:s

 

[Pleng]

Can you copy and paste the code you are now using? Because the code you originally posted doesn't alter the value of connStr at all

In fact you're using a fixed string in the btnConnect_Click:

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim strConnection As String
        'look, you are using the value below... you would need to change this to include the
        'values of your variable..
        '
        'example strConnection = "Server=" & IP & "; UID=" & USERNAME &.....
        strConnection = "Server=IP; UID=USERNAME; PWD=PASSWORD; Database=DB NAME; pooling=false"
        Dim conn As New SqlConnection(strConnection)

Have you tried stepping through connect the code and checking the values as your code progresses?

 

[pwsincd]

The code you have pasted isnt the code im using at all anymore since that was my original connection issues prior to using mysql libraries.

My current code is in post #11

Which does include your suggestion .

What do you mean "stepping through connect the code" ?

I have populated a textbox with the connection string value . upon successful connection using :

Public Shared IP As String = "MY IP ADDRESS"
Public Shared DBNAME As String = "MY DB NAME"
Public Shared USER As String = "MY USER NAME"
Public Shared PASS As String = "MY PASSWORD"
and the textbox content is : Server=;Database=:Uid=;Pwd=;
If i enter values into my string textboxes the content of the textbox is: Server=<ip addy>;Database=<db name>:Uid=<username>;Pwd=<and my password>; connection fails.

then just before i open the connection :myConnection.Open()
i write the string value to the textbox to see whats going on...

i just dont get it.

 

[Pleng]

Ummm

Ok so the code in post 11 doesn't do what I suggested.

Let me take you through a couple of points. I hope you don't find this condecending, I just need to make sure you're aware of what's going on in the code you posted

Public Class Form1
    'common variables
    Public Shared IP As String = "ok if filled with value"
    Public Shared DBNAME As String
    Public Shared USER As String = "but not if empty like above"
    Public Shared PASS As String = "W T F HELP!!!"
    Public Shared conn_ok As Boolean = False

    'ok you are declaring your connstr and connection HERE.They are never reassigned values further on in the code
    'changing the value of DBNAME, IP, etc will not automatically update the value of connstr, and updating the
    'value of connstr does not automatically update the value of myConnection.

    'you need to reassign these variables either whenever you make a change to one of the base values or, more 
    'realistically, before you first access them

    'take a look at the code I've added to the onclick event

    Public Shared connstr As String = "Server=" & IP & ";Database=" & DBNAME & ";Uid=" & USER & ";Pwd=" & PASS & ";"
    Public Shared myConnection As New MySqlConnection(connstr)


    Public Shared command As New MySqlCommand
    Public Shared adapter As New MySqlDataAdapter
    Public Shared data As MySqlDataReader
    'connection code
    'ip address
    Private Sub KryptonTextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox1.TextChanged
        IP = KryptonTextBox1.Text
    End Sub
    'Database name
    Private Sub KryptonTextBox2_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox2.TextChanged
        DBNAME = KryptonTextBox2.Text
    End Sub
    'username
    Private Sub KryptonTextBox3_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox3.TextChanged
        USER = KryptonTextBox3.Text
    End Sub
    'password
    Private Sub KryptonTextBox4_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox4.TextChanged
        PASS = KryptonTextBox4.Text
    End Sub
    'connection test
    Private Sub KryptonButton1_Click(sender As System.Object, e As System.EventArgs) Handles KryptonButton1.Click
        ' check connection by retrieving sql version
        KryptonRichTextBox1.Text = ""
        Dim stm As String = "SELECT VERSION()"
        Dim Version As String = ""
        Try
            'so our variables are being updated on the OnTextChanged event (it may just as well be to do it here
            'as opposed to the OnTextChanged, unless you specifically need it done immidiately). However we have
            'NOT yet updated connstr or myConnection. We first need to update connstr so it contains the NEW values of
            'the variables, then update the connection so it references the new connStr

  
            connstr = "Server=" & IP & ";Database=" & DBNAME & ";Uid=" & USER & ";Pwd=" & PASS & ";"
            myConnection = New MySqlConnection(connstr) myConnection.Open()

            'now we've made those changes, everything should be fine from here on.

            Dim cmd As MySqlCommand = New MySqlCommand(stm, myConnection)
            Version = Convert.ToString(cmd.ExecuteScalar())
 
            KryptonRichTextBox1.Text = "Successful connection : connected to PermissionsEx database version : " & Version
            conn_ok = True
 
        Catch ex As MySqlException
            KryptonRichTextBox1.Text = "Connection Error : Please verify your database info is correct and your internet connection is valid."
            conn_ok = False
        Finally
            myConnection.Close()
        End Try

As for stepping through. If you double click on the margin next to your code in visual studio, you will see a red blob appear. The debugger will halt at this point and you can go through each line of the code individually and see the values of any variables by hovering the mouse pointer over it to see what it contains.
Another quick method to debug would be to MessageBox.Show(ex.ToString) in your Catch block.

 

[pwsincd]

Right that did kind of indireclty help . To the point i realised i am not re-instating the connection . So i added :

        Try
            connstr = "Server=" & KryptonTextBox1.Text & ";Database=" & KryptonTextBox2.Text & ";Uid=" & KryptonTextBox3.Text & ";Pwd=" & KryptonTextBox4.Text & ";"
            myConnection = New MySqlConnection(connstr)
            myConnection.Open()

And now all is well . Seems i have to refresh the connection with the new info as i had preloaded the connection in the public shared variables.

Thanks for the help again pleng bud .

 

[Pleng]

No worries