Query Criteria / Parameter For A Whole Project

Passing Parameter Query From SQL Function To Access Project Report
I can pass a parameter from an Access Query to an Access Report (MDB) by entering [Select Date] in the Query criteria and by placing an unbound control with a control source =[Select Date] on the report.  I can't get this to work from a SQL Function Criteria to an unbound control on the Access Data Project Report.  In the Function Criteria, I enter @SelectDate.  In the Report control, I enter @SelectDate and it gives me an 'Invalide Column Name' error.  Any idea how I can pass a parameter from a SQL Function to an ADP report?

THANKS!

p.s.  I tried searching for other postings on this without any luck.

View Replies !   View Related

---

How To Ignor Filtering Criteria If There Is A Null Value For Parameter
I have gridview and i want to ignor that filtering if that parameter is empty how do i write that query.
my query is
select * from UserMAster where UserId=@USerID and RoleId=@RoleId
if @UserId is blank then i want to select all records without filtering with user Id a
nd if i have passed null or anything as parameter in roleId then alsoi want all the records if it contain value then i want to do filetring is it possible to with query. or else send me some alternative so that i don't need to execute more query.
 

View Replies !   View Related

---

Selecting Column Criteria Based On Report Parameter
I have a report with a date type parameter.  Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date.  How do I go about coding it so that it will dynamically select the right column in my query for my dataset?
 
Sincerely appreciate all the help I can get.
 
Thanks in advance.
 

View Replies !   View Related

---

Team Project In TFS - SSIS Project Sets Itself As The Startup Project
 

For some reason in a Team Foundation Team Project that has multiple project types (SSRS, SSIS, WebSite, C# Business DLL...), the SSIS project makes itself the startup project to the team project. If I explicitly set another project as the startup project to the team project and then select an SSIS package in the SSIS project in the team project, the SSIS project becomes the startup project automatically.
 
I am using Visual Studio 2005 SP1.

View Replies !   View Related

---

Best Practice Question: JOIN Criteria Vs. WHERE Criteria
For example, consider the following queries:


DECLARE @SomeParam INT
SET @SomeParam = 44

SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam

SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam


Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?

Thanks.

View Replies !   View Related

---

Using Results From One Query As Criteria For Next
I have a database with some over normalized tables in it.  The best I can do with one query is get the file ID.  In the second query I want to get all the file names, based on all the fileID's I got from the first query.  How would I go about doing this?

View Replies !   View Related

---

Query Criteria Format
I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg

The symbols are stored in the tables as: A B C D
The query string criteria would look like: IN('A', 'B', 'C', 'D')

The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC

SELECT a_Name_Symbol.Symbol, a_Financials.Revenue
FROM a_Financials INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
WHERE (a_Name_Symbol.Symbol @Symbol)
ORDER BY a_Name_Symbol.Symbol

Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ?

Thanks,

Paul

View Replies !   View Related

---

How Do I Query For Particular Criteria In Every Field In The Db?
I need to query a table for a record only if there is a certain text string in ANY OF THE TABLE'S fields. I know of using the WHERE clause with logical OR (e.g. - WHERE field1 = 'xyz' or field2 = 'xyz' or ...) but there is a limit of 40 expressions with an Access database, and I have 102 fields to look through! That's my dillema. I know I may need to go to MSSQL or Oracle, but I'd rather stay with Access. Is there a syntactical way to express the query I need? Or do I have to look somewhere other than Access for my database needs? Thanks for the advice!

View Replies !   View Related

---

Criteria Search Query
I have four criterias in my .aspx page. They are "First Name, Last Name, Title, Year". I have a Book and an Author table. The Author table would contain all the author's information and the Book table contains all the books information such as title, publisher, subject, and so on. So here's what I'm trying to do.
 
I want to write a transaction statement that will query the four criterias above if the criteria textbox  is not black. So for example, when the user click the submit button, all the four criteria fields are filled except Year. That means the query would search the Author and Book tables for "First Name, Last Name, Title" but not "Year" for any potential matches. I also wanted to use "Like" instead of "=" for a wider search.

 
Actually I'm trying to create a store procedure that will accept those four criterias and search the tables based on those criterias.
 
Any help is appreciated.

View Replies !   View Related

---

Slow Query Due To Criteria
I have a query in access that is running extremely slow and I'm trying to find a better way to write it. It appears to be the criteria statement that is causing the lag. Is there a better way to write this? And unfortunately I have to keep it in access.

query:
SELECT DISTINCT "JBC" AS ClientCode, PaymentDetail.PatientNumber, Procedures.CaseNumber, IIf(IsNull([TicketNumber]),Procedures.patientnumber & Year(Procedures.dateofservice) & Month(Procedures.dateofservice) & Day(Procedures.dateofservice),Procedures.ticketnumber) AS ClaimNumber, PaymentDetail.PaymentCounter, PaymentDetail.TransAmount, Payments.PaymentDate, PaymentDetail.AccountingDate, PaymentDetail.TransDate, payments.PaymentType & "-" & Adjustments.adjustmentcode AS CombinedPmtType, Payments.PaymentType, Payments.PaymentCode, Adjustments.AdjustmentCode, PaymentCodes.BriefDescription, PaymentCodes.LongDescription, Payments.CarrierCode, Payments.Remarks, Procedures.ProcedureCode, Procedures.DateOfService, PaymentDetail.DetailCounter
FROM ((((PaymentDetail LEFT JOIN Procedures ON (PaymentDetail.PaymentCounter = Procedures.Counter) AND (PaymentDetail.AccountingDate = Procedures.AccountingDate) AND (PaymentDetail.PatientNumber = Procedures.PatientNumber)) LEFT JOIN Payments ON (PaymentDetail.TransDate = Payments.AccountingDate) AND (PaymentDetail.TransCounter = Payments.Counter) AND (PaymentDetail.PatientNumber = Payments.PatientNumber)) LEFT JOIN PaymentCodes ON Payments.PaymentCode = PaymentCodes.PaymentCode) LEFT JOIN Adjustments ON (PaymentDetail.TransDate = Adjustments.AccountingDate) AND (PaymentDetail.PatientNumber = Adjustments.PatientNumber)) LEFT JOIN AdjustmentCodes ON Adjustments.AdjustmentCode = AdjustmentCodes.AdjustmentCode
WHERE (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=2)) OR (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=5) AND ((AdjustmentCodes.InsuranceIndicator)="N"));

