Displaying select results on the page

I have the following select statement on my page:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>" SelectCommand="SELECT emp_lname +', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @.emp_username)"> <SelectParameters> <asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" /> </SelectParameters>

I want to say

"Welcome, emp_fullname"

at the top of the page, but can't figure out how to write the results to the page. I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things.

I appreciate any help you can give.

I am assuming that you don't want to use the sql membership/profile system, and want to implement your own. You should read more about those to see if they would be a better fit for you, as both do a lot of things automatically for you already.

However, to solve your immediate problem, since the sqldatasource will return a set of records, you must use a control that knows how to handle multiple records and iterate through them. Those controls would be things like formview, detailsview, datarepeater, datalist, and gridview (I'm sure I missed a few). I'll show you how to use the datarepeater.

Drop the datarepeater on your form. Change the DataSourceID to SqlDatasource1. Switch to the source view, and add an item template with what you want to display for each record (In your case, only 1). So something like this should be in your .aspx file:

<asp:RepeaterID="Repeater1"runat="server"DataSourceID="SqlDatasource1">

<ItemTemplate><%# Eval("Username") %></ItemTemplate>

</asp:Repeater>

You can do the same thing similarly using a datalist. Drop datalist control on your page, assign the datasource. Refresh the schema. Edit item template, which should already contain a couple labels.

With that said, what I would probably do if I needed to display the username like that (Possibly across multiple pages), I would assign another session variable to hold the emp_fullname. Then on any page you wanted to display it, drop a label on it, and programmatically set the label contents during page load like:

label1.text=Session("emp_fullname").

Since the emp_fullname shouldn't change during a session, and is probably used on multiple pages, then you only need to hit the database when they first log in. It's much more efficient than hitting it every time. Although you could enable the sqldatasource's caching to help with that as well, it's still much more work for asp.net to instantiate a sqldatasource, databind it to a data control, etc.

If you still want to use the sqldatasource control (because you'd like to enable caching, etc), then you can also programmatically retrieve the dataset in page_load and set a label as well. I believe the syntax would be similiar to (This might not run/be syntaxically 100% correct):

if not ispostback() then

dim dsv as datasourceview=sqldatasource1.select(datasourcearguements.empty)

label1.text=dsv.ToTable.Rows(0).Column(0)

end if

The .ToTable.Rows(0).Column(0) I'm not really sure about, but you can access it, just probably not exactly that way.

Displaying error when using insert command

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"

InsertCommand="INSERT INTO timeoffcalc(Typeoftime, amountoftime, employeeID) VALUES (@.TypeofTime, @.Amountoftime, @.emplID)"

I have the emplyID and Typeof time as a PK. When the user enters a duplicate value it just gives them the error page. Can i set a label or something to notify the user of the error instead of the error page?

UseTry- Catch block .. in the catch block put customized error message.

Hope it helps..

|||Problem is, I dont know how to do that when using a command object. All i have is the insertcommand function. Where would i put it?|||

try

whatever.insert

catch ex as exception

lblError.text=ex.message

end try

|||

Boy I feel dumb. I am using details view with the insert. I dont see where the command is fired in the code. I click insert in the details view and it does it. Where would i put that code. I know how to do a try catch, but i am confused to where the code is fired with the click, here is my code.

<asp:SqlDataSource

ID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"

SelectCommand="SELECT emplID, lname + ' , ' + fname AS Name FROM employeemain ORDER BY lname">

</asp:SqlDataSource>

<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"BackColor="White"

BorderColor="#DEDFDE"BorderStyle="None"BorderWidth="1px"CellPadding="4"DataSourceID="SqlDataSource2"

DefaultMode="Insert"ForeColor="Black"GridLines="Horizontal"Height="50px"Width="125px"DataKeyNames="emplID">

<FooterStyleBackColor="#CCCC99"/>

<EditRowStyleBackColor="#CE5D5A"Font-Bold="True"ForeColor="White"/>

<RowStyleBackColor="#F7F7DE"/>

<PagerStyleBackColor="#F7F7DE"ForeColor="Black"HorizontalAlign="Right"/>

<Fields>

<asp:TemplateFieldHeaderText="Employee's Name"SortExpression="emplID">

<EditItemTemplate>

<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("emplID") %>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="SqlDataSource1"

DataTextField="Name"DataValueField="emplID"SelectedValue='<%# Bind("emplID") %>'>

</asp:DropDownList>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label2"runat="server"Text='<%# Bind("emplID") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Type of Time"SortExpression="Typeoftime">

<EditItemTemplate>

<asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("Typeoftime") %>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList1"runat="server"SelectedValue='<%# Bind("Typeoftime") %>'>

<asp:ListItem>Vacation</asp:ListItem>

<asp:ListItem>Personal</asp:ListItem>

<asp:ListItem>Sick</asp:ListItem>

<asp:ListItem></asp:ListItem>

</asp:DropDownList>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label1"runat="server"Text='<%# Bind("Typeoftime") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundFieldDataField="amountoftime"HeaderText="Amount per Month"SortExpression="amountoftime"/>

<asp:CommandFieldShowInsertButton="True"/>

</Fields>

<HeaderStyleBackColor="#6B696B"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"

InsertCommand="INSERT INTO timeoffcalc(Typeoftime, amountoftime, employeeID) VALUES (@.TypeofTime, @.Amountoftime, @.emplID)"

SelectCommand="SELECT timeoffcalc.Typeoftime, timeoffcalc.amountoftime, employeemain.emplID FROM employeemain INNER JOIN timeoffcalc ON employeemain.emplID = timeoffcalc.employeeID">

<InsertParameters>

<asp:ParameterName="TypeofTime"/>

<asp:ParameterName="Amountoftime"/>

<asp:ParameterName="emplID"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:LabelID="Label3"runat="server"Font-Italic="True"Font-Size="Large"ForeColor="Red"

Text="It has been Added"Visible="False"></asp:Label><br/>

<asp:ButtonID="Button1"runat="server"Text="Add Another"Visible="False"/><br/>

</asp:Content>

|||

Ah...

Well try catching the sqldatasource2_inserted event, I believe you can catch the errors there, and then set the e parameter to something to say you handled the error, but I've never actually done it.