Problem Encountered With Filterparameter On SQLDataSource For GridView
Jan 9, 2007
Hi,
I have a GridView connected to a sqldatasource control. Everything is working great, updates, paging and filtering with one exception. When the user enters in a name like O'Reilly (with a single quote), the page errors. The error returned is:
Syntax error: Missing operand after 'Reilly' operator.
Here is the definition of the sqldatasource:
<asp:SqlDataSource ID="SqlDataSourcePersons" runat="server"
ConnectionString="<%$ ConnectionStrings:database %>"
SelectCommand="SELECT [Id], [FirstName], [LastName], , [PersonTypeId], [WorkerId], [_workerNTId], [Title], [City] FROM [Person]"
FilterExpression="(FirstName like '{0}%') AND (LastName like '{1}%') AND (WorkerId like '{2}%') AND (City like '{3}%')" ProviderName="System.Data.SqlClient">
<FilterParameters>
<asp:ControlParameter ControlID="TextBoxFirstName" Name="FirstName" DefaultValue="%" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxLastName" Name="LastName" DefaultValue="%" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxWorkerId" Name="WorkerId" DefaultValue="%" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxCity" Name="City" DefaultValue="%" PropertyName="Text" Type="String" />
</FilterParameters>
</asp:SqlDataSource>
i have a gridview which is bound to sqldatasource. i have a delete button in the gridview. I have written code for "deleting" a record in app_code directory. now iam not using "deletecommand" of sqldatasource but on the click of "delete" link i want to call the procedure in the app_code. Any ideas on how to do it.??
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:GridView ID="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:BoundField DataField="DaCode" HeaderText="Sub-Station" SortExpression="DaCode" > <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="DpInfo" HeaderText="Delivery Point" SortExpression="DpInfo" > <HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" /> <ItemStyle CssClass="tdResults" /> </asp:BoundField> <asp:HyperLinkField DataNavigateUrlFields="CuCode,OrderID" DataNavigateUrlFormatString="TCCustDetail.asp?CuCode={0}&OrderID={1}" DataTextField="OrderID" HeaderText="Order No" SortExpression="OrderID"> <ItemStyle CssClass="tdResults" HorizontalAlign="Center" /> <HeaderStyle CssClass="tdHeaderResults" HorizontalAlign="Center" /> </asp:HyperLinkField> <asp:BoundField HeaderText="Order Date" SortExpression="OrderDate" DataField="OrderDate" > <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="ReqDeliveryDate" HeaderText="Req Delivery Date" SortExpression="ReqDeliveryDate" > <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="StatusDate" HeaderText="Status Date" SortExpression="StatusDate"> <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="ManifestNo" HeaderText="Manifest No" SortExpression="ManifestNo"> <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="CustomerPO" HeaderText="P.O. No" SortExpression="CustomerPO"> <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="Class" HeaderText="Class" SortExpression="Class"> <ItemStyle HorizontalAlign="Left" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="OrderStatus" HeaderText="Order Status" SortExpression="StatusSort"> <ItemStyle HorizontalAlign="Left" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" /> </asp:BoundField> </Columns> <HeaderStyle ForeColor="White" HorizontalAlign="Left" /> <AlternatingRowStyle CssClass="tdResultsAltRowColor" /> </asp:GridView> <asp:SqlDataSource ID="SqlResults" runat="server" ConnectionString="<%$ ConnectionStrings:TransportationConnectionString %>" SelectCommand="GetOrderSummaryResults" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="10681" Name="CuCode" Type="String" /> <asp:Parameter DefaultValue="" Name="DaCode" Type="String" /> <asp:Parameter DefaultValue="" Name="DpCode" Type="String" /> <asp:Parameter DefaultValue="" Name="OrderID" Type="String" /> <asp:Parameter DefaultValue="" Name="ManifestNo" Type="String" /> <asp:Parameter DefaultValue="" 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.
Hi guys.Im using a gridview to show some training data on my website which is populated by:<asp:SqlDataSource ID="ARENATraining" runat="server" ConnectionString="<%$ ConnectionStrings:ARConnection %>" SelectCommand="SELECT [EventType], [CourseLink], [EventDate], [EventTitle], [EventLocation], [EventWebsite] FROM [qry_FutureEvents] WHERE ([EventPrivate] = @EventPrivate) ORDER BY [EventDate]"> <SelectParameters> <asp:Parameter DefaultValue="FALSE" Name="EventPrivate" Type="Boolean" /> </SelectParameters> So far so good..However I want to be able to filter the data.A) Show everythingB) Show individual EventType i.e. Seminar, Training etc etcIs it possible to generate the Select Command via a function? (im not using stored procedures for this part of the site, nor really want to at the moment). Id appreciate any help, otherwise ill be forced to curse and swear and use the good ol repeater and figureing out how to page it! Cheers guys
i am now writing a web page which can allow users to edit the field(display in the gridview->gridview source is from sqldatasource), there is a problem(it seems that i have followed all steps in the book): when the i edit the field and click update, in the web site, i can see those changes. however, i cant see any change in database table. i cant find solution on this, could anyone help me to solve this problem? thx!
Hello.When I create a user at the ASP.NET database, I need to insert more fields than the defaults, and I do it like this: Dim customProfile As ProfileCommon = ProfileCommon.Create(CreateUserWizard1.UserName, True) customProfile.telephone = (CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("TelephoneText"), TextBox)).Text(telephone example)Those data are inserted at the DB at the aspnet_Profile table, in the fields PropertyNames & PropertyValuesString, but they are saved together (see image above)
I want to separate those properties in the GridView as long as each property appears in a column, is it possible? Thank you very much, i'm expecting your answers.
hi .. i have a SqlDataSource, GridView & a Button .. i want to increase the condition in the "SelectCommand" below by one everytime i click the button .. i mean to be like that ID < 8 instead of ID < 7 so, the GridView will show 7 ID's instead of 6 .. and it will increase everytime i hit the button<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT * FROM [table1] WHERE ID < 7"> </asp:SqlDataSource>
What is the easiest way to obtain number of records in SQLDataSource (using select statement)/GridView. All that I've found in forums seems to be very difficult for such trivial task... Thank you!
Hi I have a gridview which is using a SQLdatasource, to update the table. One of my parameters on the SP is username. At the moment on page load I am declaring a session variable Session("UserName") = me.user.identy.name I then amend the update parameter in the SQL Datasource to accept the username from the session variable. - works fine, I was however wondering if there is a way of returning the username directly into the sqldatasource without having to pass it through a session variable?
Hi guys! I have a simple windows form where a gridview is being populated by a database. I have checked the ability to update, insert delete. And everything is fine but when i want to delete a row it throws an exeption. The exeptions says: "Must declare the scalar variable "@id"."I haven't changed the delete, update and insert commands. They are as fallowsDeleteCommand="DELETE FROM [recepti] WHERE [id] = @id"InsertCommand="INSERT INTO [recepti] ([ime], [recepta], [snimka], [snimka2], [tip]) VALUES (@ime, @recepta, @snimka, @snimka2, @tip)" SelectCommand="SELECT * FROM [recepti]"UpdateCommand="UPDATE [recepti] SET [ime] = @ime, [recepta] = @recepta, [snimka] = @snimka, [snimka2] = @snimka2, [tip] = @tip WHERE [id] = @id" The id column is auto generated, unique, also it's a primery key.So any idea where is the problem?
Hi,I have two SQLDataSources called "LeagueTableHome" an "LeagueTableAway" on my page. I want to create another SQLDataSource called "LeagueTableTotal" on my page which adds up all the totals from each of the other two sources. The datasource looks like this: Team, Pld, W, D, L, F, A, Agg, Pts my code for LeagueTableHome looks like this: SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL) I want then to show LeagueTableTotal in a GridView. Can anybody help?
I cant seen to change the Select command for a SQL Datasourcetry #1 SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters("ProfileID").DefaultValue = pProfileID SqlDataSourceProfilesThatMatch.SelectParameters("LoggedInUsersZipcode").DefaultValue = pUsersZipCode SqlDataSourceProfilesThatMatch.SelectParameters("ZipDistance").DefaultValue = pDistance NewProfilesThatMatchGridView.DataBind() try #2 SqlDataSourceProfilesToBeMatched.SelectParameters.Clear() SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters.Add("ProfileID", pProfileID) SqlDataSourceProfilesThatMatch.SelectParameters.Add("LoggedInUsersZipcode", pUsersZipCode) SqlDataSourceProfilesThatMatch.SelectParameters.Add("ZipDistance", pDistance) NewProfilesThatMatchGridView.DataBind() No errors but no rows show in the gridview. If I debug and get the value strSQLForSearch and paste it into a new SQL query window I get results. Any ideas???? Thanks
Hello I'm experiencing some problems, binding a SqlDataSource to a GridView. The following code creates the SqlDataSource: string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; SqlDataSource ds = new SqlDataSource(strProvider, strConn); ds.SelectCommand = "SELECT * FROM rammekategori"; Then i bind the SqlDataSource to a GridView: GridView1.DataSource = ds; GridView1.DataBind();
ErrorMessage: Format of the initialization string does not conform to specification starting at index 0. Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.Line 24: GridView1.DataBind();Am i totally off target here? Can it be something about, that you have to set the datasource of the gridview, before the Page_Load event?
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.StoredProcedureSQLData1.SelectCommand = "dbo.get_players"Thanks in advance,Karls
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 have a SqlDataSource that I need to remove the first 3 rows from before it is bound to a GridView. How would I go about doing this? (if I could remove them at the db level in the sproc I would, but right now that is not an option - so I need to do it once I've already received the data) Thanks.
Hi, say I have two Sqldatasources objects:SqlDataSource1 and SqlDataSource2.... Does anybody know how can I alter programmatically these two sqldatasources in a gridview? Thanks!!!
Hi all, first post, and I am desperate. I have a SqlDataSource with a Select, Update and Delete command. From what I understand, scalar variables should be read automatically from the GridView's BoundField columns when it executes a command on it. Here is my code:
I apologize for the way the code is put in, the code thing cut off a lot of the text! The problem I'm getting is, when the 'Update' button is hit, I get the error: Must declare the variable '@TeamId'.
I've tried putting "Update Parameters", that takes away the error, but the row does not update. I've browsed the internet and saw the same problems in lots of areas, but either a) none of the solutions work for me, or b) they don't really apply to my case. I'm using ASP .NET 2.0, and (obviously) C#. SQL Server database. Any help is greatly appreciated. Thanks in advance,
hello all 1) I was using SQLdataSource and bind it with the gridviw to show its data. so that is how is running when the page load, i see the data is posted on the page on the gridview. But what i need to do when I want a user to click a button and then the Gridview show the data.
As i was creating a SQLDatasource, it was click click and click but I want to put that sqldatasouce to load the data in gridview by a click event.
2) my second question is also related to the first one or just similar. I dont want to get all the data rather filter it. so once the page load, there is a button and a text box. In the text box user going to type the EmployeeID and click the "Employee Info" button and then the data would show up. Rather i use te DetaildView Control . What should I do to load the information on the Text boxes rather then on the GridView since it is going to be a single recrod. Thanks all
i am using 2 textbox to search name and instituition of some students. on button click need to display the result on gridview. but i dont get any result.. pl adviceSqlDataSource1.SelectCommand = "SELECT Sname, Address, Instituition, Role, Testgroup, Email FROM std_det WHERE (Sname LIKE '%'+@Param1+'%') AND (Instituition = 'RASET') AND (Role LIKE '%'+@Param2+'%') AND (Instituition = 'RASET')"; name.ControlID = "TextBox3"; name.DefaultValue = "%"; name.Name = "Param1"; name.PropertyName = "Text"; inst.ControlID = "TextBox4"; inst.DefaultValue = "%"; inst.Name = "Param2"; inst.PropertyName = "Text"; //SqlDataSource1.Select(DataSourceSelectArguments.Empty); SqlDataSource1.SelectParameters.Add(name); SqlDataSource1.SelectParameters.Add(inst); SqlDataSource1.DataBind(); //DataView dv = (DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty); GridView2.DataSourceID = "SqlDataSource1"; GridView2.DataBind();
I have a gridview that is tied to a SQLDataSource control which is inside an update panel. What I'm trying to accomplish is when the page is loaded the gridview is filled from the SQLDataSource. I have some textboxes also on the page so that the user can add new items to the gridview. The update panel trigger is tied to my code behind page through a button click which added the new data to the DB with an INSERT statement. Since the gridview was inside the update panel I thought when I placed the new data into the DB and the update panel caused the portion of the page that holds the gridview to refresh that the gridview would show the newly added data but it is not. I am missing something but I do not know what. I tried adding a gridview1.databind() and SQLDataSource1.DataBind() but niether caused the update of the gridview as I had hoped. Thanks for any of your help. Ty
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
There are so many ways to use database in asp.net/ado.net, I'm a bit confused about their difference from the performance point of view.So apparently SqlDataSource in DataReader mode is faster than DataSet mode, at a cost of losing some bolt-on builtin functions.What about SqlDataSource in DataReader mode vs manual binding in code? Say creating a SqlDataSource ds1 and set "DataSourceID" in Gridview, vs manually creating the SqlConnection, SqlCommand, SqlDataReader objects and mannually bind the myReader object to the gridview with the Bind() method.Also Gridview is a very convenient control for many basic tasks. But for more complex scenarios it requires lots of customization and modification. Now if I do not use gridview at all and build the entire thing from scratch with basic web controls such as table and label controls, and mannually read and display everything from a DataReader object, how's the performance would be like compared to the Gridview-databind route?
<!-- Trying to create a GridView Survey form: Hi [User] Do you have these assets? [Gridview] Tag Number Response 123 [Yes ] [No] [Comments textbox] 234 [Yes ] [No] [Comments textbox]
Goal: The desire is for the user to click Yes or No and have the database updated with the user's name, date, response and any comments.
So far, I have created SqlDataSource with Select and Update commands, created the gridview and response buttons, setup the RowCommand and On_Updating Functions.
Problems:1) If I call the update() function for SqlDataSource1 and misuse the Defaultvalue parameters to run the sql, no update is posted to the database.
2) If I use the On_Update function to set the parameter values, I get "Data type mismatch in criteria expression." (Additionally, the On_Update function runs twice which I don't understand)Can anyone tell me what/why? (and how to fix it?)Sorry for the deluge, but here is the code: -->
<%@ Page Language="VB" %> <html> <head id="Head1" runat="server"> <title>Asset Survey</title> </head> <SCRIPT runat="server"> Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting e.Command.Parameters("Name1").Value = User.Identity.Name End Sub
Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating 'For some reason, this function executes twice '2nd execution gets error: "Data type mismatch in criteria expression." 'e.Command.Parameters("PrimaryKey1").Value = intPrimaryKey 'e.Command.Parameters("Responder1").Value = strUser 'e.Command.Parameters("ResponseDate1").Value = dtModDate 'e.Command.Parameters("Response1").Value = strResponse 'e.Command.Parameters("ResponseComments1").Value = strComments End Sub
Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) _ Handles GridView1.RowCommand Dim GridRow1 As Integer = Convert.ToInt32(e.CommandArgument) If e.CommandName = "UpdateYes" Then 'code here to disable buttons and textbox '...
SqlDataSource1.UpdateParameters("Response1").DefaultValue = "Yes" End If
If e.CommandName = "UpdateNo" Then 'code here to disable buttons and textbox '...
SqlDataSource1.UpdateParameters("Response1").DefaultValue = "No" End If
'if I use the SqlDataSource1_Updating function, I get the mismatch error 'but if I (mis)use the DefaultValue parameter, no update occurs. SqlDataSource1.UpdateParameters("ResponseComments1").DefaultValue = _
SqlDataSource1.Update() 'Error Here if I use on_updating: "Data type mismatch in criteria expression." Catch except As Exception ' Handle the Exception. End Try End Sub
</SCRIPT> <body> <form id="formInv" runat="server"> <div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LocalTestMDB %>" ProviderName="<%$ ConnectionStrings:LocalTestMDB.ProviderName %>" SelectCommand="SELECT [PrimaryKey], [Name], [AssetTag], [Response], [Responder], [ResponseDate],[ResponseComments] FROM [Tablename] WHERE ([Name] = ?) ORDER BY [Login Name], [AssetTag]" UpdateCommand="Update [Tablename] SET [Response]=@Response1, [Responder]=@Responder1, [ResponseDate]=ResponseDate1, [ResponseComments]=ResponseComments1 WHERE [PrimaryKey]=@PrimaryKey1" OnSelecting="SqlDataSource1_Selecting" OnUpdating="SqlDataSource1_Updating"> <SelectParameters>
Hi, I think the title explains it all really. I cannot configure the SqlDataSource to add Insert, Update and Delete statements - not through the wizard anyway. The view combines two tables, each containing 3 columns. I have tried using different combinations of columns, and ended up selecting all of them (making sure the foreign key column of the first table was given a different alias to the primary key column of the second table) but I still cannot enable editing. Cheers, Patrick
Using 3.5 If I have a sqldatasource on the page, is it run if it is not bound to a data object like a gridview? Seems like if i want to access the data (like set a label text) from the sqldatasource I have to use code to first create a dataview then pick throught it. This seems like I'm running it twice. I'm new at .net so I dont know how to tell. I don't want to write data select code programatically when I can just through an SDS on the page, but wondered it it ran just because it's on the page.
Hi, I got a page with 3 sqldatasources and 2 dropdownlists and 1 gridview. 2 sqldatasources may load when the page load adn fill in the 2 DDL's. But 1 sqldatasource and the gridview may only be loaded when I press a button. This is because otherwise every time I load the page more then 5000 records. (the 2 DDL's are filters for the 3th sqldatasource) Anyone an idea how to solve this problem? Best regards, Karel
Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005): <asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False" ReadOnly="True" SortExpression="ProductID" /> <asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" /> <asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" /> <asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" /> <asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True" SortExpression="loadid" /> <asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" /> <asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" /> <asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" /> </Columns> </asp:GridView> and the sqldatasource's info: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" > <UpdateParameters> <asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter> <asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter> <asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter> <asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter> <asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>
Hi all, I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:
As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work). So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event. What is the best approach? Anyone come up against this issue before? Thanks, KB
I have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :) I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query. (tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server"> 2 <asp:ListItem Value="0">Open</asp:ListItem> 3 <asp:ListItem Value="1">Completed</asp:ListItem> 4 </asp:DropDownList> 5 6 7 <asp:DropDownList ID="drpdwnCompany" runat="server"> 8 <asp:ListItem Value="">All</asp:ListItem> 9 <asp:ListItem Value="cur">Cur District</asp:ListItem> 10 <asp:ListItem Value="jho">Jho District</asp:ListItem> 11 <asp:ListItem Value="sea">Sea District</asp:ListItem> 12 <asp:ListItem Value="san">Net District</asp:ListItem> 13 <asp:ListItem Value="sr">Research District</asp:ListItem> 14 </asp:DropDownList> 15 16 17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>" 18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')"> 19 <SelectParameters> 20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" /> 21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" /> 22 </SelectParameters> 23 </asp:SqlDataSource> 24
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.
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 all... I've encountered the following error in NT Event Viewer - Application related to SQL Server.
"The description for Event ID ( 263 ) in Source ( CIS ) could not be found. It contains the following insertion string(s): ERROR Communication link failure for CAudit!." & "The description for Event ID ( 263 ) in Source ( CIS ) could not be found. It contains the following insertion string(s): Error Communication link failure in ApplicationLogon."
There are around 30-40 messages (same) in the Event Viewer & I couldn't find the cause of the problem.
& I also have this error messages in SQL Server error log (different server)
"Unable to write to ListenOn connection . pipesqlquery' loginname 'sa'. hostname 'testing'... followed by "OS Error: 232, The pipe is being closed". & this error occurs everyday & few times a day!
Not sure whether the problem is purely SQL Server or got to do with the network/NT. Can't get any help from our network/NT support until I can prove to them that it is related to network/NT!