View Replies !   View Related

---

Default Non-queried Report Parameter Not Updated When Project Is Deployed
Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1.  Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked
Available values = "From Query"
Dataset = [create a dataset that returns a table w/ a Id and Description column]
Value field = [the Id field from the table]
Label field=[the Description column from the table]
Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2.  Build and deploy the report to the reporting server.  View the report and verify the specified items are checked in the report parameter.

3.  Go back to Visual Studio and add a value to the Non-queried Default values.

4.  Build and deploy the report again.  View the report.  The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed.  However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist.  I verified that changes are being deployed by adding new parameters and changing other properties of the parameter.  I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services.  None worked.  Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1.  Create a dataset for the report that returns a table of the Ids that you want pre-selected.  The query could be something like this:


SELECT '4'  AS SelectedId
UNION
SELECT '5'  AS SelectedId
UNION
SELECT '6'  AS SelectedId

2.  Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

View Replies !   View Related

---

Passing &"Like&" Criteria In Parameter To SQL Server Stored Procedure
Hello All,
 I was hoping that someone had some wise words of wisdom/experience on this.  Any assistance appreciated... feel free to direct me to a more efficient way... but I'd prefer to keep using a stored proc.
  I'm trying to pass the selected value of a dropdownlist as a stored procedure parameter but keep running into format conversion  errors but I am able to test the query successfully in the SQLDatasource.  What I would like to do is this: select * from tblPerson where lastnames Like "A%" . Where I pass the criteria after Like.   I have the values of the drop down list as "%", "A%", "B%", ....
I've been successfully configuring all of the other params, which includes another dropdown list (values bound to a lookup table also)... but am stuck on the above...
 Thank you for any assistance,
 

View Replies !   View Related

---

Access Query Against SQL Server Works Only Without Criteria
Getting a weird error while trying out a query from Access 2003 on aSQL Server 2005 table.Want to compute the amount of leave taken by an emp during the year.Since an emp might be off for half a day (forenoon or afternoon), havethe following computed field:SessionOff: ([ForenoonFlag] And [AfternoonFlag])The query works fine when there's no criterion on SessionOff.However, when I try to get the records where the SessionOff equals 0, Iget the following error:~~~~~ODBC--call failed. [Microsoft][SQL Native Client][SQL server]Incorrect syntax near the keyword 'NOT'. (#156)~~~~~I checked the SQL of the Access query, but there's no NOT anywhere init:~~~~~SELECT tblWorkDateAttendance.*FROM tblWorkDate INNER JOIN tblWorkDateAttendance ONtblWorkDate.WorkDate = tblWorkDateAttendance.WorkDateWHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND((tblWorkDateAttendance.WorkDate)<Date()) AND((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND((tblWorkDate.HolidayFlag)=False));~~~~~What gives?

View Replies !   View Related

---

Can’t Understand The Query Execution Interval, For Every One Criteria It Takes Extra 60 Seconds. Help Please?,
Can€™t understand the Query execution interval, for every one criteria it takes extra 60 seconds. Help please?,  







Query A

SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN

(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL

AND T2.sd='20060817') AND sd='20060817'

--AND STOCKNAME like 'A%'

ORDER BY D_CUM desc;

The above query take 2 seconds to return result







Query B

SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN

(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL

AND T2.sd='20060817') AND sd='20060817' AND STOCKNAME like 'A%' ORDER BY D_CUM desc

This query same as above but " AND STOCKNAME like 'A%'" Criteria only added but it takes 01 minute 43 seconds to return result. why it is like that where i need to adjust the query i have no idea. please help

View Replies !   View Related

---

SQL Select Query Need For Following Criteria. Please Help, Retrieve Records With Independent Price And Its Total Volume Per Min
Time       Price   Volume
090048       510      6749
090122       510      2101
090135       510      1000
090204       505      2840
090213       505      220
090222       505      1260
090232       505      850
090242       505      200
090253       510      1200
090313       510      570
090343       510      250
090353       510      160
 
Criteria
Retrieve records with independent price and its total volume per minute
 
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4
WHERE sd='20060717' AND serial='0455'
GROUP BY SUBSTRING(st,1,4),d_price,l_cum
 
Result of the above query: -
Time  Price Volume                 
0900    510     6749
0901    510     1000
0901    510     2101
0902    505     200
0902    505     220
0902    505     850
0902    505    1260
0902    505    2840
0902    510    1200
0903    510    160
0903    510    250
0903    510    570
 
 
THE FOLLOWING RESULT STILL NOT A TOTAL FOR A MINUTE]
E.G
 
0901    510            1000
             +
0901    510            2101
            =         
0901    510            3101 <- I NEED THIS
 
Can any one advice or give me tips over this. Please
 

View Replies !   View Related

---

Visual Studio 2005 Automatically Sets SSIS Project As Startup Project
I have a Visual Studio 2005 solution which contains a Sql Server Integration Services (SSIS) project.
 
In this solution I have explicitly set a Web application project as startup project, but whenever I edit a DTS package within the SSIS project, VS automatically sets the SSIS project as startup project and the package I edit as startup object. 
 
Needless to say, this may cause some really unwanted incidents when running the solution - thinking that you're about to run the Web application project (that was explicitly set as startup project), but instead, you run the edited package in the SSIS project.
 
Is there any way to avoid having the SSIS project automatically setting itself as startup project, any workaround here at all? :)
 
Thanks.

View Replies !   View Related

---

Generate Create Script To Project Option In Database Project In VS2005
When using visual studio 2005 to generate create-scripts (via popup menu item "generate create script to project" ) the script is always getting saved to Create Scripts folder under the solution. We no longer have the option of selecting our own folder. Does anyone know how to get the browse window and select the folder of our choice rather than always scriptiong to Create Scripts folder?

 

 

View Replies !   View Related

---

Can't Create A Setup Project For Project Referencing Microsoft.SqlServer.ManagedDTS
I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package.

I took a SQL Server 2000 dts package and using the SQL Server 3005 legacy tools migrated it so I could still use the package withing SQL 2005 until I can build one using BI/SSIS.

Anyway,I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands:

 Dim oApp As New Application
 Dim oPkg As New Package
  oPkg = oApp.LoadPackage(g_DTSx_Directory & "AOC copy Generic1 CSV to AOC_verify_file_1.dtsx", Nothing)
 Dim oResults As DTSExecResult
 oResults = oPkg.Execute

Ok. That works fine.  Executes without a hitch.  So now I try and create a setup project for this and I use the setup wizard.

During the creation of the setup project I get a message that states:
The following files may have dependencies that cannot be determined automatically.  Please confirm that all dependencies have been added to the project.
               C:windowssystem32msxml6.dll

OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have.

How do I find this out?

Has anyone else created a project like this and experenced the same?

I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.

View Replies !   View Related

---

PSI: How To Update Custom Field Value For A Perticular Project Using PSI(Project Server 2007)
Hi,

 

I want to update value of a custom field for a perticular project in Project Server 2007 using PSI.

 

I have created 5 enterprise  custom fields(A,B,C,D,E)  through PWA/Server Settings.

 

I want to search all Projects on Server. If any project is having value for custom field A then I want to update   rest of the custom fields(B,C,D,E) for that perticular project.

 

I dont know how to do it please help.

 

 

Thanks in Advance

 

Madhukar

 

View Replies !   View Related

---

Business Intelligence Project/Integration Services Project Not Showing Up In Visual Studio 2008
I have visual studio 2005 and sql server 2005 with integration service installed on my machine.  Couple of days ago, I installed visual studio 2008 professional.  When I go to create SSIS project I dont see it in visual studio 2008.   What do I have to do to make it appear in visual studio 2008 so that I can create SSIS projects.
 
Thanks.

View Replies !   View Related

---

Unable To Use Project Professional To Establish Connection To Project Server
I fail to use project professional 2003 to access to the project server 2003 using MSDE 2000 in local area network, following message was shown,

 

Connection failed:


SQLState: '01000' SQL Server Error 1326 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect())
Connection failed:
SQLState '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

 

I have seen these pages with similiar cases but can't help.

http://support.microsoft.com/kb/818047/

http://support.microsoft.com/kb/837653/en-us

 

Can anyone help me?? Thanks a lot!

View Replies !   View Related

---

Create SQL Server Project In VB.NET 2.0 (Database Project)
Hi all  How can create trigger in sql server project in VB.NET 2.0 ?thanks in advance   

View Replies !   View Related

---

Problem In Opening The Project..&&"Make Sure The Application For The Project Type (.dtproj) Is Installed.&&"
Hi,

         I have been working on some SSIS packages for a while now and today while i was working i was trying to create a new connection and in the process there was an error and it said the BIDS has to be closed and i closed it but later when i open BIDS  and try to open my project(.sln)  from the file menu  to work on the half done package it pops up an error which shows the path to my project location on the first line and next statement on the pop up error box says:

 

"Make Sure the application for the project type (.dtproj) is installed."

 

I tried to check some forums in which some suggested to try installing SP1 which i tried but ..i dont know why but the SP1 fails to install (i dont know if its causing problem becoz i already installed SP2 as i had some  other problem before for which the cure was to install SP2).

 

Did anyone here face such a problem before ?

I'd really appreciate if the experts here can tell a cure for this problem.

 

thanks,

Ravi

View Replies !   View Related

---

Debugging SQL CLR Project - SQL Server Project
 

I am using .NET 2.0 and created a SQL Server database project. Just a stored procedure to insert the record in DB

 
Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

Private Shared myConnection As SqlConnection

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub StoredProcedure1()


Dim str As String

myConnection = New SqlConnection("data source=LR-DB002;initial catalog=LibTest;integrated security=SSPI;persist security info=False;packet size=4096")

Dim cmd As New SqlCommand

myConnection.Open()

str = "INSERT INTO [libtest].[dbo].[Test]()VALUES('b')"

cmd.Connection = myConnection

cmd.CommandType = CommandType.Text

cmd.CommandText = str

 

cmd.ExecuteNonQuery()

End Sub

End Class
 
I just want to debug this method. When I insert the breakpoint and click on the run it doesn't stop at the breakpoint.
 
Do I need to change some settings ?
Do I need to install addtional component?
 
Pl suggest

View Replies !   View Related

---

COPY/PASTE From Project To Project
Howdy again...

Do I need to re-create a new package from scratch after doing what I did?
It seems I've made what is probably a typical newbie mistake: I used COPY/PASTE
to
consolidate containers from two packages into a single new project/package.

Now my new package generates multiple duplicate errors (23 of each...) whenever I open it:



Warning loading LoadBillingData.dtsx: Process configuration failed to set the
destination at the package path of "PackageData Flow Task to Load
Profiles, Versions, and
Transactions.EventHandlers[OnError].Variables[System::Propagate].Properties[Namespace]".
This occurs when attempting to set the destination property or variable fails.
Check the destination property or variable. 

 

Error loading LoadBillingData.dtsx: Changing the name of a variable failed
because it is a system variable. System variables are read-only. 



What happened:



new guy (goes without saying...) creates:
 
01.  PROJECT A
02.  add PACKAGE
A to PROJECT A
03.  add some
containers, figger out what the heck is going on, get containers working
04.  create
PROJECT B
05.  create
PACKAGE B
06.  add
containers, they work fine
07.  whoops!
Want to consolidate the packages. Unix guy naively expects Bill has made MS
Visual Studio like all windows products (of which newbie is aware€¦):
08.  create
PROJECT C
09.  create
PACKAGE C
10.  <ctrl
a> PACKAGE A
11.  <crtl
c> PACKAGE A
12.  <ctrl
v> PACKAGE C
13.  <ctrl
a> PACKAGE B
14.  <crtl
c> PACKAGE B
15.  <ctrl
v> PACKAGE C
16.  copy Flat
File Connection Managers
17.  copy
Database Connection Manager
18.  drag the
arrows around, fix the flat file sources, renew the column maps, etc
19.  whoops! the
connections all look like a registry number! Fix the OLE DB Destinations,
Execute SQL Task containers by selecting the DB Connection Manager copied from
PROJECT A

20.  successfully
execute PROJECT C/PACKAGE C
21.  save all
22.  exit
23.  start MS
Visual Studio
24.  Open
Solution
25.  ERRORS!!!



Is this fixable?





Thanks,
Bill

View Replies !   View Related

---

Use Report Server Project To Connect To Report Model Project Data Source View?
Hello,
I've created a Report Model Project that can be used by Report Builder to generate ad-hoc reports.  I'm trying to create a connection string in my Report Server Project that points to the Report Model Project data source view. 
 
All I can do is create a regular datasource, which bypasses the metadata contained in the Data Source View.
 
Basically I want my Report Server Project and my Report Builder reports to leverage the same metadata.  Is this possible?  If so how do I get the connection string?
 
Thanks!!
-Matt

View Replies !   View Related

---

Convert A SQLExpress File Based DB Project To SQLExpress Server DB Project
Hi All,I've been struggling with this for hours...Could someone please advise me on how to convert my current File based SQL Server Express website to a Server based SQL Express one.Particularly interested in what I need to do in the SQL Express management tool, changes I need to make the projecvt itself and changes needed to get IIS to understand things have been changed.Thanks,Martin.

View Replies !   View Related

---

Query With Another Query Input Parameter
Dear Friends,

I have a long query with an input parameter. I want this input parameter be all teh values returned from another query.

 

SELECT DIR FROM DIRECCAO

         BIG QUERY with DIR input parameter.

 

How can I do?

Thanks.

        

 

View Replies !   View Related

---

Need To Add A Parameter To Query
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
SelectMethod="GetAllArticlesByCatID" TypeName="articlesTableAdapters.tblArticleTableAdapter">
<SelectParameters>
<asp:QueryStringParameter Name="articlecatid" QueryStringField="catid" Type="Int32" />
what goes here?
 
</SelectParameters>
</asp:ObjectDataSource>
I'm using this to populate some templates I'm using.
I need to add a parameter for the query:
SELECT     tblArticle.ArticleCatID, tblArticle.ArticleID, tblArticle.ArticleText, tblArticle.ArticleTitle, tblArticle.Author, tblArticle.ShortDesc, tblArticle.WebSiteID,                       tblWebSite.SiteURL, tblWebSite.PageRankFROM         tblArticle INNER JOIN                      tblWebSite ON tblArticle.WebSiteID = tblWebSite.WebSiteIDWHERE     (tblArticle.ArticleCatID = @articlecatid) and tblArticle.Active=@active
This is from a tableadapter. How to I get the True value passed into this thing?  When an article is initially saved, the active value is set to false until the article is reviewed. Once everything is ok, then the status is set to actve.  So, although everything is sitting there in the table for the article, the directory will not pull it up with other search results already approved.

View Replies !   View Related

---

Query Parameter
I have to create a stored procedure where the criteria is: "All", specific value and, all except one value so far this is what I have:@Status varchar (50)  -- as parameter from a dropdown boxDECLARE @NewStatus varchar(50)
SET @NewStatus =
CASE
WHEN @Status ='All' AND @Status <> 'All but closed' THEN '%'
WHEN @Status <>'All' AND @Status <> 'All but closed' THEN @Status
END

and in the stored procedure

.........

WHERE Status like @NewStatus  I am a little confused as to how could I return all values except those that have  the value "Closed" Thanks. 

View Replies !   View Related

---

