I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc. So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this. For only ASC sorting, I've got
ORDER BY
CASE WHEN @sortBy='' THEN DateCreated END,
CASE WHEN @sortBy='DateCreated' THEN DateCreated END
and so on. However, columns can also be sorted with DESC. I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC. How can I do this?
I have a stored procedure in my SQL 2005 Server database named Foo that accepts two parameters, @paramA and @paramB.In my ASP.NET page, I have these:<asp:GridView id="gv" runat="server" AutoGenerateColumns="true" DataSourceID="DS" AllowSorting="true" DataKeyNames="ID"/><asp:SqlDataSource ID="DS" runat="server" ConnectionString="<%$ ConnectionStrings:CS1 %>" SelectCommand="Foo" SelectCommandType="StoredProcedure" OnSelecting="DS_Selecting"> <asp:Parameter Name="paramA" Type="String" /> <asp:Parameter Name="paramB" Type="String" /></asp:SqlDataSource>In my setup, paramA and paramB are set in DS_Selecting(), where I can access the Command.Parameters[] of DS.Now, here's the problem. As you can see, the GridView allows for sorting. When you click on a header title to sort, however, the GridView becomes empty. My question is, how can I get the GV sorted and in the correct direction (i.e. asc/desc)? My first step in my attempt was to add another parameter to the SqlDataSource and sotred procedure Foo (e.g. @SortByColumn), then changed Foo appropriately: ALTER PROCEDURE Foo @paramA nvarchar(64), @paramB nvarchar(64), @SortColumn nvarchar(16) = 'SearchCount' AS SELECT * FROM Searches ORDER BY CASE WHEN @SortColumn='SearchCount' THEN SearchCount WHEN @SortColumn='PartnerName' THEN PartnerName ELSE ID ENDThat works find and dandy. But wait--I want to get the correct ORDER BY direction too! So I add another parameter to the SqlDataSource and Foo (@SortDirection), then alter Foo: ... SELECT * From Searchces ORDER BY CASE /* Keep in mind that CASE short-circuits */ WHEN @SortColumn='SearchCount' AND @SortDirection='desc' SearchCount DESC WHEN @SortColumn='SearchCount' SearchCount WHEN @SortColumn='PartnerName' AND @SortDirection='desc' PartnerName DESC WHEN @SortColumn='PartnerName' PartnerName WHEN @SortColumn='ID' AND @SortDirection='desc' ID DESC ELSE ID END ...But including DESC or ASC after the column name to sort by causes SQL to error. What the heck can I do, besides convert all my stored procedures into in-line statements inside the ASP page, where I could then dynamically construct the appropriate SQL statement? I'm really at a loss on this one! Any help would be much appreciated!Am I missing a much simpler solution? I am making this too complicated?
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!
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. Â When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. Â The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. Â The sorting is implemented using the Category Group sorting option.
Hi, I use WVD and SQL Express 2005. I have a table “SignIn� that one of fields inserted automatically by getdate() And I have GridView that I use to display this table because I would like take advantage of GridView sorting and paging methods that are embedded in. Currently I display all records at once.
My problem is how to make the GridView show today’s records only. I tried this code below, but I get only this message “There are no data records to display.�
Hi I have a business search box and gridview pair. When the user enters a business name, the search results are displayed. I also generate a "more information" link which takes the user to a new page, passing the business name ("memberId")to this page (see the template field below). The problem I have is if the name contains a QUOTE (') or other special characters. The "memberId" is chopped off at the quote (e.g. "Harry's Store" is passed as "Harry"). Can anyone tell me a way around this please? Is there anything I can do with the Eval method? Kind regards, Garrett
HiI need to add in gridview control asp code "delete from t1 where id=@id1"how to declare @id1 because the server give me mistake down is the code of asp i use GridView how i can link @id with field there id???Thank u and have a nice daybest regardsthe code if u need it i use c# <ASP:SQLDATASOURCE id=SqlDataSource1 <br ConnectionString="<%$ ConnectionStrings:libraryConnectionString %>" runat="server"><BR></ASP:SQLDATASOURCE></ASP:BOUNDFIELD>
the usual way to bid a gridview is to data soursce is there a way to do the folowing , creat a data table from the gridview shown valus " currunt page "
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?
I tried doing a text box search within Gridview. My code are as follows. However, when I clicked on the search button, nothing shown. Any help would be appreciated. I'm using an ODBC connection to MySql database. Could it be due to the parameters not accepted in MySql?
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
SqlDataSource1.SelectCommand = "SELECT * FROM carrier_list WHERE carrierName LIKE '%' + @carrierName + '%'"
End Sub
Sub doSearch(ByVal Source As Object, ByVal E As EventArgs) GridViewCarrierList.DataSourceID = "SqlDataSource1" GridViewCarrierList.DataBind()
I m creating the project in asp.net using c# and vb languages in 2005.I have used the asp standard controls(with table<td><tr>) and gridview to design the form.I m using sqldatasource to insert and update data from sql server 2005.I have written the following code <script runat="server"> Private Sub Page_Load() If Not Request.Form("SUBMIT") Is Nothing Then srccompany.Insert() End If End Sub</script> <asp:SqlDataSource id="srccompany" SelectCommand="SELECT * FROM companymaster" InsertCommand="INSERT companymaster(companyname) VALUES (@companyname)" UpdateCommand="UPDATE companymaster SET companyname=@companyname WHERE companyid=1" DeleteCommand="DELETE companymaster WHERE companyname=@companyname" ConnectionString="<%$ ConnectionStrings:companymaster %>" Runat="server"></asp:SqlDataSource> <asp:GridView id="GridCompanyMaster" DataSourceID="srccompany" Runat="server" />Please help me to insert the data in sql server.i m not been able to insert the data is there any problem in coding..Also i m not been able to edit the data and store back to sql server.Only i can do is i can view the contents in gridview Please give me some tips
I'm trying to cache the contents of a gridivew unless another page, or sorting method are being called. I tried to use the VaryByParam method, but I'm not having any luck, I keep getting the same page sent back to me. Here's what my code looks like. <%@ OutputCache Duration="180" VaryByParam="Page$, Sort$" %> Any help would be appreciated. Stephen
I have a simple gridview displaying data from an MSSQL server 2005. Every now and then I get a sql timeout error. Listed below. Can anyone explain why I am getting this error? The connection pool is 100 and the timeout is set to 360. I have checked to current connections in SQL and they never max over 23. There are not locks in SQL when the problem occurs. The query is a stored procedure in sql and when sent sample data it normally takes about 5 seconds.
Event code: 3005 Event message: An unhandled exception has occurred. Event time: 12/3/2007 9:46:37 PM Event time (UTC): 12/4/2007 3:46:37 AM Event ID: 140501f9a7744dfea2e445ed00939e44 Event sequence: 42 Event occurrence: 1 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1/ROOT-1-128412128787656250 Trust level: Full Application Virtual Path: / Application Path: c:inetpubwwwroot Machine name: DD-MAIN Process information: Process ID: 5544 Process name: w3wp.exe Account name: NT AUTHORITYNETWORK SERVICE Exception information: Exception type: SqlException Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Request information: Request URL: http://localhost/Search_DG.aspx?SearchWord=1212 Request path: /Search_DG.aspx User host address: 10.10.10.1 User: Is authenticated: False Authentication Type: Thread account name: NT AUTHORITYNETWORK SERVICE Thread information: Thread ID: 1 Thread account name: NT AUTHORITYNETWORK SERVICE Is impersonating: False Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) at System.Web.UI.WebControls.DataBoundControl.PerformSelect() at System.Web.UI.WebControls.BaseDataBoundControl.DataBind() at System.Web.UI.WebControls.GridView.DataBind() at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() at System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() at System.Web.UI.Control.EnsureChildControls() at System.Web.UI.WebControls.GridView.get_Rows() at Install_DG.Page_Load(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Custom event details: For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Hey guys, Am in need of help please, basically the program im working with at the moment is when you add a New Contract, it will grab the Contract Number you have entered, Post it over to another page, then using that number bind a Gridview. The SQL being "SELECT Contract_ID, Contract_Number, Start_Date, End_Date, Quarterly_Rent FROM ECS_Contracts_Test WHERE Contract_Number = " & conNoVar (this being the Contract Number of the recently added Contract), however then it comes to Bind the Grid it kicks up an System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value '2009P7899' to a column of data type int.But the Column Contract_Number is set to Varchar and all I really want to do is to create this gridview using this criteria and not convert anything! The Contract_ID is an int, which is needed as I increment it. Heres the error code: Public Sub BindtheGrid()'Bind the Contract Grid, where ContractID is used Dim SQL As String = "Contract_ID, Contract_Number, Start_Date, End_Date, Quarterly_Rent" Dim objConn As SqlConnection = New SqlConnection(ConnectionString) Dim cmdstock As SqlCommand = New SqlCommand("SELECT " & SQL & " FROM ECS_Contracts_Test WHERE Contract_Number = " & contractQueryID, objConn) cmdstock.CommandType = CommandType.Text objConn.Open() GridView1.DataSource = cmdstock.ExecuteReader() GridView1.DataBind() objConn.Close() End Sub If you need any more information then please let me know. Mucho Aprreciated
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.??
Hello,How do I search through the gridview? Would I do this at the sqldatasourcelevel?I figured that I sould search with the datatable.select but how do I accessthe datatable of the sqldatasource?I am using ASP.NET 2.0 with VB.Thanks for your helpJ
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.
Sqldatasources are used for a dropdownlist and a gridview. How can the dropdownlist selection refresh the gridview? This is done programmatically in ASP.NET 1.1 code behind. Can it be done in ASP.NET 2.0 without code behind? Thanks.
First of all I like to say hello. Its my first post and I hope somebody will be so kind and help me. I have 3 tables in a database. They look like follow:
1. Table called State: ------------------------------ Id Caption
2. Table called Craft: ----------------------------- Id Caption
3. Table called Wage: ------------------------------- StateId CraftId Wage (decimal)
What I'm trying to do is, create a crosstable like the following and display it in a Gridview
Texas California Washington ... digging 25,60 31,42 ...
masonry works 40.66 ... ...
So, the columns come from table State, the row titles come from Craft and the values from Wage. After changing the values I want to save values back to Wage.
I started to build this crosstable but faced several problems. For example I dont know how to set a Caption on the left side of each row (Caption of table Craft). And even worse, how shall I update table Wage if there are no Id's in the crosstable anymore to make sure that I save the Value on the right place back in table Wage.
It would be very kind if somebody has a solution or a method of resolution.
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 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:SqlDataSource ID="ds" runat="server" ConnectionString="<%$ ConnectionStrings:ds %>"
I have successfully used SQL procedures to provide data to a Gridview previously, and I just can't see what's different about the simplified case that works and the real case that doesn't. I've extracted the code into a test page to avoid extraneous bumph. 1. I have developed a SQL procedure, GDB_P_Children, and tested this in SQL Server Management Studio Express. It returned exactly what it should. The procedure is: -SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[GDB_P_Children]@IndiiD uniqueidentifier, @Option int,@Owner Varchar(50),@User Varchar(50)AS Select * from GDBChildren(@Indiid, @Option, @Owner, @User)GO2. I then tried to use it in my program to power a Gridview, but the gridview was blank. I put the Gridview into a test page that only had code: -Option Compare TextPartial Class test Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Session("INDIid") = "ffe1fb2f-88ce-4b64-a358-e21efd161d70" Label2.Text = Session("INDIid") ' These are just for debugging Session("Owner") = "robertb" Label3.Text = Session("Owner") & "," Session("Option") = 0 Label4.Text = Session("Option") & "," Session("gdvChildUser") = "" Label5.Text = Session("gdvChildUser") & "," End SubEnd ClassI then regenerated the gridview from scratch: - Toolbox: drag a gridview on to the page Click "Configure Data sourec" => New Data Source => SQL Database. Name the datasource SQLChildren. Connection string - select as normal Configure: Specify a custom SQL statement or procedure Select Stored Procedure, select GDB_P_Children Set parameters to: - INDIid Session("INDIid") Option Session("Option") Owner Session("Owner") User Session("gdbChildUser")Test Query showed that the first parameter, INDIid, had type Object, and I know that this will fail (see http://forums.asp.net/thread/1390374.aspx), so I finished data source configuration and edit the source of the test page to remove Type="Object" from the parameter definition. The parameter now has type "Empty", and filling it in with the values ffe1fb2f-88ce-4b64-a358-e21efd161d70, 0, robertb, and '' returns data as expected. Click Finish and the Gridview configures itself with the correct column headings: -Exactly correct so far. Yet executing the page shows only my debugging labels, with no sign of the gridview.I changed the stored procedure: -set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO -- Temporary testing version, stripped to bare essentialsALTER PROCEDURE [dbo].[GDB_P_Children] @IndiiD uniqueidentifier AS Select INDIid, Indiusername, dbo.gdbindinamedate(INDIid) as IndiNameDate, Indisex from nzgdb_Indi where INDIMothersindiid = @INDIidData configuration was redone, then the page was displayed. It now correclty shows data from the database. So what's different?I changed the procedure back to the original, and again the blank (except for debugging labels) page was displayed.The data configuration was changed so that only the first parameter, @INDIid, was passed, and the other were set to default values. Still the blank page.Procedure GDB_P_Children was then changed to have only one parameter, with the other three declared and given values internally: -ALTER PROCEDURE [dbo].[GDB_P_Children] @IndiiD uniqueidentifierAS Declare @Option int Declare @Owner Varchar(50) Declare @User Varchar(50) Set @Option = 0 Set @Owner = 'robertb' set @User =''Select * from GDBChildren(@Indiid, @Option, @Owner, @User)GOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO The page is now OK, with the gridview showing data as expected.So why can't I use this procedure with the four arguments that it is supposed to have? This is driving me crazy! I have other procedures with 2 arguments (both Uniqueidentifier) that work fine, so it's not the fact that this procedure has more than one argument that's causing problems. Help!!!!Regards, Robert Barnes
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
I am trying to update a field in gridview. My update is not working, sort of. The original value is being updated with a NULL value when I click on the update button.here is my code <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" AutoGenerateEditButton="True" DataKeyNames="rqt_id">UpdateCommand="UPDATE [rqt_data] SET [rqt_title]=@rqt_title WHERE [rqt_id] = @rqt_id" > <asp:Parameter Name="rqt_title" Type="String" /> the field rqt_title is set up as a NVarChar(25) in the db. I can't specify that for the Type property. Could that be the issue?thx
Info: (vs 2005, sql server 2005, asp 2.0, C# project)I need to pass a variable from my web form to a stored procedure which should return a dataset to a gridview.My stored proc works fine but it’s not returning properly (or at all). Here is my stored proc:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[HrsUsed] -- Add the parameters for the stored procedure here @idUser INT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT DATENAME(mm, leaveDate) + ' ' + DATENAME(yyyy, leaveDate) AS 'Leave Date', totalV, totalS FROM tblLeave WHERE idUser = @idUser ORDER BY leaveDate DESC
RETURN
END Here is my code: try { SqlConnection cxnHrsUsed = new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString);
SqlCommand cmdHrsUsed = new SqlCommand("HrsUsed", cxnHrsUsed); cmdHrsUsed.CommandType = CommandType.StoredProcedure;
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!
