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:
[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.
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 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].....
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'";
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)?
<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?
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.
I'm using the CommandTimeout command on a SqlDataSource in a web page to allow running queries that will take longer than 30 seconds.However, when I run the web page, it doesn't work at all. I get a timeout error.Here is my code:
[Code]....
[Code]....
And the error I'm getting after 30 seconds: Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I am trying to get the uniqueidentifier value from a insert SP and retreive that using a sqldatasource and pass that value to store files in the relevent folder.
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?
I am trying to fill a gridview with the data from Product table selecting few columns . I am using 3 - tier architecture and in DAL getproduct(userid) I am writing the query but cannot figure out exactly how to get that working .. here is piece of method I wrote
I'm trying to create multi insert query in single stored procedure along with scope_identity variable. while execute the aspx page getting error "Procedure 'sp_seller_ins' expects parameter'@new1', which was not supplied." I have created stored procedure:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_seller_ins] @txt_n_properity VARCHAR(50), @txt_t_properity VARCHAR(50), @txt_prop_for VARCHAR(50), @txt_flat_no VARCHAR(50), @txt_loc_country VARCHAR(50), @txt_loc_state VARCHAR(50), @txt_loc_district VARCHAR(50), @txt_loc_town VARCHAR(50), @txt_loc_area VARCHAR(50), @txt_loc_locality VARCHAR(50) AS BEGIN declare @new1 int declare @new2 int INSERT INTO dbo.sel_pro_details( nature_property, type_property, property_for, flat_no) VALUES ( @txt_n_properity, @txt_t_properity, @txt_prop_for, @txt_flat_no); set @new1 = scope_identity() INSERT INTO dbo.sel_loc_details( country, state, district, town, area, locality, pro_details_id) VALUES (@txt_loc_country, @txt_loc_state, @txt_loc_district, @txt_loc_town, @txt_loc_area, @txt_loc_locality, @new1); set @new2 = scope_identity() end code behind aspx page sqlconn mySql = new sqlconn(); mySql.CreateConn(); mySql.Command = mySql.Connection.CreateCommand(); mySql.Command.CommandType = System.Data.CommandType.StoredProcedure; mySql.Command.CommandText = "sp_seller_ins"; mySql.Command.Parameters.Add("@txt_n_properity", SqlDbType.VarChar); mySql.Command.Parameters["@txt_n_properity"].Value = txt_n_properity.Text; mySql.Command.Parameters.Add("@txt_t_properity", SqlDbType.VarChar); mySql.Command.Parameters["@txt_t_properity"].Value = txt_t_properity.Text; mySql.Command.Parameters.Add("@txt_prop_for", SqlDbType.VarChar); mySql.Command.Parameters["@txt_prop_for"].Value = txt_prop_for.Text; mySql.Command.Parameters.Add("@txt_flat_no", SqlDbType.VarChar); mySql.Command.Parameters["@txt_flat_no"].Value = txt_flat_no.Text; mySql.Command.Parameters.Add("@txt_loc_country", SqlDbType.VarChar); mySql.Command.Parameters["@txt_loc_country"].Value = txt_loc_country.Text; mySql.Command.Parameters.Add("@txt_loc_state", SqlDbType.VarChar); mySql.Command.Parameters["@txt_loc_state"].Value = txt_loc_state.Text; mySql.Command.Parameters.Add("@txt_loc_district", SqlDbType.VarChar); mySql.Command.Parameters["@txt_loc_district"].Value = txt_loc_district.Text; mySql.Command.Parameters.Add("@txt_loc_town", SqlDbType.VarChar); mySql.Command.Parameters["@txt_loc_town"].Value =txt_loc_town.Text; mySql.Command.Parameters.Add("@txt_loc_area", SqlDbType.VarChar); mySql.Command.Parameters["@txt_loc_area"].Value =txt_loc_area.Text; mySql.Command.Parameters.Add("@txt_loc_locality", SqlDbType.VarChar); mySql.Command.Parameters["@txt_loc_locality"].Value =txt_loc_locality.Text; mySql.Command.ExecuteNonQuery(); mySql.Command.Dispose(); mySql.Connection.Close(); mySql.CloseConn();
I have a form that gets populated by LINQ and I want to be able to update the database using that same form. The form is populated by grabbing a querystring (for example, "edit=10") and searching the primary key for that record. So far so good. I have a button attached to the function below which is supposed to use LINQ to update the database with data from the form. I get no errors, but for some reason, it is only updating one field (Last_Updated) instead of all of them.
I'm encountering a strange problem. Here is the scenario. I have built a query that accepts a parameter (WHERE LIKE clause).
I've tested this within the query builder and it returns exactly the number I would expect.
I then go back to my webpage and add the following controls:
Dropdown
Gridview
the user to make a selection from the dropdown. Postback on the dropdown is enabled so the page reloads and the Gridview displays the filtered results (via an objectdatasource). I've set the parameter to the dropdown control.
The trouble is when I run the page I make the selection in the dropdown no results are displayed. Even though the text displayed in the dropdown control is exactly the same as I inputted into the query builder when testing it.
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...
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?
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.
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.
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?
