How To Select From A List Of Values
Jun 21, 2006
Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example:
selectcmd = "Select * from mytable where myfile =" ???????
cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values:
A
B
C
D
E
I would like to build a query like that:
selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “
How can I do that?
Thanks.jsn
View 6 Replies
ADVERTISEMENT
Jul 12, 2012
I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.
I tried using Case with no success.
For example:
Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'
So the 'where' line would produce one row, however, the overall statement would produce two.
ID TimeStamp Tag
--------------------------------
1 2012-12-12 rowA
1 2012-12-12 rowB
View 4 Replies
View Related
Jan 11, 2007
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<SelectParameters>
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
</SelectParameters>
</asp:SqlDataSource>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Else
Label1.Text = Item.Value + ","
End If
End If
Next
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here? Thanks!
View 4 Replies
View Related
Jun 15, 2015
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
View 8 Replies
View Related
Mar 21, 2008
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)
View 2 Replies
View Related
Oct 14, 2015
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
View 2 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Apr 28, 2007
Dear Readers of this post,
I have a dropdownlist that is populated with an sqldatasource as follows:
SELECT [Project_ID], [Title] FROM [Projects] WHERE [Username] = @Username AND Hide ='false'
The Datavalue vield of the DDL is populated with the [Title].
When the user submits the form [including the value of the of the drop down list] i want to be able to add the Project ID and the Title Values into another database table.
any ideas
Sat
View 3 Replies
View Related
Nov 22, 2005
For a controlParameter in the ASP code, how do I retreive the selectedValue of the drop down list?Would this work?
<asp:controlParameter Name="InvoiceNumber" Type="String" ControlID="ddAdSize.Value" />
View 1 Replies
View Related
May 16, 2006
While experimenting I got this message:
[Flat File Source [212]] Error: The "output column "Column 0" (237)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 0" (237)" specifies failure on error. An error occurred on the specified object of the specified component"
I note under BIDS, Properties tab, that the Flat File is assigned an ID of 212. But is the number (237) and ID or an error value? How can I list all "ID's" used in a package?
TIA,
Barker
View 1 Replies
View Related
Oct 31, 2006
Hello I have a newbie question. If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen
View 4 Replies
View Related
Apr 20, 2006
I need to be able to order the results of a SELECT query by the order of a specific list of key IDs provided in the WHERE IN statement.
So my query looks like:
SELECT *
FROM TableName
WHERE KeyID IN (3,104,43,22,345)
ORDER BY ????
I need the results returned in the order provided in the IN list (3,104,43,22,345).
Thanks in advance!
pr0
View 5 Replies
View Related
Sep 27, 2006
I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion
Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.
IE (ticks, #, :, -,)
I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.
I know this is a nightmare but it is the contraint I am working under.
View 14 Replies
View Related
Sep 7, 2012
My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2
then run an sql statement and should return you the following
Period
1
2
4
12
31
View 3 Replies
View Related
Apr 16, 2014
I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.
I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.
View 7 Replies
View Related
May 22, 2008
Hi, does anyone know a SQL statement that will take two DATETIME values and list all minutes in between them like so:
START VALUE: 5/22/2008 10:00
END VALUE: 5/22/2008 10:10
RESULT:
5/22/2008 10:00
5/22/2008 10:01
5/22/2008 10:02
5/22/2008 10:03
5/22/2008 10:04
5/22/2008 10:05
5/22/2008 10:06
5/22/2008 10:07
5/22/2008 10:08
5/22/2008 10:09
5/22/2008 10:10
Thanks!
View 8 Replies
View Related
Nov 3, 2014
select pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 24420
select distinct pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 23528
It seems there are some rows with same birthdate and operationdate.
I want to get the list of rows(with all columns) that has got same birthdate and operationdate
View 1 Replies
View Related
Feb 3, 2015
We're using MS Access 2010 as a frontend to an SQL server back-end. From Access, I can run read-only queries and pass-through queries. I'd like to use a local Access table as part of a join to server data. As a non-pass-through query, it's slow; about 5 min to join to 2 other tables.
I could use VBA to turn the local table into part of a pass-through query, with a large in() statement, or several where x='' ors but the local table may have 50000 entries in it. Is there a good or right way to pass this data in the query if I don't have write access to the server?
View 2 Replies
View Related
Mar 24, 2015
SELECT
c.CustomerId,
c.CustomerName,
co.OrderKey,
co.OrderNumber,
co.CustomerPO,
co.DueDate,
[code]....
sb.CarrierId data is Sec.
Carriers.ExcludeCarriers I have value as 'Sec,QB' list of values.
I am trying to display sb.Carrierid that is not equal to Carriers.excludeCarriers list of values. It fails now and displays sec values in resultset.
View 1 Replies
View Related
Jul 20, 2005
Can anybody help on this question?When I select 3 values from the drop down list box, how can I insert into atable?Please help
View 3 Replies
View Related
Jul 20, 2005
Hi,I like to have a SQL script which could update a table to set one attributeto a random value picked from a given list. The prototyping code is asbelow:select @value_list = ('John', 'David', 'Mathew', 'Paul')....update EMP set emp_name = @random_namewhere ...where random name is randomly got from the given list. Of course there willbe a cursor so that different row may have different random name but notnecessary unique. Also the attribute and the list could be any valid commonSQL data typesThanks!Ximing
View 1 Replies
View Related
Aug 10, 2015
I have [TableAccount.AccountType] field which can store these values:
Value
C
S
E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
View 4 Replies
View Related
Aug 4, 2007
I have the following dataset:
State ZIP Homes Schools
WA 98007 2000 4
WA 98052 3000 5
WA 98079 2000 3
Now if I have set the group by expression on State but as display if I want to show it as €œ[98007, 98052, 98079]€? how can I accomplish this.
My report needs to show:
State Homes Schools
[98007, 98052, 98079] 7000 12
Any help will be greatly appreciated
View 6 Replies
View Related
May 8, 2008
Hello,
I have the following issue:
I have the following statement into a function:
select a,b form T where c IN @parameter
t is the table
c is datatype= integer
@parameter is a input parameter in the function, the @parameter contains more values and passed as a string.
Running the statement above I got an error due to conversion type.
How can I pass a list of parameters in the @parameter variable to make the statement works?
Thank in advance.
View 4 Replies
View Related
May 19, 2006
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?
View 3 Replies
View Related
Jan 19, 2006
I have several tables that are related with the same primary key that I need to make a consolidated list of values for. The schema of each table is similar but some have more or less fields than others. I want to make a list of all table names, field names, and the value of that field. I can select all tables included from sysobjects. The existence of the key is probable but not guaranteed in all tables. The basic table format is: Table1------ID Field1 Field2 Field31 0.0 4.1 3.92 0.5 1.3 0.23 7.1 8.8 9.3 Table2------ID Field10 0.41 3.32 2.73 5.7 Table3------ID Field1 Field22 2.4 4.63 4.3 8.1 Format of the result set:(specifying ID = 2) Table_Name Table_Field ValueTable1 Field1 0.5Table1 Field2 1.3Table1 Field3 0.2Table2 Field1 2.7Table3 Field1 2.4Table3 Field2 4.6
View 8 Replies
View Related
Apr 11, 2012
How can I store multiple selected values (from a dropdown list) in mysql database?
PHP Code:
<form method="post" action="storedetails.php">    Research Interest:<br/>   Â
<select multiple="yes" size="6" name="interest[]">   Â
<option id="webt" value="webt">Advanced Web Tech1nologies</option>   Â
<option id="mobhum" value="mobhum">Mobile and Humanoid Robots</option>   Â
[code]...
View 5 Replies
View Related
Feb 10, 2014
I have two tables, a dates table and a values table. They are joined on the date column.The date table has a range, say from today as far as 20 days from now, incrementing by 1 day each row.The values table may have a row for a day, and may not. If the day has a value I want to display that value.If the day does not have a value in the values table I want to display the last known value.
I think this can be done with windowing functions in a set based manner but have not been able to work it out. I have done it procedurally but im not happy with that at all, and really want to see if this is possible in a set based manner.Below is some simplified code to allow testing with sample data.
create table DimDate
(
DateCol date
)
create table TotalsData
(
DateCol date
[code]....
View 6 Replies
View Related
Feb 24, 2015
I have the following tables:
tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)
I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.
View 4 Replies
View Related
Aug 3, 2015
how would I compare a list of concrete values?
---table with items
SET NOCOUNT ON;
DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10))
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 15,'ITEM 6'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 16,'ITEM 7'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 17,'ITEM 8'
SELECT * FROM @items
-- table with categories
SET NOCOUNT ON;
DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10))
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE'
SELECT * FROM @categories
--table where categories are assigned to master categories
SET NOCOUNT ON;
DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT)
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107
SELECT * FROM @master_categories
-- items-categories assignment table
SET NOCOUNT ON;
DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT)
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11
[code]....
So now I need to query the table @t4 in and to determine the items that are assigned to category 'WHITE' in master category 1 and to 'CIRCLE' in master category 2.The important thing is to return items that are assigned solely to 'WHITE' in master cat 1 and solely to 'CIRCLE' in master cat 2.In the above example it would be only the ITEM 1 (id=10) that is returned:
1. ITEM 2 (id=11) is not returned because it has the assignment to category 'SQUARE' in master cat 2 additionally
2. ITEM 3 (id=12) is not returned because it has the assignment to category 'BLACK' in master cat 1 additionally
3. ITEM 4 (id=13) is not returned as it does not have assignment to category 'CIRCLE' in master cat 2 but only to 'WHITE' in master cat 1
3. ITEM 5 (id=14) is not returned as it does not have assignment to category 'WHITE' in master cat 1 but only to 'CIRCLE' in master cat 2
View 3 Replies
View Related
Jan 14, 2014
Trying to run a simple query below
Update CustomerID
set CustomerLevelID='5'
where CustomerID='97000,57700,560046,462334,454453,522444,539743'
When I try this I get
Conversion failed when converting the varchar value
Is it possible to use comma delimited values in a where command?
View 3 Replies
View Related
Aug 13, 2007
I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)
and then there are tables inside this list. the report will be displayed based on the values of this custom list.
i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..
what shud i do to select the whole grp??
thanks!
View 4 Replies
View Related
Jan 10, 2007
How to Populate multiple values for Parameters in reporting
services.i am talking about the Default values for a parameter
As i am a fresher in this field , i would like to know much about it
View 6 Replies
View Related
