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
11 1200
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
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"
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?
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
-
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)
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?
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?
Even if it didn't fail the try/catch block, it will still return before sending the data on that socket.
- 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
- 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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mudge |
last post by:
Hi,
My hosting provider only allows me to use 50 connections to my MySQL
database that my Web site will use.
I don't know what this 50 connections means exactly. Does this mean that
only 50...
|
by: cooldv |
last post by:
i am running a website on Windows 2000 server with ASP 3 webpages and
Access 2000 database. (with a hosting company)
traffic is slow at this time but expect to grow. lately i have been
reading...
|
by: Alex |
last post by:
Hi all,
I've been running a db2 V8.1 databasle to store my radius server
accounting info
for a *long* time and have never had any problems with it.
Last week we had a power outage in our...
|
by: Andante.in.Blue |
last post by:
Hello everyone! I've been working with a problematic legacy database
for a while. While I am still fairly new to Access, the more I work
with the database, the more problems I've uncovered. ...
|
by: Ryan Muller |
last post by:
I have a database that I designed for the production floor of my
company. One of the problems that I have encountered is that 2 or more
users will try to access the database to enter information...
|
by: Jeff |
last post by:
Does anyone know of any potential problems running a 2000 database with
2003?
Also, what about installing all other Office products as 2003 versions but
leaving Access as 2002 running a 2000...
|
by: MW de Jager |
last post by:
I'm having endless problems with gaining access to an Access Database that
sits on a different server. My ASP.NET app cannot gain control. The errror
message I get is:
The Microsoft Jet...
|
by: rustyc |
last post by:
Well, here's my first post in this forum (other than saying 'HI' over in the hi forum ;-)
As I said over there:
... for a little side project at home, I'm writing a ham radio web site in...
|
by: Bexm |
last post by:
Hello
I have searched through this forum and it seems some people are having similar problems to me but none of the fixes are fixing mine..! :(
I have a table in my database that has two xml...
|
by: raidvvan |
last post by:
Hi there,
We have been looking for some time now for a database system that can
fit a large distributed computing project, but we haven't been able to
find one.
I was hoping that someone can...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |