troubleshooting Question

ASP.NET C# - Customising SQLDataSource query based on variable (eg username)

Avatar of brett_benzie
brett_benzie asked on
ASP.NET
5 Comments1 Solution673 ViewsLast Modified:
First off, I'm a noob at this.  I'm fairly comfortable with ASP, just not ASP.NET or C#, so I'm using the VWD 2005 Express Edition app to do this page.  

Goal: I'm writing an app to manage our IP Addressing.  Its very simple, just a table in a SQL2k5 database which I want people to be able to edit (GridView), but I also want to record (either in a field in said table, or in a log file somewhere else.  

Problem:  I can't squeeze a variable into the SELECT string..!   I can construct the update SQL string easily enough (to debug I output the string at the bottom of each page, and the SQL syntax is valid), but if I try to call that variable, I get "Incorrect syntax near '<       ".  If I try to embed the "currentUser" variable in the UpdateCommand variable, I get "the server tag is not well formed"

Please help!!

-----------------[start]------------------------

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" clienttarget=downlevel%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>


<script language="C#" runat="server">

   
    String        currentUser;
    String myUpdateString;
        protected void Page_Load(Object Src, EventArgs E)
    {
        System.Security.Principal.IPrincipal principal = Context.User;
        System.Security.Principal.IIdentity identity = principal.Identity;
        currentUser = identity.Name;
        myUpdateString = "UPDATE [Devices] SET [MACAddress] = @MACAddress, [Description] = @Description, [Owner] = @Owner, [PhysicalLocation] = @PhysicalLocation, [RelatedUnit] = @RelatedUnit, [DeviceType] = @DeviceType, [LastEditor]= '" + Context.User.Identity.Name + "' WHERE [IPAddress] = @IPAddress";

    }
</script>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>IP Address Database</title>
</head>
<body>
<FONT size=+4>IPAddress Database</FONT>
 
 
 
    <form id="form1" runat="server">
       
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
            EmptyDataText="There are no data records to display." AllowPaging="True" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical" PageSize="30" Height="768px" Width="1280px" AllowSorting="True">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="IPAddress" HeaderText="IPAddress" SortExpression="IPAddress" />
                <asp:BoundField DataField="MACAddress" HeaderText="MACAddress" SortExpression="MACAddress" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
                <asp:BoundField DataField="Owner" HeaderText="Owner" SortExpression="Owner" />
                <asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
                <asp:BoundField DataField="PhysicalLocation" HeaderText="PhysicalLocation" SortExpression="PhysicalLocation" />
                <asp:BoundField DataField="RelatedUnit" HeaderText="RelatedUnit" SortExpression="RelatedUnit" />
                <asp:BoundField DataField="DeviceType" HeaderText="DeviceType" SortExpression="DeviceType" />
                <asp:BoundField DataField="LastEdited" HeaderText="LastEdited" SortExpression="LastEdited" />
                <asp:BoundField DataField="LastEditor" HeaderText="LastEditor" SortExpression="LastEditor" />
            </Columns>
            <FooterStyle BackColor="#CCCCCC" ForeColor="Black" Wrap="False" />
            <RowStyle BackColor="#EEEEEE" ForeColor="Black" Wrap="False" />
            <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" Wrap="False" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" Wrap="False" />
            <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" Wrap="False" />
            <AlternatingRowStyle BackColor="Gainsboro" Wrap="False" />
            <EmptyDataRowStyle Wrap="False" />
            <EditRowStyle Wrap="False" />
        </asp:GridView>
       
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:InfrastructureConnectionString1 %>"
            ProviderName="<%$ ConnectionStrings:InfrastructureConnectionString1.ProviderName %>"
            UpdateCommand="UPDATE [Devices] SET [MACAddress] = @MACAddress, [Description] = @Description, [Owner] = @Owner, [PhysicalLocation] = @PhysicalLocation, [RelatedUnit] = @RelatedUnit, [DeviceType] = @DeviceType, [LastEditor]= '" + <%= currentUser %> + "' WHERE [IPAddress] = @IPAddress"
            SelectCommand="SELECT IPAddress, MACAddress, Name, Description, Owner, Notes, PhysicalLocation, RelatedUnit, DeviceType, LastEditor, LastEdited FROM Devices ORDER BY IPAddress" >
           
            <UpdateParameters>
                <asp:Parameter Name="MACAddress" />
                <asp:Parameter Name="Description" />
                <asp:Parameter Name="Owner" />
                <asp:Parameter Name="PhysicalLocation" />
                <asp:Parameter Name="RelatedUnit" />
                <asp:Parameter Name="DeviceType" />
                <asp:Parameter Name="IPAddress" />
                <asp:Parameter Name="LastEdited" />
                <asp:Parameter Name="LastEditor" />
            </UpdateParameters>
           
        </asp:SqlDataSource>
        Logged in as: <asp:LoginName ID="LoginName1" runat="server" /><br /><%=myUpdateString %>
    </div>
    </form>
</body>
</html>

-----------------[end]------------------------
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros