Update Problem with Gridview & SQL 1

[NerdTop72]

Hello,
I am having issues updating fields I edit in my gridview. When I try to edit a field and click update, my edits to not update to SQL Server? They revert back to the old data... I was getting an error that said I needed to use DataKeyNames for updating. I have since added it with my appropriate boundfield names, and the page does not error. I still cannot update to the SQL Server. I am unsure on how to check this operation for errors as to why I cannot update to SQL server. I am using .Net 2.0 VB.net 2005
I ahve verified my Update statement in SQL Server & VB Query Builder and I am guessing the problem lies somewhere in the variables being sent , or not being sent, to the server?



Here is my code...

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="EditJob.aspx.vb" Inherits="Default2" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"  ConnectionString="Data Source=ADMINSERVER;Initial Catalog=WWTPITWMS;Persist Security Info=True;User ID=sa;Password=Password;Pooling=False"
        DeleteCommand="DELETE FROM [TBLJob] WHERE [JobID] = @JobID" InsertCommand="INSERT INTO [TBLJob] ([ComputerID], [UserID], [SoftwareID], [ErrorID], [WorkerID], [WorkOrderNum], [DateTime], [Hardware], [Software], [JobDescription], [Status]) VALUES (@ComputerID, @UserID, @SoftwareID, @ErrorID, @WorkerID, @WorkOrderNum, @DateTime, @Hardware, @Software, @JobDescription, @Status)"
        SelectCommand="SELECT TBLJob.JobID, TBLJob.WorkerID, TBLJob.WorkOrderNum, TBLJob.DateTime, TBLJob.Hardware, TBLJob.Software, TBLJob.JobDescription, TBLJob.Status, TBLSoftware.SoftwareTitle, TBLUsers.UserName, TBLComputer.ComputerName, TBLError.ErrorDescription, TBLError.EventID FROM TBLJob INNER JOIN TBLComputer ON TBLJob.ComputerID = TBLComputer.ComputerID INNER JOIN TBLUsers ON TBLJob.UserID = TBLUsers.UserID INNER JOIN TBLSoftware ON TBLJob.SoftwareID = TBLSoftware.SoftwareID INNER JOIN TBLError ON TBLJob.ErrorID = TBLError.ErrorID AND TBLSoftware.SoftwareID = TBLError.SoftwareID" 