Parameter Query
Hi,The following parameter query SQL statement on a sql analyzer gives theerror message given below.Select * from contact where cus_cust_no = ?[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax errorThanks*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related

---

Query: IIf And Parameter
Hi,Is it possible to create a query with an IIf Function but using a parameter in the expression?This is what I'm trying to do, except it doesn't work:SELECT Related_Area_Id, Course_Code, Course_Public_Title, Course_Level, Course_WBLTTG_OnlyFROM dbo.CourseWHERE (Related_Area_Id = @CurriculumArea) AND (Course_Level = @CourseLevel) AND (Course_WBLTTG_Only = IIf(@Apprenticeship = True, 1, 0)) Are there any other solutions?Thanks

View Replies !   View Related

---

Query Table With Same Parameter
Im struggling with this, I think its probably quite simple. I have a table that looks like this:



RecordId
PictureId
KeywordId

111
212
313
421
522
623
725
817
932
1044
 
I want to be able to run a query for a distinct listing of PictureIds that have KeywordId=1 and Keyword=2. When trying a simple query like:select PictureId from PicturesKeywords where (keywordid=1 and keywordid=2) group by PictureId
It obviously returns nothing. What am I doing wrong?

View Replies !   View Related

---

Use Results From First Query As Parameter In Second
Hello,
I want to select all the customerIDs where an email address exists. Easy: select customerid from customers where emailaddress = @emailaddress
Now I want to use the resulted customerIDs from the above query as a parameter to select all the email addresses with emailstatus equal to 3.
How do I create this type of while statement in a stored procedure? I prefer to not create a temporary table.
My idea was to do it like this:
select emailaddress from emailaddresseswhere emailstatus = 3 and customerid = (select customerid from customers where emailaddress = @emailaddress)
This doesn't seems to work. I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
thanks

View Replies !   View Related

---

Query Builder: How To Do A LIKE Parameter
hi,i can do"select * from products where name = @name" kind of statements in query builder but"select * from products where name LIKE @name" dosen't work!" any ideas?  i'm using sql server express. thanks 

View Replies !   View Related

---

Need Help With Parameter Update Query
I am new the asp.net.
I have written the following code and cannot get it to read from my text boxes to assign the values to the parameters.  When I use a fixed value in a string variable and set the parameter to it, I can make it work, but not from the text boxes.
Any help is greatly appreciated.  Thanks
 
Sub btnUpdate_Click(ByVal s As Object, ByVal e As EventArgs)


' Connect to DB
Dim conInventory As SqlConnection
Dim cmdUpdate As SqlCommand
Dim strCommand As String
Dim test As String
test = "hello" 'my attempt to test the sql parameter
strCommand = ""

conInventory = New SqlConnection("server=server;uid=id;pwd=pwd;database=db")


cmdUpdate = New SqlCommand("Update [tw48_inventory] Set [Date]=@Date", conInventory)
cmdUpdate.Parameters.Add(New SqlParameter("@Date", SqlDbType.VarChar, 50))
cmdUpdate.Parameters("@Date").Value = test
'cmdUpdate.Parameters.Add(New SqlParameter("@Date", test)) 'works with constant string value
conInventory.Open()
cmdUpdate.ExecuteNonQuery()

conInventory.Close()


End Sub
Sub Page_Load()
' Connect to DB
Dim conInventory As SqlConnection
Dim cmdSelect As SqlCommand
Dim dtrReader As SqlDataReader

conInventory = New SqlConnection("server=server;uid=id;pwd=pwd;database=db")
conInventory.Open()

cmdSelect = New SqlCommand("Select * From tw48_inventory", conInventory)
dtrReader = cmdSelect.ExecuteReader

dtrReader.Read()

txtDate.Text = dtrReader("Date")
txtHDP.Text = dtrReader("HDP")
txtMDP.Text = dtrReader("MDP")
txtDTPA.Text = dtrReader("DTPA")
txtMAA.Text = dtrReader("MAA")
txtHepato.Text = dtrReader("Hepato")
txtKinevac.Text = dtrReader("Kinevac")
txtSuCo.Text = dtrReader("SuCo")
txtRBC.Text = dtrReader("RBC")
txtCardiolite.Text = dtrReader("Cardiolite")
txtVent.Text = dtrReader("Vent")
txtPaper.Text = dtrReader("Paper")
txtBlackInk.Text = dtrReader("Black_Ink")
txtBlueInk.Text = dtrReader("Blue_Ink")
txtYellowInk.Text = dtrReader("Yellow_Ink")
txtRedInk.Text = dtrReader("Red_Ink")
txtSharps.Text = dtrReader("Sharps")
txtBilling.Text = dtrReader("Billing")
txtEvac.Text = dtrReader("Evac")
txt5_8cc.Text = dtrReader("cc5_8")
txt20cc.Text = dtrReader("cc20")
txtMAG3.Text = dtrReader("MAG3")
txtOther.Text = dtrReader("Other")
dtrReader.Close()
conInventory.Close()

'txtDate.Focus()


End Sub

</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>EW 48' Inventory Listing</title>

<link id="Link1" type="text/css" rel="stylesheet" href="~/styles.css" runat="server" />
</head>
<body class="inventory">
<form id="inventory" runat="server">
<div>
<h2>EW 48' Inventory</h2>

<hr class="inventory"/>

<br />

<table width="70%">
<tr>
<td>
<asp:Label ID="lblDate" runat="server" Text="Date: " CssClass="inventory" />
</td>
<td>
<asp:TextBox ID="txtDate" runat="server" width="70" TabIndex="1" />
</td>
<td colspan="6">&nbsp;</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblHDP" runat="server" Text="HDP: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtHDP" runat="server" Width="50" TabIndex="2"/>
</td>

<td class="spacer">&nbsp;</td>

<td>
<asp:Label ID="lblMDP" runat="server" Text="MDP: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtMDP" runat="server" Width="50" TabIndex="3"/>
</td>

<td class="spacer">&nbsp;</td>

<td>
<asp:Label ID="lblDTPA" runat="server" Text="DTPA: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtDTPA" runat="server" Width="50" TabIndex="4"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblMAA" runat="server" Text="MAA: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtMAA" runat="server" Width="50" TabIndex="5"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblHepato" runat="server" Text="Hepato: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtHepato" runat="server" Width="50" TabIndex="6"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblKinevac" runat="server" Text="Kinevac: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtKinevac" runat="server" Width="50" TabIndex="7"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblSuCo" runat="server" Text="SuCo: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtSuCo" runat="server" Width="50" TabIndex="8"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblRBC" runat="server" Text="RBC: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtRBC" runat="server" Width="50" TabIndex="9"/>
</td>
<td>&nbsp;</td>
<td>
<asp:Label ID="lblCardiolite" runat="server" Text="Cardiolite: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtCardiolite" runat="server" Width="50" TabIndex="10"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblVent" runat="server" Text="Vent: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtVent" runat="server" Width="50" TabIndex="11"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblPaper" runat="server" Text="Paper: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtPaper" runat="server" Width="50" TabIndex="12"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblBlackInk" runat="server" Text="Black Ink: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtBlackInk" runat="server" Width="50" TabIndex="13"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblBlueInk" runat="server" Text="Blue Ink: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtBlueInk" runat="server" Width="50" TabIndex="14"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblYellowInk" runat="server" Text="Yellow Ink: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtYellowInk" runat="server" Width="50" TabIndex="15"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblRedInk" runat="server" Text="RedInk: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtRedInk" runat="server" Width="50" TabIndex="16"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblSharps" runat="server" Text="Sharps: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtSharps" runat="server" Width="50" TabIndex="17"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblBilling" runat="server" Text="Billing: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtBilling" runat="server" Width="50" TabIndex="18"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblEvac" runat="server" Text="Evac: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtEvac" runat="server" Width="50" TabIndex="19"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lbl5_8cc" runat="server" Text="5.8 cc: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txt5_8cc" runat="server" Width="50" TabIndex="20"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lbl20cc" runat="server" Text="20 cc: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txt20cc" runat="server" Width="50" TabIndex="21"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblMAG3" runat="server" Text="MAG3: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtMAG3" runat="server" Width="50" TabIndex="22"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblOther" runat="server" Text="Other Needs: " CssClass="inventory"/>
</td>
<td colspan="7">
<asp:TextBox id="txtOther" runat="server" Width="540"
TextMode="multiLine" Height="50" TabIndex="23"/>
</td>
</tr>
</table>

<br />
<hr class="inventory"/>

<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />
</div>
</form>
</body>
</html>

View Replies !   View Related

---

Optional Parameter In Sql Query
Hi All,I have a stored proc which looks like this.Create ....(@id int,@ud int,@td int=0)if @td=0select bkah from asdf where id=@id and ud=@udelseselect bkah from asdf where id=@id and ud=@ud and td=@td---------------------------------I am wondering if i could replace if condition with the following lineselect bkah from asdf where id=@id and ud=@udand ( @td<>0 and td>@td )IS sql server 2000 smart enough not to use the td>@td in the query if@td is 0Thanks all

View Replies !   View Related

---

Parameter Query Issues
Hi all I am trying to create a parameter in sql getting an error message dont know why?/

View Replies !   View Related

---

SQL Parameter Query && Wildcard
I just migrated a stand alone MSAccess2003 .mdb to an .adp Access Data Project. In the former .mdb I had a parameter query to search for a portion of a 17 character part number, primarily the 2 grouping of 4 digits as in (00-0000-XXXX-0-00). In Access the query was written as

LIKE “*”&[Enter a portion of the part number]&”*”

Then we ported to a web based portal using MSAccess’s DAP (data access pages) and I was told due to ADO vs DAO the query had to be rewritten using the % symbol as in

LIKE “%”&[Enter a portion of the part number]&”%”

So far so good. However with the migration to a SQL Server and ANSI-92 compatible wildcard characters this has changed. The new query looks like this

LIKE @Enter_a_portion_of_the_part_number +n’%’

The problem I am having is the prompt for data entry works but the wildcard will only return matching values from the first digit place holder. Some of the books I have read said to use the Under Score character to move the search criteria to the section I want to search which would be starting with the 9th character (00-0000-XXXX-0-00) as in

LIKE @Enter_a_portion_of_the_part_number +n’________%

However the Under Score only works with Alpha Characters and not Numeric.

So how do I write a SQL Server Query to perform like it did in Access?

View Replies !   View Related

---

Pass Through Query With Parameter???
I have the following function that is run when a person
enters a value, "Cell", in an access form. I am trying to
execute the pass through query in SQL Server but i keep
getting the following message:

"The microsoft jet database engine cannot find the input
table or query 'CellDefs'. Make sure it exists and its name
is spelt correctly."


There is nothing wrong with the table name.
I cant understand what the problem is ?????

Can anyone tell me how to execute a query that passes
a parameter from access to Sql server.

Thanks,
Ruaidhri



Public Function GetData(Cell As String)
Dim dbsCurrent As Database
Dim qd1 As QueryDef
Dim SQL As String
Dim rs As Recordset
Dim qdf As QueryDef
Set dbsCurrent = CurrentDb

Set qd1 = dbsCurrent.CreateQueryDef("")
With qd1
.Connect = "ODBC;DSN=MISChief;SERVER=Janeway;UID=sa;PWD=;DATA BASE=MIS"
SQL = ""
SQL = SQL + "INSERT INTO TableX SELECT A.Name AS CellCode, C.FinderNumber"
SQL = SQL + "FROM (CellDefs AS A INNER JOIN Calls AS B ON "
SQL = SQL + "A.CellDef_id = B.CellDef_id) INNER JOIN Finders AS C ON "
SQL = SQL + "B.Finders_id = C.Finders_id "
SQL = SQL + "WHERE A.Name LIKE '" & Cell & "' "
SQL = SQL + "ORDER BY C.FinderNumber"
.ReturnsRecords = False
.ODBCTimeout = 2400

dbsCurrent.Execute (SQL)
End With
dbsCurrent.Close
End Function

View Replies !   View Related

---

Passing Parameter To MDX Query
Hi,
 

I am passing Pool Name,From Date and Two date to this quey.I want to change the requirement  a bit.
I want to select From Date in following query as Last date of Privious Month.Any help on this??
 
 
Following query works fine
 
 

="Select crossjoin(union(except([From OTS STATUS].[OTS_STATUS].members,[From OTS STATUS].[OTS_STATUS].[All]),[From OTS STATUS].[OTS_STATUS].[All]), [measures].[Loan count] ) on 1,union(except([To OTS STATUS].[OTS_STATUS].members,[to OTS STATUS].[OTS_STATUS].[All]),{[To OTS STATUS].[OTS_STATUS].[All],[To OTS STATUS].[OTS_STATUS].[flat]}) on 0 from [Roll Rate Matrix] where (" &Parameters!FromDate.Value & "," &Parameters!ToDate.Value & "," &Parameters!Pool.Value & ")"
 
I want to get my FromDate  as

DATEADD(dd,-(DAY(Parameters!FromDate.Value)),Parameters!FromDate.Value)
 
But as soon as I replace From Date parameter with above line it says End Of statement Expected.
 
Any help on this is appriciated.
 
-Thanks,
Digs

View Replies !   View Related

---

Multi Value Parameter In Db2 Query
hi,

i'm trying to perform a query against a db2 database like this:

SELECT ... FROM ... WHERE (field IN (?))

Then i let reporting services pass the parameter to the report. When i try to preview the report, i get the following error:

An error occurred during local report processing,
An error has occured during report processing,
Cannot add multi value query parameter '?' for data set ... because it is not supported by the data extension

But when i type the query like this

SELECT ... FROM ... WHERE (field IN ('value1','value2'))

it executes flawlessly.

I am using the IBM ole db driver for db2 if that matters

Can anyone help me?

View Replies !   View Related

---

Query Parameter Question
Hello:

We're using RS2005 and I have the following question:

I have two report parameters that populated by a query within RS2005 and that works fine. Based on what I selected for the two report parameters I need to use these values to retrieve my Client's Name and paste on top of report in the header section of the report. I'm new at working with RS2005 but it appears to be hanging for some reason.

This is what I do to attempt to get the Client's Name. I created two individual datasets and populate with a query within RS2005. I created a third dataset and create parameters @PCMR, @PACCTNUM in the edit dataset section. I use parameters @PCMR and @PACCTNUM in WHERE clause of SQL statement in third dataset. I hope what I'm saying is making sense.

Best regards 

View Replies !   View Related

---

Date Query Parameter
Hi There,

I've only just starting working with RSS2005. I've started designing basic reports that pulls info from our HEAT DB (incident/change logging system).

One of the report requirements is to be able to pull reports for calls that were logged for specific day/date, using the calllog.recvddate field in the HEAT DB.

What i would like to know is how do i do this ? i.e. how do i setup the date query parameter so that the user can select a date from the calender picker that returns all calls logged for the selected date ? How do i link the date parameter to the calllog.recdvddate field ?

Thanks,

RSS2005 NOOB

View Replies !   View Related

---

How Do I Send RS Parameter To XML Query?
I Have this web service method:

[WebMethod]

public DataSet EeoReport(DateTime start, DateTime end)

{}

I have this XML query that sends blanks to start and end and properly gets my data.

<Query>
<ElementPath IgnoreNamespaces="True">
    EeoReportResponse {}/
    EeoReportResult/diffgram{}/
    DataSetName {}/TableName {Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8}
</ElementPath>

<SoapAction>http://services.mydomain.com/EeoReport</SoapAction>
    <Method Namespace="http://services.mydomain.com"
        Name="EeoReport">
    </Method>
</Query>

How do i add RS parameters to this query to represent start and end in the above webmethod...?

View Replies !   View Related

---

From Query - Report Parameter
What needs to be done to allow a user to input data in a "From query report parameter"?
 
The "From query" option was used to show all possible selections the user can do but what if the user would now like to select for partial key...i.e.: like '%tss%'
 
I would like to avoid the Multi-Value parameter option.
 
NOTE: The report is done through Microsoft Visual Studio / RDL.
 
Thanks,

View Replies !   View Related

---

MDX Query With Parameter Using OleDb?
I posted this question on the Analysis Services forum ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087288&SiteID=1 ) and Irina asked me to post it over here to get an answer.

