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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

SqlDataSource and Parameters query

Posted By:      Posted Date: August 23, 2010    Points: 0   Category :ASP.Net
 

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.




View Complete Post


More Related Resource Links

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

Mapping SqlDataSource Parameters to Formview with Multiple DataKeys

  
Hi I have:a FormView that is linked to SqlDataSource1a Repeater control (inside the formview) that is linked to SqlDataSource2Thus representing a simple "1 to many" or "parent/child" relationship in the database!SqlDataSource1 is simple in that it just retrieves a straight set of records from a table and they are displayed back in the FormView (nothing special happening there). SqlDataSource2 has parameters that  filter the contents of the repeater based on the record that is shown in the Formview. This would normally be simple enough to set up by setting the parameter to the formview.selectedvalue property.HOWEVER - THE PROBLEM ARISES...The formview has multiple DataKeyNames because the table that the SqlDataSource1 links to has a compound key. So to filter the Repeater correctly SqlDataSource2 needs to map 3 parameters to 3 data key values in the formview.Any ideas how to do this?I've tried setting the SqlDataSource2 parameters to:<SelectParameters>   <asp:ControlParameter ControlID="FormView1" Name="Parameter1" PropertyName="DataKey(0)" />   <asp:ControlParameter ControlID="FormView1" Name="Parameter2" PropertyName="DataKey(1)" />   <asp:ControlParameter ControlID="FormView1" Name="Parameter3" PropertyName="DataK

Mapping SqlDataSource Parameters to Formview with multiple DataKeys

  
Hi I have:a FormView that is linked to SqlDataSource1a Repeater control (inside the formview) that is linked to SqlDataSource2Thus representing a simple "1 to many" or "parent/child" relationship in the database!SqlDataSource1 is simple in that it just retrieves a straight set of records from a table and they are displayed back in the FormView (nothing special happening there). SqlDataSource2 has parameters that  filter the contents of the repeater based on the record that is shown in the Formview. This would normally be simple enough to set up by setting the parameter to the formview.selectedvalue property.HOWEVER - THE PROBLEM ARISES...The formview has multiple DataKeyNames because the table that the SqlDataSource1 links to has a compound key. So to filter the Repeater correctly SqlDataSource2 needs to map 3 parameters to 3 data key values in the formview.Any ideas how to do this?I've tried setting the SqlDataSource2 parameters to:<SelectParameters>   <asp:ControlParameter ControlID="FormView1" Name="Parameter1" PropertyName="DataKey(0)" />   <asp:ControlParameter ControlID="FormView1" Name="Parameter2" PropertyName="DataKey(1)" />   <asp:ControlParameter ControlID="FormView1" Name="Parameter3" PropertyName="DataK

SqlDataSource with a lot of parameters

  
Hi everyone,I have asked similiar question before where I have two control on the page header says "username" and "gender". User can select both the control or either one and the page will execute searching based on the criteria supplied. So if username suppliedSELECT * FROM [this_table] WHERE username = @username and if gender suppliedSELECT * FROM [this_table] WHERE gender = @gender and if both supplied SELECT * FROM [this_table] WHERE username = @username AND gender = @genderI know this sounds not very complicated but in my case, I am having like more than 10 controls up there. It is something like username, gender, age, date of brith, title, father name, frst name, last name, blur blur blur and it's a lot then I started to lost. if i were to use if else caluse on code-behind and produce query, it would end up writing a lot of if-else and if i use stored procedure (with if-else) then my stored procedure would contains a lot of if-else statement. I am fairly new to this concept and if I'm on the wrong track, please correct me. Is there any better way to save my hair without actually going through each if-else statement to build the query ? Thanks all.p.s. I'm using GridView and SqlDataSource command to display the result back on .aspx page.

SqlDataSource : in what order do parameters get fed to stored proc?

  
Hello, I'm wondering if someone can explain how a SqlDataSource determines the order in which parameters are fed to a stored procedure. I'm not able to find clear documentation.So suppose a proc has: CREATE PROCEDURE  updateOneCar@CarActiveFlagCheck int, @CommentsForCar varchar(1500) = NULL, @manufacturerID int...and then suppose the ASP.NET page has the manufacturerID supplied by a querystring, and has a FormView as follows: <asp:FormView ID="FormView1" runat="server" DataSourceID="SQL01"          DataKeyNames="manufacturerID" EditRowStyle-Wrap="False" RenderOuterTable="False">         <EditItemTemplate>         <div>             manufacturerID: <%# Eval("manufacturerID")%>             <br />             Is my Car on?: <%# Eval("carFlag")%>             <br />             Is my car Active Flag:             <asp:CheckBox ID="CarActiveFlagCheckBox" runat="server"              

Problem with SELECT COUNT query and parameters

  
Hello!I have a problem with SELECT COUNT query in ASP.net. I want to create CMS with articles which have categories (which have the option to be deleted). The problem is that I want to get the number of articles within the specified category so if there aren't any articles with the specified category I can proceed with the category deletion.I have the following code:protected void Page_Load(object sender, EventArgs e) { } protected void GridViewKategorije_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "Uredi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell ClanakID = odabraniRed.Cells[2]; string ID = ClanakID.Text; Response.Redirect("/Portal/Administracija/Kategorija.aspx?idKategorija=" + ID); } else if (e.CommandName == "Obrisi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell KategorijaID = odabraniRed.Cells[2]; String connString = WebConfigurationManager.ConnectionStrings["CMS"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); using (SqlC

Dynamically fill query parameters in a table adapter ?

  
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>

sqldatasource parameters from code

  

Hi I'm trying to pass default value to a sql parameter which is

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
    SelectCommand="SELECT * FROM [Activ] WHERE ([DayNum] = @DayNum)">
    <SelectParameters>
        <asp:Parameter Name="DayNum" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>
and the Page_Load goes like this
Int32  MyDay;
            MyDay = (Int32)DateTime.Today.DayOfWeek;
            MyDay = MyDay + 1;
            SqlDataSource1.SelectParameters["DayNum"].DefaultValue = MyDay;
Intellisense says that I can´t convert int into string underlining red MyDay, I get it but, if I use ToString
then the SQL statement won't work since DayNum is int.
 
How can I work this out? Thank you


 


All parameters null in Update of SQLDataSource?

  

Hi All,

I have a FormView which is bound to an SQLDataSource. When a user clicks on a save button the form, I need to write any changes made to the FormView back to the table.

I have a Stored Procedure on my MSSQL 2005 database that should be executed when the user clicks on the save button.

It appears the Update is firing, but when I trap the DbCommand object in the SQLDataSource's 'Updating' event, all of the parameters that should be passed to the Stored Procedure are coming through as NULLs. Consequently, the Stored Procedure isn't updating anything.

In the click event of the save button I'm explicitly firing the SQLDataSource's Update method:

this.SqlDataSource4.Update();


The parameters in the SQLDataSource <UpdateParameters></UpdateParameters> section appear to be correct.

Can anyone give me an idea on what to investigate to figure out why it appears the Update is only picking up NULLs?

Many thanks in adavnce,

pt


Query with parameters in data flow

  

Hello,

How can I run a query with parameters in a data flow task? I know I can use a script component and write code that executes the query, but I'm looking for a simpler solution, if there's such one.

Thanks.

 


Using query results as parameters

  
Hello all, i was wondering if there is a way of, only using tsql, solve this: i have a query that returns me a name of a column and i want to use that result like this: "Select (the_name_of_the_returned_column) from mytable"

Update Parameters with SQLDataSource and VB code behind

  

