GridView via SQLDataSource and Stored Procedure

Help!

I am trying to fill my datagrid using the SQLDataSource, using a stored procedure.

The stored procedure expects a parameter which I can collect via the querystring, or a string. How can I pass the parameter through the SQLDatasSource?

My SQLDataSource is SQLData1. I already have:

SQLData1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
SQLData1.SelectCommand = "dbo.get_players"

Thanks in advance,

Karls

I think you need SQLData1.SelectParameters property, take a look at this article:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selectparameters(VS.80).aspx

|||Thanks,

I found it after I posted the question.

Cheers

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 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 delete function

This is killing me. I've searched the forums for hours and can't find the answer. My SQLDataSource is working fine except when I want to delete. I've allowed the delete function to be shown on the gridview. This is my SQLDataSource:

 <asp:SqlDataSource ID="IndexDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:IndexConnectionString%>" SelectCommand="SELECT * FROM [Index] WHERE (Type LIKE '%' + @.SearchText2 + '%') OR (Product LIKE '%' + @.SearchText2 + '%') OR (Version LIKE '%' + @.SearchText2 + '%') OR (Binder LIKE '%' + @.SearchText2 + '%') OR (Language LIKE '%' + @.SearchText2 + '%') OR (CDName LIKE '%' + @.SearchText2 + '%') OR (Details LIKE '%' + @.SearchText2 + '%') OR (ISOLink LIKE '%' + @.SearchText2 + '%')" DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.original_ID" UpdateCommand="UPDATE [Index] SET Type = @.Type, Product = @.Product , Version = @.Version, Binder = @.Binder, Language = @.Language, CDName = @.CDName, Details = @.Details, ISOLink = @.ISOLink WHERE ID = @.ID"> <SelectParameters> <asp:ControlParameter Name="SearchText2" Type="String" ControlID="SearchText2" PropertyName="Text" ConvertEmptyStringToNull="False" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Type" /> <asp:Parameter Name="Product" /> <asp:Parameter Name="Version" /> <asp:Parameter Name="Binder" /> <asp:Parameter Name="Language" /> <asp:Parameter Name="CDName" /> <asp:Parameter Name="Details" /> <asp:Parameter Name="ISOLink" /> </UpdateParameters> </asp:SqlDataSource>

It doesn't give me an error if I click delete but it doesn't delete the record. I've tried changing the DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> but it gives me the error "Must declare the scaler variable of '@.ID'"... I saw in this post http://forums.asp.net/p/1077738/1587043.aspx#1587043 that the answer was that "The variable you have declared in the definition of the proc isdifferent from the variable you are using in the WHERE clause." when they are both the same. Thanks for any help.

-Brandan

Hello

What if you add a semicolumn after @.original_ID ? like this"DELETE FROM [Index] WHERE [ID] = @.original_ID;"

|||

Is the ID column your table's primary key? If yes, you need to make sure that the ID is set in your GridView's DadaKeyNames and you should change your DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> and DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.ID"

If you cannot make this work, please post your GridView part here and if you can list all your columns' name instead of a * in your SELECT statement, that would be great. Thanks.

|||

If it doesn't fixes it it might just be the DataKeyNames field of the gridview that need to be set to ID .

|||

**RESOLVED**

It was definitely the DataKeyNames. I had recreated the gridview so many times I forgot to put it back in. thanks ya'll.

Gridview and DropDownList

Hello:

I have add a DropDownList to my GridView and binded the dropdownlist to a field from a select statement in the SQLDataSource. I have EnabledEditing for my GridView. The GridView is populated with information from the select statement. Some of the information returned from the select statement is null. The field where the dropdownlist is binded it is null in some cases and does not have a value that is in the dropdownlist so I get and error when I attempt to do an update.

'DropDownList1' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

Is there a way to get around this besides initializing all the columns in the table that are going to be binded to a dropdownlist to a value in the dropdownlist?

You can exclude the NULL from your SELECT statement by adding a where clause like: SELECT thefield FROm yourTable WHERE thefield IS NOT NULL. You don't need to worry about this NULL value for your test..

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

GridView - SqlDataSource

I have created a GridView that uses a SqlDataSource. When I run the page it does not pull back any data. However when I test the query in the SqlDataSource dialog box it pulls back data.

Here is my GridView and SqlDataSource:

<

asp:GridViewID="Results"runat="server"AllowPaging="True"AllowSorting="True"CellPadding="2"EmptyDataText="No records found."AutoGenerateColumns="False"Width="100%"CssClass="tableResults"PageSize="20"DataSourceID="SqlResults"><Columns><asp:BoundFieldDataField="DaCode"HeaderText="Sub-Station"SortExpression="DaCode"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="DpInfo"HeaderText="Delivery Point"SortExpression="DpInfo"><HeaderStyleHorizontalAlign="Left"CssClass="tdHeaderResults"/><ItemStyleCssClass="tdResults"/></asp:BoundField><asp:HyperLinkFieldDataNavigateUrlFields="CuCode,OrderID"DataNavigateUrlFormatString="TCCustDetail.asp?CuCode={0}&OrderID={1}"DataTextField="OrderID"HeaderText="Order No"SortExpression="OrderID"><ItemStyleCssClass="tdResults"HorizontalAlign="Center"/><HeaderStyleCssClass="tdHeaderResults"HorizontalAlign="Center"/></asp:HyperLinkField><asp:BoundFieldHeaderText="Order Date"SortExpression="OrderDate"DataField="OrderDate"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="ReqDeliveryDate"HeaderText="Req Delivery Date"SortExpression="ReqDeliveryDate"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="StatusDate"HeaderText="Status Date"SortExpression="StatusDate"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="ManifestNo"HeaderText="Manifest No"SortExpression="ManifestNo"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="CustomerPO"HeaderText="P.O. No"SortExpression="CustomerPO"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="Class"HeaderText="Class"SortExpression="Class"><ItemStyleHorizontalAlign="Left"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Left"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="OrderStatus"HeaderText="Order Status"SortExpression="StatusSort"><ItemStyleHorizontalAlign="Left"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Left"CssClass="tdHeaderResults"/></asp:BoundField></Columns><HeaderStyleForeColor="White"HorizontalAlign="Left"/><AlternatingRowStyleCssClass="tdResultsAltRowColor"/></asp:GridView><asp:SqlDataSourceID="SqlResults"runat="server"ConnectionString="<%$ ConnectionStrings:TransportationConnectionString %>"SelectCommand="GetOrderSummaryResults"SelectCommandType="StoredProcedure"><SelectParameters><asp:ParameterDefaultValue="10681"Name="CuCode"Type="String"/><asp:ParameterDefaultValue=""Name="DaCode"Type="String"/><asp:ParameterDefaultValue=""Name="DpCode"Type="String"/><asp:ParameterDefaultValue=""Name="OrderID"Type="String"/><asp:ParameterDefaultValue=""Name="ManifestNo"Type="String"/><asp:ParameterDefaultValue=""Name="PONo"Type="String"/></SelectParameters></asp:SqlDataSource>

I can get it to fill with data by manually filling the GridView without using a SqlDataSource but then I cannot get the sorting to work when I do it that way. Actually not sure if the sorting will work this way either as I cannot get it to fill with data. Any ideas would be much appreciated.

It doesn't appear as though your parameters are collecting any data in SqlDataSource. For instance, if you were storing your parameters in the querystring, you would have in your <asp:QueryParameter /> tags something such as QueryString="", or similar...