.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Dynamically fill query parameters in a table adapter ?

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :ASP.Net
SO I have created an object data source on a page from a table adapter using the various Wizards in Visual Studio.Its meant to query a diary Database by a DateTime parameter called diaryDate. I want to query from a date Now to the next 14 days ahead.I want to display the entries in a Grid View when the page loads.So I'm a bit stuck now as to how to set the two query parameters date1 and date2. I want to set date1 = now() and date2 = now() + 14 days.How do I set those parameters when the page loads ?Should I have used the adapter Wizard approach of do I have to write all the code in the code behind page ??This is my datasource code'<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GET_nextTwoWeeks" TypeName="query_getNextTwoWeeksTableAdapters.diaryDatesTableAdapter"> <SelectParameters> <asp:FormParameter DefaultValue="" FormField="date1" Name="date1" Type="DateTime" /> <asp:FormParameter DefaultValue="" FormField="date2" Name="date2" Type="DateTime" /> </SelectParameters> <InsertParameters>

View Complete Post

More Related Resource Links

Serialising Data Table returned from Table Adapter Fill method


I am having issues when attempting to serialise a datatable returned from a TableAdapter fill method created via the data set designer.

I have a server executable (which contains the dataset), a client executable and interface referenced by both for proxy purposes.

I guess this is to do with the designer's datatable actually deriving from a datatable (where it adds custom properties and events specific for the data retrieved by the tableadapter).  So I have temporarily made an ugly method of creating a standard new datatable and populating it with column and row data via enumerating the table from the adapter for its column and row data, but I don't want to have to do that!

Standard copy methods on the adapter's table does not work.  Whenever I try to get a datatable via remoting and it references the adapter's table, it returns a serializationerror syaing my server assembly cannot be found (well, only the server knows about these derived datatables so that s understandable).  I don't want to move the dataset into the commonly referenced assembly as that sounds like overkill and probably not good practice?


How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

FormView -- Parameters and asp:Table


I've already solved this problem to my satisfaction, I just want to know WHY it works this way.

I have a FormView control bound to an ObjectDataSource.  Let's say our data source is a hypothetical table with three columns:  id, name, age.  Why is it that when I use the <asp:Table> element inside of the FormView, the parameter values are not set, but when I use the standard <table> tag, it works fine?

This seems like a .NET problem to me.  I expect the <asp:Table> element to work in the same way as <table> because they are, for all intents and purposes, the same thing.  Am I missing something?

SqlDataSource and Parameters query


Hi everyone,

I'm on the edge wondering is it possible ? I have got 2 questions. Please help me out.

1. I have a GridView on my page and it uses sqldatasource with parameterized query. What I want to do is, on page load (where nothing has been selected so no parameter supplied), I want it to query everything (something like SELECT * FROM [this_table]) but since my SelectCommand is something like

SELECT * FROM [this_table] WHERE [this_column] = @someParameters AND [that_column] = @someParameters.

Can I play around with default value to achieve something like that but how ? Now, when the page loads, it doesn't show anything (No Gridview).

2. On my page, I made something like (username, gender, address, and more) and one single search button. That means, no single control enable auto postback. What I am trying to accomplish is building dynamic query

(if username specifed -> SELECT * FROM [this_table] WHERE [username] LIKE @username).

If both username and gender are specified (SELECT * FROM [this_table] WHERE [username] LIKE @username AND [gender] = @gender) and you know the rest. How can I do this using GridView and SqlDataSource ? To my knowledge, I can only specify one SELECT statement in a sqldatasource.  I am stucked and desparately looking for help. Thanks all.

lookup table query


we are doing our cms migration. both cms's have their own articleID/contentID, we have a lookup table that contain the article URL and its content id.  Now we want to query that table on every single click to get its respective id. 

How and where i can implement this?

Want to Create Table Dynamically With text Control in WinForm Csharp

Hello To All... from Many Days i m tryng to create a simple table using csharp in WinForm.Which will include textbox in each cell according the Passed No of Col & Row. but not getting the proper result. Actually i want to trace each cell data and want to save each cell data to data base : eg. in cell 11(1col & 1row) i m having Combobox which having some item list and when the user select the list item the data will save to the database. Regards, Praveen    What is the Key of Life ... Its Sharing Your Thought & Knowledge ...

Create Table from MDX query

Hi,I want create a table from the result of the MDX query. When I finish the wizard of import data in SQL Server Environment. at the last stage, the error is : Pre-execute (Error) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - Query" (31)" wrote 0 rows.(SQL Server Import and Export Wizard) please guid  me.Thank you a lot.Naeimeh

