Results 1 to 5 of 5

Thread: Simple DB connection question

  1. #1

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    45

    Simple DB connection question

    Hi,

    I am trying to connect to a sql DB my test connection works fine from the TOOLS connect to DATASOURCE but i cannot access the DB.

    Code:
        Private Sub btnlogon_Click(sender As Object, e As EventArgs) Handles btnlogon.Click
            If txtpassword.Text = "" Or txtusername.Text = "" Then
                MessageBox.Show("Please enter a Username or Password...", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
    
                Dim Sqlcon As SqlConnection = New SqlConnection("Data Source=PHONE-SYSTEM\RFID;Initial Catalog=RFID;Persist Security Info=True;User ID=sa;Password=******")
                Dim SQLcmd As SqlCommand
                Dim QueryOpen As String = "select * from [AdminAccess] Where userid='" & txtusername.Text & "' and password = '" & txtpassword.Text & "'"
    
                Try
                    SQLcmd = New SqlCommand(QueryOpen, Sqlcon)
                    Sqlcon.Open()
                    Dim reader As SqlDataReader = SQLcmd.ExecuteReader()
                    If reader.Read() Then
                        Form3.Show()
                        txtusername.Text = ""
                        txtpassword.Text = ""
                        Me.Close()
                    Else
                        MessageBox.Show("Username and password do not match...", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                        txtusername.Text = ""
                        txtpassword.Text = ""
                        txtusername.Focus()
                    End If
                Catch
                    MessageBox.Show("Failed to connect to database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
    
    
            End If
    
        End Sub
    I just get the failed to connect to database error anyone got any idea's i just cannot get me head around it.

    Cheers James

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,459

    Re: Simple DB connection question

    There are loads of things wrong with that, but to focus on the first one, that's a horrible exception handler. An exception at that point could be all kinds of different things, but you ignore the exception object and the information it contains and just assume that it's a connection problem. It may be, but you won't know with that exception handler. You may have connected just fine, and have a problem with your SQL statement.
    My usual boring signature: Nothing

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,429

    Re: Simple DB connection question

    Quote Originally Posted by VBJames View Post
    I just get the failed to connect to database error
    No you don't. You get that MESSAGE because that's the message you wrote but that's not the error. As suggested above, you ignore the actual error. As the developer, you should be looking at the exception object to see what the error actually was. Given the code that in that Try block, you shouldn't even have just one Catch block, because, as suggested above, there could be a number of different things that went wrong. Bew a developer and actually look at the exception that is thrown to see what went wrong. Maybe it is a connection issue but maybe not. Find out.

  4. #4
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    6,355

    Re: Simple DB connection question

    One of the things I love about SQL Server is that it gives you very meaningful errors when something fails. As suggested above, you should definitely be checking what Exception is being thrown.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena


    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,598

    Re: Simple DB connection question

    You could (as already recommended) use a try/catch e.g. taken from one of my GitHub repositories for logging in with defined user in SQL-Server and place a breakpoint on the try then examine the error.

    Code:
    Using cn = New SqlConnection With {.ConnectionString = connectionString}
        Try
            cn.Credential = credentials
            cn.Open()
            loginResult.Success = True
        Catch failedLoginException As SqlException When failedLoginException.Number = 18456
            loginResult.Success = False
            loginResult.GenericException = False
            loginResult.Message = "Can not access data."
        Catch genericSqlException As SqlException
            loginResult.Success = False
            loginResult.GenericException = False
            loginResult.Message = "Can not access data."
        Catch ex As Exception
            loginResult.Success = False
            loginResult.GenericException = True
            loginResult.Message = ex.Message
        End Try
    
    End Using
    Note the text for Message can be whatever you want.

    For error code 18456, see the following.

    Once the connection issue is resolved, consider using SqlCredential Class for better security for login passwords.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width