Group Update of Prices

I am trying to write a SP that will update the price of products that are in a particular category.

I have 3 tables;
PRODUCT
- productID, price

CATEGORY
- categoryID, categoryName, parentID

PRODUCT_CATEGORY_MAP
- productID, categoryID

What I want to do, is allow admin to pass is a new price and categoryID, and it will update all the products that are within the supplied category.

I will start with:
UPDATE PRODUCT SET price = @.price WHERE categoryID = @.categoryID
however I do not have categoryID within the PRODUCT table

Do I place a SELECT statement after the WHERE?

DECLARE @.productID int
UPDATE PRODUCT SET price = @.price WHERE (SELECT @.productID = productID FROM PRODUCT_CATEGORY_MAP WHERE categoryID=@.categoryID)

Not sure if that would work or not or if I am on the right track.
Thanks for any help,
Mick

You just join in the other tables like this (given that you only need categoryID and that's in the map table, there is no need to join Category too, although you could if you were filtering on Category name for example)

UPDATE Product SET price = @.price
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID = @.categoryID

|||

Ok, I ended up figuring it out, but using IN instead of INNER JOIN. I like yours better.

So I want to extend this a little more...
Say I have the categories
ID Name ParentID
1 Cat1 0
2 Cat2 1

I have assigned a product to Cat2
productID categoryID
1 2

I now want my SP to work so that if a user selects the category Cat1, it will also update all child products.
I have a SP that gets all the child categories...
SELECT categoryID FROM Category
WHERE categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))

I have this so far, but it only updates categories in the selected category, not it's childs as well.
UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID IN
(SELECT categoryID FROM Category C
WHERE C.categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))
AND PCM.categoryID=@.categoryID)

Thanks again,
Mick

|||

UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id

--or

UPDATE Product SET ourUSDPrice = @.newPrice
Where Exists (Select ProductId From Product P INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id)

|||Mani has posted two ways to make your query work - do you have the GetChildren function working, so that it gets all the children at all levels? I wasn't sure from your post whether you were asking about that or not.

Group By and Update

I am new to SQL.
I need to do the following. Create a subtotal value from a table and then
populate another table with that value.
Table 1 consists of PO Orders.
I need to group the subtotals by Item Number.
Table 2 is our Item Master File. I would like to populate this table with
the subtotal derived above based on Item Number (the common field).
I have been successful running a group by Query on Table 1 to return the
results of Item Number with the subtotal value. However, I cannot figure out
the methodology to get that information into the second table.
Thanks.
AnnTry this:
UPDATE Table2
SET total = (SELECT SUM(x)
FROM Table1
WHERE Table1.item_num = Table2.item_num)
Are you sure you want to store the total? It's normally inefficient and
undesirable to store any derived calculations in the database. Have you
considered using a view instead? You have the option of an indexed view
to achieve much the same thing.
--
David Portas
SQL Server MVP
--|||I need to be able to access the data from Infopath. Therefore it must be in
a table. I believe is only for reference and is not consider a table. Am I
correct?
"David Portas" wrote:
> Try this:
> UPDATE Table2
> SET total => (SELECT SUM(x)
> FROM Table1
> WHERE Table1.item_num = Table2.item_num)
> Are you sure you want to store the total? It's normally inefficient and
> undesirable to store any derived calculations in the database. Have you
> considered using a view instead? You have the option of an indexed view
> to achieve much the same thing.
> --
> David Portas
> SQL Server MVP
> --
>|||You should be able to create a VIEW which will utilize the calculations and
have InfoPath pull data from the view.
Rick Sawtell
MCT, MCSD, MCDBA

Group By and Update

I am new to SQL.
I need to do the following. Create a subtotal value from a table and then
populate another table with that value.
Table 1 consists of PO Orders.
I need to group the subtotals by Item Number.
Table 2 is our Item Master File. I would like to populate this table with
the subtotal derived above based on Item Number (the common field).
I have been successful running a group by Query on Table 1 to return the
results of Item Number with the subtotal value. However, I cannot figure out
the methodology to get that information into the second table.
Thanks.
Ann
Try this:
UPDATE Table2
SET total =
(SELECT SUM(x)
FROM Table1
WHERE Table1.item_num = Table2.item_num)
Are you sure you want to store the total? It's normally inefficient and
undesirable to store any derived calculations in the database. Have you
considered using a view instead? You have the option of an indexed view
to achieve much the same thing.
David Portas
SQL Server MVP
|||I need to be able to access the data from Infopath. Therefore it must be in
a table. I believe is only for reference and is not consider a table. Am I
correct?
"David Portas" wrote:

> Try this:
> UPDATE Table2
> SET total =
> (SELECT SUM(x)
> FROM Table1
> WHERE Table1.item_num = Table2.item_num)
> Are you sure you want to store the total? It's normally inefficient and
> undesirable to store any derived calculations in the database. Have you
> considered using a view instead? You have the option of an indexed view
> to achieve much the same thing.
> --
> David Portas
> SQL Server MVP
> --
>
|||You should be able to create a VIEW which will utilize the calculations and
have InfoPath pull data from the view.
Rick Sawtell
MCT, MCSD, MCDBA

Group By and Update

I am new to SQL.
I need to do the following. Create a subtotal value from a table and then
populate another table with that value.
Table 1 consists of PO Orders.
I need to group the subtotals by Item Number.
Table 2 is our Item Master File. I would like to populate this table with
the subtotal derived above based on Item Number (the common field).
I have been successful running a group by Query on Table 1 to return the
results of Item Number with the subtotal value. However, I cannot figure ou
t
the methodology to get that information into the second table.
Thanks.
AnnTry this:
UPDATE Table2
SET total =
(SELECT SUM(x)
FROM Table1
WHERE Table1.item_num = Table2.item_num)
Are you sure you want to store the total? It's normally inefficient and
undesirable to store any derived calculations in the database. Have you
considered using a view instead? You have the option of an indexed view
to achieve much the same thing.
David Portas
SQL Server MVP
--|||I need to be able to access the data from Infopath. Therefore it must be in
a table. I believe is only for reference and is not consider a table. Am I
correct?
"David Portas" wrote:

> Try this:
> UPDATE Table2
> SET total =
> (SELECT SUM(x)
> FROM Table1
> WHERE Table1.item_num = Table2.item_num)
> Are you sure you want to store the total? It's normally inefficient and
> undesirable to store any derived calculations in the database. Have you
> considered using a view instead? You have the option of an indexed view
> to achieve much the same thing.
> --
> David Portas
> SQL Server MVP
> --
>|||You should be able to create a VIEW which will utilize the calculations and
have InfoPath pull data from the view.
Rick Sawtell
MCT, MCSD, MCDBA

GridView wont delete or update

I have had this problem before but it turned out to be dodgy SQL created by the wizard. Doesn't seem to be the case this time.

The following does a postback but makes no changes.

1<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>"2DeleteCommand="DELETE FROM [tblSubRegions] WHERE [SubRegionID] = ?"3InsertCommand="INSERT INTO [tblSubRegions] ([SubRegionID], [RegionID], [SubRegionName]) VALUES (?, ?, ?)"4ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>"5SelectCommand="SELECT tblSubRegions.SubRegionID, tblSubRegions.RegionID, tblSubRegions.SubRegionName, tblRegions.RegionName FROM (tblSubRegions INNER JOIN tblRegions ON tblSubRegions.RegionID = tblRegions.RegionID) WHERE (tblSubRegions.RegionID = ?) ORDER BY tblSubRegions.SubRegionName"6UpdateCommand="UPDATE [tblSubRegions] SET [RegionID] = ?, [SubRegionName] = ? WHERE [SubRegionID] = ?">78<DeleteParameters>9 <asp:Parameter Name="SubRegionID" Type="Int32" />10</DeleteParameters>1112<UpdateParameters>13<asp:Parameter Name="RegionID" Type="Int32" />14<asp:Parameter Name="SubRegionName" Type="String" />15<asp:Parameter Name="SubRegionID" Type="Int32" />16</UpdateParameters>1718<SelectParameters>19<asp:ControlParameter ControlID="dropRegions" Name="RegionID" PropertyName="SelectedValue" Type="Int32" />20</SelectParameters>2122<InsertParameters>23<asp:Parameter Name="SubRegionID" Type="Int32" />24<asp:Parameter Name="RegionID" Type="Int32" />25<asp:Parameter Name="SubRegionName" Type="String" />26</InsertParameters>2728</asp:SqlDataSource>29303132<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>"33ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>"34SelectCommand="SELECT [RegionID], [RegionName] FROM [tblRegions]">3536</asp:SqlDataSource>37383940<asp:DropDownList id="dropStates" runat="server" OnSelectedIndexChanged="dropStates_SelectedIndexChanged" AutoPostBack="True">41</asp:DropDownList>4243<asp:DropDownList id="dropRegions" runat="server" OnSelectedIndexChanged="dropRegions_SelectedIndexChanged" AutoPostBack="True">44</asp:DropDownList>45464748 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"49 AutoGenerateColumns="False" EnableViewState=false Width="100%" DataSourceID="SqlDataSource1">50 <Columns>51 <asp:TemplateField HeaderText="SubRegionName" SortExpression="SubRegionName">52 <EditItemTemplate>53 <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"54 DataTextField="RegionName" DataValueField="RegionID" SelectedValue='<%# Bind("RegionID") %>'>55 </asp:DropDownList>56 </EditItemTemplate>57 <ItemTemplate>58 <asp:Label ID="Label1" runat="server" Text='<%# Bind("SubRegionName") %>'></asp:Label>59 </ItemTemplate>60 </asp:TemplateField>61 <asp:BoundField DataField="RegionName" HeaderText="RegionName" SortExpression="RegionName" />62 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />63 </Columns>64 </asp:GridView>

