Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ADO.net to display one record

Posted on 2011-09-19
2
179 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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