SQL IN

Hi all,

Using ASP.NET, SQL SVR Express 2008

I want to use IN to select records that have one of a list of values like 
this:

SELECT * FROM MyTable WHERE MyField IN ("Value1","Value2","Value3")

MyField is a nvarchar(MAX)

My query has a palceholder for the parameter, so it looks like this:

SELECT * FROM MyTable WHERE MyField IN (@MyList)

I create my command object ans set the parameter:

myCommand.Parameters.AddWithValue("MyList", myList)

where myList = "Value1,Value2,Value3"

I get no values returned even though there are matches.

If I were to build this by hand I would surround each ValueN in quotes 
(like I did above), but since I am using the AddValueWith I thought I did 
not need delimiters, but if I do it by hand like that I get an error

Invalid column name near Value1

How can I make this work?

Thanks,
kpg
0
kpg
9/10/2010 8:03:05 PM
sqlserver.server 1327 articles. 0 followers. Follow

3 Replies
1232 Views

Similar Articles

[PageSpeed] 25

kpg <ipost@thereforeiam> wrote in
news:Xns9DEF9926AD454ipostthereforeiam@207.46.248.16: 

OK...I got the 'by hand' part to work by delimiting the values in single 
quote, not double quotes.

I'm usre I'v used double quotes in the past...oh well.

My questions still reamins, however.

I want to use the @MyList parameter and set the parmaeter in code rahter
than build the query by hand.

thanks,
0
kpg
9/10/2010 8:16:13 PM
kpg (ipost@thereforeiam) writes:
> Using ASP.NET, SQL SVR Express 2008
> 
> I want to use IN to select records that have one of a list of values like 
> this:
> 
> SELECT * FROM MyTable WHERE MyField IN ("Value1","Value2","Value3")
> 
> MyField is a nvarchar(MAX)
> 
> My query has a palceholder for the parameter, so it looks like this:
> 
> SELECT * FROM MyTable WHERE MyField IN (@MyList)
> 
> I create my command object ans set the parameter:
> 
> myCommand.Parameters.AddWithValue("MyList", myList)
> 
> where myList = "Value1,Value2,Value3"
> 
> I get no values returned even though there are matches.

It you have a row with the value "Value1,Value2,Value3", there is a 
match, else there isn't. 

There is article on my web site that describes the way to go:
http://www.sommarskog.se/arrays-in-sql-2008.html
-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/10/2010 8:36:02 PM
On Fri, 10 Sep 2010 13:03:05 -0700, kpg <ipost@thereforeiam> wrote:

>Hi all,
>
>Using ASP.NET, SQL SVR Express 2008
>
>I want to use IN to select records that have one of a list of values like 
>this:
>
>SELECT * FROM MyTable WHERE MyField IN ("Value1","Value2","Value3")
>
>MyField is a nvarchar(MAX)
>
>My query has a palceholder for the parameter, so it looks like this:
>
>SELECT * FROM MyTable WHERE MyField IN (@MyList)
>
>I create my command object ans set the parameter:
>
>myCommand.Parameters.AddWithValue("MyList", myList)
>
>where myList = "Value1,Value2,Value3"
>
>I get no values returned even though there are matches.
>
>If I were to build this by hand I would surround each ValueN in quotes 
>(like I did above), but since I am using the AddValueWith I thought I did 
>not need delimiters, but if I do it by hand like that I get an error
>
>Invalid column name near Value1
>
>How can I make this work?
>
>Thanks,
>kpg


Hi 

You could look at table valued parameters or dynamic SQL
as two possible methods for more information see
http://www.sommarskog.se/arrays-in-sql-2008.html

John
0
John
9/10/2010 8:46:23 PM
Reply:

Similar Artilces:

SQL Error when running Paid Trx Removal
When running Paid Trx Removal, we get the following error: Microsoft ODBCSQLServerDriver Cannon insert duplicate key row in object RM30101 with unique index AK4RM30101. Then another error: The stored procedue RMPaidTransRemoval returned the following results DBMS 2601, Great Plains: 0. I ran check links and that didn't fix it. What can I do? Is there a query that I need to run to delete this dup. key? Any help would be great, this removal/report needs to be run asap. Thank you. Beth Beth, The problem here is that the RM30101 has duplicate key for one your transactions. If you kn...

