Displaying data from Queries

Hello,

I've fairly new to ASP.NET, but recently I've been trying to expand my knowledge of it. I've designed a simple database with a single table and only few columns and rows. Basically I want to be able to display the information from this table in straight out text, not in the default drag&drop tools that Visual Studio 2005 offers, such as GridView and DataList.

An example of what I'm after is... Say I have someone in my table, which is named Employees, and I want to return some of his information in a sentence, like so:

The phone number of [FName] [LName] is [Phone].

I've been looking around at some other sites, as well as some of the threads on this forum, and have used some of the suggestions I've seen. An http://www.asp.net/ tutorial that I saw inspired me to create a Dataset for my table, as well as a couple of SQL commands for me to run off of this table for some desired information.

I currently know SQL, a very basic bit of ASP.NET C#, and some simple web-based client-side languages like Javascript and HTML - So you may have to respond to me as you would an infant. ^_~

Thanks!

 

Use a repeater along with <%# Eval("FieldName") %>.

Please refer to http://www.sitepoint.com/article/asp-net-repeater-control for more information.

I hope this helps.

 

Thanks! <3

I'll check that out and respond with my results. =D

 

Alright, so I looked at that website and as I started understanding it, I also began wondering if performed a function that would live up to its name: repeat what I have over and over again. With my new understanding, however, I was able to use the FormView method and tweek it around a bit to get what I wanted! =D

My code ended up like this after a bit of formatting:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
            SelectCommand="SELECT * FROM [Employee] WHERE ID=1"></asp:SqlDataSource>
        &nbsp;
        <table cellpadding="0" cellspacing="5" border="0" style="text-align:left; vertical-align:top">
          <asp:FormView ID="FormView1" runat="server" DataKeyNames="ID" DataSourceID="SqlDataSource1">
            <ItemTemplate>
                <!--ID:
                <asp:Label ID="IDLabel" runat="server" Text='<%# Eval("ID") %>'></asp:Label><br />
                -->
            <tr>
                <td><asp:Label ID="FNameLabel" runat="server" Text='<%# Bind("FName") %>'></asp:Label>
                
                <asp:Label ID="LNameLabel" runat="server" Text='<%# Bind("LName") %>'></asp:Label></td>
        
                <td rowspan="4" style="width:200px">[We'll pretend this is a 200 x 200 picture]</td>
            </tr>        
            <tr>
                <td><i><asp:Label ID="TitleLabel" runat="server" Text='<%# Bind("Title") %>'></asp:Label></i></td>
            </tr>        
            <tr>
                <td><asp:Label ID="PhoneLabel" runat="server" Text='<%# Bind("Phone") %>'></asp:Label></td>
            </tr>        
            <tr>
                <td><asp:Label ID="URLLabel" runat="server" Text='<%# Bind("URL") %>'></asp:Label></td>
            </tr>
            <tr>
                <td>Responsibilities:</td>
                <td><asp:Label ID="ResponsibilitiesLabel" runat="server" Text='<%# Bind("Responsibilities") %>'>
                </asp:Label></td>
            </tr> 
            <tr>
                <td>MonthlyGoals:</td>
                <td><asp:Label ID="MonthlyGoalsLabel" runat="server" Text='<%# Bind("MonthlyGoals") %>'>
                </asp:Label></td>
            </tr>
            <tr>
                <td>Misc:</td>
                <td><asp:Label ID="MiscLabel" runat="server" Text='<%# Bind("Misc") %>'></asp:Label></td>
            </tr>
            </ItemTemplate>
          </asp:FormView>
        </table>

Code (markup):

But here's another question... How can I get it to switch between each row/individual in my database? I currently have four rows in there, and I know I can switch around which row it shows by changing my SQL statement's WHERE clause, but I'm not sure how I could do so otherwise.

When I was initially searching around for tutorials, I saw something called a Query String that would just kinda place itself at the end of the URL - I know I've seen them before (Heck, there's one on the address bar right now), I just don't know how to implement one into this page. Does anyone know of a good place to learn how to use those?

Thanks again.

 

It sounds like what you need is paging.

The way I do paging is to pull a complete set of primary keys from the database based on your query then pick the ones you want for this page and load the complete record.

I wrote an FAQ on this technique here:
http://www.vbcity.com/forums/faq.asp?fid=37&cat=Database&#TID126287

If your dataset is only small you can use built in .net paging functionality.

 

I may be wrong, but I don't think I'm after Paging... Ya see, I basically want to be able to click on a link and have it manipulate my Address, so that when it loads the next page, my code will pull the data from the URL. This data would then be used in the WHERE clause of my statement to draw upon a single, specific primary key and print that data out in a customized format.

Kinda like the current page we're on:

http://forums.digitalpoint.com/showthread.php?t=96411