I'm trying to run an MDX query against SSAS2005 with a parameter. It works fine with ADOMD.NET, but I can't get it to work using OleDb. (I have to use OleDb because of limitations of the calling application.) Is this possible?

The following code works:
//using Microsoft.AnalysisServices.AdomdClient;

string MDX = "with member [Measures].[Test] as Str(@Param1) "
 + "SELECT [Measures].[Test] on 0, "
 + "[Product].[Category].[Category].Members on 1 "
 + "from [Adventure Works]";

AdomdConnection conn = new AdomdConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");
conn.Open();
AdomdCommand cmd = new AdomdCommand(MDX, conn);
cmd.Parameters.Add("Param1", "abcde");
System.Data.DataSet ds = new System.Data.DataSet();
AdomdDataAdapter adp = new AdomdDataAdapter(cmd);
adp.Fill(ds);
Console.WriteLine(ds.Tables[0].Rows[0][1]);
conn.Close();

The following code fails:
//using System.Data.OleDb;
string MDX = "with member [Measures].[Test] as Str(@Param1) "
 + "SELECT [Measures].[Test] on 0, "
 + "[Product].[Category].[Category].Members on 1 "
 + "from [Adventure Works]";
OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");
conn.Open();
OleDbCommand cmd = new OleDbCommand(MDX, conn);
cmd.Parameters.AddWithValue("Param1", "abcde"); //changing it to @Param1 doesn't help
System.Data.DataSet ds = new System.Data.DataSet();
OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
adp.Fill(ds); //produces error: "The following system error occurred:  The parameter is incorrect. . Error Code = 0x80070057, External Code = 0x00000000:."
Console.WriteLine(ds.Tables[0].Rows[0][1]);
conn.Close();

