Solved

ADO.net to display one record

Posted on 2011-09-19
2
177 Views
Last Modified: 2012-05-12
Hi Experts

Been a while since I used ADO.NET and have pretty much forgot everything! I am trying to retrieve one field froma table (from one record) to check if a user has entered the correct password,  think Im nearly there but can some one help please!
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings _
                                              ("MyConnection").ConnectionString())
            Dim pwDataset As New DataSet
            Dim PasswordCheck As SqlDataAdapter = _
            New SqlDataAdapter()

            PasswordCheck.SelectCommand = New SqlCommand(
                "Select User_Password from tblUser where User_Name ='" & Me.cmbUserList.SelectedValue _
                        & "'", connection)
            PasswordCheck.Fill(pwDataset)
'display the one password value
me.passwordtext.text = pwdataset.value


                   End Using

Open in new window

0
Comment
Question by:MrDavidThorn
2 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 36561113
In your code, pwDataset refers to a DataSet, which means that there are DataTables within that object. What the dataadapter does is that it fills the datatables with the results of the queries you specified.

So in order to retrieve the values, you need to check the datatable, specify the row and column where your data resides.

I'm not in VS right now, but if I remember correctly it should be something like:

me.passwordtext.text = pwdataset.Tables(0).Rows(0)([yourcolumnNumber]).ToString()

Note 1: You're also trying to assign a DataSet object to a Text value, which I think should be giving you trouble.

Note 2: Using a dataset for a simple query is not recommended, because it's performance is not the best. You can maybe consider using a dataReader for this.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 250 total points
ID: 36561168
Since your command retrieves only one value, the best thing to do is simply execute the command through ExecuteScalar:
Dim cmd as SqlCommand = New SqlCommand(
                "Select User_Password from tblUser where User_Name ='" & Me.cmbUserList.SelectedValue _
                        & "'", connection)

me.passwordtext.Text = CStr(cmd.ExecuteScalar())

Open in new window

You do not need to create a DataSet, that creates a DataTable, just to retrieve one value.

By the way, you never use a DataSet when you have only one table. A DataSet is used to maintain relations between tables. If you have only one table, or if you have many tables but without relations between them, use a DataTable. Most of the commands you use on the DataSet, such as Fill, are used exactly the same way on a DataTable. You just lose resources for nothing when creating a DataSet that has no relations in it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Tabcontrol Caption? 1 30
Help with Syntax 9 27
Access to class from any project within a solution. 6 14
Get Client IP on RDS - VB.NET 15 27
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now