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?
I have 2 tables (table1KKK, table2KKK), and want to run the same query on them by using parameter with the value "1" or "2". Is it possible to use that parameter as a part of the queried table name? Something like (only for demonstration €“ doesn€™t work): Select * From table +myParameter + kkk Thanks in advance!
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest] ( -- Add the parameters for the function here @Source_Tbl sysname ) RETURNS TABLE AS RETURN ( select @Source_Tbl.yr from @Source_Tbl )
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12 Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4 Incorrect syntax near the keyword 'table'. Msg 137, Level 15, State 2, Procedure fnTest, Line 12 Must declare the scalar variable "@Source_Tbl".
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate] /* Input Parameters */ @TableName Varchar(20), @Dist Varchar(20) AS Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar(2000)
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.
</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.
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
I am working with a Report Designer where I am using SQL to get data which user wants to see so I am using parameters.In my SQL database, name and surname columns can not be NULL, but nick can be NULL.The problem is with 3rd parameter ${Nick}. I need to do some condition like this: if user lefts 3rd parameter empty, it should select people where table.nick is NULL, otherwise it should select people by entered nick. But I do not know how to rework my 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!
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
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
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
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
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?
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()
I've a query which i need to run as Parameter Driven, where SQL asks me for an input let's say for a Date in a query. is this possible in SQL SERVER like in Oracle, if so can anybody suggest me where do i look at.
here's my quer.
SELECT * FROM TABLE WHERE DATE > '1/01/2008'
instead of 1/01/2008 Sql Prompts me to enter my own Date and display the result.
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
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.
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
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.
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 ?
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();
Hello I have aspx page and I need to insert 15 rows to a table so am thinking of passing them as table to my sp but I dont know how to use table as parameter in the sql2005 so PLs help me
I am getting the following error: (even though I have table1 in db).
Server: Msg 208, Level 16, State 1, Procedure AddNewSKUsToExistingStyleTest, Line 6 Invalid object name '@table_nm'.
Any ideas on how to use table name as parameter?? Thanks.
script for sp: ----------------------------------------------------------------------------------------- CREATE Procedure AddNewSKUsToExistingStyle
@table_nm varchar (20)
as Set nocount on /* Step No. 1 */ Update site_product_xref Set Site_Product_End_dt = '12/31/09' --select sk.eb_style_id, sk.eb_sku_id, spx.Site_Product_Id from [@table_nm] c inner join Book b On (c.book_id = b.book_id) Inner Join Size_Color SZ On (SZ.EB_Style_Id = c.EB_Style_Id) Inner Join EB_SKU SK On (C.EB_Style_Id = SK.EB_Style_Id and sz.color_id = sk.color_id and sz.size_id = sk.size_id) Inner Join site_product_xref spx On (sk.eb_sku_id = spx.eb_sku_id and spx.Site_id = 1) where spx.Site_Product_start_dt < b.live_dt
I have to create a tabla with a different name each time. I have a stored procedure to do so like the following. Step 1 works fine but not step 2. The table name cannot include a parameter. How can I solve this? Any help on this will be appreciated.
CREATE PROCEDURE NewTable @UserName as varchar AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLE_' + @UserName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Hi, I have a little problem with a SqlDataSource. A table in my database has an ID collumn, which is an integer auto-increment. The datasource has a SelectQuery with an [ID] parameter, which it retrieves from the querystring. I want the DataSource to return all rows when no querystring parameter is passed, and when an ID is given via the querystring, a 'WHERE' clause in the SelectQuery would return only certain records. However, suppose I have the following query: SELECT * FROM SomeTable WHERE ([ID] = @ID) When I bind @ID to the QueryString with '*' as DefaultValue, it throws an exception saying it can't convert '*' to a smallint, which makes sense. I haven't got a clue how to solve this, except for using different DataSources based on the presence of the querystring parameter. Any idea's?
Hi all, I am using the below parameterized query and get an error while executing it....can anyone please spot the error. Any help will be appreciated. I have gone cross-eyed now looking at it all day. The error I get it isParameterized Query '(@Re_UK_Eligible nvarchar(4000),@Re_Aus_Eligible nvarchar(33),@R' expects parameter @Re_JobType_Temp, which was not supplied. sqlStmt = "UPDATE Re_Users SET Re_UK_Eligible=@Re_UK_Eligible,Re_Aus_Eligible=@Re_Aus_Eligible,Re_Can_Eligible=@Re_Can_Eligible,Re_USA_Eligible=@Re_USA_Eligible,Re_Address1=@Re_Address1,Re_Address2=@Re_Address2,Re_Address3=@Re_Address3,Re_City=@Re_City,Re_Postcode=@Re_Postcode,Re_Country=@Re_Country,Re_Homephone=@Re_Homephone,Re_Mobile=@Re_Mobile,Re_JobType_Per=@Re_JobType_Per,Re_JobType_Temp=@Re_JobType_Temp,Re_JobType_Con=@Re_JobType_Con,Re_Hours_Full=@Re_Hours_Full,Re_Hours_Part=@Re_Hours_Part,Re_Sector=@Re_Sector,Re_StepTwoDone=1 WHERE Re_UserCount=" + Session["ReUserIdentity"]; cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ReConnectionString"].ConnectionString); cmd = new SqlCommand(sqlStmt, cn); cmd.CommandType = CommandType.Text;
//Insert UK if (chkUK.Checked == false) { cmd.Parameters.Add(new SqlParameter("@Re_UK_Eligible", DBNull.Value)); } if ((chkUK.Checked == true) && (UKRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_UK_Eligible", UKRadioButtonList.SelectedItem.Text)); }
//Insert AUS if (chkAUS.Checked == false) { cmd.Parameters.Add(new SqlParameter("@Re_Aus_Eligible", DBNull.Value)); } if ((chkAUS.Checked == true) && (AUSRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_Aus_Eligible", AUSRadioButtonList.SelectedItem.Text)); }
//Insert CAN if ((chkCAN.Checked == false)) { cmd.Parameters.Add(new SqlParameter("@Re_Can_Eligible", DBNull.Value)); } if ((chkCAN.Checked == true) && (CANRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_Can_Eligible", CANRadioButtonList.SelectedItem.Text)); }
//Insert USA if (chkUSA.Checked == false) { cmd.Parameters.Add(new SqlParameter("@Re_USA_Eligible", DBNull.Value)); } if ((chkUSA.Checked == true) && (USARadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_USA_Eligible", USARadioButtonList.SelectedItem.Text)); }
