?
Solved

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

Posted on 2007-11-24
7
Medium Priority
?
564 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
[X]
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
  • 3
  • 3
7 Comments
 
LVL 7

Accepted Solution

by:
jax79sg earned 600 total points
ID: 20344560
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 300 total points
ID: 20344577
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
ID: 20344608
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:T-Dev
ID: 20346287
   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
ID: 20348287
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
ID: 31410806
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
ID: 20378080
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

649 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