Help with ASP and SQL Databases
Sunday, December 9th, 2007 - 3:26am
The fact that I'm asking for help on this here means that my brain is officially fried.
I've been working on this ASP.NET website for my final project for about 3 weeks now. In that time, I've used an SQL db to fill about 2 details views, 3 datalists, and 2 grids, and Jeebus knows how many listboxes, comboboxes, and dropdownlists, but this for some reason escapes my mind, and Google fails me.
I'm trying to return a value (in this case an Integer) from a database table using a ReadOnly Property. I can make the connection, but have no clue how to pull that 1 value out.
This is all written in VB, but I know enough to understand some C#, so if you know how to do it there, I can probably figure it out.
"The pen is mightier than the flaming bag of poop" - Bart Simpson
Xbox Live | Steam | Rockband Profile
From my very limited exposure to raw SQL, I'm not sure there's any such thing as 'just a value'. Everything I've seen that has been reachable from a client has been in a table. It sounds like you're talking about a property OF a table, rather than a value IN a table.
If that's the case, you probably need to query a special system table. All properties of user tables are stored in system tables, so your specific property is probably just another row with a single value in it. Look for the system table that contains the names and locations and characteristics of all the user tables, and your value is probably there.
Now, if it's just a value IN the table, that's a standard SELECT -- you either specify your values tightly enough that only one row can be returned, or else add a LIMIT 1 at the end, so you get only one value.
edit: note that I could easily be wrong here, don't chase this too far without more feedback.
That's mainly what I've been trying to do. I'm searching by primary key, so there will only be 1 returned row. Here's what I got so far:
All that has is the connection to the database, which I'm pretty sure works. It's searching for an email (primary key) in the table, and I'm trying to get the value of customerID.
"The pen is mightier than the flaming bag of poop" - Bart Simpson
Xbox Live | Steam | Rockband Profile
I haven't done VB for about 10 years, so take this with a pinch of salt. but if IIRC doing the select statement gets you a record set type object. You then iterate through that record set by whatever set of fields and rows you asked for. In your case you'll want the [0][0] element from the record set. The fact that you've searched by the primary key or any other unique identifier doesn't alter what you get back from the select statement.
I haven't done VB for a long time, but that's what I think you generally do. I found this code snippet somewhere that explains what I mean:
This guy opens a record set object (Set rs) using the SQL query, and then obtains the value he's after from the record set.
It will do good to heart and head
When your soul is in my soul's stead;
And I will friend you, if I may,
In the dark and cloudy day.
- AE Houseman, trailblazing XBL user
XBox Live
IT WORKS!
Dudley that code looks like VB6. Quite old, but it inspired me to look at some code I wrote 2 years or so ago. Which led to this:
I ended up creating a datatable and filling it with a dataadapter, then stripped the value from there.
While I'm at it, I might as well ask this as well. Does anyone know how to reference a value inside of a detailsView? I've got a textbox in the InsertTemplate that I need to fill with data from another control, but I can't seem to reach that textbox. I tried doing it with .FindControls("name") but it never finds the damn thing.
[update] Never mind, I figured it out.
"The pen is mightier than the flaming bag of poop" - Bart Simpson
Xbox Live | Steam | Rockband Profile
You want to reference it by the row index and then the cell index. If you have a PK type field in the detailsview you can loop through the rows tog et the one you need and then set the vale accordingly.
Unfortunately, if I slash my wrist with my lightsaber it cauterizes instantly. - PurEvil on emo Star Wars plots.
Just by the way, don't do it like that, since you're opening yourself up for an SQL injection vulnerability (e.g. someone entering the email address: " '); DROP DATABASE;" ).
Check out this page for info on how to parametrize your query instead:
http://www.hugolim.com/2007/07/25/lesson-101-for-sql-query-in-vb/
*shudder*
For future reference, jmdandy, if you're returning exactly one value, it's easier to use ExecuteScalar. At least then you don't have to fuss with the DataAdapter and such. Just be careful when casting it as you need to use exactly the right type. For instance, if the value is an integer, but you want to store it as a double, you have to cast it to an integer first. I'm not 100% sure why, but there it is.
Quotation is a serviceable substitute for wit. - Oscar Wilde
Quidquid latine dictum sit, altum sonatur. (Whatever is said in Latin sounds profound.) - Roedy Green
Huh, shows you how new I am at this kinda stuff. I never would have thought of something like that. I don't think it will work in this case though, as I'm using a RegEx to check that the value entered is an email. Then again. I'm still keeping the link for future reference. This is for class, but it will be for my dad who'd like a website for his work. When the site finally goes live, (end of month, or beginning of next) I'll be supporting it for quite a while.
Tried it and it works, thanks. Now my code is 3 lines cleaner.
Am I that happy?
"The pen is mightier than the flaming bag of poop" - Bart Simpson
Xbox Live | Steam | Rockband Profile
It's a good idea to always parameterize your queries.... it's just slightly different syntax, and if you start doing it now, there's a whole bunch of nasty, icky, awful security holes you'll avoid. If you're going to be building your own SQL strings anyway, you might as well type the extra 20 characters to do it properly.
Yea, I changed it to parameters shortly after my last post. I figured that, even though I'm using it one way for this case, I may want to call that sub from another page where it may be a little more vulnerable.
"The pen is mightier than the flaming bag of poop" - Bart Simpson
Xbox Live | Steam | Rockband Profile
Exactly! Do it right every time and you never have to worry.
Listen to doihaveto. I do SQL stuff for a living, and if you do that, you are going to be beaten by someone like me.
Trust me, you don't want that.
Mystic Violet wrote:
Presentations done. Project submitted.
Presentation score: 60/60
f*ck YEA! My professor was quite pleased and even impressed by the work that was shown. So much so that he wishes for me to send him a link to the final version when it finally goes live. (hopefully within the next month)
All that awaits is for him to gaze upon my beautiful code, and undoubtedly point out stupidity or laziness (kinda go hand-in-hand) on my style of coding. But, I appreciate criticism. It shows that I still have much to learn, and it keeps me from feeling like I actually know what the hell I'm doing.
Stay tuned. I'm bound to ask more ridiculous questions in the near future.
Thanks.
"The pen is mightier than the flaming bag of poop" - Bart Simpson
Xbox Live | Steam | Rockband Profile