Database Problems

[ANGELSDAGGER]

I have a client server application in VB.NET. I have a table named login_details, column names Password and Username. I have two variables that are sent from the server called UsName and PWord.

Im having trouble with the Syntax for the query.

Psuedocode:

Select Password from login_details where Username = UsName

Can anyone write the correct query for this in VB? Everything I've tried so far has returned an error or nothing at all.

I'd appreciate any help.

Thanks

[Frinavale]

Hi Angelsdagger,

Have you seen the .NET Article on How to use a database in your program?

It gives examples on how to retrieve information from your database.


Cheers!

-Frinny

[ANGELSDAGGER]

Yeah I looked at that but it didn't help. Here's the code I have... can anyone help me?

Try
            'Access DB File path
            Dim File_Path As String
            'Put database file in
            ' Projects\DatabaseProg\DatabaseProg\bin\Debug
            'or equivalent folder depending on your folder structure
            File_Path = AppDomain.CurrentDomain.BaseDirectory & "serverDetails.mdb"
 
            'Connection String for New DB File
            Dim Access_ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & " Data Source= " & File_Path
            'OleDb Connection for database
            Dim Access_Conn As OleDb.OleDbConnection = New OleDb.OleDbConnection(Access_ConnStr)
            'Access_Conn = New OleDb.OleDbConnection(Access_ConnStr)
            Access_Conn.Open()
 
            'Database is now open and can be queried or updated
            'Define SQL query and send to database
            Dim strSQL As String
            Dim cmd As OleDbCommand = Access_Conn.CreateCommand()
            Dim cmd2 As OleDbCommand = Access_Conn.CreateCommand()
            'Create SQL query relevant to your database/table structure
 
 
            strSQL = "SELECT Passwords FROM(login_details)WHERE (Usernames = " & UsName & ")"        'returns username column"
 
 
            cmd.CommandText = strSQL
 
 
            If strSQL = PWord Then
                MsgBox("OK")
                lstbox5entries.Items.Add(PWord)
            End If
 
            Dim reader As OleDbDataReader
            reader = cmd.ExecuteReader
            reader.Read()
 
 
 
 
 
            'The result of the query is now available in "reader"
 
            Dim col As Integer
            Dim colname, value As String
 
 
            For col = 0 To reader.FieldCount
                colname = reader.GetName(col) 'Gets Column Name
                value = reader.GetValue(col).ToString 'Gets value for a
                ' given column number
                lstbox5entries.Items.Add(value)
            Next
            'Now clean up all connections and release resources
            reader.Close()
            cmd.Dispose()
            cmd = Nothing
            Access_Conn.Close()
            Access_Conn.Dispose()
            Access_Conn = Nothing
        Catch ex As Exception 'if it all goes pear-shaped
            MsgBox(ex.Message)
        End Try

[Frinavale]

Try changing your strSql from:

strSQL = "SELECT Passwords FROM(login_details)WHERE (Usernames = " & UsName & ")"        'returns username column"
 

To be

strSQL = "SELECT Passwords FROM login_details WHERE Usernames = '" & UsName & "'"        'returns username column"

[ANGELSDAGGER]

Ok that worked, it put the password into the box, but I got a message box saying "Index outwith bounds of the array".

Any ideas?

[Frinavale]

Ok that worked, it put the password into the box, but I got a message box saying "Index outwith bounds of the array".

Any ideas?

It's probably

For col = 0 To reader.FieldCount
                colname = reader.GetName(col) 'Gets Column Name
                value = reader.GetValue(col).ToString 'Gets value for a
                ' given column number
                lstbox5entries.Items.Add(value)
Next
 

Try changing it to

For col = 0 To reader.FieldCount -1
                colname = reader.GetName(col) 'Gets Column Name
                value = reader.GetValue(col).ToString 'Gets value for a
                ' given column number
                lstbox5entries.Items.Add(value)
Next
 

[Frinavale]

Try changing your strSql from:

strSQL = "SELECT Passwords FROM(login_details)WHERE (Usernames = " & UsName & ")"        'returns username column"
 

To be

strSQL = "SELECT Passwords FROM login_details WHERE Usernames = '" & UsName & "'"        'returns username column"

Just so you know ....it is not a good idea to insert your variables directly into the Sql query string like you are doing here.