Thanks in advance.

Shaun

You need to set DataKeyNames="yourprimarykey"

GridView update, with SqlDataSource UpdateCommand set from Code-behind. (C#)

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

It turned out, that the SQL-statements where wrong. I got it all to work now, by using a ?-mark, instead of @.Parametername in the SQL.

So this works:

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 = ? WHERE Kategorinavn = ?";
ds.DeleteCommand = "DELETE FROM rammekategori WHERE Kategorinavn = ?";

Parameter Kategorinavn = new Parameter("Kategorinavn");
Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse");
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;
}

I was working on a MySQL server, and not a MS-SQL server, as I normally do...

|||Is there any particular reason why you are adding the SqlDataSource dynamically rather than declaring it in your .aspx code?|||

>>Is there any particular reason why you are adding the SqlDataSource dynamically rather than declaring it in your .aspx code?

Yes - there sure is. I want to define alle data-access information, such as SQL-statements in a lower-tier-layer, so i would be able to remove the GUI, and change it with another GUI. It just gives a better architecture to it...

|||

martinhn wrote:

Yes - there sure is. I want to define alle data-access information, such as SQL-statements in a lower-tier-layer, so i would be able to remove the GUI, and change it with another GUI. It just gives a better architecture to it...

It sounds like the ObjectDataSource is more suited for what you are trying to do.

HTH,
Ryan

GridView UPDATE Problem

Hi Gutys

I am having problem with my UPDATE in GridView, it is saying that I have too many argument, I don't
This happens when I click the UpdateCommand in GRIDVIEW

Procedure or function UpdateCountry has too many arguments specified.

<asp:SqlDataSource ID="mySqlDataSource" Runat="server"
SelectCommandType="StoredProcedure" SelectCommand="ShowCountry"
UpdateCommandType="StoredProcedure" UpdateCommand="UpdateCountry"
ConnectionString="<%$ ConnectionStrings:ConnString %>">
<UpdateParameters>
<asp:Parameter Type="String" Name="CountryName"></asp:Parameter>
<asp:Parameter Type="String" Name="CountryID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource
Just In case these are my Stored Procedures
**************************************************************************
CREATE PROCEDURE [dbo].[UpdateCountry]
@.CountryName varchar(50),
@.CountryID varchar(50)
AS
UPDATE EkeanyanwuO.tCountry SET [CountryName] = @.CountryName WHERE [CountryID] = @.CountryID
GO
************************************************************************************
CREATE PROCEDURE [dbo].[ShowCountry] AS
SELECT TOP 100 PERCENT EkeanyanwuO.tIMSREGION.ImsRegionName AS ImsRegionName, EkeanyanwuO.tCountry.CountryName AS CountryName,
EkeanyanwuO.tIMSREGION.ImsRegionID AS ImsRegionID, EkeanyanwuO.tCountry.CountryID AS CountryID
FROM EkeanyanwuO.tCountry INNER JOIN
EkeanyanwuO.tIMSREGION ON EkeanyanwuO.tCountry.ImsRegionID = EkeanyanwuO.tIMSREGION.ImsRegionID
ORDER BY EkeanyanwuO.tCountry.CountryName
GO
************************************************************************************

The ConflictDetection property of DataSource set to OverrideChanges. The problem will be solved. :D

Gridview refresh after update

Hi All,

I am new to development of asp. I have an SQLDataSource set as the data source for a grid view. When I click on the edit link in the Gridview, change the data, and click update, the old data is still displayed in the row.

I found exact same issue as here --http://forums.asp.net/thread/1217014.aspx

Solution in the above thread is to add this

{
if (reader != null) reader.Close();
}
conn.Close();

