Gridview - Updating Is Not Supported By Data Source 'SqlDataSourceGridView' Unless UpdateCommand Is Specified
May 13, 2008
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.
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
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
I have a few Stored procedures that return values, some need parameters passing and others don't. Up to now to access that data in a Web App I have called this procedure using VB after seting up a data block etc. However I notice that the Web Developer express edition has some tools that look like they should help. I have used the in-built tools to create a SQL data source linked to a details view. I then specified that this should connect to my database (sql2005 hosted) and then specified Custom SQL or Stored procedure. From the drop down I can then select the SP that purely returns values. When I try to test this query I get an error to say that the query did not return any data tables. Is there a way to get values returned from a SP in this way? Regards Clive
hello all, I ran into a problem using the "sql data source updating " method. i'm using a form view with about 20 parameters that are bind to controls in the form view and 5 other parameters that i'm setting the value to in the "sql data source updating" method. Every thing updates find execept for the last 5 parameters that i'm setting the values to in the "sql data source updating" method. My store procedure(sp) updates and insert mulitple tables. For some UNKOWN reason the tables that uses the parameteres in the "sql data source updating " are inserting multple records when only 1 record should be inserted. Have anyone have this problem?
I have a sql data source that is filtered by a date range. The results are then presented in a Gridview. What I am hoping to do is add a button that will update all the filtered rows in the sql data source. Is this possible? The data source is shown below: <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:MYSTR%>" SelectCommand="SELECT * FROM [dbo_cheques] WHERE (([chq_banked] >= @chq_banked) AND ([chq_banked] <= @chq_banked2))" UpdateCommand="UPDATE dbo_cheques SET chq_printed = @chq_printed WHERE (chq_id = @chq_id)"> <SelectParameters> <asp:ControlParameter ControlID="Calendar1" Name="chq_banked" PropertyName="SelectedDate" Type="DateTime" /> <asp:ControlParameter ControlID="Calendar2" Name="chq_banked2" PropertyName="SelectedDate" Type="DateTime" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="chq_printed" /> <asp:Parameter Name="chq_id" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource>
I have a SSIS package that simply moves data from a SQL database A to another SQL database B. I have update (increased) the size of a nvarchar column, on both A and B.I am wondering if there is a way to "refresh" somehow the SSIS package so I don't have to rebuild and redeploy it.The error I get now is a truncation error: "Text was truncated or one or more characters had no match in the target code page".
Hi, I've got a gridview that displays some (but not all) columns from a sql database table. One of the columns that is NOT displayed is the table's primary key column. I'm putting together this row updating sub based on an article I found on the 'Net. Here's the code I've added for the GridView's edit routine (based on the article):Protected Sub gvPersonnelType_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles gvPersonnelType.RowEditing gvPersonnelType.EditIndex = e.NewEditIndex gvPersonnelTypeBindData() End Sub
Protected Sub gvPersonnelType_RowCancellingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs) gvPersonnelType.EditIndex = -1 gvPersonnelTypeBindData() End Sub
Protected Sub gvPersonnelType_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs) 'Dim PersonnelTypeID As Integer'This is not displayed in the gridview, but is the primary key in the database Dim LaborForceTypeID As Integer Dim DefaultPayRate, DefaultPieceRate, Rebill As Decimal'These are money datatype in the database Dim DefaultAdminCostPercent, MarkUp As Double 'These are float datatype in the database'Don't know if the following lines properly "capture" the values in the textboxes LaborForceTypeID = Integer.Parse(gvPersonnelType.Rows(e.RowIndex).Cells(0).Text) DefaultPayRate = CType(gvPersonnelType.Rows(e.RowIndex).Cells(1).Controls(0), TextBox).Text DefaultPieceRate = CType(gvPersonnelType.Rows(e.RowIndex).Cells(2).Controls(0), TextBox).Text DefaultAdminCostPercent = CType(gvPersonnelType.Rows(e.RowIndex).Cells(3).Controls(0), TextBox).Text Rebill = CType(gvPersonnelType.Rows(e.RowIndex).Cells(4).Controls(0), TextBox).Text MarkUp = CType(gvPersonnelType.Rows(e.RowIndex).Cells(5).Controls(0), TextBox).Text
Dim gvSqlConn = New SqlConnection("DATABASECONNECTION")
gvSqlConn.open() Dim UpdatePersonnelTypeCmd As New SqlCommand("UPDATE rsrc_PersonnelType SET LaborForceTypeID = @LaborForceTypeID, " & _ "DefaultPayRate = @DefaultPayRate, DefaultPieceRate = @DefaultPieceRate, " & _ "DefaultAdminCostPercent = @DefaultAdminCostPercent, Rebill = @Rebill, Markup = @Markup", gvSqlConn)
'Sql doesn't know what row to update: NEED WHERE STATEMENT!!!
End Sub As you can see, I've added some notes in the RowUpdating sub. I don't understand how to use the PersonnelTypeID (the database table's primary key) for the WHERE clause to update the correct row in the database. As for the GridView itself, it's populated from the code-behind as well (as you can see from the call to "gvPersonnelTypeBindData()") and the SELECT statement used to populate the gridview doesn't use the PersonnelTypeID column either. I'd appreciate it if someone could point me in the right direction to get this RowUpdating sub working. Thanks!
Hi,I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @TransactionID AND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @Itemid) " It gives the error that Quantityavailable is not a SET type?Thanks if you can suggest a remedy! Jon
Hi - Once again I've been looking at this forever and not able to see the problem. Have a grid table everything updates except the training date field. That get's wiped out each time - no matter if something is in it or not. Everything else updates correctly. Here's the code: <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><Columns><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True"><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:BoundField><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:TemplateField> <asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>" ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"UpdateCommand="UPDATE [trainingLog] SET [trainingDate] = @trainingDate WHERE [eventID] = ?" > <UpdateParameters><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>
Hi, I'm new in ASP 2.0. I need to incorporate edit and delete capability in GridView. Using the wizard, i've generated this code. When I delete a row, it gets deleted but update does not work. I've tried several ways. I got no error or exception. But row is not updated. I've checked database, and I think the update query is not executing at all. Please let me know, what I'm doing wrong? Here is the source code for reference. I'm using Visual Studio 2005 with SQL server 2005 Express Edition. Regards
I'm using the XML source component with an autogenerated XSD. There's a small error in the XSD definition, the ID column is defined as unsigned short instead of non-negative integer. I've changed this in the XSD file itself, and renamed it but the DTSX package keeps failing with an error that the id value is to large. Obviously it's still using the old XSD instead of then new one. How do I fix this? (The XSD location in the XML component is referring to the correct XSD file)
I have to update a table in my sql database with data exported (csv, txt or xls) from another source. The source data and my table have a matching unique field. The source data may contain records I already have and need to updated with any changed data in the source file or new records that need to be inserted into my sql table. I am using SQLExpress, SSMSE, ASP.NET 2.0, and VWD Express. I am relatively new to this environment. Can anyone suggest a solution or where I can find a solution? I could do this when I was building apps in Access. Thanks George
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having. Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks!
, Hi In this code how can I create a new data source and new data source view and model and structure that it run dynamic. In this code I have a lot of errors, that they are about server and database don€™t have in current code, In this code, first I should definition server or no?
How can I create data source and data source view and model and structure? Please say code of that, and guide me. databasename and srv is unknown. Do I add other reference with analysis services? Please explain about these codes: ************************************************************************ 1) RelationalDataSource dsNew = new RelationalDataSource( datasourceName, Utils.GetSyntacticallyValidID( datasourceName, typeof(RelationalDataSource)));
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.
Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?
The error in the subject line "The property 'Use Encryption for Data' is not supported" is encountered when running a DTS package via a job step in SQL Server 7.0. The problem is not encountered when executing the DTS package interactively via Enterprise Manager from my PC. When I view the Advanced Properties of the OLE DB driver from Enterprise Manager on my PC, I notice a Use Encryption for Data Property. When I view the Advanced Properties of the OLE DB driver from Enterprise Manager on the Server, the Use Encryption for Data Property does not appear. Service Pack 3 for SQL 7.0 is installed on both my PC and on the SQL Server. I suspect this Property difference for OLE DB could be related to the fact that I installed Enterprise Manager for SQL 2000 on my PC at one time and then uninstalled. Any ideas how to correct this situation?
select * from openquery (DM,'SELECT * FROM [Model Association TB].Content where NODE_TYPE = 8;')
Msg 7354, Level 16, State 1, Line 2
The OLE DB provider "MSDMine" for linked server "DM" supplied invalid metadata for column "NODE_DISTRIBUTION". The data type is not supported.I'm getting an error with the metadata. Appreciate any assistance. Thank you in advance.
i need to retrieve data from a particular field of the gridview according to the selected row to stored it into session .....what i had done so far as following: Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) DetailsView1.PageIndex = GridView1.SelectedIndex Session.Add("receiverName", GridView1.??????) End Sub????? is the part i am not sure what to code.... i tried different method by seems to have problem of convertion.
Hi, I am trying to set a default value for a date field in my update parameters:When I try an update with the DefaultValue="<% Now %>", I get this error:"String was not recognized as a valid DateTime."The updates work fine if no default value is set and it also works ok if I change the default value to a set string, such as "6/24/06". I've tried using different data sources and datasets, but no luck.Anyone have any ideas on this?Thanks! <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:db1ConnectionString %>" ProviderName="<%$ ConnectionStrings:db1ConnectionString.ProviderName %>" SelectCommand="SELECT [ID], [Name], [Date] FROM [Table1]" DeleteCommand="DELETE FROM [Table1] WHERE [ID] = ?" InsertCommand="INSERT INTO [Table1] ([ID], [Name], [Date]) VALUES (?, ?, ?)" UpdateCommand="UPDATE [Table1] SET [Name] = ?, [Date] = ? WHERE [ID] = ?"> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Date" Type="DateTime" DefaultValue="<% Now() %>" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Date" Type="DateTime" /> </InsertParameters> </asp:SqlDataSource>
Hi,I’m new to SQL Express, but I have created a table and a stored proc to populate it.I have dragged the table into a form so I can view the data in a GridView.I have added a button to add new rows to the table. All the above works fine, except when I hit add, the data gets added, but the GridView doesn’t update and show the new data. Is there some code I can add to the add button that also refreshed the GridView? ThanksMike
Hi,I want to add Sql data dependency in GridView. Whenever any changes in database, same will be reflected in Grid. but this should work in both Sql server 2000 and sql server 2005. And when we click on "Pause" button it should stop refreshing.
PAUSE
Index
First name
Last Name
Address
1
Xyz
Abc
Asdf, asdf
2
Pqr
Asd
Sdsa, asad
Is there any way to fulfill this kind of requirement? Please suggest me optimal solution to do this.Regards,Mustakim Mansuri
I created an Integration Services Package that runs fine from my local computer using BIDS. However when I imported into our SQL Server and try to run it from there I get the following error:
DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion"
We are running SQL Server 2005 Standard Edition 64-bit.
We have integration services installed on the server. Is data conversion something that is not supported on Standard Edition?
Also have a similar message for "Send Mail Task."
Is there anywhere that outlines what features are supported on each version?
I'm trying to import data from an Informix Database to my SQL Server 2005 database with the import/export wizard. I have an ODBC-connection that works, Access fetches data with no errors. From the wizard I can only choose to use the IBM Informix OLE DB Provider as datasource. As fare as I know the OLE DB Provider is using settings from the ODBC-driver?
In SQL Server 2000 there is a posibillity to choose Other(ODBC provider) as datasource which works against this database. Any ideas how i can use an ODBC-datasource?
If I try to use the IBM Informix OLE DB Provider I get an error message after the first step of the wizard:
Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=uid;Data Source=db@server".
one of my webpages uses the following sql query to allow the user to search through the database and present the qualifying data in gridview: SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%') how could i expand this so that the user can also search through the database but instead by searching through another column such as [type]? thanks in advance
I have a products table in a Sql Server 2005 database. One of the fields contains the products attributes in xml. The most commonly used attribute is color. An element of color is inventory. This is the only way I could think of to maintain inventory numbers of each color of each product using someone else's (opensource) code. An example of an attribute field would be <?xml version="1.0"?> <ArrayOfAttribute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Attribute> <Name>Color</Name> <Description>Shown in Black</Description> <Selections> <AttributeSelection> <FormattedValue>483 - Black</FormattedValue> <Value>483 - Black</Value> <Inventory>25</Inventory> </AttributeSelection> <AttributeSelection> <FormattedValue>484 - Lt. Tan</FormattedValue> <Value>484 - Lt. Tan</Value> <Inventory>15</Inventory> </AttributeSelection> <AttributeSelection> <FormattedValue>485 - Pink</FormattedValue> <Value>485 - Pink</Value> <Inventory>17</Inventory> </AttributeSelection> </Selections> <SelectionType>SingleSelection</SelectionType> </Attribute> </ArrayOfAttribute> How can I read this into the gridview control so that it makes sense to the user?Diane
I have a webform that I am wanting to display data from a database. Right now I am using the gridview but for future purposes when it comes to maintenance what would be the easiest way to do updates? For example, if I add a new record into the database I would like the webform to automatically update itself to show all the records including the new record I added. Do I need to use a "table" and somehow connect it to a database? Do any stored procedures need to be created? Suggestions/ideas/codes help help help would be verrrry much appreciated!!! Also I am using MS Server 2003 and C# as the programming language. Thank you!!!!
Hello all, I am trying to modify the output of a SELECT statement in a VB asp.net page that pulls data from a SQL DB. I only need to modify one column in the gridview, but I'm having some issues. Here is the situation:SELECT TOP 24 ID, RecID, Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM mTrafficORDER BY Timestamp DESC The Timestamp column returns a number value (i.e. 564566).In terms of a date, 564566 doesn't mean anything to the user, so I need to convert this number into a recognizable and accurate date. The formula I need to implement is Timestamp/1440 - 1 The following SELECT statement returns the number as a date, but makes all records the same date: SELECT TOP 24 ID, RecID, Convert(datetime, Timestamp/1440 + 1) as Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM mTrafficORDER BY Timestamp DESC What do I need to do to implement this function dynamically into my gridview? Thanks in advance!
Good Day, Please help me, I have a database that looks like this, Building No. Floor No. Room No. Customer Name Appliances Appliances Codea 1 01 John TV 12a 1 02 //up tob 20 10 james Ref. 22b 20 10 joe Microwave 15 //and soon so if you would notice some rooms are vacant, as it usually happens, the buildings, floors and rooms are fixed except for the customers and the appliances. so at first i made this gridview and display this data in this manner:Customer Location (Building + floor) total rooms Used Rooms Available rooms A1 2 1 1 B2010 2 2 0 so if you would look at the table above, it just SUM up all tenants in a specific Location then Group it by Customer location,but i need to add the appliances in a different format like this: Customer Location (Building + floor) total rooms Used Rooms Available rooms TV Ref Microwave A1 2 1 1 tick B2010 2 2 0 tick tick so this is what i need to show, i already have done the first table (w'o appliances) but how can i make this second table?? please help me, SALAMAT PO.,Thanks
