Solved

ADO.net to display one record

Posted on 2011-09-19
2
176 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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

9 Experts available now in Live!

Get 1:1 Help Now