Select rows where column equals one of the string values in array

Question

I'm trying to get relevant rows into datatable, i want only rows that have column value equal to one of the values in other sql table, that's my code:

try
{
     sc.Open();
     for (int i = 0; i < queryTokens.Length; i++)
     {
          cmd = new SqlCommand("INSERT INTO QueryIndex (Term) Values ('" + queryTokens[i] + "')", sc);
          cmd.ExecuteNonQuery();
     }
     cmd = new SqlCommand("SELECT * FROM TermsIndex Where Term EXIST (Select Term From QueryIndex)");
     SqlDataAdapter da = neuw SqlDataAdapter(cmd);
     da.Fill(tempTable);
     da.Dispose();
     sc.Close();
}
catch (Exception e)
{
     Console.WriteLine(e.ToString());
}

the first part of the insert going well i'm sure, but in the second part, i hope you all understand what i'm trying to do, i know i have syntax errors here so please correct me, and i'm getting this exception message: {"Value cannot be null.\r\nParameter name: dataTable"} Null exception..

this is my sql create table:

CREATE TABLE [dbo].[TermsIndex]
(
    [ID] VARCHAR(35) NOT NULL,
    [Term] VARCHAR(35)  NOT NULL,
    [Frequency] int NOT NULL,
    [Offset] VARCHAR(MAX) NOT NULL,
)


CREATE TABLE [dbo].[QueryIndex]
(
    [Term] VARCHAR(MAX) NOT NULL,
)

so why i'm getting that null error? and anyway i'm creating a table just to be able to compare it's entries with column from the first one, i already had it string array, any chance to be able to compare that column value to changing number of strings (tokens) and return row results if it were equal to one of those strings in sql command? or any more elegance way to get the wanted result?

Hamlet Hakobyan · Accepted Answer · 2013-01-20 21:20:59Z

1

I assume you have DataTable tempTable; in your code. You mast initialize it.

DataTable tempTable = new DataTable();

answered Jan 20, 2013 at 21:20

Hamlet Hakobyan

33.5k6 gold badges55 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Fleming · Accepted Answer · 2013-01-20 21:16:04Z

0

There are a few ways you can do the query:

SELECT DISTINCT T.* FROM TermsIndex T
INNER JOIN QueryIndex Q ON T.Term = Q.Term;

SELECT T.* FROM TermsIndex T
WHERE T.Term IN (SELECT Q.Term FROM QueryIndex Q);

SELECT T.* FROM TermsIndex T
WHERE EXISTS(SELECT Q.Term FROM QueryIndex Q WHERE Q.Term = T.Term);

SELECT T.* FROM TermsIndex T
WHERE T.Term IN('term1', 'term2', 'term3');

Note: There's no dataTable in your posted code.

answered Jan 20, 2013 at 21:16

Paul Fleming

24.6k8 gold badges79 silver badges113 bronze badges

2 Comments

k-man Over a year ago

thanks, nice and elegance but i'm still getting the null exception , and i have datatable, tempTable and it's class member. thanks.

k-man Over a year ago

my bad i didn't notice i never initialized that table!

Collectives™ on Stack Overflow

Select rows where column equals one of the string values in array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related