Programatically Change Sqldatasource Select Statement

Oct 28, 2007

Hi Everyone,

I am trying to change the select statement of an sqldatasource if a check box is checked.

I am using the SqlDataSourceSelectingEventArgs but i can't get it to work, anyone got any pointers?

Code BehindProtected Sub LocMan_Searching(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs) Handles LocManSearch.Selecting

If cb_Today.Checked = True ThenLocManSearch.SelectCommand = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() & "+ '%') AND [available] LIKE '%' + " & Date.Today & "+ '%')"

 

Else : LocManSearch.SelectCommand = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() & " + '%')"

End If

End Sub

My SQLDATSOURCE

<asp:SqlDataSource ID="LocManSearch" runat="server" ConnectionString="<%$ ConnectionStrings:MYLOCDEVConnectionString %>" >

<SelectParameters>

<asp:ControlParameter ControlID="dd_Area" Name="area" PropertyName="SelectedValue"

Type="String" />

</SelectParameters>

</asp:SqlDataSource>

 

Thanks in advance

Chris

View 6 Replies


ADVERTISEMENT

Change SqlDataSource Programatically

Jun 10, 2008

I have a list box populated by a SqlDataSource with a basic statement of Select name where location = @location.       Location is bound to a dropdownlist control.  I want to have a checkbox for the Location dropdownlist and a checkbox for another dropdownlist (for the alphabet) that will select only those people with a last name of the letter selected.  Changing the checkbox would change the sql statement. 
OK so there are two questions here:
1.  How can I write a sql statement that would select only people whos last name begins with a certain letter.  Would it use some sort of convert function to a string with only 1 letter?
2.  How can I programatically change the sqlDataSource's 'select' statement.  I need to switch between sorting by location and sorting by alphabetical last name.  All of my previous experience has had me configuring the datasource in the asp.net code. 
 
Thanks in Advance. 

View 7 Replies View Related

Sqldatasource Select Statement

Mar 27, 2007

Hi all,I drag sqldatasource to my form, and then adding a button there. I want when clicking the button to be able to use the sqldatasource1.select statement . I found some parameters that this method used but still dont know how to figure it out, which was IEnumerable Select (DataSourceSelectArguments a)for example when the button it clicked I want to perform the select * from employee Thanks

View 1 Replies View Related

SqlDataSource - Executing Select Statement

May 16, 2006

I have multiple SqlDataSources on a page.  I only want them to execute the select query when the user performs a certain action.  Is this possible?  Or do they all execute as soon as the page is loaded?

View 1 Replies View Related

Return Select Statement Or Values Using SqlDataSource?

Sep 21, 2007

Hello all,
 I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be?
 I have a SqlDataSource"
 <asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)"
UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Action" Type="Byte" DefaultValue="2" />
</UpdateParameters>
</asp:SqlDataSource>
 And my SP:/* 09-19-07 Used to update affiliate profile */

CREATE PROCEDURE spUpsertAffiliateProfile
@Action tinyint,
@AffiliateId int,
@MemberId int = -1,
@CategoryId int,
@WebAddress varchar(50),
@Email varchar(50),
@Comments varchar(1500)
AS

SET NOCOUNT ON

-- Find errors first, check is not needed if deleting
IF @Action <> 3
IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%')
BEGIN
SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END
IF @Action = 1
IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId)
BEGIN
SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END


IF @Action = 1 AND @AffiliateId = 0-- insert
BEGIN
INSERT INTO tAffiliateMaster
(member_id, category_id, profile_web_address, profile_email_1, comments)
VALUES
(@MemberId, @CategoryId, @WebAddress, @Email, @Comments)

SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id
RETURN
END

ELSE IF @Action = 2 AND @AffiliateId > 0-- update
BEGIN
UPDATE
tAffiliateMaster

SET
category_id= @CategoryId,
profile_web_address=@WebAddress,
profile_email_1=@Email,
comments=@Comments

WHERE
affiliate_id = @AffiliateId AND member_id = @MemberId

SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id
RETURN
END

ELSE IF @Action = 3 AND @AffiliateId > 0-- delete
BEGIN
DELETE
tAffiliateMaster

WHERE
affiliate_id = @AffiliateId

SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id
RETURN
END
GO

 My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event?
 Thanks!
 

View 3 Replies View Related

Dynamically Assign Select Statement To SqlDataSource

Jan 31, 2008

I wish to dynamically assign Select Statement to SqlDataSource. Anyone has any idea?? Is it possiable at all?
I am coding using C# under Framwork 2.0

View 1 Replies View Related

Dynamically Assign Select Statement To SqlDataSource

Jun 16, 2008

How can i assign select statement to a sqldatasource
I mean if the user clicks button 1
sqldatasource=-"select * from customers where status='ClOSED'  "
or else if the user clicks button2
 sqldatasource="Select * from Customers Where Status='OPEN'  "
 I want to bind the sqldatasource to a gridview finally .
 Is this possible ?
I tried sqldatasource filters  But am reallly confused
Any suggestions/solutions are appreciated
thankyou

View 8 Replies View Related

Using SqlDataSource To Execute A Select Statement In The Code File???

Nov 27, 2006

Hi you all,
In abc.aspx, I use a GridView and a SqlDataSource with a SelectCommand. The GridView's DataSourceID is the SqlDataSource.
In abc.aspx.cs, I would like to use an IF statement in which if a criterion is not satistied then I will use the SqlDataSource with another SelectCommand string. Unfortunately, I have yet to know how to write code lines in order to do that with the SqlDataSource. Plz help me out!

View 1 Replies View Related

SQLDataSource - How To Retreive The SQL Select Statement With The Values Of The Parameters

May 1, 2007

Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
 
Thank you

View 4 Replies View Related

Programatically Get Values From Sqldatasource

Oct 1, 2007

Hi All,
I am having problems getting values out of an sqldatasource.
I have 2 on the page, i am using one to insert into a table and the other to get a couple of values from another table for the insert.
the datasource i am trying to get the values from does a select with a querystring filter i need to grab a couple of fields out of it trhen do the insert on the other datasiource with a button click.
The insert is fine, i just don't know how to get the values out of the first 'select source'
Any pointers or suggestions most appreciated.
Cheers

View 2 Replies View Related

SQLDatasource - Managing Programatically

Nov 25, 2005

HiI'm trying to access the properties of my datasource programatically so I can change the stored procedure it connects to depending on the value of a querystringIn my page load I have
Dim myMode = Request.Params("mode")Select Case myMode   Case "Category"      sqldatasource1.SelectCommand = "productsbycategory"      sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure      ******'Need to add querystring parameter   Case "Wishlist"      sqldatasource1.SelectCommand = "productsbywishlist"      sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure      ******'Need to add Session variable parameter   Case Else      sqldatasource1.SelectCommand = "allProducts"      sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureEnd SelectThe problem that I have is that I need to pass a parameter to the first 2 procedures above, a querystring to the first and a session variable to the second.How do I add parameters programaticallyMany thanks

View 2 Replies View Related

SQLDATASOURCE ~ ACCESSING DATA PROGRAMATICALLY

May 9, 2007

Can anyone point me in the direction of code that will allow me to acce3ss the data held in an SQLDataSource.
 
The Control is link Selection Staement has been set at run, what I want to do is loop thru any data that is rertrieved, setting oter controls on the page depending on values
 Thanks
 Steve (I have not got much hair left, please help soon)

View 3 Replies View Related

How To Change Date Format In A Select Statement

Dec 1, 2006

 when i use this command in a aspx file
"SELECT DISTINCT Format$([dbo.classgiven.classdate], 'mm/yyyy') AS monthyear,{.............................. 
 
'Format$' is not a recognized function name.
 
so how do i change date from mm/dd/yyyy to mm/yyyy

View 2 Replies View Related

Programatically Change DBs

Sep 19, 2005

Carlos writes "I am trying to create a sp that would jump from one DB to another using the USE command. I built a string and then I tried to run EXECUTE(@SQLString) with no lock. This is the sample code I am using:

declare @DBName varchar(200)
declare @SQlString varchar(300)
set @DBName='model'
set @SQlString = 'use '+@DBName
execute(@SQlString)

Is there any way I can accomplish that?

Thanks

Carlos"

View 2 Replies View Related

Change NULL Values To Default In SELECT Statement

Dec 22, 2006

I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?
 
 

View 4 Replies View Related

How To Change Column Names In The Grid View Programatically?

Aug 31, 2007

I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer.  If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005?
Here's my post to the SQL devs to give you an idea what I'm trying to do.
I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3....Week26.  Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption". fldfieldno    Fldcaption-----------  --------------11             9/07/200712             9/14/200713            9/21/2007So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptionswhere fldfieldno = 11That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.My current query looks like this:SELECT  SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,So can I do something like the following?SELECT  SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...

View 6 Replies View Related

Change Output Alias On Synchronous Component Programatically

Nov 21, 2006



Wanted to enquire how this is done. Tried doing it in the setUsageType method I have overwritten but only allows description to be changed. Basically need to change "Name".

Best option would be to change it instantly when a user selects a column from the inputs in the custom component, ie. it changes the Output Alias to a desired value. (Input tab in advanced editor)

All this is being done in a custom component which I would like to be synchronous, can achieve a similar result asynchronously.

Thanks

View 2 Replies View Related

SELECT Columns Programatically

Jul 31, 2007