Code (markup):

Where that last "?t=96411" part of the code is being manipulated to print this specific thread.

Then again, what's in my head may be totally incorrect and impossible in ASP.NET... But there has to be a way to at least achieve the same results.

 

Oh right for some reason I thought you were using a data repeater.....

In that case you need to pass the unique ID via a query string and this is possible. simply use the request method to retrieve it.

Dim intId as integer = request("ID")

then have a parmeterised query

SELECT * FROM tbl_MyTable WHERE PrimaryKey = @PrimaryKey

 

!

I love you Dan. =D

I'll respond with my results once I get things goin'.

 

Since you are using asp.net 2.0 I would recommend using a querystringparameter; something along the lines of

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
        SelectCommand="SELECT * FROM [Employee] WHERE ID=@ID">
        <SelectParameters>
            <asp:QueryStringParameter Name="@ID" QueryStringField="t" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

Code (markup):

 

Alright, so I first tried the code that Dan suggested, but Visual Studio just replied to me with a few errors.

Since that didn't work, I went onto Benjy's, which keeps it working - but I still don't know how to change the value of ID so that it shows other entries. How exactly do I do that?

 

SqlDataSource looks like it will do it but I've never used it as I use a tiered development model.

I guess what you need to do is get your intID into this parameter :
<asp:QueryStringParameter Name="@ID" QueryStringField="t" Type="Int32" />

 

Right now I'm tryin' to set my intID variable up in my Page_Load section, but I'm getting a few errors.

    protected void Page_Load(object sender, EventArgs e)
    {
        Dim intID as Int32 = Request("ID");
    }

Code (markup):

Visual Studio says that "as" is an invalid expression, and a semicolon is expected after Int32 (I switched integer to Int32, because that matches my QueryStringParameter and Visual Studio wasn't recognizing integer).


I also just rebuilt my code with the QueryStringParameter, and suddenly it just... doesn't load anything. It loads the whole page except for anything within the asp:sqldatasource and asp:formview tags... My guess is that I haven't defined a Primary Key for my code to look for, so it just doesn't display anything.

I've tried adding ?t=1, ?ID=1, and ?@ID=1 to the end of my URL. The ?t=1 gives me some huge error that says I "Must declare the scalar variable '@ID'." Both ?ID=1 and ?@ID=1 produce the same page as it would without the extensions, which is none of the asp.net code.

There's probably just one little thing that I need to do/know in order to fix this whole problem, I just know it.

 

the 'Dim as' issue is because that is VB and you are using C#

Int32 intID = Request("ID");

 

That'd pretty much do it. >_O

It's now giving me an error that says: "System.Web.UI.Page.Request is a property but is used like a method"

Strange, I've seen that exact code in some of the tutorials I've been blazing through, and it seems to work just fine for them.

 

This thread's getting a little confusing and off-topic... I'm gonna make a new one that's a bit more clarified.

 

for C#
int intID = Request.QueryString["ID"];

 

So I put that in my Page_Load section, but it gave the request an error that it was not able to implicitely convert a string into an integer... I decided to switch the code to:

String StringID = Request.QueryString["ID"];

Code (markup):

That one didn't give me any errors... But I'm unsure if it will work properly (Can you convert strings into integers in C#?).

I'll post some pictures of what I'm getting.

This is my code without the QueryStringParameter, as well as what it produces when I manually set my SQL statement to "WHERE ID=1".

http://img.photobucket.com/albums/v256/TehJehu/woutquerystringparameter.jpg
http://img.photobucket.com/albums/v256/TehJehu/woutquerystringparameterbrowser.jpg

And this is my code with the QueryStringParameter:

http://img.photobucket.com/albums/v256/TehJehu/querystringparameter.jpg

With the query string Parameter, this is the result of leaving the URL the same, adding '?ID=1', or '?@ID=1' to the end:

http://img.photobucket.com/albums/v256/TehJehu/querystringparameterid1.jpg

Lastly, this is what occurs when I change the end of my URL to '?t=1' (It seems like that would be the part of the URL that would change by looking at the code):

http://img.photobucket.com/albums/v256/TehJehu/querystringparametert1.jpg


What do I need to do to allow me to alter my query string and successfully change the page?

 

Sorry about the Double Post...

But I figured it out. I had to change this code:

<asp:QueryStringParameter Name="@ID" QueryStringField="t" Type="String" />

Code (markup):

To this code:

<asp:QueryStringParameter Name="ID" QueryStringField="t" Type="String" />

Code (markup):

That @ sign was messing up the entire thing. Thanks for your time - if I have any more questions, I'll make sure to ask. =D

 

You can cast types in C# but I'm not sure if it's the same as VB

Convert.ToString()
Convert.ToInt16()
Convert.ToInt32()
etc.