Solved

ADO.net to display one record

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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