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?
[code=vbnet]
Try
'Access DB File path
Dim File_Path As String
'Put database file in
' Projects\Databa seProg\Database Prog\bin\Debug
'or equivalent folder depending on your folder structure
File_Path = AppDomain.Curre ntDomain.BaseDi rectory & "serverDetails. mdb"

'Connection String for New DB File
Dim Access_ConnStr As String = "Provider=Micro soft.Jet.OLEDB. 4.0;" & " Data Source= " & File_Path
'OleDb Connection for database
Dim Access_Conn As OleDb.OleDbConn ection = New OleDb.OleDbConn ection(Access_C onnStr)
'Access_Conn = New OleDb.OleDbConn ection(Access_C onnStr)
Access_Conn.Ope n()

'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.Cre ateCommand()
Dim cmd2 As OleDbCommand = Access_Conn.Cre ateCommand()
'Create SQL query relevant to your database/table structure


strSQL = "SELECT Passwords FROM(login_deta ils)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.ExecuteRead er
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.FieldCou nt
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.Clo se()
Access_Conn.Dis pose()
Access_Conn = Nothing
Catch ex As Exception 'if it all goes pear-shaped
MsgBox(ex.Messa ge)
End Try[/code]

[Frinavale]

Try changing your strSql from:
[code=vbnet]
strSQL = "SELECT Passwords FROM(login_deta ils)WHERE (Usernames = " & UsName & ")" 'returns username column"
[/code]
To be
[code=vbnet]
strSQL = "SELECT Passwords FROM login_details WHERE Usernames = '" & UsName & "'" 'returns username column"[/code]

[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
[code=vbnet]
For col = 0 To reader.FieldCou nt
colname = reader.GetName( col) 'Gets Column Name
value = reader.GetValue (col).ToString 'Gets value for a
' given column number
lstbox5entries. Items.Add(value )
Next
[/code]

Try changing it to
[code=vbnet]
For col = 0 To reader.FieldCou nt -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
[/code]

[Frinavale]

Try changing your strSql from:
[code=vbnet]
strSQL = "SELECT Passwords FROM(login_deta ils)WHERE (Usernames = " & UsName & ")" 'returns username column"
[/code]
To be
[code=vbnet]
strSQL = "SELECT Passwords FROM login_details WHERE Usernames = '" & UsName & "'" 'returns username column"[/code]

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:
[code=vbnet]
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("L ast5.txt")
For i = 0 To i < 4
strContents = objReader.ReadT oEnd()
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("L ast5.txt")
objReader.Write (strData)
objReader.Close ()
bAns = True
Catch Ex As Exception
ErrInfo = Ex.Message

End Try
Return bAns
End Function

[/code]
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
[code=vbnet]
Dim myFileContents As String = GetFileContents (valueStr, fullPathStr)
[/code]
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:
[code=vbnet]
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("L ast5.txt")
For i = 0 To i < 4
strContents = objReader.ReadT oEnd()
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("L ast5.txt")
objReader.Write (strData)
objReader.Close ()
bAns = True
Catch Ex As Exception
ErrInfo = Ex.Message

End Try
Return bAns
End Function

[/code]
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]

[code=vb]dim cmd as new sqlclient.sqlco mmand
cmd.commandtext ="select pw from table where usname=' "& texbox1.text &" ' "
cmd.connection= con
con.open()
dim dr as sqldatareader=c md.execunonquer y
while dr.read
label1.text=dr( 0)
end while
dr.close()
con.close[/code]

[Frinavale]

[code=vb]dim cmd as new sqlclient.sqlco mmand
cmd.commandtext ="select pw from table where usname=' "& texbox1.text &" ' "
cmd.connection= con
con.open()
dim dr as sqldatareader=c md.execunonquer y
while dr.read
label1.text=dr( 0)
end while
dr.close()
con.close[/code]

Thanks for your input Rachid :)