7 Replies - 2574 Views - Last Post: 16 August 2012 - 10:06 AM Rate Topic: -----

#1 gns1497  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 27-June 12

Unable to update or delete records from SQL 2008 database

Posted 27 June 2012 - 04:24 PM

Hello all,

I'm new to ASP.net and I'm having a tough time getting my sql to update/delete/update my database. My select statement reads fine. I'm wondering if its because I am auto-generating columns at run time when the user selects a control on my .aspx form. I'm using VB.net for the background code, though I'm not sure it comes into play here. Here's my asp code for the data source and the gridview control.





<asp:SqlDataSource ID="dsApp" runat="server" 
        SelectCommand="SELECT [fldAppName] as 'Application', [fldGroupName] as 'Group Name', [fldAppSymID] as 'Symposium ID', 
                       [fldAppPhone] as 'Phone', [fldAppPhoneOpt] as 'Options' FROM [tblApplication] 
                       WHERE ([fldGroupName] = @fldGroupName) ORDER BY [fldAppName]"
        ConnectionString="<%$ ConnectionStrings:DiscussSQLConnectionString %>" 
        DeleteCommand="DELETE FROM [tblApplication] WHERE [fldAppName] = @original_fldAppName AND 
                      (([fldGroupName] = @original_fldGroupName) OR ([fldGroupName] IS NULL AND @original_fldGroupName IS NULL)) AND 
                      (([fldAppSymID] = @original_fldAppSymID) OR ([fldAppSymID] IS NULL AND @original_fldAppSymID IS NULL)) AND 
                      (([fldAppPhone] = @original_fldAppPhone) OR ([fldAppPhone] IS NULL AND @original_fldAppPhone IS NULL)) AND 
                      (([fldAppPhoneOpt] = @original_fldAppPhoneOpt) OR ([fldAppPhoneOpt] IS NULL AND @original_fldAppPhoneOpt IS NULL))" 
        InsertCommand="INSERT INTO [tblApplication] ([fldAppName], [fldGroupName], [fldAppSymID], [fldAppPhone], [fldAppPhoneOpt]) 
                      VALUES (@fldAppName, @fldGroupName, @fldAppSymID, @fldAppPhone, @fldAppPhoneOpt)" 
                      
        OldValuesParameterFormatString="original_{0}" 
        
        UpdateCommand="UPDATE [tblApplication] SET [fldGroupName] = @fldGroupName, [fldAppSymID] = @fldAppSymID, 
                      [fldAppPhone] = @fldAppPhone, [fldAppPhoneOpt] = @fldAppPhoneOpt 
                      WHERE [fldAppName] = @original_fldAppName AND (([fldGroupName] = @original_fldGroupName) OR 
                      ([fldGroupName] IS NULL AND @original_fldGroupName IS NULL)) AND (([fldAppSymID] = @original_fldAppSymID) OR 
                      ([fldAppSymID] IS NULL AND @original_fldAppSymID IS NULL)) AND (([fldAppPhone] = @original_fldAppPhone) OR 
                      ([fldAppPhone] IS NULL AND @original_fldAppPhone IS NULL)) AND (([fldAppPhoneOpt] = @original_fldAppPhoneOpt) OR 
                      ([fldAppPhoneOpt] IS NULL AND @original_fldAppPhoneOpt IS NULL))">
                      
        <SelectParameters>
            <asp:ControlParameter ControlID="cboGroup" Name="fldGroupName" 
                PropertyName="SelectedValue" Type="String" />
        </SelectParameters>
        
        <DeleteParameters>
            <asp:Parameter Name="original_fldAppName" Type="String" />
            <asp:Parameter Name="original_fldGroupName" Type="String" />
            <asp:Parameter Name="original_fldAppSymID" Type="String" />
            <asp:Parameter Name="original_fldAppPhone" Type="String" />
            <asp:Parameter Name="original_fldAppPhoneOpt" Type="String" />
        </DeleteParameters>
        
        <UpdateParameters>
            <asp:Parameter Name="fldAppName" Type="String" />
            <asp:Parameter Name="fldGroupName" Type="String" />
            <asp:Parameter Name="fldAppSymID" Type="String" />
            <asp:Parameter Name="fldAppPhone" Type="String" />
            <asp:Parameter Name="fldAppPhoneOpt" Type="String" />
            <asp:Parameter Name="original_fldAppName" Type="String" />
            <asp:Parameter Name="original_fldGroupName" Type="String" />
            <asp:Parameter Name="original_fldAppSymID" Type="String" />
            <asp:Parameter Name="original_fldAppPhone" Type="String" />
            <asp:Parameter Name="original_fldAppPhoneOpt" Type="String" />
        </UpdateParameters>
        
        <InsertParameters>
            <asp:Parameter Name="fldAppName" Type="String" />
            <asp:Parameter Name="fldGroupName" Type="String" />
            <asp:Parameter Name="fldAppSymID" Type="String" />
            <asp:Parameter Name="fldAppPhone" Type="String" />
            <asp:Parameter Name="fldAppPhoneOpt" Type="String" />
            <asp:Parameter Name="original_fldAppName" Type="String" />
            <asp:Parameter Name="original_fldGroupName" Type="String" />
            <asp:Parameter Name="original_fldAppSymID" Type="String" />
            <asp:Parameter Name="original_fldAppPhone" Type="String" />
            <asp:Parameter Name="original_fldAppPhoneOpt" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>








<asp:GridView ID="gvGroupApps" runat="server"
                      AutoGenerateColumns="True"
                      ShowFooter="True" AllowPaging="True" CellPadding="4" 
            DataSourceID="dsApp" ForeColor="#333333" GridLines="None">
            <RowStyle BackColor="#E3EAEB" />
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
            </Columns>
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#7C6F57" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>










When I try to edit a record in my Gridview, I get the fields to open up and make my changes, then after I click "Update", I get this error within my browser...










Server Error in '/DISCuSS' Application.
--------------------------------------------------------------------------------

Incorrect syntax near 'nvarchar'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'nvarchar'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.]
System.Data.SqlClient.SqlConnection.onerror(SqlException exception, Boolean breakConnection) +1950522
System.Data.SqlClient.SqlInternalConnection.onerror(SqlException exception, Boolean breakConnection) +4856715
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(Runbehavior runbehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1121
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Runbehavior runbehavior, String resetOptionsString) +200
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(Commandbehavior cmdbehavior, Runbehavior runbehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(Commandbehavior cmdbehavior, Runbehavior runbehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +325
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +907
System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704
System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565





Hope someone can help me solve this. Any help is greatly appreciated.

Thank you,
Brad

Is This A Good Question/Topic? 0
  • +

Replies To: Unable to update or delete records from SQL 2008 database

#2 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: Unable to update or delete records from SQL 2008 database

Posted 28 June 2012 - 04:39 AM

Are all of the columns in your database (that you're trying to update) nvarchar or varchar data types?
Was This Post Helpful? 0
  • +
  • -

#3 gns1497  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 27-June 12

Re: Unable to update or delete records from SQL 2008 database

Posted 28 June 2012 - 05:26 AM

All of the columns in the table that I am updating through this query are nvarchar.

Column Name Data Type
fldAppName nvarchar(40)
fldGroupName nvarchar(30)
fldAppSymID nvarchar(5)
fldAppPhone nvarchar(10)
fldAppPhoneOpt nvarchar(10)


There is a "timestamp", a "datetime2(0)" and a "bit" field type in this table, however, they are not built into the query I am using with my dataset and are not being updated. All fields except the primary key (fldAppName) allow null values.
Was This Post Helpful? 0
  • +
  • -

#4 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: Unable to update or delete records from SQL 2008 database

Posted 29 June 2012 - 04:09 PM

Right now your update statement looks like this.

UPDATE [tblApplication] SET [fldGroupName] = @fldGroupName, [fldAppSymID] = @fldAppSymID, 
                      [fldAppPhone] = @fldAppPhone, [fldAppPhoneOpt] = @fldAppPhoneOpt 
                      WHERE [fldAppName] = @original_fldAppName AND (([fldGroupName] = @original_fldGroupName) OR 
                      ([fldGroupName] IS NULL AND @original_fldGroupName IS NULL)) AND (([fldAppSymID] = @original_fldAppSymID) OR 
                      ([fldAppSymID] IS NULL AND @original_fldAppSymID IS NULL)) AND (([fldAppPhone] = @original_fldAppPhone) OR 
                      ([fldAppPhone] IS NULL AND @original_fldAppPhone IS NULL)) AND (([fldAppPhoneOpt] = @original_fldAppPhoneOpt) OR 
                      ([fldAppPhoneOpt] IS NULL AND @original_fldAppPhoneOpt IS NULL))



In order to maintain concurrency it is attempting to validate that none of the existing values have been modified before updating with the values you've entered. I'm not sure why, but this usually gives me problems as well. Try simplifying the where statement and see if it allows you to perform an update.

This post has been edited by Nakor: 29 June 2012 - 04:10 PM

Was This Post Helpful? 0
  • +
  • -

#5 gns1497  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 27-June 12

Re: Unable to update or delete records from SQL 2008 database

Posted 05 July 2012 - 03:15 PM

View PostNakor, on 29 June 2012 - 04:09 PM, said:

In order to maintain concurrency it is attempting to validate that none of the existing values have been modified before updating with the values you've entered. I'm not sure why, but this usually gives me problems as well. Try simplifying the where statement and see if it allows you to perform an update.



Thanks for your reply. I had already tried this, however get the same result. I even took the where statement out completely and get the same error. I think I was missing the code on the code behind page (VB.net). I added a reference to a sub in my code behind page. I've added the code to execute the update on the VB side in the code behind page. Here's the code.

ASP
<asp:GridView ID="gvGroupApps" runat="server" ShowFooter="True" AllowPaging="True" CellPadding="4" DataSourceID="dsApp"
            ForeColor="#333333" GridLines="None" OnRowUpdating="GvGroupApps_Update">
            <RowStyle BackColor="#E3EAEB" />
            <Columns>
                <asp:CommandField ShowInsertButton="True" ShowDeleteButton="True" ShowEditButton="True"/>
            </Columns>
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#7C6F57" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>




Here's the vb code for the "GvGroupApps_Update" reference in the gridview tag.


Protected Sub GvGroupApps_Update(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
        dsApp.Update()
        dsApp.DataBind()
End Sub





dsApp is my sql datasource. Am I on the right track here?
Was This Post Helpful? 0
  • +
  • -

#6 gns1497  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 27-June 12

Re: Unable to update or delete records from SQL 2008 database

Posted 19 July 2012 - 04:04 PM

Okay, I was able to find what what I was missing on my vb code, but now I'm getting a different error when I run the page and I believe I'm missing something on the ASP.net side.

The error I'm getting now is:



Server Error in '/DISCuSS' Application.
--------------------------------------------------------------------------------

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'fldAppName'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'fldAppName'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[HttpException (0x80004005): DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'fldAppName'.]
System.Web.UI.DataBinder.GetPropertyValue(Object container, String propName) +8681317
System.Web.UI.WebControls.GridView.CreateChildControls(IEnumerable dataSource, Boolean dataBinding) +2178
System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data) +57
System.Web.UI.WebControls.GridView.PerformDataBinding(IEnumerable data) +14
System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +114
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +31
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +72
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +44
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

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







Here's my ASP code (note the Gridview tag, as I think this is where the page bombs)




<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>DISCuSS.Net</title>
    <style type="text/css">
        #form1
        {
            height: 743px;
            width: 858px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
    <asp:SqlDataSource ID="dsApp" runat="server" 
        SelectCommand="SELECT [fldAppName] as 'Application', [fldGroupName] as 'Group Name', [fldAppSymID] as 'Symposium ID', 
                       [fldAppPhone] as 'Phone', [fldAppPhoneOpt] as 'Options' FROM [tblApplication] 
                       WHERE ([fldGroupName] = @fldGroupName) ORDER BY [fldAppName]" 
        ConnectionString="<%$ ConnectionStrings:DiscussSQLConnectionString %>" 
        DeleteCommand="DELETE FROM [tblApplication] WHERE [fldAppName] = @original_fldAppName AND 
                      (([fldGroupName] = @original_fldGroupName) OR ([fldGroupName] IS NULL AND @original_fldGroupName IS NULL)) AND 
                      (([fldAppSymID] = @original_fldAppSymID) OR ([fldAppSymID] IS NULL AND @original_fldAppSymID IS NULL)) AND 
                      (([fldAppPhone] = @original_fldAppPhone) OR ([fldAppPhone] IS NULL AND @original_fldAppPhone IS NULL)) AND 
                      (([fldAppPhoneOpt] = @original_fldAppPhoneOpt) OR ([fldAppPhoneOpt] IS NULL AND @original_fldAppPhoneOpt IS NULL))" 
        InsertCommand="INSERT INTO [tblApplication] ([fldAppName], [fldGroupName], [fldAppSymID], [fldAppPhone], [fldAppPhoneOpt]) 
                      VALUES (@fldAppName, @fldGroupName, @fldAppSymID, @fldAppPhone, @fldAppPhoneOpt)" 
                      
        OldValuesParameterFormatString="original_{0}" 
        
        UpdateCommand="UPDATE [tblApplication] SET [fldGroupName] = @fldGroupName, [fldAppSymID] = @fldAppSymID, 
                      [fldAppPhone] = @fldAppPhone, [fldAppPhoneOpt] = @fldAppPhoneOpt
                      WHERE [fldAppName] = @original_fldAppName AND (([fldGroupName] = @original_fldGroupName) OR 
                      ([fldGroupName] IS NULL AND @original_fldGroupName IS NULL)) AND (([fldAppSymID] = @original_fldAppSymID) OR 
                      ([fldAppSymID] IS NULL AND @original_fldAppSymID IS NULL)) AND (([fldAppPhone] = @original_fldAppPhone) OR 
                      ([fldAppPhone] IS NULL AND @original_fldAppPhone IS NULL)) AND (([fldAppPhoneOpt] = @original_fldAppPhoneOpt) OR 
                      ([fldAppPhoneOpt] IS NULL AND @original_fldAppPhoneOpt IS NULL))">
                      
        <SelectParameters>
            <asp:ControlParameter ControlID="cboGroup" Name="fldGroupName" 
                PropertyName="SelectedValue" Type="String" />
        </SelectParameters>
        
        <DeleteParameters>
            <asp:Parameter Name="original_fldAppName" Type="String" />
            <asp:Parameter Name="original_fldGroupName" Type="String" />
            <asp:Parameter Name="original_fldAppSymID" Type="String" />
            <asp:Parameter Name="original_fldAppPhone" Type="String" />
            <asp:Parameter Name="original_fldAppPhoneOpt" Type="String" />
        </DeleteParameters>
        
        <UpdateParameters>
            <asp:Parameter Name="fldAppName" Type="String" />
            <asp:Parameter Name="fldGroupName" Type="String" />
            <asp:Parameter Name="fldAppSymID" Type="String" />
            <asp:Parameter Name="fldAppPhone" Type="String" />
            <asp:Parameter Name="fldAppPhoneOpt" Type="String" />
            <asp:Parameter Name="original_fldAppName" Type="String" />
            <asp:Parameter Name="original_fldGroupName" Type="String" />
            <asp:Parameter Name="original_fldAppSymID" Type="String" />
            <asp:Parameter Name="original_fldAppPhone" Type="String" />
            <asp:Parameter Name="original_fldAppPhoneOpt" Type="String" />
        </UpdateParameters>
        
        <InsertParameters>
            <asp:Parameter Name="fldAppName" Type="String" />
            <asp:Parameter Name="fldGroupName" Type="String" />
            <asp:Parameter Name="fldAppSymID" Type="String" />
            <asp:Parameter Name="fldAppPhone" Type="String" />
            <asp:Parameter Name="fldAppPhoneOpt" Type="String" />
            <asp:Parameter Name="original_fldAppName" Type="String" />
            <asp:Parameter Name="original_fldGroupName" Type="String" />
            <asp:Parameter Name="original_fldAppSymID" Type="String" />
            <asp:Parameter Name="original_fldAppPhone" Type="String" />
            <asp:Parameter Name="original_fldAppPhoneOpt" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
      

    
        <asp:SqlDataSource ID="dsGroupName" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DiscussSQLConnectionString %>" 
        
        SelectCommand="SELECT [fldGroupName] FROM [tblGroup] ORDER BY [fldGroupName]"></asp:SqlDataSource>
      

    
        <br />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Image ID="Image1" runat="server" Height="87px" 
        ImageUrl="~/images/HCA.jpg" Width="224px" />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Image ID="imgDISCuSS" runat="server" Height="42px" 
        ImageUrl="~/images/DISCuSS.jpg" style="margin-top: 3px" Width="131px" />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <br />
    <br />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Button ID="cmdInformation" runat="server" Text="Information" 
        Width="107px" Font-Size="X-Small" />
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:DropDownList ID="cboGroup" runat="server" DataSourceID="dsGroupName" DataTextField="fldGroupName" DataValueField="fldGroupName" AppendDataBoundItems="true" onselectedIndexChanged="cboGroup_SelectedIndexChanged" AutoPostBack="True"
        Height="18px" Width="309px">
    </asp:DropDownList>
    
    <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:Button ID="cmdDictionaries" runat="server" Text="Dictionaries" 
            Width="107px" Font-Size="X-Small" />
    <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:Button ID="cmdManual" runat="server" Text="User Manual" 
            Width="107px" Font-Size="X-Small" />
    <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:Button ID="cmdMain" runat="server" Text="Main Menu" 
            Width="107px" Font-Size="X-Small" />
            
            
     &nbsp;&nbsp;&nbsp;
    <br />
    <br />
   <div style="margin-left: 280px">
              
        <asp:GridView ID="gvGroupApps" runat="server" ShowFooter="True" AllowPaging="True" CellPadding="4" DataSourceID="dsApp"
            ForeColor="#333333" GridLines="None" OnRowUpdating="GvGroupApps_Update" datakeynames="fldAppName">
            <RowStyle BackColor="#E3EAEB" />
            <Columns>
                <asp:CommandField ShowInsertButton="True" ShowDeleteButton="True" ShowEditButton="True"/>
            </Columns>
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#7C6F57" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
    </div>   
    
    
    </form>
    </body>
</html>





Here's my vb code from the codebehind page:

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.DataRowView


Partial Class ApplicationSearch
    Inherits System.Web.UI.Page

    Protected Sub cmdInformation_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdInformation.Click
        Response.Redirect(String.Format("~/Information.aspx?"))
    End Sub

    Protected Sub cmdDictionaries_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdDictionaries.Click
        Response.Redirect(String.Format("~/Dictionaries.aspx?"))
    End Sub
    Protected Sub cmdManual_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdManual.Click
        Response.Redirect(String.Format("~/Manual.aspx?"))
    End Sub

    Protected Sub cmdMain_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdMain.Click
        Response.Redirect(String.Format("~/Default.aspx?"))
    End Sub

    Protected Sub cboGroup_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboGroup.SelectedIndexChanged
        Dim cboGroup As String = TryCast(sender, DropDownList).SelectedValue
        Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("DiscussSQLConnectionString").ConnectionString)
        Dim sSql As System.Xml.Linq.XElement = <sql>

            SELECT [fldAppName], [fldGroupName], [fldAppSymID], [fldAppPhone], [fldAppPhoneOpt] 
            FROM [tblApplication]

            WHERE ([fldGroupName] = '<%= cboGroup %>') 
            ORDER BY [fldAppName] </sql>

        Dim sqlCmd As New SqlCommand(sSql.Value, sqlConn)
        Dim sqlRead As SqlDataReader
        If sqlConn.State <> ConnectionState.Open Then
            sqlConn.Open()
        End If
        sqlRead = sqlCmd.ExecuteReader()
        sqlRead.Read()

        sqlRead.Close()
        sqlConn.Close()
    End Sub

    Function SendSQLCommand(ByVal tsSQLCmd As String, ByVal tsqlConn As SqlConnection) As Integer
        Dim iRetVal As Integer = -1
        Dim sqlCmd As New SqlCommand(tsSQLCmd, tsqlConn)
        If tsqlConn.State <> ConnectionState.Open Then
            tsqlConn.Open()
        End If
        Try
            iRetVal = sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        End Try
        Return iRetVal
    End Function

    Protected Sub GvGroupApps_Update(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
        Dim keyValue As String = e.Keys("cAppPK").ToString()
        Dim cAppName As String = Session("fldAppName")
        Dim cGroupName As String = Session("fldGroupName")
        Dim cAppSymID As String = Session("fldAppSymID")
        Dim cAppPhone As String = Session("fldAppPhone")
        Dim cAppPhoneOpt As String = Session("fldAppPhoneOpt")

        Dim strfldAppNameOld As String = RTrim(LTrim(e.OldValues("cappname").ToString()))
        Dim strfldAppNameNew As String = RTrim(LTrim(e.NewValues("cappname").ToString()))

        Dim strfldGroupNameOld As String = RTrim(LTrim(e.OldValues("cgroupname").ToString()))
        Dim strfldGroupNameNew As String = RTrim(LTrim(e.NewValues("cgroupname").ToString()))

        Dim strfldAppSymIDOld As String = RTrim(LTrim(e.OldValues("cappsymid").ToString()))
        Dim strfldAppSymIDNew As String = RTrim(LTrim(e.NewValues("cappsymid").ToString()))

        Dim strfldAppPhoneOld As String = RTrim(LTrim(e.OldValues("cappohone").ToString()))
        Dim strfldAppPhoneNew As String = RTrim(LTrim(e.NewValues("capphone").ToString()))

        Dim strfldAppPhoneOptOld As String = RTrim(LTrim(e.OldValues("cAppPhoneOpt").ToString()))
        Dim strfldAppPhoneOptNew As String = RTrim(LTrim(e.NewValues("cappphoneopt").ToString()))

        gvGroupApps_Updated(keyValue, strfldAppNameNew, strfldGroupNameNew, strfldAppSymIDNew, strfldAppPhoneNew, strfldAppPhoneOptNew)

        'http://support.microsoft.com/kb/301248

    End Sub

    Protected Sub gvGroupApps_Updated(ByVal cAppPK As String, ByVal fldAppName As String, ByVal fldGroupName As String, ByVal fldAppSymID As String, ByVal fldAppPhone As String, ByVal fldAppPhoneOpt As String)
        Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("DiscussSQLConnectionString").ConnectionString)
        Dim sSql As XElement
        sSql = <sql>
            Update [tblApplication] SET [cAppName] = '<%= [fldAppName] %>' 
                   ,[cGroupName] = "<%= [fldGroupName] %>', [cAppSymID] = <%= [fldAppSymID] %>' 
                   ,[cAppPhone] = <%= [fldAppPhone] %>', [cAppPhoneOpt] = <%= [fldAppPhoneOpt] %>'
            Where [cAppPK] = '<%= [fldAppName] %>' </sql>

        'MsgBox(Err.Description, MsgBoxStyle.Critical, "SQL Error")

        SendSQLCommand(sSql.Value, sqlConn)
        sqlConn.Close()
    End Sub
End Class




Can anyone tell me what I'm missing? Any help is much appreciated!
Was This Post Helpful? 0
  • +
  • -

#7 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: Unable to update or delete records from SQL 2008 database

Posted 19 July 2012 - 04:33 PM

SELECT [fldAppName] as 'Application', [fldGroupName] as 'Group Name', [fldAppSymID] as 'Symposium ID', 
       [fldAppPhone] as 'Phone', [fldAppPhoneOpt] as 'Options' 
FROM [tblApplication] 
WHERE ([fldGroupName] = @fldGroupName) ORDER BY [fldAppName]



You're giving an alias to your columns. The boundfields will be looking for the alias name not the real column name. You would need to bind to "Application" instead of "fldAppName". Either that or remove the aliases.
Was This Post Helpful? 2
  • +
  • -

#8 gns1497  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 27-June 12

Re: Unable to update or delete records from SQL 2008 database

Posted 16 August 2012 - 10:06 AM

View PostNakor, on 19 July 2012 - 04:33 PM, said:

SELECT [fldAppName] as 'Application', [fldGroupName] as 'Group Name', [fldAppSymID] as 'Symposium ID', 
       [fldAppPhone] as 'Phone', [fldAppPhoneOpt] as 'Options' 
FROM [tblApplication] 
WHERE ([fldGroupName] = @fldGroupName) ORDER BY [fldAppName]



You're giving an alias to your columns. The boundfields will be looking for the alias name not the real column name. You would need to bind to "Application" instead of "fldAppName". Either that or remove the aliases.




Thanks for all the help, Nakor. I found that once I changed this to use the Alias it worked, but took me back to the original error that I mentioned in the first post of this thread. After much use of Google :) I found this was being caused by the spaces in my alias'. Once I replaced the spaces with underscores...voila. I actually ended up removing all of the alias' and manually defining my columns in the gridview and setting the "HeaderText" property with what I wanted them to display as. The worked perfectly. Thanks again for your help.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1