Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ADO.net to display one record

Posted on 2011-09-19
2
Medium Priority
?
190 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 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Loops Section Overview
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

571 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