Make sure that anything you are using in the query string has been sanitized...and it is strongly recommended that you add parameters to your Sql commands as outlined in the how to use a database in your program article.

This is to make sure that you are securely using your database.
(I recommend looking this stuff up on MSDN for a more in depth understanding of why this is good practice)

[ANGELSDAGGER]

You're a star, thank you!!!

One last question. Do you know how to call a function in VB.NET. I have the following:

 Public Function GetFileContents(ByVal value As String, ByVal FullPath As String, Optional ByRef ErrInfo As String = "") As String
 
        Dim strContents As String
        Dim objReader As StreamReader
        Dim bAns As Boolean = False
        Dim i As Integer
 
        Try
 
            objReader = New StreamReader("Last5.txt")
            For i = 0 To i < 4
                strContents = objReader.ReadToEnd()
                objReader.Close()
                Return strContents
                socket.SendData(strContents)
            Next
 
        Catch Ex As Exception
            ErrInfo = Ex.Message
        End Try
 
 
    End Function
 
    Public Function SaveTextToFile(ByVal strData As String, ByVal FullPath As String, Optional ByVal ErrInfo As String = "") As Boolean
 
 
        Dim bAns As Boolean = False
        Dim objReader As StreamWriter
        Try
 
 
            objReader = New StreamWriter("Last5.txt")
            objReader.Write(strData)
            objReader.Close()
            bAns = True
        Catch Ex As Exception
            ErrInfo = Ex.Message
 
        End Try
        Return bAns
    End Function
 
 

And I want to call that Function after my SQL query has a result but it won't let me. Do you know how to do this?

[Frinavale]

I'm not quite sure what you are asking.
Your GetFileContents Function is supposed to return a String value....if it fails within the Try/Catch block your function doesn't return. Maybe this is your problem? I'd move the Return strContents outside of the Try/Catch block so that it always returns a String (even if it's empty).

I'm not quite sure what you mean by "it won't let you" call a function after you've retrieved your data from the database.....

To call a function in .NET you just...call it...

For instance if you were calling your GetFileContents function you would do something like

Dim myFileContents As String = GetFileContents(valueStr, fullPathStr)
 

This will call the function and store the function's output into the myFileContents String....(but if there was a problem then nothing's returned...you should fix this)

Could you be more clear about the problem?

Thanks

-Frinny

You're a star, thank you!!!

One last question. Do you know how to call a function in VB.NET. I have the following:

 Public Function GetFileContents(ByVal value As String, ByVal FullPath As String, Optional ByRef ErrInfo As String = "") As String
 
        Dim strContents As String
        Dim objReader As StreamReader
        Dim bAns As Boolean = False
        Dim i As Integer
 
        Try
 
            objReader = New StreamReader("Last5.txt")
            For i = 0 To i < 4
                strContents = objReader.ReadToEnd()
                objReader.Close()
                Return strContents
                socket.SendData(strContents)
            Next
 
        Catch Ex As Exception
            ErrInfo = Ex.Message
        End Try
 
 
    End Function
 
    Public Function SaveTextToFile(ByVal strData As String, ByVal FullPath As String, Optional ByVal ErrInfo As String = "") As Boolean
 
 
        Dim bAns As Boolean = False
        Dim objReader As StreamWriter
        Try
 
 
            objReader = New StreamWriter("Last5.txt")
            objReader.Write(strData)
            objReader.Close()
            bAns = True
        Catch Ex As Exception
            ErrInfo = Ex.Message
 
        End Try
        Return bAns
    End Function
 
 

And I want to call that Function after my SQL query has a result but it won't let me. Do you know how to do this?

[Plater]

Even if it didn't fail the try/catch block, it will still return before sending the data on that socket.

[rachid]

dim cmd as new sqlclient.sqlcommand
cmd.commandtext="select pw from table where usname='  "& texbox1.text &"  ' "
cmd.connection=con
con.open()
dim dr as sqldatareader=cmd.execunonquery
while dr.read
label1.text=dr(0)
end while
dr.close()
con.close

[Frinavale]

dim cmd as new sqlclient.sqlcommand
cmd.commandtext="select pw from table where usname='  "& texbox1.text &"  ' "
cmd.connection=con
con.open()
dim dr as sqldatareader=cmd.execunonquery
while dr.read
label1.text=dr(0)
end while
dr.close()
con.close

Thanks for your input Rachid :)