DataSource Controls :: Make Parametrized SqldataSource Query In C#?
Jun 10, 2010
I have a gridview which I connect by C# and SqldataSource. I use asp.net 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.SelectParameters.Clear();
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
Similar Messages:
Aug 13, 2010
SELECT ClientMaster.ClientName,*
FROM ProjectMaster LEFT JOIN ClientMaster
ON
ProjectMaster.ClientCode=ClientMaster.ClientCode
WHERE
ProjectMaster.RefCode IN (@refcode) AND ProjectMaster.ProjectStatus=@status
@refcode should be able to contain multiple values but how should I pass value to the parameterized query?
I have tried many ways such as @refcode = 'VALUE1','VALUE2', @refcode = VALUE1,VALUE2 but to no avail?
View 3 Replies
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
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
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:
[Code]....
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:
[Code]....
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
Feb 21, 2011
I have a SqlDataSource connected to database and use a parametrized query to fetch out data.Query has got a very large varchar field on which i have to perform a search.To make search, i have 2 combobox and a textbox organized in this way
MAIN CATEGORY (combobox)-> CATEGORY TYPE(combobox) -> MODEL(textbox).
Based on main category choose, category type is filled from database. At last, it's added a "nothing" combobox item.Now i should search into big varchar field adding category type condition but, if nothing is specified, i should make identical search like all combobox value are selected. How should i, in your opinion, make this using SqlDataSource?
View 5 Replies
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
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]
FROM Items WITH (NOLOCK)
WHERE [DateAdded] between @DateFrom AND @DateTo
[Code]....
When I call select in codebehind, after 30sec it throws timeout expired SqlException:
[Code]....
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
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
Apr 9, 2010
I have a the following code to get data from an oracle database using
a parameterized query:
conn = New OracleConnection(connectionstring)
comm = New OracleCommand("select name from Cust_name
from Name_Table Where last_name = :lastname", conn)
parmLastName = New OracleParameter("lastname", OracleDbType.Varchar2)
parmLastName.Value = "Jones"
comm.Parameters.Add(parmLastName)
I am trying to write an audit that will tell me what users are doing, so I would like to get the actual sql that is submitted. This is just one example of the query, there are many more so I would like to make it as general as possible. I would like to actually see "
select name from Cust_name from Name_Table
Where last_name = "Jones". I know I could just create the text
View 2 Replies
Sep 14, 2010
I'm using parameterized sql query to get data from database
string query = "Select * from employee where employee_city in (@value)";
strign city ="'NewDelhi','Bangalore','Mumbai'";
I'm using following code to achive this
[code]...
But this is not giving the result. If run the query in SQLServer query window as "Select * from employee where employee_city in ('NewDelhi','Bangalore','Mumbai')", records are there.
But the same query will not return any records from ADO.Net.
View 4 Replies
Nov 19, 2010
I have a number of automatically generated data access classes to handle things like inserts & updates. These look like:
cmd.CommandType = CommandType.Text
cmd.CommandText = "UPDATE myTable set f1 = :f1, f2 = :f2, f3 = :f3 where id = :id"
cmd.Parameters.AddWithValue(":f1", _f1)
cmd.Parameters.AddWithValue(":f2", _f2)
cmd.Parameters.AddWithValue(":f3", _f3)
cmd.ExecuteNonQuery()
If I were to re-write the sql to do what I want, it would look something like
cmd.CommandText = "UPDATE myTable set f1 = pkg.getMyValue, f2 = :f2, f3 = :f3 where id = :id"
Is there any way to do this by setting _f1 to a "special" value, without changing the update SQL or the way the parameters are set? Only in certain cases will I need to set the value of f1 - most of the time, it will be null.
Using .net 2.0, and system.data.oracleclient against oracle 11.
View 1 Replies
Apr 11, 2010
in my application i should make an union query thay union 15 tables
and this query begin with 150000 rows and will grow .
i need get just 10 rows from this query
i want make this query as best as possible
2 qustion
-----------------------------------------------------------1------------------------------------------
********View1*******
select id, lang, active from table1
union
select id,lang,active from table2
...--same way to all other 15 tables
*******SP***********
select top 10 from View1 where active=1 and lang='he' order by id desc
or
********View1*******
select id, lang, active from table1 where active=1 and lang='he'
union
select id,lang,active from table2 where active=1 and lang='he'
...--same way to all other 15 tables
*******SP***********
select top 10 from View1 order by id desc
---------------------------------------------2---------------------------------------
becauseof that this query is very complicate i want to know if i need take just 10 from each table
------------------------------------------------3--------------------------------------
if there is an idea how make this query better, i will glad to know.
View 7 Replies
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
Nov 9, 2010
Here is the sqldatasource config code: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
View 1 Replies
May 14, 2010
I am trying to tie a sqlDatasource to a Stored Procedure. The stored procedure looks like...
[Code]........
View 2 Replies
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...
[Code]....
View 2 Replies
Feb 25, 2010
i have two sqldatasource controls. one i use to display data in textbox's ,filter'd by a select parameter in page behind code. Once checked, i want to copy this data to the other datasource ,by selecting checkbox.Then display this data in detailsview control.
At present the two datasource controls declared , render data to the page simultaneously during pageload. I want to first check data in textbox's from first source, before second datasource is rendered to screen. note, both are filtered by a page variable. i wish to leave the textbox datasource control in situ, as other controls and code depend on it.The other detailsview datasource is my problem?
View 1 Replies
Feb 16, 2010
I'm using VS2008 to write a web app. The page has 3 inputs (select date from calander, and items from two dropdownlists). User will be able to select any of the three or nothing. I start with a "select * from ... and append a where stmt to my select; depending on the user's choices. Once the user has selected (or not) they will click a "get info" button. That passes the select command to the SQLDATASOURCE, where it successfully returns what was requested. If I have PAGING enabled (perferred because there could be MANY rows returned), the first 10 rows return with the appropriate page numbers. When a page number is picked (ie. 2 or next), the page reloads and the query reruns but regardless of the original query; the rerun query selects everything. Obviously, this will not work.i'll even ZIP my code and email it to "you"; if needed.
View 1 Replies
Jan 15, 2010
[code]...
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
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
Mar 10, 2010
select Groupid,GroupName,onorusername from palgroup where groupid in (select distinct Groupid
View 5 Replies
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
May 31, 2010
I would like to get validation messages for validation controls from resource files. I know I can do that easily by following code snippet.
<%$ Resources:[filename prefix,]resource-key %>
or
<asp:Label ID="Label1" runat="server" meta:resourcekey="resource-key-prefix" />
But I would also like to parameterized it.
e.g.
Above Resource Expression will give me message like "Fill Information.". What I have in resource file is "Fill {0} Information." which should show end user message like "Fill Address Information.".
View 1 Replies
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