What's the next release after SQL Server 2008 R2 & when?
I am confused as to the release of SQL Server 2008 R2. Is this the supposed SQL Server 2010 release? or some sort of stop gap release? What's the next planned release, SQL Server 2011, or 2012 or....? any advise would be great. thanks, GrampaJoe When it comes to confidential information about unannounced Microsoft release dates, there are two types of people. Those who don't know and can only make (possibly educated) guesses (like me) and people who have access to the confidential information and are not allowed to tell you. The next release of SQL Server is code ...

Create XML From access, Fill SQL 2000 Table
Are there .NET commands to create a new table from an XML Schema. I want to create a program which converts an access table to SQL 2000 using XML. Regards ...

Best SQL Backup Solution.
Hello All, New to the forum and look forward to getting and giving help. I am in the process of researching some sql backup solutions. Few thing I was looking for was; Object level restoration, High Compression, Centralized Management and lastly fast backup and restoration. I have found three solution that meet these needs. I would like your thoughts on them. Have you used any of them, recommend, dont recommend, etc. Or is there something esle that you recommend? They are listed below: 1. Litespeed SQL Backup 2. Idera SQL Safe 3. Redgate SQL Backup Pro Any input is appreciated. M...

Access/jet to SQL Server
We have a client who has an urgent need for one of our "packaged" apps to be used with full SQL Server (not just MSDE) and not Jet and as we know nothing in this area we have some questions and any help is appreciated: 1. Our app uses Access Security with an mdw file for development which is not distributed as the app is accessed by the user's own system.mdw file as we have a couple of tables blocked (i.e. hold registration info etc) and only accessed by RWOP queries in the FE. Can this still be done with SQL? 2. Some of our tables start with "Usys" and it seems the wiz...

Query not working for SQL Server
I am trying to convert an Access backend database to SQL Server with an ODBC connection and linked tables from the Access frontend to the SQL Server backend. The SQL statements remain Jet SQL. I have a SELECT statement with a Datediff function that works in Access but not in SQL Server. The following works in the existing Access: SELECT DateDiff("n",startdateandtime+Nz(lunchtime,0),enddateandtime)/60) It does not work with SQL Server. The problem is with lunchtime. I tried removing lunchtime from the SQL Server version and that much works: SELECT DateDiff(...

Can I disable the Microsoft Search service which is installed with SQL 2000?
I am trying to troubleshoot the problem in my other post "dfrgifc.exe & dfrgntfs.exe starting for some reason". Can I disable the Microsoft Search service without having a problem to see if that service is the cause? -- Thanks! ...

Is it a SQL 2008 or VB 2008 problem? I don't know
Hello, I have created an application with Visual Basic2005 Standard Edition 2 years ago. The application imports CSV files in the database. The application is connecting to the database that I made it with SQL Server 2005. This is the connection string: Data Source=XXX; Initial Catalog=YYY; Integrated Security=SSPI; Pooling=true; The application uses the TransactionScope class this way with no problem: Using Scope As New Transactions.TransactionScope(Transactions.TransactionScopeOption.RequiresNew) ... Scope.Complete() End Using In the transaction, the app...

Update Table Field Value with SQL Query
Good day, I am seeking help to resolve a query that I want to run from a class Module in access 2003. I first designed the query by linking the two tables and set the wanted criteria and then copied the query from SQL View and pasted it in the Class Model which is as follows: UPDATE Tbl_Risk_LocalProfiles LEFT JOIN Tbl_Risk_LocalProfiles_ResponsiblePersons ON Tbl_Risk_LocalProfiles.ProfileID = Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID SET Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus = Tbl_Risk_LocalProfiles.Frame_ProfileStatus WHERE (((Tbl_Risk_Lo...

Access 2007 SQL Stmt returns the incorrect value ?
Hello Access 2007 Google Groupmeisters, need your help and support on this one.... My Table: Field1 Field2 Field3 Field4 MPIID MPIID 07499750 MEDREC 06 000014180210 MEDREC OHMC 679558 MEDREC OHSC 0385633 MEDREC OSO OSO-679558 MPIID MPIID 08931110 MEDREC OSO OSO-1203758 The Select Stmt: SELECT Table1.Field2, Min(Table1.Field3) AS MinOfField3 FROM Table1 WHERE Table1.Field2 <> 'MPIID' GROUP BY Table1.Field2; The result of the stmt above: Field2 MinOfField3 06 000014180210 OHMC 67...

Deadlocks on SQL Server and Workflow
Has anyone ever been able to create (import) or reassign 200 leads/accounts and send an email to each account owner? I get deadlock errors from my sql server and I need to determine if the problem is with SQL server or the CRM code calling the transactions that deadlock.... Thanks! are you sure your workflow rule isn't going in an infinite loop? -Gary "Kristina Ledford" <noemails@noemails.com> wrote in message news:O9SGvU$lDHA.3320@tk2msftngp13.phx.gbl... > Has anyone ever been able to create (import) or reassign 200 leads/accounts > and send an email to each a...

SQL Service wont Auto Start with Mcafee McShield enabled
On certain customer machines, the SQL Server service won't AutoStart when McAfee McShield (on access scan) is enabled. I have tried specifically excluding the SQL Binn and Data directories, but still no luck. I have to actually disable the McShield Service, or make SQL dependent on the McShield Service so it starts up after McShield. The customer is using SQL 2005 Express and McAfee VirusScan Enterprise + AntiSpyware Enterprise 8.5.0i with Patch 8" on XP Pro. Is this a known issue with McShield? I posted on a McAfee community board as well but no responses. Thank...

External Data from SQL Server
I have a worksheet that is importing data from SQL Server. MS Query is configured to execute a stored procedure. Sometimes when I refresh the query with new parameters I lose formatting and formulas. It is as if Excel is inserting extra columns. the cells with the formulas are moved to the right and lose their reference. In MS Query and SMSS everything looks fine, but when the data is brought into Excel it goes bonkers. I should point out that some of the data is fine, then others are not. Any suggestions on what to look for? Thanks I've done this before and did...

Are barcodes stored? List of all tables in SQL?
Are the barcodes for all the ILCs stored? If so, on what table? Is there a way to get a list of all the tables in the database? Or are the barcodes generated on the fly? Thanks. The ILC is usually the barcode number. Alias can also be used. Barcode type is stored in the item table. It is only needed if you are printing barcodes "Jonathan Smith" <JonathanSmith@discussions.microsoft.com> wrote in message news:F8082ADD-B3F7-4F9C-BAF8-0532B2787068@microsoft.com... > Are the barcodes for all the ILCs stored? If so, on what table? > > Is there a way to get a list ...

SQL Trigger
I would like to write a record to up to our legacy database (linked server to mainframe in SQL 2005) when we post the receipt of an item on a PO. I started off by creating an Insert trigger on POP10110, but i am having some problems. Can I use VBA and write this record after clicking the "Post" button. If so, I am not sure how to make sure the VBA code executes after the Dynamics code. Can anyone point me in the right direction. Thanks -- JoeS You will not be able to put a trigger on POP10110 for receipts. After all, POP10110 are the purchase order lines. You will want t...

eConnect on named sql instance
I'm trying to install eConnect 8.0 using SQL Server 2000 sp3 in a named instance. Does anyone knows if this is possible. I have been able to install eConnect but it cannot acces the database it says the user doesn't have the privileges to access the database. I checked the user privileges and it has them as the documentation ask for. I even tried using "administrator" and "sa" Regards. Hi, Hopefully I am understanding your issue, it sounds like the install went fine but now you are trying to call the eConnect API? If that is the case, check the COM+ Packa...

insert xml data into sql server?
i need to insert data from an xml file into sql server table. the xml file contains only one record. how to insert the data? how to map the names of the fields in the xml file and the table? thank you in advance. Assuming you're using SQL Server 2000, you could use SQLXML 3.0 (http://www.microsoft.com/downloads/details.aspx?FamilyId=4C8033A9-CF10-4E22 -8004-477098A407AC&displaylang=en). There's a couple of ways you could do this. The first is to annotate a schema and use the SQLXMLBULKLOAD component - this is a COM-based component that can be used to import XML into SQL Server ...

SQL Questions
Hi, i am from Spain, excuse my Inglish I have a Windows Server 2003 R2 x64 1) can I install SQL 2000 Standard Edition? 2) Can I downgrade SQL 2008 Standard Edition licences to SQL 2000 Enterprise? 3) Need Windows 2003 R2 x64 SQL Enterprise Edition or can i install the SQL Standard? Thanks for all and excuse again my Inglish ZIDAC (ZIDAC@discussions.microsoft.com) writes: > I have a Windows Server 2003 R2 x64 > > 1) can I install SQL 2000 Standard Edition? Yes. You will get a message saying that SQL 2000 RTM is not supported on Win2003, but that you need to insta...

