Retrieving row info

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

Teo
Fri Jul 11 09:45:26 CDT 2003

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
"David Sceppa" <davidsc@online.microsoft.com> wrote in message
news:4gcr7uzRDHA.2284@cpmsftngxa06.phx.gbl...
> Teo,
>
> > 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.
>
> I'm a little unclear on what you're asking. Did the query
> return multiple rows and you want to locate the contents of a
> particular row?
>
> Chapter 8 of "Microsoft ADO.NET" talks about different ways
> to search for and filter data - DataTable.Find, DataTable.Select,
> and DataView.Find. That may be the information you're looking
> for.
>
> If you could supply a little more information on the data
> the query returns, someone may be able to provide you with more
> specifics.
>
> 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.
>

davidsc
Fri Jul 11 16:44:37 CDT 2003

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.

Teo
Tue Jul 15 15:39:25 CDT 2003

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
"David Sceppa" <davidsc@online.microsoft.com> wrote in message
news:GtLqkW$RDHA.2472@cpmsftngxa06.phx.gbl...
> 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.
>

davidsc
Tue Jul 15 17:03:44 CDT 2003

Teo,

> what do I need to do to run the Stored procedure
> and get the results?

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.

Teo
Tue Jul 15 22:49:20 CDT 2003

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

"David Sceppa" <davidsc@online.microsoft.com> wrote in message
news:af874zxSDHA.2316@cpmsftngxa06.phx.gbl...
> Teo,
>
> > what do I need to do to run the Stored procedure
> > and get the results?
>
> 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.
>

Teo
Thu Jul 17 10:40:09 CDT 2003

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
"David Sceppa" <davidsc@online.microsoft.com> wrote in message
news:khqCZg$SDHA.2044@cpmsftngxa06.phx.gbl...
>
> > Is there something I am doing wrong cause I
> > keep getting a message that it's not working.
>
> Here's some simple code to execute a similar query against
> the Northwind database. 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.
>
> Dim strConn As String
> strConn = "Data Source=(local);Initial Catalog=Northwind;" & _
> "Trusted_Connection=Yes;"
> Dim cn As New SqlConnection(strConn)
> cn.Open()
>
> CreateProcedure(cn)
>
> Dim cmd As New SqlCommand("CustomersInCountry", cn)
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@Country", "Canada")
> Dim intCustomers As Integer = cmd.ExecuteScalar()
> Console.WriteLine("Query returned " & intCustomers)
>
> cn.Close()
>
> Private Sub CreateProcedure(ByVal cn As SqlConnection)
> Dim strSQL As String
> strSQL = "CREATE PROCEDURE CustomersInCountry " & _
> "(@Country varchar(32)) AS " & _
> "SELECT COUNT(CustomerID) FROM Customers " & _
> "WHERE Country = @Country"
> Dim cmd As New SqlCommand(strSQL, cn)
> cmd.ExecuteNonQuery()
> End Sub
>

Teo
Thu Jul 17 11:36:50 CDT 2003

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
"David Sceppa" <davidsc@online.microsoft.com> wrote in message
news:khqCZg$SDHA.2044@cpmsftngxa06.phx.gbl...
>
> > Is there something I am doing wrong cause I
> > keep getting a message that it's not working.
>
> Here's some simple code to execute a similar query against
> the Northwind database. 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.
>
> Dim strConn As String
> strConn = "Data Source=(local);Initial Catalog=Northwind;" & _
> "Trusted_Connection=Yes;"
> Dim cn As New SqlConnection(strConn)
> cn.Open()
>
> CreateProcedure(cn)
>
> Dim cmd As New SqlCommand("CustomersInCountry", cn)
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@Country", "Canada")
> Dim intCustomers As Integer = cmd.ExecuteScalar()
> Console.WriteLine("Query returned " & intCustomers)
>
> cn.Close()
>
> Private Sub CreateProcedure(ByVal cn As SqlConnection)
> Dim strSQL As String
> strSQL = "CREATE PROCEDURE CustomersInCountry " & _
> "(@Country varchar(32)) AS " & _
> "SELECT COUNT(CustomerID) FROM Customers " & _
> "WHERE Country = @Country"
> Dim cmd As New SqlCommand(strSQL, cn)
> cmd.ExecuteNonQuery()
> End Sub
>

davidsc
Thu Jul 17 18:00:20 CDT 2003

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.

Teo
Thu Jul 17 22:17:24 CDT 2003

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
"David Sceppa" <davidsc@online.microsoft.com> wrote in message
news:gWHi1cLTDHA.2448@cpmsftngxa06.phx.gbl...
> 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.
>

davidsc
Fri Jul 18 12:12:06 CDT 2003


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.