Application to Constantly query Table Values and Refresh with most recent value

Hello, I must apologize in advance because I am not sure if this is the correct place for this question. I am looking for ideas on how to build an application that in the most basic sense contains a field with the most current data from a table in a SQL Server database (this would be based off of a datetime timestamp in the table). I am new to this kind of problem and am unsure where to start looking for solution ideas. The table value updates variably and could possibly update every second to every hour. I would like to design an application that would be lightweight to both the server and the user's machine. This would take the place of an unneeded report on my Reporting Services server. Any and all suggestions would be welcome because I am not sure of options I even have on this. Thanks in advance for your time and consideration, Matt

Transact-sql - tricky query to get a list of people from a table where i belong too.

I've no idea how to manage and optimize this query: I've four table. - User (userId, name) - Member (memberId, name, ownerId) The ownerId = user.userId - PresentPeople (activityId, presentId, ...) so presentId = memberId - Activity (activityId, ownerId, startDate...) and ownerId = user.userId So here for an user, I need members created by this user, and for this members I need the list of presentPeople for the activity that theyjoined. But I don't know how to get this list because they are in the list. Not sure if i'm clear there, Big help would be very helpfull :)) thanks

Form Action Button to pass parameters through URL and how to fill a NewForm with them

Hi I'm trying to solve a bigger problem we have in babysteps, and I guess after days of searching and trying I simply need to ask for help. The big picture is, that we're trying to put a Form Action Button (via Sharepoint Designer 2007) above a custom DataForm DispForm.aspx which is supposed to navigate to a NewForm.aspx where the Title Field is supposed to be prefilled with a parameter from the DispForm and the user should be able to add information to the item before it is saved. I found several explanations from people on the internet, but nothing really worked. The first babystep would be to get the parameter through the button into the url of the new item.   < input type = "button" value = "Zugriff Beantragen" name = "btnFormAction0" onclick = "javascript: {ddwrt:GenFireServerEvent('__redirect={../Zugriff Beantragen 2/NewForm2.aspx?test={ItemID}')}" />   That's the code for my button. Somebody wrote that should definitely work because SharePoint is able to replace {ItemID} with the actual ID of the item. Well.... it kind of doesn't. What I get as the end of the new URL is exactly "...?test={ItemID}"   I tried with different names for the ID, I added an @ before those ... no change. I read somewhere about javascript.functionName() which should work with all parameters of the item, but

sql server query get a list of a table

Hi guys, I've a hard query to manage ... I need to get for a report the number of required people to an appointment, but this query works but just for the regarding person related to the appointment. SELECT CRMAF_FilteredContact.OwnerID ,CRMAF_FilteredContact.OwnerIDname ,SU.businessunitidname ,SU.eu_reporthubname -- ,SU.eu_reportcountryidname ,CRMAF_FilteredContact.ContactID ,CRMAF_FilteredContact.FullName ,CRMAF_FilteredContact.invoke_tiername ,CRMAF_FilteredAppointment.ActivityID ,CRMAF_FilteredAppointment.RegardingObjectID ,CRMAF_FilteredAppointment.Subject ,AP.ActivityPartyID ,AP.PartyID ,case when CRMAF_FilteredAppointment.ActivityID is null then null else CRMAF_FilteredContact.ContactID end as s_contactsvisited -- # of contactsvisited FROM FilteredContact CRMAF_FilteredContact JOIN FilteredSystemUser SU ON CRMAF_FilteredContact.ownerid = SU.SystemUserID <strong> LEFT JOIN FilteredActivityParty AP ON ( AP.PartyID = CRMAF_FilteredContact.ContactID or AP.PartyID = CRMAF_FilteredContact.OriginatingLeadId) AND AP.participationtypemask = '8' -- regarding</strong> LEFT JOIN FilteredAppointment CRMAF_FilteredAppointment ON AP.ActivityID = CRMAF_FilteredAppointment.ActivityID AND CRMAF_FilteredAppointment.ownerid = SU.SystemUserID AND CRMAF_FilteredAppointment.stat

T-SQL 2005 table query

