Retrieving row info

  • Thread starter Thread starter Teo
  • Start date Start date
T

Teo

Hi! I learned how to use Adapters to create a Connection and retrieve a
Dataset based on a query using the VB control tools on the Data toolbar.
Now, if I don't have any fields to display the info, how would I go about to
go into my dataset, and check to see if my query returned a value or not.
For example, I am building a login system, I run a stored procedure based on
the parameters login and password inside the DataAdapter and create a
Dataset.
Now where do I go from there. I want to check the results and compare it to
the fields entered by the users in the text boxes to see if they match.

Sorry for asking so much, I am in the process of learning and I bought David
Sceppa book, but I don't seem to find many examples using the graphical
DataAdapter.

Hope someone can help, I know it's really basic stuff but if I learn it well
from the begining I will be able to learn it well.


Thanks so much in advance,
Teo
 
Hey David, thanks for your prompt reply.

I am trying to build a login system for my program. I have a Users table
where I will store my users that will use the application.
I created a login screen where I get the input from the user (username and
password) through textfields.
I also added an OK button that saves both values from the textboxes to
variables.
Now, what I'm trying to do is to run a query on my stored procedure (that
checks whether the user exists or not on the DB) and if the information is
correct. What I tried to do was to drag an OleDbAdapter into the form,
select the stored procedure and that was it.
Now, how can I go and check the DB to see whether it returned a value or not
and also if the info in the textboxes is the same as the info in the DB.

The whole example is a login system for a program, but I have been coding a
lot of coldfusion lately and I am a little confused on the way VB handles
data connections.

Thanks so much in advance,
Teo
 
Teo,

If you're simply trying to check to see if there's a row
that matches the criteria that the user entered, I'd use a
parameterized query inside of the stored procedure like:

SELECT COUNT(ID) FROM MyUsersTable
WHERE UserName = @UserName AND Password = @Password

Supply the values that the user entered as parameters, then call
Command.ExecuteScalar(). If the return value is 0, there's no
match. If the return value is 1 or more, there's a match. This
approach would be a great deal easier than calling
DataAdapter.Fill, locating the row in the DataTable and comparing
column values to contents of textboxes.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
The Command.ExecuteScalar() funcion is for the OleDbCommand? I Opened the
connection for OleDbConnection2 object, now what do I need to do to run the
Stored procedure and get the results?

Thanks,
Teo
 
Teo,
what do I need to do to run the Stored procedure
and get the results?
Click to expand...

Create a Command.
Set the CommandText to the name of your stored procedure.
Set CommandType to CommandType.StoredProcedure.
Append parameters to the Command.

How you execute the Command and check for results will
depend on how you've constructed the stored procedure and what
type of results you want to examine.

If your stored procedure returns a resultset, call
Command.ExecuteReader and fetch the results from the DataReader.

If your stored procedure returns data via output parameters
and also returns a resultset, call Command.ExecuteReader, fetch
the results from the reader, close the reader, then check the
value of your Parameter objects.

If your stored procedure returns data only via output
parameters, call Command.ExecuteNonQuery and check the value of
your Parameter objects.

ExecuteScalar is a method that's available on the various
Command objects (OleDbCommand, SqlCommand, etc.) that can
simplify your coding. It's designed for queries that return a
single value, like "SELECT COUNT(*) FROM MyTable WHERE ...". It
creates, consumes, and closes a DataReader under the covers, and
returns the first column value for the first row.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Hey David! First of all, thanks so much for helping me with this, I know I
will get to understand it better once this example works. I understood the
Command properties and how to create a command. I did a SQLDBADAPTER,
attached it to my stored procedure and then created a Command, and did all
the renaming and selected it as a StoredProcedure.
Now, I want to call my stored procedure that is like the following
SELECT COUNT(Usuario_ID) FROM Usuario WHERE ......

I have two parameters, @login and @password so based on that, the query will
return 1 or 0 depending whether the user is found or not. Now, this is the
code I have on the OK button on my login form that gets values from my
textboxes and attaches it to the parameters of the stored procedure. Is
there something I am doing wrong cause I keep getting a message that it's
not working. Check the code below.

Dim login As String

Dim password As String

Dim oklogin As Boolean

login = txtlogin.Text

password = txtpassword.Text

Dim callds As String

Dim query As String

query="checklogin"&login,password

callds = checklogin.CommandText(query)

Dim loginresult As Integer

loginresult = checklogin.ExecuteScalar

If loginresult = 0 Then

MsgBox("Usuario no valido, vuelva a intentar...", "", "Usuario no valido")

ElseIf loginresult = 1 Then

MsgBox("Bienvenido a Flight School Professional!", "", "Usuario
Autenticado")

End If

Thanks so much,
Teo
 
Hey David!
Sorry I keep asking but I'm still having dificulties writing that code. Do
you want me to use the data controls on VB, or do I just write that code
under the OK button so it can run the stored procedure.
Keep in mind that I already created the stored procedure in the Server
Explorer.
Is there a step by step tutorial in your book or on the internet that I
could look at? I am getting really confused as whether I should use the
controls that come in the Data Toolbox or should I just write the code for
each connection.
It's really confusing.
Thanks so much
Teo
 
Also, how would I execute a stored procedure, not passed as a string, for
example, if my stored procedure is called checklogin and I would have two
parameters, @login and @password, I would do
checklogin,"value1","value2"

I tried doing that in VB and it doesn't work, how would you put that in a
string to execute?
I already got it to open the connection and also assigned the parameters to
my variables. Now, how can I execute the stored procedure with the values
assigned as Parameter.Value?
Thanks,
Teo
 
Teo,

The ultimate goal is to apply this approach to your
application. When I get stuck in a large project, I generally
isolate the problem in a simple Console project (all code) or
Windows Forms project (code plus components from the toolbox if
you'd like). You should be able to take the code from my
previous post and paste it into a simple isolated project, then
change the code to call your stored procedure. Once you get the
code to work the way you want in the isolated project, apply that
knowledge to your application.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Great! will do that. I'm actually working on a rather huge project by myself
and I wanted to get this problem out of the way. Now I modified my stored
procedure to see if I can get it to return a 1 or 0 bit saying if it
returned a value or not.
If that doesn't work, I'll move on to isolate the problem.

Teo
 
Sounds like a good plan.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Back
Top