Query Table With Same Parameter
Mar 4, 2008
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 12 Replies
ADVERTISEMENT
Mar 12, 2008
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!
View 3 Replies
View Related
Aug 8, 2002
i want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.
for example i want to use
select p1 = sp_diff d1,d2 from table1
sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value
View 1 Replies
View Related
Nov 19, 2014
I'm using SS 2012.
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".
The input table can have several thousand rows.
View 9 Replies
View Related
Aug 4, 2015
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)
[code]....
View 9 Replies
View Related
Oct 18, 2006
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 4 Replies
View Related
Mar 2, 2007
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 2 Replies
View Related
Mar 23, 2008
<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 2 Replies
View Related
Jul 11, 2007
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 2 Replies
View Related
Aug 28, 2014
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.
SELECT
table.name,
table.surname,
table.nick
[code]....
View 4 Replies
View Related
Jul 20, 2005
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 2 Replies
View Related
Aug 9, 2006
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"> </td>
</tr>
<tr>
<td colspan="8"> </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"> </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"> </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"> </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> </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> </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"> </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> </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> </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"> </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> </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> </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"> </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> </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> </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"> </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> </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> </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"> </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> </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> </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"> </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 1 Replies
View Related
Nov 2, 2006
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 3 Replies
View Related
May 13, 2007
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 4 Replies
View Related
Dec 13, 2000
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 1 Replies
View Related
Mar 22, 2006
Hi all I am trying to create a parameter in sql getting an error message dont know why?/
View 6 Replies
View Related
Sep 22, 2006
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 2 Replies
View Related
Oct 5, 2005
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 1 Replies
View Related
Mar 28, 2008
Hi all,
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.
pls.help..me and thanks in advance.
View 2 Replies
View Related
Nov 6, 2005
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 3 Replies
View Related
Mar 14, 2007
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 3 Replies
View Related
Mar 19, 2007
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 5 Replies
View Related
Jan 19, 2007
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 4 Replies
View Related
Apr 26, 2006
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 1 Replies
View Related
Nov 28, 2007
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 2 Replies
View Related
Feb 19, 2007
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 3 Replies
View Related
Jan 9, 2007
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 1 Replies
View Related
Jan 11, 2008
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
thanks all
View 4 Replies
View Related
Jan 4, 2002
How do I pass a table name as a parameter to a stored procedure in SQL 7?
Thanks
View 1 Replies
View Related
Jul 31, 2001
When I execute this sp like this:
exec AddNewSKUsToExistingStyle 'table1'
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
View 3 Replies
View Related
Dec 16, 2004
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)
CREATE TABLE [dbo].[TABLE_' + @UserName + '] (......
View 2 Replies
View Related
Aug 8, 2006
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?
View 7 Replies
View Related
Oct 13, 2006
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));
}
//Contact Details
cmd.Parameters.Add(new SqlParameter("@Re_Address1", Address1TextBox.Text));
if (Address2TextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Address2", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Address2", Address2TextBox.Text));
}
if (Address3TextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Address3", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Address3", Address3TextBox.Text));
}
cmd.Parameters.Add(new SqlParameter("@Re_City", CityTextBox.Text));
cmd.Parameters.Add(new SqlParameter("@Re_Postcode", PostcodeTextBox.Text));
cmd.Parameters.Add(new SqlParameter("@Re_Country", CountryDropDownList.SelectedItem.Text));
if (HomeTelephoneTextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Homephone", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Homephone", HomeTelephoneTextBox.Text));
}
if (MobileTelephoneTextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Mobile", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Mobile", MobileTelephoneTextBox.Text));
}
//Job Preferences
for (int i = 0; i < JobTypeCheckBoxList.Items.Count; i++)
{
if (JobTypeCheckBoxList.Items[i].Text == "Permanent" && JobTypeCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Per", 1));
}
else if (JobTypeCheckBoxList.Items[i].Text == "Permanent" && JobTypeCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Per", 0));
}
if (JobTypeCheckBoxList.Items[i].Text == "Temporary" && JobTypeCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Temp", 1));
}
else if (JobTypeCheckBoxList.Items[i].Text == "Temporary" && JobTypeCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Temp", 0));
}
if (JobTypeCheckBoxList.Items[i].Text == "Contract" && JobTypeCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Con", 1));
}
else if (JobTypeCheckBoxList.Items[i].Text == "Contract" && JobTypeCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Con", 0));
}
}
//Hours and Sector
for (int i = 0; i < HoursCheckBoxList.Items.Count; i++)
{
if (HoursCheckBoxList.Items[i].Text == "FullTime" && HoursCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Full", 1));
}
else if (HoursCheckBoxList.Items[i].Text == "FullTime" && HoursCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Full", 0));
}
if (HoursCheckBoxList.Items[i].Text == "PartTime" && HoursCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Part", 1));
}
else if (HoursCheckBoxList.Items[i].Text == "PartTime" && HoursCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Part", 0));
}
}
cmd.Parameters.Add(new SqlParameter("@Re_Sector", SectorDropDownList.SelectedItem.Text));
cn.Open();
cmd.ExecuteNonQuery();
thanks,vijay
View 2 Replies
View Related