Hi, I have SQL 2005 table like below one declare @OrderTable TABLE (OrderID varchar(10),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',455.85,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',866.45,'2010-01-25') INSERT INTO @OrderTable VALUES('P06',749.61,'2010-01-28') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-03-23') Final result I am looking for is SET NULL value in SELECT query Whenever Same OrderID and OrderDate is found... OrderID  OrderValue   OrderDate P06         25.22   2010-01-24 P06        NULL             2010-01-24 P06      NULL             2010-01-24 P06        NULL             2010-01-24 P06      NULL              2010-01-24 P06 &

SQL 2010 SP1 does not fill in Stored Procedure Parameters

I am executing a Stored Procedure in Server Management Studio.  The Procedure gives the expected results but it does not fill in the parameters?  I have stop and restarted the server to no avail. Please help? Thanks. STORED PROCEDURE USE   [char68003f] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectDonorXiD] ( @DonorID varchar(50) , @ContactTitle varchar(7)=null OUTPUT, @ContactFirstName varchar(40)=null OUTPUT, @ContactLastName varchar(40)=null OUTPUT, @ContactAddress1 varchar(40)=null OUTPUT, @ContactAddress2 varchar(40)=null OUTPUT, @ContactCity varchar(30)=null OUTPUT, @ContactStateCode varchar(2)=null OUTPUT, @ContactZipCode varchar(10)=null OUTPUT, @ContactPhone varchar(30)=null OUTPUT, @ContactEmail varchar(80)=null OUTPUT, @BillingTitle varchar(7)=null OUTPUT, @BillingFirstName varchar(40)=null OUTPUT, @BillingLastName varchar(40)=null OUTPUT, @BillingAddress1 varchar(40)=null OUTPUT, @BillingAddress2 varchar(40)=null OUTPUT, @BillingCity varchar(30)=null OUTPUT, @BillingStateCode varchar(2)=null OUTPUT, @BillingZipCode varchar(10)=null OUTPUT, @BillingPhone varchar(30)=null OUTPUT, @BillingEmail varchar(80)=null OUTPUT ) AS Select ContactTitle, ContactFirstName, ContactLastName, ContactAddress1, ContactAddress2, ContactCity, ContactStateCode, ContactZipCode, ContactPhone, ContactEmail, BillingTitle, Bil

Tricky SELECT query from a Single Table

Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks

Table Adapter generates bad SQL under load

I have an application (ASP.NET 2.0/SQL Server 2005) which makes heavy use of table adapters for pulling records from SQL. Under heavy load, we get a lot of SQL Server Timeout errors. We have run a trace on SQL Server and it shows that several of the SQL statements being passed into SQL Server, from the Table Adapters, have bad SQL. For example, here is the SQL in one of the table adapters SELECT    HomeMsgID, messageName, messageHTML, messageText, populationID FROM        MyUCR_HomeMessages WHERE    (populationID IN                           (SELECT    populationID                             FROM          MyUCR_Population_CPID AS MyUCR_Population_CPID_1                             WHERE      (CPID = @CPID))) AND (isVisible = 1) AND (showDate <= @showDate) AND (removeDate >= @removeDate) I call it with the following: DateTime showDate = DateTime.Today; DateTime removeDate = DateTime.Today; myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter ta = new myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter(); myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable dt = new myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable(); ta.FillByCPID(dt, showDate, removeDate, CPID); What the SQL trace shows, when it fails, is this (notice the extra single quotes around the showDate, removeDate parameters): E000 exec sp_executesql N'SELECT    HomeMsgID, messageN

application role how query a view whose reference table is in another db?

Hi everybody. I created  an application role in a database (DB1) and gave it all the rights on a view  in DB1 which refers to a table located in another db (DB2).  I also gave the rights to the app role on a table of  DB1 I tried to use this app. role through the sp_setapprole launched by a user  (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:   The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated.   Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania

Analysis Service Oracle Number inconsistent Data Type for TABLE or Named Query

Dear Gurus, I'd VERY OLD PROBLEM. And I believe it addressed since 2006. When I design DataSource Views from Oracle Data Source. I found it return different oracle number data type for TABLE or NAMED QUERY   Provider Data Type Column Data Type Data Source View Data Type Oracle OLE DB Provider (OraOLEDB.Oracle.1) Table Number System.Int64   View Number System.Decimal   Named Querey Number System.Decimal Microsoft OLE DB Provider for Oracle (MSDAORA.1) Table Number System.Double   View Number System.Double   Named Query   1 System.Int64   Named Query   1.1234 System.Int64 Althought I know I can fix IT via MANUALLY EDIT DATASOURCE VIEW XML SOURCE. But I don't think this is a better solution. Is anybody have ideas ?  Wilson
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend