SQLDataSource: Update 2 Tables With 1 UpdateCommand?
Mar 16, 2007
I have a Gridview (edit enabled), connected to a SQLDataSource.
SQLDataSource populates the gridview from 1 SelectCommand.
figure1:
ColumnA | ColumnB | ColumnC
I have 2 SQL Server 2005 tables: Table1 and Table2
Now on my SQLDataSource's UpdateCommand, I want the value of ColumnB to go to Table1's ColumnB, and ColumnC to go to Table2's ColumnC.
How do I do this?
I understand I can't do this with 1 UPDATE-SET sql query. Maybe I can with stored procs or something, but im kinda noobish when it comes to this. How? Thanks
Hi all I have a GridView on an aspx page, that is enabled for editing, deletion and sorting. In the Page_Load event of the aspx page, i add a SqlDataSource to the page, and bind the source to the GridView. When i click the update, or delete button, it makes a PostBack, but nothing is affected. I'm sure this has got something to do with the parameters. First, i tried having the GridView.AutoGenerateColumns set to True. I have also tried adding the columns manually, but no affect here either. The code for setting the commands, and adding the SqlDataSource to the page are as follows: string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; string selectCommand = "SELECT * FROM rammekategori"; SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand); ds.ID = "RammeKategoriDS"; ds.UpdateCommand = "UPDATE rammekategori SET Kategoribeskrivelse = @Kategoribeskrivelse WHERE (Kategorinavn = @Kategorinavn)"; ds.DeleteCommand = "DELETE FROM rammekategori WHERE (Kategorinavn = @Kategorinavn)"; Parameter Kategorinavn = new Parameter("Kategorinavn", TypeCode.String); Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse", TypeCode.String); ds.UpdateParameters.Add(Kategorinavn); ds.UpdateParameters.Add(Kategoribeskrivelse); ds.DeleteParameters.Add(Kategorinavn); Page.Controls.Add(ds); SqlDataSource m_SqlDataSource = Page.FindControl("RammeKategoriDS") as SqlDataSource; if (m_SqlDataSource != null) { this.gvRammeKategorier.DataSourceID = m_SqlDataSource.ID; } As mentioned - no affect at all! Thanks in advance - MartinHN
I have a custom setup so bear with me here in my explanation. The SelectCommand for my SQLDataSource gets data from a table using a QueryString for filtering by the record ID. A Repeater control is then used to populate the data into different Textboxes on the page (and a custom HTML-editor control). This works 100%. I'm trying to use the UpdateCommand for the SQLDataSource to then update the record with the data that the user changes. (Example: FieldA is "foo" and the user changes it to "foobar". When the form is submitted, it then updates FieldB to be "foobar"). The form is being submitted, but the data isn't being updated at all. Any ideas? Thanks for the help in advance.
I have a SQLDataSource that has an UpdateCommand assigned to it. At certain times I may need to change the UpdateCommand to save different info to the database. I thought I would be able to use sqldatasource.updatecommand.equals() to change the UpdateCommand and then call the sqldatasource.update method, but it didn't work...the default command was still used. Is there something else I need to change? I figured my next option would be to create another sqldatasource. Any help would be great!
Hello all, I am trying to update the record which involed the modification of key in Datakeynames, but when i click the update button from gridview, it doesn't allow to change the value of modified column. <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>" OldValuesParameterFormatString="old_{0}" SelectCommand="SELECT * FROM [ServiceAgents]" InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)" UpdateCommand="UPDATE ServiceAgents SET AGENT=@AGENT, PHONE=@PHONE WHERE (STATE=@STATE and CITY=@CITY and AGENT=@old_AGENT and EQUIPMENT=@EQUIPMENT)" DeleteCommand="DELETE FROM ServiceAgents WHERE (STATE=@STATE and CITY=@CITY and AGENT=@AGENT and EQUIPMENT=@EQUIPMENT)" > <UpdateParameters> <asp:Parameter Type="String" Name="AGENT" /> <asp:Parameter Type="String" Name="PHONE" /> <asp:Parameter Name="old_AGENT" /> <asp:Parameter Type="String" Name="STATE" /> <asp:Parameter Type="String" Name="CITY" /> <asp:Parameter Type="String" Name="EQUIPMENT" /> </UpdateParameters> <InsertParameters> <asp:ControlParameter Name="Ser_STATE" ControlID="TextBox_state"/> <asp:ControlParameter Name="Ser_CITY" ControlID="TextBox_city"/> <asp:ControlParameter Name="Ser_AGENT" ControlID="TextBox_agent"/> <asp:ControlParameter Name="Ser_PHONE" ControlID="TextBox_phone"/> <asp:ControlParameter Name="Ser_EQUIPMENT" ControlID="TextBox_equip" /> </InsertParameters> <DeleteParameters> <asp:Parameter Type="String" Name="STATE" /> <asp:Parameter Type="String" Name="CITY" /> <asp:Parameter Type="String" Name="AGENT" /> <asp:Parameter Type="String" Name="EQUIPMENT" /> </DeleteParameters> </asp:SqlDataSource> Does anyone got any ideas about it? Where is wrong in the control?
SqlDataSource UpdateCommand plus Insert I have a SqlDataSource with an UpdateCommand but besides that i need also an Insert command triggered on the same update command. That´s because i need to update a record and at the same time include a log of that update in another database. Thanks
Hi I have a Page with a DetailsView and SqlDataSource. When Editing the DetailsView the Sql;DataSource UpdateCommand does not seem to pick up the @parameters of the form fields. I have used OnItemUpdating to view all Keys/OldValues/NewValues passed into the UpdateCommand but nothing seems to be picked up. When I place a value into UpdateCommand it updates correctly but does not when I use the form. At this stage I have simplified the code down to typical Master-Details Pages (Separate) using a QueryString to filter the SelectCommand. But nothing seems to work. Please help! I have wasted so much time trying to resolve this and I am on deadline and need this to work. Thanks Dave
Hi, All I'm using Gridview and SqlDataSource to dynamically display the contents in different tables, as followed: <% dataSource.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"]; dataSource.SelectCommand = "SELECT * FROM " + tableName; gridView.DataBind(); dataSource.UpdateCommand = "";%> <asp:SqlDataSource ID="dataSource" runat="server"></asp:SqlDataSource><asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" PageSize="20" DataKeyNames="ID" OnRowDataBound="tableGridView_RowDataBound"> <HeaderStyle BackColor="#C0C0FF" /> <AlternatingRowStyle BackColor="#C0FFC0" /></asp:GridView> The datasource take the "tableName" as argument to determine which table to display. My problem is I can't figure out a way to programmatically construct the UpdataCommand for the SqlDataSource. I try to get the field names from the HeaderRow, but all the cells are empty in this row. Does anyone know what causes the problem or how to construct the UpdateCommand properly. Thanks!
I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below: btnUpate_Click(object sender, EventArgs e){ sqlDataSource1.Update(); sqlDateSource2.Update();} But, the records haven't updated. In SqlDataSource2_Updating() function, I found all the parameters is null. So, how to modify my code to do it.
I'm new to ASP and ASP.NET so I used the Wizards in Visual Web Deverlopment Express 2005 to build the following code:<asp:DetailsView ID="DetailsView" runat="server" DataSourceID="TracksDataSource" Height="50px" Width="125px" AutoGenerateEditButton="True" AutoGenerateRows="False"> <Fields> <asp:BoundField DataField="pk_trackID" HeaderText="pk_trackID" ReadOnly="True" SortExpression="pk_trackID" /> <asp:BoundField DataField="trackName" HeaderText="trackName" SortExpression="trackName" /> <asp:BoundField DataField="trackPath" HeaderText="trackPath" SortExpression="trackPath" /> <asp:BoundField DataField="lyrics" HeaderText="lyrics" SortExpression="lyrics" /> </Fields> </asp:DetailsView>
<asp:SqlDataSource ID="TracksDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:connectionString %>" SelectCommand="SELECT * FROM [Tracks] WHERE ([pk_trackID] = @pk_trackID)" UpdateCommand="UPDATE [Tracks] SET [trackName] = @trackName, [trackPath] = @trackPath, [lyrics] = @lyrics WHERE [pk_trackID] = @pk_trackID" > <UpdateParameters> <asp:Parameter Name="trackName" Type="String" /> <asp:Parameter Name="trackPath" Type="String" /> <asp:Parameter Name="lyrics" Type="String" /> <asp:Parameter Name="pk_trackID" Type="String" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="TracksListBox" Name="pk_trackID" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> However, when I click Edit, change something, and then Update it doesn't update the database. However, if I remove the DataField bindings and use the AutoGenerateRows feature it works fine.
One of the requirements of the UpdateParameters for a GridView I'm building is that the fields that are being edited via EditItemTemplates are passed back to the UpdateParameter as XML. How would I go about combining the fields from the GridView/EditItems into an XML string that I can set as an asp:Parameter? Thanks.
OK guys, I'm sure I'm doing something stupid here but after 2 days I find myself extremely frusted... Any help would be appreciated I'm tring to write an update procedure for an SqlDataSource control that will allow me to chage the primary key values of a record in the table. Everything seems to work fine with the exception of obtaining the old values (the primary key before its changed by the end user) during the update. I've read several articles on the web an in the forums on this topic. Much of it has to do with the .NET beta version alteration from "original_{0}" to "{0}". I believe those conversations do not describe my problem. Anyhow, onto some source code. Here's the ASP source code for a grid view and sql data source. <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="productName,productURL" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:BoundField DataField="productName" HeaderText="productName" ReadOnly="True" SortExpression="productName" /> <asp:BoundField DataField="productURL" HeaderText="productURL" ReadOnly="True" SortExpression="productURL" /> <asp:BoundField DataField="productSupportURL" HeaderText="productSupportURL" SortExpression="productSupportURL" /> <asp:BoundField DataField="description" HeaderText="description" SortExpression="description" /> <asp:BoundField DataField="modified" HeaderText="modified" SortExpression="modified" /> <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" /> <asp:BoundField DataField="useridDomain" HeaderText="useridDomain" SortExpression="useridDomain" /> <asp:BoundField DataField="sortOrder" HeaderText="sortOrder" SortExpression="sortOrder" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SBAAdminConnectionString1 %>" DeleteCommand="DELETE FROM [homePageList] WHERE [productName] = @old_productName AND [productURL] = @old_productURL" InsertCommand="INSERT INTO [homePageList] ([productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder]) VALUES (@productName, @productURL, @productSupportURL, @description, @modified, @userid, @useridDomain, @sortOrder)" ProviderName="<%$ ConnectionStrings:SBAAdminConnectionString1.ProviderName %>" SelectCommand="SELECT [productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder] FROM [homePageList]" UpdateCommand="UPDATE homePageList SET productSupportURL = @productSupportURL, description = @description, modified = @modified, userid = @userid, useridDomain = @useridDomain, sortOrder = @sortOrder, productName = @productName, productURL = @productURL WHERE (productName = @original_productName) AND (productURL = @original_productURL)" OldValuesParameterFormatString="original_{0}"> <DeleteParameters> <asp:Parameter Name="old_productName" Type="String" /> <asp:Parameter Name="old_productURL" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productName" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productURL" PropertyName="SelectedValue" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> </InsertParameters> </asp:SqlDataSource>I added a text box for every field in the grid to the form and update the fields when the user selects a record in the GridView. I then added an Update button to the form and tied the event to this procedure: Protected Sub bUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bUpdate.Click 'if the user has not entered key fields! If missingKeyFields() Then Exit Sub
End If
Dim count, so As Integer Dim uDomain, uid As String
'parse out the user id and domain. Try If HttpContext.Current.User.Identity.Name = "" Then uid = "unknow" uDomain = "unknown"
'insert the record. count = Me.SqlDataSource1.Update() Me.lCommandInfo.Text = count.ToString + " record was updated."
End Sub The net result of the procedure is the message "0 record was updated." when I select a record in the gridView and then edit the "productName" field and click Update. In an attempt to trace down the problem I added this code: Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating
Message.Text = e.Command.CommandText Message.Text += "<br>" For lp As Integer = 0 To e.Command.Parameters.Count - 1 Message.Text += e.Command.Parameters(lp).ParameterName + "=" + e.Command.Parameters(lp).Value Message.Text += "<br>"
Next
End Sub The message label displays the following text:UPDATE homePageList SET productSupportURL = @productSupportURL, description = @description, modified = @modified, userid = @userid, useridDomain = @useridDomain, sortOrder = @sortOrder, productName = @productName, productURL = @productURL WHERE (productName = @original_productName) AND (productURL = @original_productURL) @description=interesting! @modified=3/15/2007 4:07:31 PM @userid=unknow @useridDomain=unknown @sortOrder= @productName=ok22 @productURL=http @productSupportURL=http @original_productName=ok @original_productURL=ok Notice how the "@original_productURL" has the same value as the "@original_productName"? This should not be the case. The @original_productURL should equal "http" since I didn't update the field's value. It seems like the second field of the primary key is not getting updated or passed to the @original_productURL parameter. Since .NET handles the @original_{0} parameters I don't know why the field isn't being properly updated. Any thoughts on how to track down the root cause of my problem... Or better yet any thoughts on a work around to my problem? Thanks,Johnny
I need to know how to setup the ControlParameter for a template control in my Gridview? I have a datepicker in my template and I need to know how to refer to it in the ControlParameter of the SQLDataSource control. <UpdateParameters> <asp:ControlParameter Name="dp_start" ControlID="bdpPlanStart" PropertyName="SelectedValue" Type="Datetime" /> Here is the template:<asp:TemplateField HeaderText="Start" SortExpression="dp_start"> <ItemTemplate> <%#DataBinder.Eval(Container, "DataItem.dp_start", "{0:d}")%> </ItemTemplate> <EditItemTemplate> <BDP:BasicDatePicker id="bdpPlanStart" SelectedValue='<%# DataBinder.Eval(Container.DataItem,"dp_start") %>' runat="server" DateFormat="d"> </BDP:BasicDatePicker> </EditItemTemplate> </asp:TemplateField>
I am trying to reload an AJAX control without reloading the page. This question may belong under AJAX, but I'm not sure if I'm managing the SqlDataSource correctly. I want to set the SelectCommand to a new SQL statement and refresh the form on the screen. My code is below. It doesn't seem to be working. Do I need to call something else or is this correct and the problem is with that nasty AJAX?SqlDataSource oSqlDataSource1 = (SqlDataSource) oContentPlaceHolder1.FindControl("SqlDataSource1");oSqlDataSource1.SelectCommand = "SELECT * FROM WorkHistory WHERE WorkHistoryID = " + sWorkHistoryID;oSqlDataSource1.Select(DataSourceSelectArguments.Empty);
I have a number of textboxes in a WizardStep of my CreateUserWizard and I can't update the contents to the database (see below for an example). Any suggestions would be appreciated. Dim txt As TextBox = cuwMoreInfo.FindControl("txtAddress") srcMoreInfo.UpdateParameters("Address").DefaultValue = DirectCast(txt, TextBox).Text
Hi All, i want to use sqldatasource with controlparameters.there are some textboxes and a dropdownlist on my page.i can easily insert and delete records on database table by using these controls as controlparameters by sqldatasource insert() and delete() method but updating fails.i use try catch block to see the error but no errors found.And also update() method returns 1 that indicates that it worked fine but when i look into the database i see that the record is not updated.i am stucked and surprised.any ideas please? Thanks
Hi, I have a repeater which i need to build editing capabilities into in a similar way as the GridView, just a little unsure how to tie it together. Ideally i would like to use a GridView as this would do all of the work for me however it looks like I need to go with the repeater due to the nested nature of the data I am working with. What is the best way to go about updating the data source from within a repeater? I have already built the edit interface with some MultiViews in the Repeater control and handling the ItemCommand event, just not sure how to actually update the data source. Can I still use the SqlDataSource wizard to generate all of the SQL for me? If so, within the ItemCommand event handler I have the data i want to update back to the database by using the FindControl method and retrieving the Text, but how do i get this data into the SqlDataSource object and update the database? Are there any samples available which show how to update a datasource using a repeater? Your help is much appreciated trenyboy
I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast. And then firing off the update command that contains that parameter from a button. But it's not changing my data on my server when I do so. I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes. All this works fine, just not sp that should update my data when I hit the submit button. It's supposed to update one of my tables to whatever the selected value is from my drop down list. But it doesn't even change anything. It just refreshes the page and goes back to the original value for my drop down list. Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc. It just won't change the data from the page when I try to.
This is what the stored procedure looks like: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS BEGIN UPDATE [Current_tbl] SET ID = @SelectedID WHERE PrimID = '1' END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ And here's my aspx page: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %> <!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 runat="server"> <title>Data Verification Editor</title> </head> <body> <form id="form1" runat="server"> <asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>" SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet"> </asp:SqlDataSource> <asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>" SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader" UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure"> <UpdateParameters> <asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" /> </UpdateParameters> </asp:SqlDataSource> <table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;"> <tr> <td colspan="2" style="font-family:Tahoma; font-size:10pt;"> Please select one of the following:<br /> </td> </tr> <tr> <td colspan="2"> <asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title" DataValueField="ID" style="width: 100%; height: 24px; background: gold"> </asp:DropDownList> </td> </tr> <tr> <td style="width:50%;"> <asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True" Font-Size="8pt" Width="100%" /> </td> <td style="width:50%;"> <asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True" Font-Size="8pt" Width="100%" /> </td> </tr> <tr> <td colspan="2"> <asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label> </td> </tr> </table> </form> </body> </html> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ And here's my code behind: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Imports System.Data Imports System.Data.SqlClient Partial Class Editor Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load Saved_lbl.Text = "" Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;") Dim View1 As New DataView Dim args As New DataSourceSelectArguments View1 = SQLDS_Fill.Select(args) Dim Row As DataRow For Each Row In View1.Table.Rows Ver_ddl.SelectedValue = Row("ID") Next Row SQLDS_Fill.Dispose() End Sub Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click SQLDS_Update.Update() Saved_lbl.Text = "Thank you. Your changes have been saved." SQLDS_Update.Dispose() End Sub End Class ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I'm referring to this bug, which I've seen a lot of people (including me) run into: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93937 Has anyone heard if MS plans to fix this soon? Thanks
Hi,I'm trying to create a registration page that I've divided into multiple pages (first page for basic details, next page for address, etc.). I insert the record in the first page, and update it in the other pages. I pass the newly created ID to the other pages using the Page.PreviousPage property. In the second page, I have the SqlDataSource configured as "SELECT * FROM [Table] WHERE ID = ?", and the UpdateCommand is "UPDATE .... WHERE ID = ?". In Page_Load, I am updating the SelectCommand to "SELECT ... WHERE ID = " & intID, and the UpdateCommand similarly. The I do a dtlsvw.Databind()But when I go to the next page (the newly created ID is being passed properly), the update doesn't do anything. The new record doesn't contain the values in the detailsview. Can somebody help me out? Thanks,Wild Thing
Hi, I have such a problem:I try to update (or insert) a row in my table and i fail althought i,ve read many posts here. I.ve created a button and "on_click" event to this button and want that event to update or insert a record in my table. I wrote: protected void selectButton_Click(object sender, EventArgs e) { String taskID = projectsGridView.SelectedRow.Cells[0].Text; usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@task where [UserID]=1"; usersSqlDataSource.UpdateParameters.Add("task", taskID); usersSqlDataSource.Update(); } The application creates error in the last line of code (usersSqlDataSource.Update();) and i receive such an error:You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is empty. Pass in a valid dictionary for update or change your mode to OverwriteChanges. For me it looks like there is a problem while setting parameters. Shall i change some properties of the sqlDataSource or GridView? Please help..
ello all Would someone be so kind as to save me from getting balder through pulling my hair out. My aim is to extract data from a database using SQLDataSource, then edit the data and update the database using the SQLDataSource. I have achieve the problem of retrieving the data from the sqlDataSource:DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty); //Int32 openRemindingSeats = SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView; foreach (DataRowView rowProduct in openReminding) { //Output the name and price lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString(); } Within the sqlDataSource the sql code is as follows:SELECT [refNumber], [refRemaining] FROM [refFlights] WHERE ([refNumber] = @Number) So at the moment my problems is being able to edit and update data to the same SELECTed data.Thank you for any help that you might have... SynDrome
I’m trying to create a sqlDataSource using the wizard, I can get the select statement, the here clause and the order by clause but when I click on the Advanced button the options to generate the insert, update and delete statements are not available. Does anyone know what am I doing wrong?
Here is the problem. I have table such as this PK selection VARCHARPK selection_choice VARCHAR show BIT When I use the sqlDataSource to create an update it creates this.UPDATE lu_selection_choices SET show = @show WHERE (selection = @selection) AND (selection_choice = @selection_choice) Now this is OK, but when you perform an update and in say a details control you change the selection_choice the value of the parameter @selection_choice is going to be equal to the old data. So it performs a successful update on show which isn't being changed selection_choice is. So the questions is. Is there anyway to tell the update function of the datasource to insert the a new parameter with the SET selection_choice = @SOME NEW PARAM slection_choice? Thanks, Darren King
Hi - I'm using .net2, and have a gridview, populated by a SQL Datasource (Edit, Insert, Delete, Select). Like we all used to do with the datagrid, I've added text boxes into the footer, and a link button, which I'd like to use to fire the Update command. How do I get the link button to trigger the update command? Thanks, Mark
I have added a gridview and under its properties i have changed AutoGenerateDeleteButton and AutoGenerateEditButton to true. The problem is... when I try and delete a record, I get this error: Deleting is not supported by data source 'SqlDataSource1' unless DeleteCommand is specified If I try and update.. i get the error: Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified. Can someone please help me out... maybe with some code... or a link to a tutorial.. Thanks Canning
Hello everyone, I am a bit new to ASP .Net so forgive me, if I dont understand something right off. I am writing a page that gets code from the SQL database and puts it into a GridView. To get the information I am using SqlDataSource. I cant post an error message because I am running this off remote server but though testing I figure that it crashes when I add Analysis = @Analysis line into the UpdateCommand. First I thought that maybe my parameters were not read correcty but when I tried something like Analysis = 6 it worked. Then I tried to replace CQNo=@CQNo with CQNo = @Analysis and it also worked. I am very much puzzed at this. In SQL database CQNo is varchar, WorkDate is DateTime and Analysis is Money type. Can someone please help, I am out of ideas. Thanks! <asp:SqlDataSource ID="myEfforts" runat="server" SelectCommand="SELECT SNo, CQNo, WorkDate, Analysis, Design, Coding, Testing, DesRev, CodeRev, PeerRev, SysTest, PostInstall, Others, TotEff FROM Effort WHERE EmpId = @EmpId ORDER BY WorkDate DESC" DeleteCommand="DELETE FROM Effort WHERE SNo=@SNo" UpdateCommand="UPDATE Effort SET CQNo = @CQNo, WorkDate=@WorkDate Analysis=@Analysis WHERE SNo=@SNo"> <SelectParameters> <asp:SessionParameter DefaultValue="" Name="EmpId" SessionField="PR_EmpIDVal" Type="String" /> </SelectParameters>
Coming from an asp world, I am totally lost on this one. I have two tables. tblUsers and tblUserInfo I have a gridview that gives me the list of users and when selected, it gives me the detailsview of the the record. However, When I need to add a new user, I am trying to add that user into tblUsers (which happens with no problems), and then add a record, using the same user number (UserID) has the key or ID for the tblUserInfo. So basicly, when I create a new user in tblUsers I want to create a record in tblUserInfo with the same userid. In ASP, this takes me minutes... I have been trying to figure this out for weeks. vs2005, sql express. Thanks.
Hi, I have some problem with inserting data into two separate table which are connected via foreign key like in this example:Students (id_s, name, id_subject)Subjects (id_subject, name)So the point is to add a new student and subject for that chosen student. You have to make two inserts right? but how to pass the primary key to the table with foreign key? The form consists of three texbox: name, name_of_subject. Both id_s and id_subject are increment automatically. Any idea how to do it? I would be very grateful for the working code.Thanks in advance and best regards,N.
