This is topic ASP .Net Meta Thread in forum Books, Films, Food and Culture at Hatrack River Forum.


To visit this topic, use this URL:
http://www.hatrack.com/ubb/main/ultimatebb.php?ubb=get_topic;f=2;t=050501

---

Posted by Blayne Bradley (Member # 8565) on :
 
I know how to submit a query but how do I display a query result to a textbox? I am using Visual Studios 2005.


Right say I have a textbox and a button, I enter a query into the next box.

"Please enter Query, enter the stock number:[ textbox ]"

Say I enter a stock number "4" and hit enter.

I want to run a query on a database on disk and return say the name field of the item found. In this case a walk man.

So a textbox would say "[ Walkman ]" or

We have found [ numOfItem "40" ] [ nameOfItem "Walkmans" ]

So far the best I can do is get a gridview control to work but my techer doesnt want me to have 20 gridviews on the page.
 

---

Posted by TomDavidson (Member # 124) on :
 
20 gridviews? *blink* I don't think you understand how a datagrid is supposed to work.

There are multiple ways to display query data in a textbox in ASP.NET after hitting a button. The easiest probably is a gridview. You can also bind the textbox to a field in your datasource, assuming you have a datasource.

But since you're talking about 20 gridviews, I'm going to assume that you don't understand how datasources (or datasets) work.

How are you holding the data that comes back out of the query -- and are you using SQLDataReader or another method?

(BTW, you aren't seriously inserting the contents of a textbox into your query string, are you? Google "SQL insertion attack" to understand why this is a bad idea.)
 

---

Posted by Dagonee (Member # 5818) on :
 
The worst case scenario is that you write code in the click even of the button to run the query, read the dataset manually, and set the value of the text box. This is actually better in some circumstances.

In .Net, there are fewer circumstances where such "manual" population of controls is better, though, so it's probably better to simply bind the controls to a dataset that gets reset on each click. There are many, many examples of this out there.
 

---

Posted by Nighthawk (Member # 4176) on :
 
I personally don't use gridviews or data binding myself... When I'm doing database work, I have a tendency to want to be in complete and total control of what happens, so I do it the "old fashion" way similar to what Dagonee describes: in the "Page_Load" (by checking "IsPostBack") or in the event handler, I run my query and fill in the text box.

I also have my own database class I use that returns me data structures and arrays that are easier to use and, in some cases, strong typed.

And an "SQL Insertion Attack" is only an issue if he isn't filtering the string before using it in an actual query. The only reason I wouldn't recommend it is because it can be easily screwed with and is unsightly.
 

---

Posted by Dagonee (Member # 5818) on :
 
Oh, crap - disregard what I said, since I flaked out and missed the "ASP" part of your question.

Sorry about that.
 

---

Posted by TomDavidson (Member # 124) on :
 

quote:

---

And an "SQL Insertion Attack" is only an issue if he isn't filtering the string before using it in an actual query.

---

code:

---

under AppSettings

<AppSettings>
	<add Key= "ConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;user id=Admin;Data Source=C:\...">
	            ^willuse nameti get code
	<add Key= "MyLang" value="French">
				
</appSettings>

code for show behind on the button:

Dim myobj As New clsGeneral
Dim dt As Data.DataTable
     ^-> local pointer
dt=myobj.GetQuery("select... from where " & textbox1.text)
          ^-> function/method
Gridview1.datasource=dt
gridview1.databind()


'on a datagrid it is always 2 steps

Class
-------

You RightClick and add class to program


imports Microsoft.VsualBasic
Imports System.data
imports system.configuration


in the class

Public function GtQuery( ByVal qry As String ) as Datatable 
													^return type
													
	Dim dt as Datatable               //your connection
	Dim objcmd as new oledbcommand   //your sql
	dim objdatareader as oledataReader //your answer
	        ^--> your catcher of results of sql
//set up connection

dim connstr as string
connstr = configurationsettings.Appsettings("connectionstring")
objconn.connectionstring=connstr

objconn.open()


//set up the command

objcmd.commandtext=qry

//the objcmd knows now what to do but how does it know on what database?

objcmd.connection = objconn

//aha! no objcmd knows.
//now we run the command and somehing jas to hold the results

objdatareader=objcmd.executereader(commandbehavior.closeconnection)
   ^            ^-----^-->means run the command.'
   |    			in the .commandtext property
   your results	
 //mess: your datareader must be converted
 // to a datatable before it can be sent
 // back to the datagrid (this is gridview1's datasource

	dt=maketablefromreader(objdatareader) //a method i'll write
	return dt

end function // end of methd

---

code:

---

objdatareader.Open()
If objdatareader.Read() Then
txtFieldname.Text = objdatareader("Fieldname")
End If
objdatareader.Close()

---

quote:

---

Originally posted by Blayne Bradley:
this isnt for an actual website its more inregards to my milestone III but my notes on the matter aren't presiely clear on how to get a textbox to display the results of a query.

The code would read it as

SQLstm (or qry) = ("select * from tablename WHERE" & textbox1.text & ";")

---

code:

---

I killed your table'; drop table tablename--

---

quote:

---

Originally posted by MrSquicky:
I think you might be thinking of mu.

It looks to me like you don't understand what meta means. Using it or other basic terminology can be a semi-serious problem in programming, because many people are going to take that as a sign that you don't generally know what you are doing.

If you use meta incorrecly in a job interview, for example, it is likely going to be a black mark against you.

---

quote:

---

Originally posted:
its an harddisk assignment, the actual assignment will never see the light of day on the net so SQL/html injection is not an issue at this stage security will be discussed at a later date.

---

quote:

---

I've been programming for over twenty years and I don't think I have *ever* used the word "meta" in conversation.

---

I'm going to make a couple of guesses. 1) You're self-taught. 2) You've never worked with interpreted languages. How'd I do?
 

---

Posted by TomDavidson (Member # 124) on :
 
Why would "meta" come up in conversation, Squicky? Once you know the concept, it's not like you need to talk about it -- especially during a job interview.
 

---

Posted by MattP (Member # 10495) on :
 

quote:

---

I've been programming for over twenty years and I don't think I have *ever* used the word "meta" in conversation.

---

quote:

---

Originally posted by TomDavidson:
Why would "meta" come up in conversation, Squicky? Once you know the concept, it's not like you need to talk about it -- especially during a job interview.

---

Well, if you think it's a cool sounding word, but you don't know what it really means, you might be likely to use it in contexts where it's not actually appropriate, such as in the title of this thread or in a job interview.
 

---

Posted by Phanto (Member # 5897) on :
 
I use meta often, am in no way a programmer, and use it in its more colloquial form .
 

---

Posted by MrSquicky (Member # 1802) on :
 
I'm not saying it would. There's various places, however, where the concept could be talked about though, from metasyntatical examples, meta-programming with interpreted languages, meta-information in document formats and processing, the role of meta information in web page search engine optimization, etc.

Also, my warning was about Blayne using the word incorrectly. If he started talking about, I don't know, the meta assignment threads he posted here or whatever, he'd be striking a wrong note.
 

---

Posted by Nighthawk (Member # 4176) on :
 

quote:

---

Originally posted by MrSquicky:

quote:

---

I've been programming for over twenty years and I don't think I have *ever* used the word "meta" in conversation.

---

I'm going to make a couple of guesses. 1) You're self-taught. 2) You've never worked with interpreted languages. How'd I do?

---

Right on #1, wrong on #2... 50%... FAIL!
 

---

Posted by TomDavidson (Member # 124) on :
 
Ah. Gotcha. Got to love meta-meta conversations.
 

---

Posted by MrSquicky (Member # 1802) on :
 
How do you work with interpreted languages and not talk about meta-programming?

Actually, ohhh...silly me. I should have said "for purposes other than scripting."
 

---

Posted by Nighthawk (Member # 4176) on :
 
OK, partial credit then.
 

---

Posted by MrSquicky (Member # 1802) on :
 
Have you never done SEO, Nighthawk? I'd gotten the impression from somewhere that you do primarily web-based stuff.
 

---

Posted by Nighthawk (Member # 4176) on :
 
I'm doing primarily web development work because that's what the customers want, but deep down I'm a core application developer. I also do game development on the side, which is application based.

I've never directly done SEO myself, but there's always someone who handles that aspect of the business and, if has any tech requirements in order to properly do SEO (such as URL redirection to avoid parameterized URLs) then I give him what he needs, but generally I'm not the one modifying the raw HTML pages for this.

A lot of my customers simply don't care much about SEO, no matter what I tell them. Those that do create static HTML pages to submit to search engines, and those pages are generally hosted outside of the application pages I'm working on anyhow.
 

---

Posted by MrSquicky (Member # 1802) on :
 

quote:

---

A lot of my customers simply don't care much about SEO, no matter what I tell them.

---

quote:

---

Originally posted by Nighthawk:
As far as SQL injection... if you turned that in and the teacher doesn't point out the obvious flaw, your teacher's a tool. If I were the teacher, I'd take that flaw and beat you over the head with it; it's a fundamental necessity to know things like that, moreso than syntax or a lot of other things they might teach you. If you do such a mistake in the real world and someone exploits it, you will get fired or sued.

Besides, don't students go the extra mile anymore? Wouldn't it be nice to acknowledge the potential injection and do something about it first? You think your teacher won't notice your effort to do things right from the start?

---

quote:

---

You wouldn't beleive some of the crazy requests one of my clients has been making due to their SEO consultants recommendations.

---

quote:

---

We remapped .html to so it would be treated as .asp on the client's server at his insistence once.

---

quote:

---

We kept the whole query string after the "?" because the client was convinced it was just the extension that mattered. All attempts at explaining why this wouldn't help were futile.

---

code:

---

        Dim dt As Data.DataTable
        Dim dt2 As Data.DataTable
        Dim myobj As New mile2class

        dt = myobj.GetQuery("select * from customer;")

        DDLcu_code.DataSource = dt
        DDLcu_code.DataValueField = "cu_code"
        DDLcu_code.DataBind()

        dt2 = myobj.GetQuery("select * from customer;")

        DDLcu_name.DataSource = dt2
        DDLcu_name.DataValueField = "cu_name"
        DDLcu_name.DataBind()

---

code:

---

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Configuration


Public Class mile2class

    Public Function GetQuery(ByVal qry As String) As DataTable

        Dim dt As DataTable
        Dim objcmd As New OleDb.OleDbCommand
        Dim objdatareader As OleDb.OleDbDataReader

        Dim connstr As String
        Dim objconn As New OleDb.OleDbConnection
        connstr = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
        objconn.ConnectionString = connstr
        objconn.Open()

        objcmd.CommandText = qry
        objcmd.Connection = objconn

        objdatareader = objcmd.ExecuteReader(CommandBehavior.CloseConnection)

        dt = MakeTableFromReader(objdatareader)
        Return dt

    End Function

---

So to use your sample code I would chuck everything having to do with 'dt'?
 

---

Posted by TomDavidson (Member # 124) on :
 
I would actually advise that you not use that sample code until you understand it well enough to not need to ask that question.

Do you understand what a datareader is? What about a datatable? Do you know the difference between these two things, and why it's necessary to convert a reader to a table if you want to use a datatable?
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
I would assume a datatable is a table with data and a reader is a method inwhich to read it.
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
Like currently especially in regards to the drop down lists, If I select an item in one of them, it will do a query on the database in the table, then return what it found, from there I make it so that the second drop down list displays this item.

So if I select cu_code 5, I get cu_name 'BESTBUY117'


so the query i would use for example would be query = "select cu_name from customer where cu_code = " & DDLCode.selectedvalue & ";"


How would I execute this query in such a way as to get

DDLName.selectedvalue = queryResult....?
 

---

Posted by TomDavidson (Member # 124) on :
 
Blayne, isn't this the stuff they're actually teaching you in class?
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
I've had such a horrible few weeks that if I was I had forgotten it and my notes aren't sufficient for whats said in class only whats written down, I had just gone to see my teacher and he says that using his class we can get a datatable that only returns 1 row and just use that for now.
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
WOOHOO I gotz it to work I am l33tz0rz.
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
Except now Im getting an unhandled exception when i do it in reverse.

code:

---

Server Error in '/Milestone 3' Application.
------------------------------------------------
No value given for one or more required parameters. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error: 


Line 21:         objcmd.Connection = objconn
Line 22: 
Line 23:         objdatareader = objcmd.ExecuteReader(CommandBehavior.CloseConnection)
Line 24: 
Line 25:         dt = MakeTableFromReader(objdatareader)
 

Source File: H:\User Integration\Milestone 2\App_Code\mile2class.vb    Line: 23 

---

code:

---

        Dim dt As Data.DataTable
        'Dim dt2 As Data.DataTable
        Dim myobj As New mile2class
        dt = myobj.GetQuery(" SELECT cu_name FROM customer WHERE cu_code = " & DDLcu_code.SelectedValue & ";")

        DDLcu_name.SelectedValue = dt.Rows(0)(0)

        lbltest.Text = DDLcu_code.SelectedValue

---

code:

---

        Dim dt2 As Data.DataTable
        Dim myobj2 As New mile2class
        dt2 = myobj2.GetQuery(" SELECT cu_code FROM customer WHERE cu_name = " & DDLcu_name.SelectedValue & ";")

        DDLcu_code.SelectedValue = dt2.Rows(0)(0)

        'lbltest2.Text = DDLcu_name.SelectedValue

---

 

---

Posted by Blayne Bradley (Member # 8565) on :
 
Hmm I tried to edit it so that I doesnt do that wide screen thing but it doesnt let me.
 

---

Posted by TomDavidson (Member # 124) on :
 
I'm assuming cu_code is an integer and stored as an integer in your database?
 

---

Posted by fugu13 (Member # 2859) on :
 
Btw, I strongly suggest using more descriptive variable names. You're better than many, but the more clearly you name your variables, the better you will understand your own code.
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
its stored as an autonumber in the .mdb file yes.
 

---

Posted by fugu13 (Member # 2859) on :
 
Then why are you checking if it = ''? A number can never be an empty string.
 

---

Posted by TomDavidson (Member # 124) on :
 
Remembering that cu_name is a string, for example, solves your current problem.
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
fugu where am I checking for that.
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
Like do you mean the sql statement:

dt = myobj.GetQuery(" SELECT cu_code FROM customer WHERE cu_name = " & ddlcu_name.selectedvalue &";")

?

I am selecting the customer code from the customer table where the corresponding customer name equals the one currently selected in the customer name drop down list.

I am not aware of how else to do that.
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
the query according to the debugger posseses

select customer_code from customer where customer_name = EBGAMES1795;

(renamed variables for clarification)

according to my tables that does exist I am not sure whats causing the error.
 

---

Posted by TomDavidson (Member # 124) on :
 
Examine your SQL string. Mentally plot out what it would be if you stuck in a cu_name. Heck, you could test it by sending it to a string variable, then trying to run that string variable directly against your database.

Have you tried examining what's happening using the debugger?
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
I am such an idiot. How can I miss it.
 

---

Posted by Blayne Bradley (Member # 8565) on :
 
I used the debugger but as far as I could tell everything was working fine, it was only when I used my schools sequel server to test it that I realized that I was missing single quoutes.

I started typing in iSQL *Plus "SELECT cu_code from customer where cu_name = (pause...) ' (wait a second.....)

DOH!
 

---

Posted by TomDavidson (Member # 124) on :
 
Congrats.
 

---