How do I apply above solution in my situation ?

I am updating through stored procedure.and don't have code at background. My code is

Datasource :

<

asp:SqlDataSourceID="ds"runat="server"ConnectionString="<%$ ConnectionStrings:ds %>"

CancelSelectOnNullParameter="False"ProviderName="<%$ ConnectionStrings:ds.ProviderName%>"UpdateCommand="usp_save"UpdateCommandType="StoredProcedure"EnableCaching="False">

<UpdateParameters>

<asp:ParameterName="field1"Type="String"/><asp:ParameterName="field2"Type="String"/><asp:ParameterName="field3"Type="String"/><asp:ParameterName="field4"Type="String"/><asp:ControlParameterName="field5"Type="String"ControlID="label7"/></UpdateParameters>

Anyone Please ?? Help me with this . I am still not able to find the solution.

Thanks in advacne

|||Where is your select statement?|||

Thanks for the reply .

I do select also using stored procedure. I can post stored procedure code if needed.

Here is the full sqldatasource and function

<asp:SqlDataSource
ID="idpl"
runat="server"
ConnectionString="<%$ ConnectionStrings:idpl %>"
SelectCommand="sp_Mapping"
SelectCommandType="StoredProcedure"
CancelSelectOnNullParameter="False"
ProviderName="<%$ ConnectionStrings:idpl.ProviderName%>"
UpdateCommand="sp_SaveMapping"
UpdateCommandType="StoredProcedure"
OnUpdating="pnl_Updating" EnableCaching="False">
<SelectParameters>
<asp:ControlParameter ControlID="Txt1" Name="OriginalID" Type="String" PropertyName="Text" DefaultValue="" />
<asp:ControlParameter ControlID="Txt2" Name="Name" Type="String" PropertyName="Text" DefaultValue="" />
<asp:ControlParameter ControlID="DDL" Name="sName" Type="String" DefaultValue="None" PropertyName="SelectedValue" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="OriginalID" Type="String" />
<asp:Parameter Name="sName" Type="String" />
<asp:Parameter Name="PartNum" Type="String" />
<asp:Parameter Name="plantName" Type="String"/>
<asp:ControlParameter Name="userID" Type="String" ControlID = "label7" />
</UpdateParameters>
</asp:SqlDataSource>

protected void pnl_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
DbParameterCollection CmdParams = e.Command.Parameters;
ParameterCollection UpdParams = ((SqlDataSourceView)sender).UpdateParameters;

Hashtable ht = new Hashtable();
foreach (Parameter UpdParam in UpdParams)
ht.Add(UpdParam.Name, true);

for (int i = 0; i < CmdParams.Count; i++)
{
if (!ht.Contains(CmdParams[i].ParameterName.Substring(1)))
CmdParams.Remove(CmdParams[i--]);
}

}

|||

Does the database values change?

If no, then the update isn't happening correctly, use the sql profiler to see what is being generated, and why it is failing to update correctly.

If yes, then in the sqldatasource's Updated event, add a gridview.databind and see if that resolves your problem. If it does not, place a breakpoint in the sqldatasource's Selecting event, and make sure that it is getting called after an update.

|||

Yes .. Database value changes.So stored procedure is definitely working.

I'll try to follow your suggestions on updated event and update the post soon.

Thanks for your help.

|||

Hi Motley,

I followed your suggestion.

1. Added gridview.databind at "updated" event.

2. Applied the breakpoint and made sure that the even is getting fired.

Still having the same issue. Gridview still shows two rows. Old and newly updated.

Any more pointers will be greatly appreciated.

Thanks

|||

Not sure if your problem is fixed or not. Sounds like your update statement has truncated to an insert statement. Have you looked into make sure it is pulling the PK of the table that singularly references the field you are looking for?

GridView based on SQLServerDataSource using a Select Union statement, impacts on Update an

I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):

SELECT
FirstName,
LastName
FROM
Master
UNION ALL
SELECT
FirstName,
LastName
FROM
Custom
ORDER BY
LastName,
FirstName

I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated!

Thanks,

Randy

SELECT
FirstName,
LastName,0 AS Editable
FROM
Master
UNION ALL
SELECT
FirstName,
LastName,1 AS Editable
FROM
Custom
ORDER BY
LastName,
FirstName

Only allow rows that editable is 1 to be edited, then use an update statement directly on custom for the rows that get editted.

|||

Thank you for the direction. I am unclear on your last sentence...can you provide a code snippet that illustrates what you are explaning?

I appreciate the help

Randy

|||