UpdateCommand="UPDATE TBLJob SET UserID = (SELECT UserID FROM TBLUsers WHERE (UserName = @UserID)), ComputerID = (SELECT ComputerID FROM TBLComputer WHERE (ComputerName = @ComputerID)), SoftwareID = (SELECT SoftwareID FROM TBLSoftware WHERE (SoftwareTitle = @SoftwareID)), ErrorID = (SELECT ErrorID FROM TBLError WHERE (ErrorDescription = @ErrorID)), WorkerID = (SELECT WorkerID FROM TBLWorker WHERE (WorkerName = @WorkerID)), WorkOrderNum = @WorkOrderNum, DateTime = @DateTime, Hardware = @Hardware, Software = @Software, JobDescription = @JobDescription, Status = @Status WHERE (JobID = @JobID)">
        <DeleteParameters>
            <asp:Parameter Name="JobID" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:ControlParameter ControlID="GridView1" Name="UserID" PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="GridView1" Name="ComputerID" PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="GridView1" Name="SoftwareID" PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="GridView1" Name="ErrorID" PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="GridView1" Name="WorkerID" PropertyName="SelectedValue" />
           
            <asp:ControlParameter ControlID="GridView1" Name="WorkOrderNum" PropertyName="SelectedValue"
                Type="Decimal" />
            <asp:ControlParameter ControlID="GridView1" Name="DateTime" PropertyName="SelectedValue"
                Type="DateTime" />
            <asp:ControlParameter ControlID="GridView1" Name="Hardware" PropertyName="SelectedValue"
                Type="Boolean" />
            <asp:ControlParameter ControlID="GridView1" Name="Software" PropertyName="SelectedValue"
                Type="Boolean" />
            <asp:ControlParameter ControlID="GridView1" Name="JobDescription" PropertyName="SelectedValue"
                Type="String" />
            <asp:ControlParameter ControlID="GridView1" Name="Status" PropertyName="SelectedValue"
                Type="String" />
            <asp:ControlParameter ControlID="GridView1" Name="@JobID" PropertyName="SelectedValue" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="ComputerID" Type="Int32" />
            <asp:Parameter Name="UserID" Type="Int32" />
            <asp:Parameter Name="SoftwareID" Type="Int32" />
            <asp:Parameter Name="ErrorID" Type="Int32" />
            <asp:Parameter Name="WorkerID" Type="Int32" />
            <asp:Parameter Name="WorkOrderNum" Type="Decimal" />
            <asp:Parameter Name="DateTime" Type="DateTime" />
            <asp:Parameter Name="Hardware" Type="Boolean" />
            <asp:Parameter Name="Software" Type="Boolean" />
            <asp:Parameter Name="JobDescription" Type="String" />
            <asp:Parameter Name="Status" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" DataKeyNames="UserName,ComputerName,softwareTitle,ErrorDescription,DateTime,Hardware,Software,Jobdescription,Status"  AllowPaging="True" AllowSorting="True"
        AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1"
        ForeColor="#333333" GridLines="None">
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <Columns>
            <asp:CommandField ShowEditButton="True" />
            <asp:BoundField DataField="DateTime" DataFormatString="{0:MM/dd/yyyy}" HeaderText="DateTime"
                HtmlEncode="False" SortExpression="DateTime" />
            <asp:BoundField DataField="Hardware" HeaderText="Hardware" SortExpression="Hardware" />
            <asp:BoundField DataField="Software" HeaderText="Software" SortExpression="Software" />
            <asp:BoundField DataField="JobDescription" HeaderText="JobDescription" SortExpression="JobDescription" />
            <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" />
            <asp:BoundField DataField="SoftwareTitle" HeaderText="SoftwareTitle" SortExpression="SoftwareTitle" />
            <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
            <asp:BoundField DataField="ComputerName" HeaderText="ComputerName" SortExpression="ComputerName" />
            <asp:BoundField DataField="ErrorDescription" HeaderText="ErrorDescription" SortExpression="ErrorDescription" />
            <asp:BoundField DataField="EventID" HeaderText="EventID" SortExpression="EventID" />
        </Columns>
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <EditRowStyle BackColor="#999999" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:WWTPITWMSConnectionString %>"
        SelectCommand="SELECT [SoftwareID], [SoftwareTitle] FROM [TBLSoftware]"></asp:SqlDataSource>
</asp:Content>

