Anyone help with VB.NET NooB.

Discussion in 'Computer Programming, Emulation, and Game Modding' started by pwsincd, Apr 16, 2013.

Apr 16, 2013
  1. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    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.

    Code:
    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()
     
  2. Pleng

    Member Pleng GBAtemp Maniac

    Joined:
    Sep 14, 2011
    Messages:
    1,449
    Country:
    Thailand
    You need to put the correct IP address, portno username etc in the connection string:

    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:

     
  3. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    lol thanks .. it was for security . hmm i have a look at that although theres no reference to that on the MSDN
     
  4. Pleng

    Member Pleng GBAtemp Maniac

    Joined:
    Sep 14, 2011
    Messages:
    1,449
    Country:
    Thailand
    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?
     
  5. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    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.
     
  6. Pleng

    Member Pleng GBAtemp Maniac

    Joined:
    Sep 14, 2011
    Messages:
    1,449
    Country:
    Thailand
    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)
     
  7. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    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.
     
  8. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    oops duplicate post.
     
  9. Pleng

    Member Pleng GBAtemp Maniac

    Joined:
    Sep 14, 2011
    Messages:
    1,449
    Country:
    Thailand
    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.
     
  10. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    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.
     
  11. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    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 :

    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
     
  12. OrGoN3

    Member OrGoN3 GBAtemp Maniac

    Joined:
    Apr 23, 2007
    Messages:
    1,003
    Country:
    United States
    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.
     
  13. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    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...
     

    Attached Files:

  14. Pleng

    Member Pleng GBAtemp Maniac

    Joined:
    Sep 14, 2011
    Messages:
    1,449
    Country:
    Thailand
    In the onChanged events you're updating the IP, UID etc variables, but you don't appear to be updating the connection string

    example
    Code:
    Private Sub KryptonTextBox4_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox4.TextChanged
        PASS = KryptonTextBox4.Text
    End Sub
    
    Should read
    Code:
    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
    
     
  15. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    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
     
  16. Pleng

    Member Pleng GBAtemp Maniac

    Joined:
    Sep 14, 2011
    Messages:
    1,449
    Country:
    Thailand
    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:

    Code:
    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?
     
  17. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    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.
     
  18. Pleng

    Member Pleng GBAtemp Maniac

    Joined:
    Sep 14, 2011
    Messages:
    1,449
    Country:
    Thailand
    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


    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
    
        '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.
     
  19. pwsincd
    OP

    Member pwsincd Garage Flower

    Joined:
    Dec 4, 2011
    Messages:
    2,975
    Location:
    Manchester UK
    Country:
    United Kingdom
    Right that did kind of indireclty help . To the point i realised i am not re-instating the connection . So i added :

    Code:
            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 .
     
  20. Pleng

    Member Pleng GBAtemp Maniac

    Joined:
    Sep 14, 2011
    Messages:
    1,449
    Country:
    Thailand
    No worries
     

Share This Page