Cant use Import-Module in Powershell in SQL Server 2008 SQL Agent
Yo Geeks, SQL 2008 SP1 CU 5 Enterprise x64 Windows Server 2008 R2 Datacenter Wrote a basic Powershell script to get Membership of Certains Groups from AD using the ActiveDirectory Module new in 2008 r2/ Windows , and insert into a SQL table required for some old dodgy app. Tested outside of SQL Agent - all fine. Want to use as SQL Agent Job / Powershell step. Created a job and got message like "Import-Module is invalid command" Thought SQL might be running Powershell 1.0 , but $Host run from SQL Agent says 2.0 From same SQL Agent job do a "Get-Comm...

CRM3.0: How the SQL Reports handled, as RDL or RDLC?
Hi folks, my question is in the subject, if i use the SQL Reporting Engine for Reports, how CRM handle the Reports, in which Format, RDL or RDLC? thank you in advance, taxi Hello Taxi, According to the Microsoft CRM 3.0 SDK, you will use .rdl files to upload reports. Please refer to the topic "Report Writers Guide" in the SDK. Best regards, Merijn van Mourik "taxi007" wrote: > Hi folks, > my question is in the subject, if i use the SQL Reporting Engine for > Reports, how CRM handle the Reports, in which Format, RDL or RDLC? > > thank you in adv...

Data Upload from Excel to SQL Server
I have the following code which does the following: 1. Deletes all rows having a value of "0" in column C 2. Uploads the data in Row 2 to my SQL Server What I need for the code to do is to upload all rows on the worksheet -- how would I modify the code to upload all rows, or iterate on each row having data? Here's my code: ======================== Private Sub DeleteBlankRows() Dim lastrow As Long Dim r As Long lastrow = Range("C" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 T...

Consequences to setting the db compatibility level to SQL Server 2000
We have a Sql Server 2005 DB. For whatever reason, the compatibility level (e.g. via sp_dbcmptlevel) is set to 80 (which is Sql Server 2000). What are the consequences of that? Is there a performance penalty for my apps because of the compatibility level? Regards Frank No, it does not relate to performance, it does to the new features such as ROW_NUMBER() function that won't work "Frank Rizzo" <none@none.net> wrote in message news:O7CsZS8nKHA.4648@TK2MSFTNGP06.phx.gbl... > We have a Sql Server 2005 DB. For whatever reason, the compatibility level...

SQL 2000 and MSDE
Hi I have a client that is running SQL Server 2000 on their sales peoples laptops. They also want to install the MSCRM SFO client on these laptops Will there be a conflict between SQL Server 2000 and the SFO client database? Thanks No problem, it is fine to run both. Dave "Christopher" <Christopher@discussions.microsoft.com> wrote in message news:E98C2AD2-6DD0-4783-9891-43A06F233A87@microsoft.com... > Hi > > I have a client that is running SQL Server 2000 on their sales peoples laptops. They also want to install the MSCRM SFO client on these laptops > > W...

Working with SQL Server CE under vista (UAC)
I have a .net 3.5 project written with visual studio 2008. I also have a setup project for this application. The project includes a SQL Server CE database, and needs to read from and write to this database. The database file (sdf) is included in the application project as type "content". The setup project sends "content files" to the users application folder. This works under XP, but not under vista. When the program is run, an error occurs, saying that permission is denied on the database file. What is the solution to this for vista? Do I need to change...

US-MD-Silver Spring: SQL VMWare TV Network DBA
<http://www.filcro.com/SQL-Database.html> � Installing, configuring, troubleshooting and maintaining MS SQL environments � Experience working with VMWare (ESX 4.0 preferred) � Strong SQL / scripting skills � This position is approximately 75% Database Administration and 25% Systems � Strong Active Directory and Windows Administration skills � Experience with Cisco Pix Firewalls and VPN configuration � Must have excellent customer service skills and the ability to work with various levels of users � Must have strong problem solving skills and the ability to prioritize heavy ...