Is there a way to step through this to check what is being sent to SQL? I am use to VB IDE :-(

Thanks

 

[ca8msm]

The first thing I would do is to run SQL Profiler on your SQL Server. This will show you all commands that are being executed against the database.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[NerdTop72]

Thanks ca8msm, I always forget about the cool tools I have access to. I was monitoring my UPDATE statement and my variables were all being sent wrong. I recreated my page but this time I used the designer differently...

I created my select statement in the designer and used the same update statement as the old page but this time I did not try to bind my parameters to my gridview. After I did this, I added the edit option to my gridview and loaded the page up and it updated everything!!! I also noticed in the designer properties of the gridview there is a DataKeyNames Property. I was adding every field in my table to DataKeyNames Property but, it was already setup to be my primary key of the table I wanted to update. I guess I was over thinking the steps necessary to get it to do what I wanted it to do. I thought the compiler needed to know more information about what it is updating then what I was giving it. like not matching up Username with @Username.

Here is my new code...

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Edit_Job.aspx.vb" Inherits="Default2" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <asp:SqlDataSource ID="SqlJob" runat="server" ConnectionString="<%$ ConnectionStrings:WWTPITWMSConnectionString %>"
        SelectCommand="SELECT TBLJob.DateTime, TBLComputer.ComputerName, TBLUsers.UserName, TBLSoftware.SoftwareTitle, TBLJob.JobDescription, TBLError.ErrorDescription, TBLError.EventID, TBLError.ErrorCode, TBLJob.Hardware, TBLJob.Software, TBLError.Solution, TBLWorker.WorkerName, TBLJob.Status, TBLJob.JobID, TBLJob.WorkOrderNum FROM TBLJob INNER JOIN TBLSoftware ON TBLJob.SoftwareID = TBLSoftware.SoftwareID INNER JOIN TBLWorker ON TBLJob.WorkerID = TBLWorker.WorkerID INNER JOIN TBLUsers ON TBLJob.UserID = TBLUsers.UserID INNER JOIN TBLComputer ON TBLJob.ComputerID = TBLComputer.ComputerID INNER JOIN TBLError ON TBLSoftware.SoftwareID = TBLError.SoftwareID"
        UpdateCommand="UPDATE TBLJob SET UserID = (SELECT UserID FROM TBLUsers WHERE (UserName = @UserName)), ComputerID = (SELECT ComputerID FROM TBLComputer WHERE (ComputerName = @ComputerName)), SoftwareID = (SELECT SoftwareID FROM TBLSoftware WHERE (SoftwareTitle = @SoftwareTitle)), ErrorID = (SELECT ErrorID FROM TBLError WHERE (ErrorDescription = @ErrorDescription)), WorkerID = (SELECT WorkerID FROM TBLWorker WHERE (WorkerName = @WorkerName)), WorkOrderNum = @WorkOrderNum, DateTime = CONVERT (DATETIME, @DateTime, 102), Hardware = @Hardware, Software = @Software, JobDescription = @JobDesc, Status = @Status WHERE (JobID = @JobID)">
        <UpdateParameters>
            <asp:Parameter Name="UserName" />
            <asp:Parameter Name="ComputerName" />
            <asp:Parameter Name="SoftwareTitle" />
            <asp:Parameter Name="ErrorDescription" />
            <asp:Parameter Name="WorkerName" />
            <asp:Parameter Name="WorkOrderNum" />
            <asp:Parameter Name="DateTime" />
            <asp:Parameter Name="Hardware" />
            <asp:Parameter Name="Software" />
            <asp:Parameter Name="JobDesc" />
            <asp:Parameter Name="Status" />
            <asp:Parameter Name="JobID" />
        </UpdateParameters>
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="JobID"
        DataSourceID="SqlJob" CellPadding="4" ForeColor="#333333" GridLines="None">
        <Columns>
            <asp:CommandField ShowEditButton="True" />
            <asp:BoundField DataField="DateTime" HeaderText="DateTime" SortExpression="DateTime" />
            <asp:BoundField DataField="ComputerName" HeaderText="ComputerName" SortExpression="ComputerName" />
            <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
            <asp:BoundField DataField="SoftwareTitle" HeaderText="SoftwareTitle" SortExpression="SoftwareTitle" />
            <asp:BoundField DataField="JobDescription" HeaderText="JobDescription" SortExpression="JobDescription" />
            <asp:BoundField DataField="ErrorDescription" HeaderText="ErrorDescription" SortExpression="ErrorDescription" />
            <asp:BoundField DataField="EventID" HeaderText="EventID" SortExpression="EventID" />
            <asp:BoundField DataField="ErrorCode" HeaderText="ErrorCode" SortExpression="ErrorCode" />
            <asp:CheckBoxField DataField="Hardware" HeaderText="Hardware" SortExpression="Hardware" />
            <asp:CheckBoxField DataField="Software" HeaderText="Software" SortExpression="Software" />
            <asp:BoundField DataField="Solution" HeaderText="Solution" SortExpression="Solution" />
            <asp:BoundField DataField="WorkerName" HeaderText="WorkerName" SortExpression="WorkerName" />
            <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" />
            <asp:BoundField DataField="JobID" HeaderText="JobID" InsertVisible="False" ReadOnly="True"
                SortExpression="JobID" />
            <asp:BoundField DataField="WorkOrderNum" HeaderText="WorkOrderNum" SortExpression="WorkOrderNum" />
        </Columns>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <EditRowStyle BackColor="#999999" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
</asp:Content>

I have always been boggled by the UPDATE Statement when using more then 1 table. I am glad this issue has happened because I learned the proper way to use the designer. To my surprise it was alot easier then I anticipated. I am still confused by the DataKeyNames and the linking that goes on to update properly without having to bind my parameters? but hey, I'm glad it works and I know what to do for future uses!

I'm giving you a star for the SQL Profiler that really helps!

Thanks!

 

[jbenson001]

In the future, consider using stored procedures with parmameters. This will make debugging and maintenance, easier, and exectuion time better.