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..
I've a SqlDataSource control that has stored procedures specified for each of its commands: SelectCommand, InsertCommand, UpdateCommand, DeleteCommand . And for Insert, Update and Delete, I've specified asp:parameters for each stored procedure's parameters. Now, my stored procedures all have return values, and I've successfully accessed the return values for Insert and Update, but for some reason, I'm getting very wrong results for Delete. <DeleteParameters> <asp:Parameter Name="result" Type="Int32" Direction="ReturnValue" /> <asp:Parameter Name="myID" Type="Int32" /></DeleteParameters>The moment I add my "result" with the direction ReturnValue, I instantly get a "Procedure or function <storedprocedurename> has too many arguments specified." error. I checked my SQL Profiler, and it seems that the page is passing result as an Input parameter, instead of keeping it as a ReturnValue! e.g. exec spName @myID=1, @result=NULLwhen it should be exec spName @myID=1I get the correct behavior with Update and Insert, so I'm wondering whether if this is a bug or by-design behavior or something very screwy with my computer?Help? Thoughts?
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>
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!
I know there are loads of posts on this, but this just will not update. I have tried various forms of the code all to no avail. And now I'm ready to throw my PC out the window........ What am I doing wrong? cheers Mike Dim da As SqlDataAdapterDim dSetOrp As DataSet oCn = New SqlConnection(db.m_ConnectionString) oCn.Open()da = New SqlDataAdapter("Select * from contact WHERE conid = " & lngConId, oCn) dSetOrp = New DataSet da.Fill(dSetOrp, "locTable") ' Fill the DataSet. dSetOrp.Tables(0).Rows(0)("ConSttDate") = dtEffDateDim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(da) myBuilder.GetUpdateCommand() da.UpdateCommand = myBuilder.GetUpdateCommand() lRows = da.Update(dSetOrp, "locTable")
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
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
We've got an employee database that I'm modifying to include two photos of each employee, a small thumbnail image and a full-size image. The HR department maintenance page contains a listbox of employee names, which, when clicked, populates a detailsview control.To get the images to display and be updatable, I've had to structure the following SqlDatasource and DetailsView: 1 <asp:DetailsView ID="dvEmp" runat="server"2 AutoGenerateRows="false"3 DataSourceID="dsEmpView"4 DataKeyNames="empID">5 <Fields>6 <asp:CommandField ShowEditButton="true" ShowCancelButton="true" ShowInsertButton="true" />7 <asp:BoundField HeaderText="Name (Last, First)" DataField="empname" />8 <asp:TemplateField HeaderText="Thumbnail photo">9 <ItemTemplate>10 <asp:Image ID="imgThumbnail" runat="server" ImageUrl='<%# formatThumbURL(DataBinder.Eval(Container.DataItem,"empID")) %>' />11 </ItemTemplate>12 <EditItemTemplate>13 <asp:Image ID="imgThumbHidden" runat="server" ImageUrl='<%# Bind("thumbURL") %>' Visible="false" />14 <asp:FileUpload ID="upldThumbnail" runat="server" />15 </EditItemTemplate>16 </asp:TemplateField>17 <asp:TemplateField HeaderText="Full Photo">18 <ItemTemplate>19 <asp:Image ID="imgPhoto" runat="server" ImageUrl='<%# formatImageURL(DataBinder.Eval(Container.DataItem,"empID")) %>' />20 </ItemTemplate>21 <EditItemTemplate>22 <asp:Image ID="imgPhotoHidden" runat="server" ImageUrl='<%# Bind("photoURL") %>' Visible="false" />23 <asp:FileUpload ID="upldPhoto" runat="server" />24 </EditItemTemplate>25 </asp:TemplateField>26 </Fields>27 </asp:DetailsView>28 29 <asp:SqlDataSource ID="dsEmpView"30 runat="server"31 ConnectionString="<%$ ConnectionStrings:eSignInConnectionString %>"32 OnInserting="dsEmpView_Inserting"33 OnUpdating="dsEmpView_Updating"34 SelectCommand="SELECT empID, empname, photoURL, thumbURL FROM employees where (empID = @empID)"35 InsertCommand="INSERT INTO employees (empname, photoURL, thumbURL) values(@empname, @photoURL, @thumbURL)"36 UpdateCommand="UPDATE employees SET empname=@empname, photoURL=@photoURL, thumbURL=@thumbURL WHERE (empID = @empID)">37 <SelectParameters>38 <asp:ControlParameter ControlID="lbxEmps" Name="empID" PropertyName="SelectedValue" Type="Int16" />39 </SelectParameters>40 </asp:SqlDataSource>41 42 -----43 44 Protected Sub dsEmpView_Updating(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)45 Dim bAbort As Boolean = False46 Dim bThumb(), bPhoto() As Byte47 If e.Command.Parameters("@ename").Value.trim = "" Then bAbort = True48 Dim imgT As FileUpload = CType(dvEmp.FindControl("upldThumbnail"), FileUpload)49 If imgT.HasFile Then50 Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream)51 bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength)52 e.Command.Parameters("@thumbURL").Value = bThumb53 End Using54 End If55 Dim imgP As FileUpload = CType(dvEmp.FindControl("upldPhoto"), FileUpload)56 If imgP.HasFile Then57 Using reader As BinaryReader = New BinaryReader(imgP.PostedFile.InputStream)58 bPhoto = reader.ReadBytes(imgP.PostedFile.ContentLength)59 e.Command.Parameters("@photoURL").Value = bPhoto60 End Using61 End If62 e.Cancel = bAbort63 End SubIf the user updates both images at the same time by populating their respective FileUpload boxes, everything works as advertized. But if the user only updates one image (or neither image), things break. If they upload, say, just the full-size photo during an update, then it gives the error "System.Data.SqlClient.SqlException: Operand type clash: nvarchar is incompatible with image".I think this error occurs because the update command is trying to set the parameter "thumbURL" without having any actual data to set. But since I really don't want this image updated with nothing, thereby erasingthe photo already in the database, I'd rather remove this parameter from the update string.So, let's remove the parameter that updates that image by adding the following code just after the "End Using" lines: Else Dim p As SqlClient.SqlParameter = New SqlClient.SqlParameter("@thumbURL", SqlDbType.Image) e.Command.Parameters.Remove(p) (Similar code goes into the code block that handles the photo upload)Running the same update without an image in the thumb fileupload box, I now get this error: "System.ArgumentException: Attempted to remove an SqlParameter that is not contained by this SqlParameterCollection."Huh? It's not there? Okay, so lets work it from the other end: let's remove all references to the thumbURL and photoURL from the dsEmpView datasource. We'll make its UpdateCommand = "UPDATE employees SET empname=@empname WHERE (empID = @empID)", and put code in the dsEmpView_Updating sub that adds the correct parameter to the update command, but only if the fileupload box has something in it. Therefore: If imgT.HasFile Then Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream) bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength) e.Command.Parameters.Add(New SqlClient.SqlParameter("@thumbURL", SqlDbType.Image, imgT.PostedFile.ContentLength)) e.Command.Parameters("@thumbURL").Value = bThumb End Using End If (Similar code goes into the code block that handles the photo upload)But reversing the angle of attack only reverses the error. Uploading only the photo and not the thumb image results in: "System.Data.SqlClient.SqlException: The variable name '@photoURL' has already been declared. Variable names must be unique within a query batch or stored procedure."So now it's telling me the parameter IS there, even though I just removed it.ARRRGH!What am I doing wrong, and more importantly, how can I fix it?Thanks in advance.
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!
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
I am getting the above error if i try to create a update command through a Command builder in .net cf on a SQLCE 3.0 database
The Select query I used for creating the update command is 'SELECT * FROM Users'
I populated a datatable with the above query through a DataAdapter, to check for the primary key and the primary key was available in the table. But still the CB could not able to create an Update Command for that query,
The Primary Key column that I use is of type GUID
Then when try to do the above job using a Typed DataSet with TableAdapters , then it throws the following error
'Update requires a valid UpdateCommand when passed DataRow Collection with modified rows'
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
Hi all,I have a gridview that bound to a SqlDataSource called SqlDataSourceGridView. I have enabled Edit in my GridView, but I do all the updating in code behind with stored procedure (using onRowUpdating). Now each time when I click "Update", the update went through and all the data got updated, but I received this error: Updating is not supported by data source 'SqlDataSourceGridView' unless UpdateCommand is specified.
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.NotSupportedException: Updating is not supported by data source 'SqlDataSourceGridView' unless UpdateCommand is specified. What do I need to do to fix this problem?Thanks a lot.
Hey, I changed the database name in the initial cataloge in the web.config conncetion string so that it now connects to other databas that contains same tables as the old one,but now i am getting that error at the update stmt ! thank u in advance Hiba
