Solved

How to execute a SELECT command and fill TextBox1.Text with its output?

Posted on 2007-11-24
7
553 Views
Last Modified: 2013-12-25
Hello,
     In a form using VB (please, no <ASP:> things unless necessary), I need a simple way to execute a SELECT command against a SQLEXPRESS db, retrieve data based on the query, and fill a textbox with its output or a boolean value that, at least, lets me know if there were any matches at all. Here's my code so far:

Dim myFabDataSource as New SqlDataSource
myFabDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("FabDBConnectionString1").ToString()
myFabDataSource.SelectCommandType = SqlDataSourceCommandType.Text
myFabDataSource.SelectCommand = "select * FROM Insect_Table where insectname = @insectname"
myFabDataSource.SelectParameters.Add("insectname", Visitors_Interest_Box1.Text)
Feedback_Box1.text = myFabDataSource.SelectCommand()

As you can probably see, when I run it, my Feedback_Box1 gets filled with the words, "select * FROM Insect_Table where insectname = @insectname", which really doesn't do anybody any good. I've even found this question asked quite directly at http://forums.asp.net/t/1072103.aspx, but I don't understand the dataview stuff at the end and how it solves the objective. If you could explain it, that would be great. Here is the solution provided by user, ecbruck. Please visit that URL or check this out. This appears to be the accepted solution:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
      Dim myDataSource As New SqlDataSource()
      myDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ToString
      myDataSource.SelectCommandType = SqlDataSourceCommandType.Text
      myDataSource.SelectCommand = "SELECT * FROM [Products] WHERE ProductID=@ProductID"
      myDataSource.SelectParameters.Add("ProductID", 1)

      Dim dv As Data.DataView = CType(myDataSource.Select(Web.UI.DataSourceSelectArguments.Empty), Data.DataView)
      TextBox1.Text = dv(0)("ProductName").ToString
End Sub

    Now, I suppose that what I need occurs in the line----> TextBox1.Text = dv(0)("ProductName").ToString but I don't understand what ProductName is or if that should be substituted with the search term. I more or less understand it all except for that business at the end. I suppose what I need is someone to please explain to me this DataView business and how I can use it or some other tool to get rows from the DB or boolean values that tell me if rows exist, etc. Thank you.
0
Comment
Question by:T-Dev
  • 3
  • 3
7 Comments
 
LVL 7

Accepted Solution

by:
jax79sg earned 200 total points
Comment Utility
I suppose the only thing you have problems understanding is those in the code snippet below.You can focus on the following lines in code snippet.

.SELECT * FROM [Products] WHERE ProductID=@ProductID"
The above sql returns you a single record, assuming there is no duplicate product IDs.
For example, a single record which contains the ProductId, ProductName, ProductDescription is returned.

TextBox1.Text = dv(0)("ProductName").ToString
dv(0) will refer to the first row in your records. The number '0' refers to the record number. Note that in this case there is only 1 record assumed, thus dv(0) is used. If there are more records, use dv(N), where N is number you would place. Remember, 0 refers to the first record, 1 refers to the second and so on.

dv(0)("ProductName")  returns you the particular column in the record. In my example, a record can contain a ProductID, ProductName, ProductDescription, so in this case, you are retreiving the ProductName. Note that 'ProductName' refers to the Table column name in this case. If you use SQL statement like 'select ProductName as PName from Products......', then you will need to phrase dv(0)("ProductName") to dv(0)("PName");

Hope this helps.


myDataSource.SelectCommand = "SELECT * FROM [Products] WHERE ProductID=@ProductID"

TextBox1.Text = dv(0)("ProductName").ToString

Open in new window

0
 
LVL 6

Assisted Solution

by:PaultheBroker
PaultheBroker earned 100 total points
Comment Utility
I would make a rational guess that "ProductName" is a field name - and what TextBox1.Text = dv(0)("ProductName").ToString is doing is setting the text value of TextBox1 to be the value that is in the first row of the field 'ProductName' (recordsets are zero indexed, I think - so dv(1) would be the second row

The query is acutally getting executed in this statement :
Dim dv As Data.DataView = CType(myDataSource.Select(Web.UI.DataSourceSelectArguments.Empty), Data.DataView)

So without that, you have nothing.  Howveer, once the query has been executed, the data will be in the recordset (DataView) called dv

So I'm guessing you need to subsititute "ProductName" with the name of the database field that contains the data you want to display in you text box, and then add these two lines to your code, and you should be all set.... :)
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
I'm pretty sure I'm wrong about the DataView statement executing the query (but I'm sure dv(0) won't work unless you declare it and define it...but then Jax actually sounds like he knows what he's taliking about, unlike me.... :)
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:T-Dev
Comment Utility
   I think I "got it" now; thanks to you guys. I suppose what I'm learning is that I have to first declare some kind of variable in VB that can hold the results of the SQL query.... however, there is no data type in VB created just for that purpose that would be really easy to use like:

Dim myQueryResults as New sqlQueryResultTable

    Am I going in the right direction?... I've also learned about Visual Web Developer's GridView control as a result of your feedback. It promises to be a capable alternative, but I need to work a bit more in the "laboratory" to know about it. It also performs SELECT queries and puts their results on web pages, BUT I don't always want results on web pages, which is why I might have to use this dataview method.

    I tested the information supplied by Jax, which worked (and confirmed that Paul is correct also). dv(0) did indeed return the first result IF there was a result, and "ProductName" is indeed a reference to a column name in the SQL table. If there were no results, I got this exception:

System.IndexOutOfRangeException    {"Index 0 is either negative or above rows count."}
  Data {System.Collections.ListDictionaryInternal}
  {System.Collections.ListDictionaryInternal} {System.Collections.ListDictionaryInternal}
  Count 0
 
    So, here is the direction I'm headed in now. Please let me know if I'm going in the right one, and I'll then hand out the points. As Paul said, "once the query has been executed, the data will be in the recordset (DataView) called dv". Therefore, I just have to query parameters relating to dv like dv.Count or dv(loop through numbers)("the columns I want") and I can get info or data from the query; right?

    Would you know of other things can I do OR other parameters I can read with recordsets? I learned about .count only because of the exception. THANKS.


0
 

Author Comment

by:T-Dev
Comment Utility
I MUST KNOW HOW TO UPDATE THE DATABASE ALSO. Therefore, would I be correct in saying that I do that by using the same code as we have below but change the SELECT statement to one of those UPDATE ones?
      myDataSource.SelectCommand = "SELECT * FROM [Products] WHERE ProductID=@ProductID"

      myDataSource.SelectParameters.Add("ProductID", 1)
 

      Dim dv As Data.DataView = CType(myDataSource.Select(Web.UI.DataSourceSelectArguments.Empty), Data.DataView)

      TextBox1.Text = dv(0)("ProductName").ToString

Open in new window

0
 

Author Closing Comment

by:T-Dev
Comment Utility
You guys coulda come back to read my followup, but sokay, I can see that it's another question.
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
Sorry T-DEV - I've been out of the office for the lat week, and didn't see your follow up, but again, I'm not really a VB person - strictly DB actually - thanks for the 'assist' points, though - and I'm glad that you sorted out the basic problem - I hope that you got the answers you needed with your second post.. :)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

13 Experts available now in Live!

Get 1:1 Help Now