I have been going around the houses for several days now. I have an sql datasource that uses a stored Proc  to uodate.  I set the updateParameters values from vbcode behind

        Me.SqlAdministration.UpdateParameters("Licence").DefaultValue = Me.TxtLicence.ToString
        Me.SqlAdministration.UpdateParameters("CompanyName").DefaultValue = CType(FormView1.FindControl("CompanyNameTextBox"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("Status").DefaultValue = CType(FormView1.FindControl("status"), DropDownList).SelectedValue.ToString
        Me.SqlAdministration.UpdateParameters("EmailAccounts").DefaultValue = CType(FormView1.FindControl("txtEmailAccounts"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("EmailGeneral").DefaultValue = CType(FormView1.FindControl("txtEmailGeneral"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("TelAccounts").DefaultValue = CType(FormView1.FindControl("txtTelAccounts"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("TelGeneral").DefaultValue = CType(FormView1.FindControl("txtTelGeneral"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("CompanyAddress").DefaultValue = CType(FormView1.Find

How to view the Query with parameters

  

I run queries in my stored procedure and pass values to it as a perameter e.g.

SELECT p.Profile_Id

FROM   Profile p
WHERE  (p_Gender = p.gender_id)


now is there a way that i can get the actual query executed with paramaters value like this:-

SELECT p.Profile_Id

FROM   Profile p
WHERE  ('M' = p.gender_id)



can you use if else in sqldatasource if else parameters?

  

I am trying to check whether a username exists in my database before inserting the new row & display a message to the user if it does.

On my SqlDataSource my InsertCommand looks like this:

InsertCommand="IF NOT EXISTS (SELECT * FROM Users WHERE UserName = @UserName) BEGIN INSERT [Users] ([UserName], [FirstName], [LastName], [City], [State], [Country], [CompanyName], [Active], [PasswordHash]) VALUES (@UserName, @FirstName, @LastName, @City, @State, @Country, @CompanyName, @Active, @PasswordHash) END"

 

This works as expected: If the username is unique, the row is created.

If the username already exists, the row is not added.

 

The problem is, if the row already exists, the insert is rejected but the user is not notified.

So my question is, is there a way I can notify the user?

Maybe something like: InsertCommand = "If Not Exists (select....) Begin Insert... Else Notify User Somehow End

 

This is a basic database, I'm not using the built-in membership provider nor will be.

 

Thanks!

 


Breaking long query into small one using SQLDataSource (using Temp table)

  

Currently, I have long script which does major processing and then puts data into temp table.

Finally I read data from this temp table to show on the chart, based on user's selection of parameter. So I can divide my query into two parts and while I am researching this, I will appreciate any pointers/guidance.

 

Is it possible to do processing in one Datasource and read table from second datasource?

Is it even possible?

If not, what is recommended method for this (breaking large processing into small one)?


Trouble linking multiple tables in a sql query within a sqldatasource

  

I am trying to link 3 tables within a sqldata source.  when I debug/run the code I recieve an error stateing that I am missing an operator.

My code:

<asp:SqlDataSource ID="SqlDataSource2" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
        SelectCommand="SELECT [ProductCode], [Title], [TechID], [Description], [DateOpened], [DateClosed]
            FROM [Incidents] INNER JOIN [Technicians]
                ON [Incidents.TechID] EQUALS [Technicians.TechID]
            INNER JOIN [Products]
                ON [Incidents.ProductCode] EQUALS [Products.ProductCode]
            WHERE ([CustomerID] = @CustomerID)
            ORDER BY [ProductCode]">
   

Having issues with Query parameters not returning my filter value when specifying >=

  

SSRS 2008 w SP2
VB.Net to pass the parameters
Report Viewer on a form setup for Server Report

I am currently creating a SSRS Report and having problems with my query filters. Basically I am trying to pass 2 values to filter (there are actually 4 range values that can be used to filter the report, all of which work the same way). The parameter values are passed to the server report through an exe with a seperate front end form for the user to specifiy their filters. The report has a Text Query for the Dataset using ODBC connection and unnamed parameters. For the Range parameter I am doing as "WHERE ord_no >= param.from AND ord_no <= param.to" in the dataset query. The paramaters are setup and in the correct order. The problem is that whatever "param.from" value I use, It never shows in the report. When I catch the query from Profiler and run it in Sql Management Studio, it returns the results correctly.

For example:
My filter range would be for orders number 60 through 62; When displaying the SSRS report, orders number 61 and 62 show, but 60 does not (it seems to always return the results as if it was WHERE ord_no > param.from AND ord_no <= param.to)... If I catch the query from Profiler and run it Management Studio, the results include orders 60, 61, and 62 correctly like I would expect to see on display o

Categories: 
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