SQLDataSource does not update or throw an error : The Official Microsoft ASP.NET Forums

Reply

SQLDataSource does not update or throw an error

Apr 23, 2012 04:37 PM|LINK

This has me confused to no end.

I have a SQLDataSource with the following Commands:

SelectCommand="SELECT [employees].[id],[employees].[forename], [employees].[surname], [employees].[department], [employees].[role], [employees].[telephone], [employees].[email], [departments].[name] FROM [employees] INNER JOIN [departments] ON [employees].[department] = [departments].[id] WHERE ([employees].[id] = @id)"
UpdateCommand="UPDATE [employees] SET [forename]=@forename, [surname]=@surname, [role]=@update_role, [telephone]=@telephone, [email]=@email WHERE [id]=@id"
InsertCommand="INSERT INTO [employees] ([forename],[surname],[department],[role],[telephone],[email]) VALUES (@forename,@surname,@insert_department,@insert_role,@telephone,@email)">

Update has the following parameters:

<UpdateParameters>
                <asp:ControlParameter ControlID="ctl00$ContentPlaceHolder1$insertEdit_details$edit_role" Name="update_role" Type="Int32" PropertyName="SelectedValue" />
                <asp:QueryStringParameter Name="forename" QueryStringField="forename" Type="String" />
                <asp:QueryStringParameter Name="surname" QueryStringField="surname" Type="String" />
                <asp:QueryStringParameter Name="telephone" QueryStringField="telephone" Type="Int32" />
                <asp:QueryStringParameter Name="email" QueryStringField="email" Type="String" />
                <asp:QueryStringParameter Name="id" QueryStringField="id" Type="Int32" />
            </UpdateParameters>

Select and Insert work absolutely fine. Update does not actually update the data and does not throw any form of error.

What is going on and how can I fix it?

Edited by benanderson on Apr 23, 2012 04:38 PM

Reply

Re: SQLDataSource does not update or throw an error

Apr 23, 2012 04:44 PM|LINK

If you've got some server side code that ties into this, you need to run the tableadapter.Update method to send the data back to the database otherwise you're only updating the dataset.

Also, are you checking the querystring parameters in the URL to ensure the ID is being passed?

Reply

Re: SQLDataSource does not update or throw an error

Apr 23, 2012 04:54 PM|LINK

No, there is no server side code. Its all done directly in the ASPX page. It worked before, I've not changed the page and it now it just... doesn't! Hence why I'm baffled.

The ID String is being sent correctly. As stated, the select statement works.

Reply

Re: SQLDataSource does not update or throw an error

Apr 23, 2012 05:05 PM|LINK

Been there my friend. The only option I've found, time-wise, is to just drop the dataset and adapter and recreate it. I know it's not the answer you're looking for, but it'll become a matter of spending more time hunting rather than just rebuilding.

Reply

benanderson

0 Points

7 Posts

Re: SQLDataSource does not update or throw an error

Apr 24, 2012 10:55 AM|LINK

Unfortunatly, reqriting the page has done nothing to help it.

The details view on the page looks like this:

<asp:DetailsView runat="server" ID="edit_insert_details" CssClass="dataTable px800" AutoGenerateRows="False" EnableViewState="True" GridLines="None" Width="500" CellPadding="3">
            <Fields>
                <asp:TemplateField HeaderText="ID">
                    <EditItemTemplate>
                        <asp:Label runat="server" ID="label_id_edit" Text='<%# Eval("id").toString() %>' />
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:Label runat="server" ID="label_id_insert" Text="ID's are automatically generated by the system" />
                    </InsertItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Forename">
                    <EditItemTemplate>
                        <asp:TextBox runat="server" ID="txt_edit_fname" Text='<%# Eval("forename") %>' Columns="25" />
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:TextBox runat="server" ID="txt_insert_fname" MaxLength="50" Columns="25" />
                    </InsertItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Surname">
                    <EditItemTemplate>
                        <asp:TextBox runat="server" ID="txt_edit_sname" Text='<%# Eval("surname") %>' Columns="25" />
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:TextBox runat="server" ID="txt_insert_sname" MaxLength="50" Columns="25" />
                    </InsertItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Department">
                    <EditItemTemplate>
                        <asp:Label runat="server" ID="label_edit_department" Text='<%# Eval("name") %>' />
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:DropDownList ID="drop_insert_department" runat="server"
                            DataSourceID="SelectDepartments" DataTextField="name" DataValueField="id">
                        </asp:DropDownList>
                    </InsertItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Position">
                    <EditItemTemplate>
                        <asp:DropDownList ID="drop_edit_role" runat="server"
                            DataSourceID="SelectRoles" DataTextField="role" DataValueField="id">
                        </asp:DropDownList>
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:DropDownList ID="drop_insert_role" runat="server"
                            DataSourceID="SelectRoles" DataTextField="role" DataValueField="id">
                        </asp:DropDownList>
                    </InsertItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Email">
                    <EditItemTemplate>
                        <asp:TextBox ID="txt_edit_email" runat="server" MaxLength="128" Columns="50" Text='<%# Eval("email") %>' />
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:TextBox ID="txt_insert_email" runat="server" MaxLength="128" Columns="50" />
                    </InsertItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Telephone Extention">
                    <EditItemTemplate>
                        <asp:TextBox ID="txt_edit_phone" runat="server" MaxLegnth="3" Columns="3" Text='<%# Eval("telephone") %>' />
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:TextBox ID="txt_insert_phone" runat="server" MaxLength="3" Columns="3" />
                    </InsertItemTemplate>
                </asp:TemplateField>
            </Fields>
        </asp:DetailsView>
    </p>
    <p>
        <asp:Button runat="server" ID="btn_save" CssClass="mainButton" Text="Save" />
        <asp:Button runat="server" ID="btn_cancel" CssClass="mainButton" Text="Cancel" CommandName="Cancel" PostBackUrl="Default.aspx" />
    </p>

The btn_save control has its CommandName set dynamically depending on which mode the details view is in (edit or insert)

The VB code behind looks like this for the update command:

    Protected Sub btn_save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_save.Click
        If btn_save.CommandName = "save_edit" Then
            saveEdit()
        ElseIf btn_save.CommandName = "save_new" Then
            saveNew()
        End If
    End Sub

    Protected Sub saveEdit()
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("employeeDataBase").ToString()

        Dim connection As New SqlConnection(connectionString)
        connection.Open()

        Dim command As New SqlCommand()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "updateEmployeeById"

        command.Parameters.Add("@id", SqlDbType.Int)
        command.Parameters.Add("@forename", SqlDbType.NVarChar, 50)
        command.Parameters.Add("@surname", SqlDbType.NVarChar, 50)
        command.Parameters.Add("@role", SqlDbType.Int)
        command.Parameters.Add("@email", SqlDbType.NVarChar, 128)
        command.Parameters.Add("@telephone", SqlDbType.Int)

        Dim fname As TextBox = CType(edit_insert_details.FindControl("txt_edit_fname"), TextBox)
        Dim sname As TextBox = CType(edit_insert_details.FindControl("txt_edit_sname"), TextBox)
        Dim role As DropDownList = CType(edit_insert_details.FindControl("drop_edit_role"), DropDownList)
        Dim email As TextBox = CType(edit_insert_details.FindControl("txt_edit_email"), TextBox)
        Dim tel As TextBox = CType(edit_insert_details.FindControl("txt_edit_phone"), TextBox)

        command.Parameters("@id").Value = Request.QueryString.Get("id")
        command.Parameters("@forename").Value = fname.Text
        command.Parameters("@surname").Value = sname.Text
        command.Parameters("@role").Value = role.SelectedValue
        command.Parameters("@email").Value = email.Text
        command.Parameters("@telephone").Value = tel.Text

        command.ExecuteNonQuery()
        connection.Close()
    End Sub

It doesn't throw an error and it tually DOES update the table. Unfortunatly, it reverts everything to the default values.

For example; The forename is "joe" in the table so I change it to "john" and I select the 3rd option in the dropdown control drop_edit_role.

When I fire the saveEdit() prodecure the name stays as "Joe" and the table overwrites the dropdown list defaults to the first item in the list and then commits it to the table. So name hasn't changed and the value for role is now incorrect.

I've checked over my stored procedure and it executes as expected so I'm lost as to what may be causing this.

Reply

adamturner34

Contributor

3964 Points

999 Posts

Re: SQLDataSource does not update or throw an error

Apr 24, 2012 12:13 PM|LINK

You've changed the code around.

You said there was no server-side code but you posted server-side code.

The .net code looks fine as long as the findcontrol is returning the correct object.

If the values being passed into the parameters are correct, I'd take a second look at the stored procedure itself.

Reply

benanderson

0 Points

7 Posts

Re: SQLDataSource does not update or throw an error

Apr 24, 2012 12:31 PM|LINK

Yes, the original had no server side code. I rewrote it all as per your advice and took a completley different approach.

To test, I used the following Query and it worked just fine.

exec updateEmployeeById 100, "Benjamin", "Anderson", 101, 264, "email@email.com"

Rather than calling the stored procedure directly in VB, I'll define an exec command instead.

Edited by benanderson on Apr 24, 2012 12:35 PM

Reply

adamturner34

Contributor

3964 Points

999 Posts

Re: SQLDataSource does not update or throw an error

Apr 24, 2012 01:03 PM|LINK

Then the only avenue left to explore is the values passed to your parameters.

Set a breakpoint over the .Value lines and make sure they're getting values.

If they are, here's a little insider information...Microsoft gives you about 5 different ways to pass parameters and it lets you mix and match but can cause issues.

As a test, simply use command.Parameters.AddWithValue("@ID", Request.QueryString("ID"))

Keep in mind that this will allow casting exceptions because it is no strong typed but it will get you going in the right direction.

Marked as answer by benanderson on Apr 24, 2012 01:08 PM

Reply

benanderson

0 Points

7 Posts

Re: SQLDataSource does not update or throw an error

Apr 24, 2012 01:08 PM|LINK

I did a trace output and found that it was firing correctly, just not with the values I had entered (it was using the values originally pulled from the database) and the Drop Down list was only returning the first item in the list.

I added this to my DetailsView:

ViewStateMode="Disabled"

and it works perfectly now!

Little things, eh? :)

SQLDataSource does not update or throw an error RSS

8 replies

benanderson

SQLDataSource does not update or throw an error

adamturner34

Re: SQLDataSource does not update or throw an error

benanderson

Re: SQLDataSource does not update or throw an error

adamturner34

Re: SQLDataSource does not update or throw an error

benanderson

Re: SQLDataSource does not update or throw an error

adamturner34

Re: SQLDataSource does not update or throw an error

benanderson

Re: SQLDataSource does not update or throw an error

adamturner34

Re: SQLDataSource does not update or throw an error

benanderson

Re: SQLDataSource does not update or throw an error

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft