I tried to develop a dynamic pivot query with following code to avoid hard coding. I can got the 'Q1, Q2, Q3, Q4' dynamically and assigned to @(@quarter. But it cannot be passed to the pivot query.
[Code]....
I am using following pivot query but not getting result as single row getting as 4 rows.
One more problem i am unable to pass dates as parameters to storeprocedure something like this
select [@date] .
SELECT 'Forecasted' AS HeadCount,
[8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010]
FROM
(SELECT *
FROM TblEmpCount) AS SourceTable
PIVOT
(
SUM(EmpCount)
FOR StartDate IN ([8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010])
) AS PivotTable;
I am getting result as 4 rows but i want result in single row like this
HeadCount 8/1/2010 8/8/2010 8/15/2010 8/222/2010
Forecasted 191 182 176 169
I have an ASP.NET VB website in which I am using the Membership functionality to authenticate users. I am doing this through the Login control on the HTML page. I have working that it will authenticate the user and then opens a welcome screen in which I have a button they can push to open a Reports.aspx page. On that page I have a dropdown box that is being populated from a datasource connection query to my SQL Server database table. It is a hard coded query but want it to query the table off the username text value that the user provided on the login (i.e. select <something> from inventory where username = <username>). I have not behind code but only HTML at this point. Here it is ...
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Login.aspx.vb" Inherits="Login" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
[Code] .....
Here is the Reports form that has the dropdown on it:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Report.aspx.vb" Inherits="Report" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
[Code] .....
I am unable to date as dynamic parameter to stored procdure with pivot.i am getting
error
Msg 8114, Level 16, State 1, Procedure Sample, Line 3 Error converting data type nvarchar to datetime. Msg 473, Level 16, State 1, Procedure Sample, Line 3 The incorrect value "@date1" is supplied in the PIVOT operator.
below is my stored procedure
[code].....
i have a table for customer forecast which contains customer, week number and quantit as columns. now i need to write query and list the week as column for each customer and each cell should have a quantity. how to write query for this using pivot logic. i dont want to use cursor.
View 3 RepliesWe are using a asp.net page to submit data to server. what we do is we will pass as parameter in url Query string) and in page load we take that values and submitting to database I have these following doubt
1, What is the default event happening while we call a ASP.NET page is it a POST or GET? We tried to call this url from a mobile application, it is not happening , we are not getting any error also in the mobile application.
2, IF we are calling the url to submit data from mobile applications which one we have to use (GET or POST)
3, IF we are calling this url from an application running in PC to submit which one we have to use ?
I'm using Visual Studio 2008 Query Builder and am trying to pass a value to a parameter that uses the IN OPERATOR.Here is my sql from qb:
SELECT COURSE_TITLE, INSTR_NAME, ADMIN_UNIT
FROM vwHRIC_EC_ContractSummary
WHERE (ADMIN_UNIT IN (@ADMIN_UNIT))
The trouble I am having is that if the parameter @ADMIN_UNIT contains more than one value such as (8X, CV) that the query doesn't return any data. If I run the same query with only one value such as (8X) it works fine.I have tried different formats like (8X, CV), ('8X', 'CV'), (8X; CV) but none have worked.I ran this same query in SQL Server Management Studio and and it returns data no problem but can't get it to work in Query Builder for my aspx application.Does anyone have any experience with this problem and how did you correct it?
I have a database with a Referrals table connected to a Facilities lookup table. I'd like to create a report using ReportViewer in my asp.net web application that shows the number of referrals for each month in a given date range for each Facility. So the report would look like (for the date range 1/1/2010 - 4/30/2010):
[code]....
I have a table that could look like the left table in the following picture, but i want to present it as the right table in the picture. http://pici.se/549963/?size=fullsize
So i've found out that i could use pivots to do this, but am having some trouble getting it to work, so if you know how to do this.
i am passing the serverside control as parameter, but it throws OBJECT ERROR.
View 5 RepliesI have a server control that performs some logic. I'd love to provide a default implementation of this logic in the code of the control, but allow a developer to pass an alternate implementation of this logic in as a delegate, if they wish.
So...
<prefix:MyControl FooLogicMethod="MyUtilityClass.MyFooLogicMethod" runat="server"/>
If they don't specify this, I'll use my default method. If they do specify it, I'll use the method they passed in.
Can I do this as a delegate, or do I need to reflect it?
This may be elementary user control stuff, but I can't get it to work.
How can I pass the Membership.GetUser.UserName variable (or any variable) through a user control parameter at load time?
[Code]....
The only thing that I was able to do was to create the User Control object in the code behind and then add it to the page. Although that works, it doesn't seem like the correct way to program.
I have one more question. How can I use a column in the stored prcedure and pass it to a parameter in the code above?
Can we access a column like that?
I have a stored procedure that is close to working. It takes the values of a details view puts them into the respective table 'entity' then should also put the @@identity into 'entloan' and should put the LoanID which is in the query string of the page the detials view is loaded on, into the LoanID field of entLoan as well. Everything works except for getting the querystring value to pass to a insert variable on my stored procedure.
Stored Procedure
[Code]....
I do not have a LoanID Insert parameter set as it is not in entity table:
[Code]....
I have been playing around with different code in the backend but always get errors:
[Code]....
I want to pass to my control a value to one of its parameter of type DropDownList. Something like:
<uc1:MyControl Id="01" runat="server" OtherControl="ddl1" />
Where "ddl1" is a DropDownList defined in the same page my control resides. How to create this kind of property?
sales.aspx.vb
Custom function which stores the current DATE into todayDate. I use this function to convert system date into SQL friendly date.
sales.aspx
I used Data Grid and SQL Data source.
SelectCommand="select * from sales where aab = 110026"
I want my select statement should take the date from todayDate.
i.e.: SelectCommand="select * from sales where aab = todayDate"
How to write this statement in correct syntax.
I've a MDX Query that has where clause as shown below.I'm designing report using SSRS 2008. How can i pass date as parameter? I tried to setup @from and @to as parameter but not working?
WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )
need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )
I'm not even sure if I'm using the correct terminology of "Pivot". I've seen several examples of pivoting in SQL but I have not found anything like I am trying to do.Can someone tell me if it's possible to pivot columns for one row like so?:
[code]...
I am having a problem with my sql query in c#, basically it's inline query with parameters, but when I run it it tells me that parameter 1 or parameter 2 is not there here is my query declared on top of the page as public:
public const string InsertStmtUsersTable = "insert into Users (username, password, email, userTypeID, memberID, CM7Register) " +
"Values(@username, @password, @email, @userTypeID, @memberID,@CM7Register ); select @@identity";
this is my code for assigning the parameters, I know I am having problem so I am assigning the params twice:
Username =(cmd.Parameters["@username"].Value = row["username"].ToString()) as string;
cmd.Parameters["@username"].Value = row["username"].ToString();
In 1 methopd it calls this query and tries to insert to table, here is the code:
Result = Convert.ToInt32(SqlHelper.ExecuteScalar(con, CommandType.Text,InsertStmtUsersTable));
Exact error message is: Must declare the variable '@username'. Could this code be a problem, because all the previous coding is declared with in this using statement, except declaration of query, here the using statement: using (SqlCommand cmd = new SqlCommand(InsertStmtUsersTable, con))
i have the table like
TId DG rating DGMake DGCapacity
1 1 4 DG1 1X1
1 2 5 DG2 2X2
1 3 3 DG3 3X3
i need the output like this.
TId DG1 DG2 DG3 DG1Rating DG2Rating DG3Rating DG1Make DG2Make DG3Make DG1Capacity DG2Capaciy DG3Capacity
1 1 2 3 4 5 3 DG1 DG2 DG3 1X1 2X2 3X3
I have a table with the following structure and Values:
ID | Property | Value
-----------------------------
1 | FirstName| ABC
1 | LastName| XYZ
I want to get results in the following format:
ID | FirstName | LastName
----------------------------------
1 | ABC | XYZ
Please suggest a way to get the desired result.
I want this result
Id
Name
Service [code].....
I got the following table:
Lane Bin1 Bin2 Bin3
1 B11 B21 B31
2 B21 B22 B23
3 B31 B32 B33
How can i change the layout to
Bin1(Lane1) Bin1(Lane2) Bin1(Lane3) Bin2(Lane1) Bin2(Lane2) Bin2(Lane3) Bin3(Lane1) Bin3(Lane2) Bin3(Lane3)
B11 B21 B31 B21 B22 B23 B31 B32 B33
I tried using pivot, but cant get it.
I have three tables.
CustomProfile
UserID (pk)
DisplayName
CompanyID
Teams
TeamRecordID (Pk)
TeamID
CompanyID
EmployeesInTeams
UserID
TeamID
I want to assign employees to teams via a gridview control with a templated checkbox.
I have a dropdownlist control to select the TeamID.
I am having problems with the select statement.
The gridview needs to select employees that are not assigned to any teams as well as assigned to other teams. An employee can be assigned to many teams.
What I have been trying is:
cp.UserID, cp.DisplayName, et.TeamIntID, cp.CompanyID
it possible to pass the query dynamically to sql server report from .net or from any other source.
View 1 Replies