View Replies !   View Related

---

Function Help: Parameter Query
What SQL Function Criteria string replaces [forms]![myForm].[myField]?

I have a function that I want to pass criteria to from a drop down list.   I tried using the same Access string in the Function but it does not work.

View Replies !   View Related

---

DTS Package - Need Help W/ Parameter Query
I am trying to get a DTS package to work w/ an ActiveX box to prompt user for a Date. I am pulling data out of a table into an Excel spreadsheet. The date keyed by user gets passed into parameter ? in query. I have assigned a global variable to package also. The DTS package executes fine but no row appear in the Excel spreadsheet. I'm guessing that the date keyed in input box does not match date from database for some reason. I have tried multiple ways of inputing date in input box and have tried different case/convert statements in query. I know the dates are there because I can hard code in the date as '2005-10-03' and it works. Just can't get it work by feeding in user input into where clause. Does anyone know what I'm missing.

I have a much more complex query but here's the simple one I'm using just to try to get it to work.


select consumerid,
convert(char(10),servicedatefrom, 101) 'Date of Service',
convert(char(10),updatedate, 101) 'Update Date'
from tbl_cs_sv_service
where convert(char(10),updatedate, 101) = ?


Here's the ActiveX script to prompt user for date input

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()

dim tamdate
tamdate = inputbox ("Enter Date")
msgbox ("You entered: " & tamdate)

Main = DTSStepScriptResult_ExecuteTask

End Function


Any advice would be wonderful as I'm pulling my hair out at this point.

Thanks much......wisa64

View Replies !   View Related

---