UPDATE Custom SETFirstName=@.FirstName,LastName=@.LastName WHEREFirstname=@.original_FirstName ANDLastName=@.original_LastName

|||

Thanks, the Update statement makes sense.

Last question: when you say "Only allow rows that editable is 1 to be edited", can you provide direction on what the code would be such that the EditTemplate never appears for Editable = 0? (i.e. so that Rows where Editable = 1 can go into edit mode but rows where Editable = 0 cannot).

Sorry for what may be basic questions...

|||

I really can't without knowing more about what it is you are trying to do, or how you've implemented your edit functionality.

Gridview / SqlDataSource error - Procedure or function <stored procedure name> has t

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

Post your Gridview and SQL Proceedure code.|||

SqlDataSource:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:TPCConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:TPCConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters>

<asp:ParameterName="ID"/>

</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>

Stored Procedure:

CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS

UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO

But also fails if I specify ID parameter in SqlDataSpurce--here is the error:

Procedure or function updExceptionList has too many arguments specified.

|||

SqlDataSource:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:xxxConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:xxxConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters>

<asp:ParameterName="ID"/>

</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>

Stored Procedure:

CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS

UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO

But also fails if I specify ID parameter in SqlDataSpurce--here is the error:

Procedure or function updExceptionList has too many arguments specified.

|||

Change your UpadteParametrs their Control Id's are wronge.

Are u using some DropDownlists inside a Gridview?

|||

In what way are they wrong? They are for control Gridview2.

I actually solved this problem by entering the entirety of the stored procedure in the SqlDataSource configuration, which is the most unideal solution I could make work. I do not want any sql at all in my application but it seems that I'm forced to put it there.

|||

I got the same error.

As it turned out, the ConflictDetection on my datasource was set to "CompareAllValues" which forces the datasource to supplies all the columns to my stored procdure. Hence, the error because the stored procedure only take one parameter.

My fix, was just change the ConflictDetection to "OverwriteChanges". Then it worked.

NOTE: I did NOT have to write any code to add new parameter or set the parameter's value for the delete command at all.

Regards,

Minh

|||

correction on the "NOTE".

I did have to add parameter and value for the stored procdure in the RowDeleting event.

But make sure you don't add the parameters in your designer.

protectedvoid GridView1_RowDeleting(object sender,GridViewDeleteEventArgs e)

{

foreach (DictionaryEntry entryin e.Keys)

{

this.SqlDataSource1.DeleteParameters.Add(entry.Key.ToString(), entry.Value.ToString());

}

}

|||

Minh,

Thank you for looking at the issue. I revisited this page and found that the ConflictDetection parameter was set to "OverwriteChanges" so that doesn't seem to be the issue. I am finding many other gridview / parameter problems, although I've had some better success since this post. I would like to understand why you had to delete all the parameters in code, does the designer not function properly? I'm not really interested in writing code for my next update which has like 20 parameters.

|||

Hi sestyd,

I've had a similar problem before, where the update command is sending more parameters than I have defined in the Sqldatasource. (Assuming you have this connected to a grid view), what it seems to be doing is sending any parameter that is defined in the grid view that is not specified as read only (as well as the data keys).

I pretty much just either made the parameters read only in the grid view (if that was viable) or defined the parameters in the stored procedure, then just ignored them.

BTW here is some code that I wrote that will display all of the parameters and their values in a label on the web page for an update function and stop the function from executing, this helped me work out what was going on.

[VB Code]

Protected Sub MyDataSource_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles MyDataSource.Updating lblTest.Text =""For iAs Integer = 0To e.Command.Parameters.Count - 1Step 1 lblTest.Text &= e.Command.Parameters.Item(i).ParameterName.ToString &" :: " & e.Command.Parameters.Item(i).Value &"<br>"Next e.Cancel =TrueEnd Sub

[/VB Code]

HTH

Grid view-cant update or delete

I put a grid view on a web form ,when I run it -the SELECT, EDIT works

the UPDATE,DELETE makes an error although I use the sama data,I added the error :

Anyone can help?

Server Error in '/CrystalReportsWebSite1' Application.

---

The data types text and nvarchar are incompatible in the equal to operator.

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: The data types text and nvarchar are incompatible in the equal to operator.

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): The data types text and nvarchar are incompatible in the equal to operator.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140

Hey,

What does those update/delete stored procedures look like? It seems like it may be an issue with the query.

|||I'm guessing he has a text field, and he told it to use optimistic concurrency or (CompareAllValues), which doesn't work with text fields.