DataSource Controls :: SqlDataSource And Parameters Query / Trying To Accomplish Is Building Dynamic Query

Aug 22, 2010

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.

View 11 Replies

Similar Messages:

DataSource Controls :: Sqldatasource On Inserting Query?

Jul 15, 2010

I have an editable grid where user can add/edit data. They also have the option to load previously entered data from last month. When the user then clicks save, i want to readd this newly modified data as a new record for this month. So basically I want to call the insertcommand, but it does not work. My 'save' method just 'updates' the records.

Providing sqldatasource, grid and save method
<asp:SqlDataSource ID="SqlDataSourceLoadActivities" runat="server" [code].....

View 4 Replies

DataSource Controls :: SqlDataSource Query Not Working?

Feb 18, 2011

I have a strange problem...I am programatically adding an SQL SELECT query to my SqlDataSource that my GridView is bound to. If I use the follwing statement it works fine:


But if I use the following one, the gridview fails to load. The only way that this is different is it uses RTRIM and CAST on one of the fields:


I don't understand why this can be. If I try the statement exactly how it is in the query builder and run it it returns the rows no problem.

View 2 Replies

DataSource Controls :: Make Parametrized SqldataSource Query In C#?

Jun 10, 2010

I have a gridview which I connect by C# and SqldataSource. I use 2.0 - 3.5.

I want to parameterized the queries and how do I do this in the C# file ? how do I set the selectparameters in C# ?. I do not want to do this in the aspx fil.

My code in C# is:

SqlDataSourceMachineName.SelectCommand = "Select MachineName from tblMachine inner join tblLocation on tblMachine.MachineLocationID = tblLocation.LocationID where tblLocation.LocationName = 'New York' and
tblMachine.StatusID = '1'";

View 9 Replies

DataSource Controls :: Breaking Long Query Into Small One Using SQLDataSource?

Oct 25, 2010

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,

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)?

View 2 Replies

DataSource Controls :: SqlDataSource Query Not Using Index On Database Table?

Jan 20, 2011

I have this sqldatasource:

<asp:sqldatasource id="SqlDataSourceX" runat="server" connectionstring="<%$ ConnectionStrings:MyConn%>" providername="System.Data.SqlClient"
selectcommand="SELECT ItemNum, MIN(DateAdded) as [DateAdded]
WHERE [DateAdded] between @DateFrom AND @DateTo


When I call select in codebehind, after 30sec it throws timeout expired SqlException:


again , this select took up to 3 sec to be executed. In application I tried to use SqlConnection and SqlTableAdapter instead of SqlDataSource, with the same query, and again it took up to 3 sec.

If I choose tighter interval (e.g '2011-01-20 00:00:00' - '2011-01-21 00:00:00') , there is no timeout exception, so I know that sqldatasource select is executed correctly. So the conclusion is that sql server is not using index while executing query sent from sqldatasource, but is using index whenever else. I have also checked the index statistics on sql server and it is like that. In profiler i can see slight difference between this two cases. As for SqlDataSource, in events table, in first column "EventClass" after 30 sec, there is this: "RPC:Completed" and application throws timeout. If i use SqlConnection and SqlTableAdapter then at the same place there is this: "SQL:BatchStarting" and "SQL:BatchCompleted" and application does not throws timeout(the index has been used).

what is so special on SqlDataSource connection, that Sql Server performs differently compared to other types of connections?

View 3 Replies

DataSource Controls :: Combobox And Query Parameters With IN?

May 19, 2010

with query parameters in ASP.NET. For example, if I have query:


for parameter @id I intend to use combobox. Everything is just fine with this example, problem begins when I try to use IN clause.What I need is query like this:



View 2 Replies

DataSource Controls :: Test The Performance Of When The Query Is Included Directly In The SqlDataSource?

Feb 16, 2011

I have a query that I want to test the performance of when the query is included directly in the SqlDataSource versus when I call a stored procedure. Does anyone have suggestions on how I would be able to closely watch the actual steps here? I was thinking of turning on tracing and adding Trace.Write for each of the events that fire along the way. This doesn't seem efficient and I wanted to see if there was a better approach.

View 2 Replies

Passing Dynamic Parameters To A Mysql Query From Page?

May 20, 2010

I've a requirement that I need to read an excel sheet programmatically using and write the data obtained into a mysql table.The excel sheet contains something around 50 columns and 2000 records.I am able to read the data from the excel sheet and store it in a dataset.I am using the following code to write the data into mysql table.

for (int i = 1; i <= myDataSet1.Tables[0].Rows.Count - 1; i++)
MySqlCommand cmd = new MySqlCommand();
for (int j = 0; j <= myDataSet1.Tables[0].Columns.Count - 1; j++)
paramset[j] = myDataSet1.Tables[0].Rows[i][j].ToString();
cmd.Parameters.AddWithValue("val" + j, paramset[j]);
cmd.CommandText = "Insert into faqimport
cmd.CommandType = CommandType.Text;
int x=cmd.ExecuteNonQuery;

When I try to run the above code I am getting 'Unknown column 'val0' in 'field list'' error.I understand that i am manually creating the parameters val0,val1,val2.....instead I am creating them dynamically using the integer 'j'.But I do not want to create around 50 parameters to insert data into the database table.

Also is there a way i can get the column datatypes from the excel sheet in order to create a new table in mysql with the columns in the excel sheet?

View 1 Replies

DataSource Controls :: Sql Query - Get Minimum Price When Pass Parameters - Code Is Duplicating Value

Feb 1, 2010

here is my query which is not working properly. I have 5 fields in my table Company, Reseller, Item, Code, Price. How can i get minimum Price when i pass parameters for Code is dublication value

Company Reseller Item Code Price
A A-1 I1 C1 20
B A-21 I2 C2 13
C A-B I1 C1 19

select Company,Reseller,Item,Code,Min(Price) from Table1 where code='C1' order by Code HOW CAN I GET THIS BELOW TYPE OF RESULT

Company Reseller Item Code Price
A A-1 I1 C1 20
C A-B I1 C1 19

View 17 Replies

DataSource Controls :: Use Is Null In Query Without Using Parameters For It Or Should Use Parameters For This Field Where Value Is NULL

Jan 21, 2010

here is my code for selectiong some records from db table

string strSql = "select * from mtblNBD where SentTo=@SentTo and InternalStatus Is NULL order by DeadLine desc";
SqlCommand com = new SqlCommand(strSql, con);
com.Parameters.Add("@SentTo", SqlDbType.NVarChar, 50).Value = (string)Session["uname"];

here I am using parameters for SenTo field but not for NULL so it is ok... or should I use parameters for this field where value is NULL , if yes then how can I use parameter for this

View 8 Replies

DataSource Controls :: Unable To Put Space To Dynamic Sql Query?

Jan 20, 2010

i have a query like this and its running correctly

FROM P INNER JOIN PCommunication ON P.ID = PCommunication.ID
WHERE P.Telephone IS NOT NULL AND CAST(REPLACE(LTRIM(RTRIM(SUBSTRING(P.Telephone, 2, LEN(P.Telephone)))), ' ', '') AS BIGINT) > 5000000000
AND PCommunication.TypesID = '7667d187-5fe1-4aaf-9e31-79761561da58'

i am trying to use it with dynamic query like this but its not working

@query = 'SELECT CAST(REPLACE(LTRIM(RTRIM(SUBSTRING(P.Telephone, 2, LEN(P.Telephone)))), ''' ''', '''''') AS BIGINT)

View 9 Replies

Query Datasource From Code-behind Versus SqlDataSource

Aug 28, 2010

What ive been using now is getting datas in code-behind, passing datasource into a Session, re-binds it to a gridview whenever there's paging/sorting needed. What i want to know is what is better if its performance we are talking about?

Is it datasource into a session Or SqlDataSource for which I know whenever paging/sorting needed is that sqldatasource opens up a DB connection to get the datas again?

View 6 Replies

DataSource Controls :: Null Option In Qurery Parameters / Using Query With DropDownList And Textbox Controls?

Dec 18, 2010

I have SqlDataSource with a few parameters (code below). If I understand this code correctly if parameter is NULL I should receive all items from the query. It works fine with query editor. But when I try to use this query on my page - with DropDownList and Textbox controls as source of parameters - it doesn't work. I suspect that main reason of problem is value which these controls pass to the query. Instead of NULL this is an empty string. Is it possible to pass NULL value from DropDownList or TextBox to
the SqlDataSource?


View 1 Replies

DataSource Controls :: Update Query Not Working When Using Query Builder To Configure Table Adapter

Jan 15, 2010


This query works perfectly on the query analyser.

But when configuring the Table adapter ,I try executing the query and i get 0 rows affected.

What could I be getting wrong in this case.

NB:Existing GalleryID has been supplied.

View 1 Replies

DataSource Controls :: Can't Find Query's / By Right Clicking On Database Created A Query In Server Explorer?

May 17, 2010

By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.

View 10 Replies

DataSource Controls :: How To Convert Sql Query Into Linq Query

Mar 10, 2010

select Groupid,GroupName,onorusername from palgroup where groupid in (select distinct Groupid

View 5 Replies

DataSource Controls :: Accessing FK From A Query Via Entity Query

Jan 21, 2010

I've got a query such as

Dim MediaQuery =
From m
In dB.DOWNLOADS _Where m.ID = id _Select

which returns a record from the database. One of the fields in the record is a FK to another table. I need to read this value to be able to set a dropdown based on the ID but I can't work out how to access it. For a standard record I can just do the following txtTitle.Text = MediaQuery.FirstOrDefault().TITLE

However with the foreign key it doesn't work like that. I've tried drpGroup.SelectedIndex = MediaQuery.FirstOrDefault().DOWNLOAD_GROUPS.ID where DOWNLOAD_GROUPS is the FK field but it returns Object reference not set to an instance of an object. If you're simply wanting to read some values from a single db record in the entitiy framework and one is a foreign key how should I go about getting the value?

View 2 Replies

DataSource Controls :: Used Sqldatasource For Performing And Update Query But How To Update The Record On Click

Nov 9, 2010

Here is the sqldatasource config code: <asp:SqlDataSource ID="SqlDataSource1" runat="server"

View 1 Replies

DataSource Controls :: SqlDataSource With A Lot Of Parameters?

Sep 4, 2010

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 supplied

SELECT * FROM [this_table] WHERE username = @username
and if gender supplied
SELECT * FROM [this_table] WHERE gender = @gender
and if both supplied
SELECT * FROM [this_table] WHERE username = @username AND gender = @gender

I know this sounds not very complicated but in my case, I am having like more than 10 controls up there. It is something likeusername, 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 ?p.s. I'm using GridView and SqlDataSource command to display the result back on .aspx page.

View 2 Replies

DataSource Controls :: Can Use If Else In Sqldatasource If Else Parameters

Oct 4, 2010

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.

View 1 Replies

Forms Data Controls :: Dynamically Set Query In SQLDataSource For Chart?

Dec 14, 2010

I've been wrestling with this for a while. What I want to do SEEMS like it would be very simple, buy nothing about it so far has been. Any assistance would be absolutely outstanding.Basic concept of what I want to do is...


View 2 Replies

Getting All The Record Of Particular Month - Building SQL Query?

Jan 13, 2010

I have table having data like:

ID Date Name
1 1/1/2009 a
2 1/2/2009 b
3 1/3/2009 c

I need to get result something like...

1 1/1/2009 a
2 1/2/2009 b
3 1/3/2009 c
4 1/4/2009 Null
5 1/5/2009 Null
6 1/6/2009 Null
7 1/7/2009 Null
8 1/8/2009 Null
30 1/30/2009 Null
31 1/31/2009 Null

I want query something like..

Select * from tbl **where month(Date)=1 AND year(Date)=2010**

Above is not completed query.

I need to get all the record of particular month, even if some date missing..

I guess there must be equi Join in the query, I am trying to build this query using Equi join

View 3 Replies

DataSource Controls :: All Parameters Null In Update Of SQLDataSource?

Sep 23, 2010

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:


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?

View 1 Replies

DataSource Controls :: Sqldatasource Insert Parameters Wont Set

Mar 9, 2010

if i do this to set the fetch parameters:

dsList.SelectParameters["client_id"].DefaultValue = Session["client_id"].ToString();

where dsList is my datasource, it works.

but wen i do the samething for the *%^&* insert, it doesnt:

dsList.InsertParameters["client_id"].DefaultValue ="1234";

it keeps saying its null, i dont understand why it woudl be doing this?

View 2 Replies

Copyrights 2005-15, All rights reserved