Hi, I've written a SELECT statement that returns columns dependant on a bitwise parameter @Populate.SELECTCASE WHEN (1 & @Populate) = 1 THEN [Column1] ELSE Null END AS [Column1],CASE WHEN (2 & @Populate) = 2 THEN [Column2] ELSE Null END AS [Column2],CASE WHEN (4 & @Populate) = 4 THEN [Column3] ELSE Null END AS [Column3],CASE WHEN (8 & @Populate) = 8 THEN [Column4] ELSE Null END AS [Column4],etcIs this the most efficient way to acheive this since I am only seeing a small performance gain. It still returns all columns but depending on @Populate will leave some columns with Null values.Any help much appreciated, thanks. 

View 7 Replies View Related

SELECT A Single Row With One SqlDataSource, Then INSERT One Of The Fields Into Another SqlDataSource

Jul 23, 2007

What is the C# code I use to do this?
I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.

View 7 Replies View Related

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

Hello... im having a problem with my query optimization....

I have a query that looks like this:


SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)


it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View 3 Replies View Related

Change Dynamically(via Code) The SqlDataSource For A GridView....

Mar 7, 2007

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!!!

View 3 Replies View Related

SQLDataSource IF Statement

May 18, 2008

I have the following query but i need to add an IF Statement in as well, 
SELECT     HomeTeam, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS AggFROM         tblFixturesWHERE     (CompID = 1) AND (HomeScore IS NOT NULL)
 the if statement needs to be:
IF HomeScore>AwayScore Then 3 is shown else 0 is shown.
Thanks

View 1 Replies View Related

Subselect Within A Case Statement In Sqldatasource

May 31, 2007

I have a sqldatasource, and on the selectcommand I'm trying to use a case statement with a subselect. The case statement works fine without the subselect, but I'm trouble getting it to work with the case statement. Could you help me with the syntax? ThanksSelectCommand="
SELECT DISTINCT RecipeID, Title
FROM [Recipes]
WHERE
(CASE WHEN @Type='Appetizer' THEN Appetizer
WHEN @Type='Pies' THEN
(Select Distinct RecipeID, Title From Recipes
WHERE Title like '%Pie%')
WHEN @Type='Beverages' THEN Beverage
WHEN @Type='Dessert' THEN Dessert
WHEN @Type='Kids' THEN Kids
WHEN @Type='Side' THEN Side
WHEN @Type='Soup' THEN Salad
WHEN @Type='Main' THEN Main
WHEN @Type='Breakfast' THEN Breakfast
END) = 1"
 

View 1 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

UPDATE Statement Syntax Help Required C# && SqlDataSource Control

May 3, 2007

Hi,
I need to UPDATE the IP Address of a newly created user into a table (the value is currently set to default - " Not Available"), and I really dont know the syntax required to do this. So far I've derived all the variables needed using the following code:
protected void ContinueButton_Click(object sender, EventArgs e)
{
//Get the ip address and put it into the customer table - (the instance of this user now exists)
MembershipUser _membershipUser = Membership.GetUser();
Guid UserId = (Guid)_membershipUser.ProviderUserKey;<--------------------------------------------------------------I can see the UserId here if I pause the prog
SqlDataSource customerDataSource = new SqlDataSource();
customerDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
customerDataSource.UpdateParameters.Add("IPAddress", Request.UserHostAddress.ToString());<---------------------------------------I can see the IPAddress here
customerDataSource.UpdateCommandType = SqlDataSourceCommandType.Text;
 what next
I've not got a clue as to what to write next. There is a try / catch statement after this using :
rowsAffected = customerDataSource.Update();
which remains at 0 no matter what I try.
Any help greatly appreciated.

View 15 Replies View Related

How Do I Use A Value Returned From SQLDataSource Select?

Mar 28, 2007

I would like to use the value returned from my SqlDataSource SELECT method, in the INSERT method for the same SqlDataSource.
 Any ideas how this is done?

View 1 Replies View Related

If SqlDataSource.Select = Empty Then...?

May 28, 2007

(Newbie). I'm trying to: 1) check if the SELECT command has returned any records, and 2) put a msg box on the screen if there are no records returned from the SELECT query.  The type of code I'm heading towards is:  (but it's wrong). Thank you in advance for your C# code suggestion.
 protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{if (SqlDataSource1.SelectParameters.Contains is "") then
MessageBox.show ("There are no records available") ;
}

View 8 Replies View Related

SqlDataSource + Parameter + SELECT IN

Jul 20, 2007

I'm sure that is really simple, but how do I pass a parameter with multiple value to a SQLdatasource?
ex: SELECT field1 from tblTableA where idTableA IN ( @Param1)
 Let's say I want to pass 1,2,3,4  as Param1   (SELECT field1 from tblTableA where idTableA IN ( 1,2,3,4))
How I am supposed tu use the .SelectParameters.Add()  to pass a list of integers instead of  a single value??
Thanks in advance.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved