WHERE IN() with parameters

[Guest]

Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters in
queries with WHERE IN() clauses.

Given the code:
....
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
....

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN() clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Jeff Dillon]

What do you mean "for every record ..to get"?

IN behaves as follows:

select fields from table where field in (1, 2, 3)

is equivalent to where (field = 1 or field = 2 or field = 3)

 

[Guest]

What do you mean "for every record ..to get"?
for each record I want ... uhm..

IN behaves as follows:
select fields from table where field in (1, 2, 3)
is equivalent to where (field = 1 or field = 2 or field = 3)

Yes, I do know that.

Thanks for the reply

Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN() clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Jeff Dillon]

Do you mean for each value within the IN clause list? Multiple records
could certainly be returned for where field in (2) for example. Unless the
IN contained discreet primary key values.

Haven't tried it, but I assume you tried a single parameter as a comma
separated list of values?

cmd.Parameters.Add().Value = "1,2,3"

It's also common practice (using server-side solutions) to add these values
to a permanent or #temp table, and perform a join instead of IN.

Or use sp_executesql on a string variable (dynamic sql).

set @str = "select * from mytable where id in (" + myinlist + ")"
sp_executesql @str

Jeff

What do you mean "for every record ..to get"?

for each record I want ... uhm..

IN behaves as follows:
select fields from table where field in (1, 2, 3)
is equivalent to where (field = 1 or field = 2 or field = 3)

Yes, I do know that.

Thanks for the reply

in message news:[email protected]...

Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN() clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Guest]

Hi Jeff,

I was too tired when I wrote the question, so I forgot to give all the
parameters on the Add method.

The complete code listings that works is:
....
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?, ?, ?)";
int ids[] = {1, 3, 7 };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add( "@bar", OleDbType.Integer).Value = ids[ i ];
...
Given you have more than 7 records in the table and 'bar' is the only key
and incrementing from 1.

What I really want to archieve is to avoid the string concatenation I
normally have to do when using queries like this.
In a perfect world, it would be possible to pass the ids array to
Parameters...

Do you mean for each value within the IN clause list? Multiple records
could certainly be returned for where field in (2) for example. Unless the
IN contained discreet primary key values.

Haven't tried it, but I assume you tried a single parameter as a comma
separated list of values?

cmd.Parameters.Add().Value = "1,2,3"

It's also common practice (using server-side solutions) to add these values
to a permanent or #temp table, and perform a join instead of IN.

Or use sp_executesql on a string variable (dynamic sql).

set @str = "select * from mytable where id in (" + myinlist + ")"
sp_executesql @str

Jeff

What do you mean "for every record ..to get"?

for each record I want ... uhm..

IN behaves as follows:
select fields from table where field in (1, 2, 3)
is equivalent to where (field = 1 or field = 2 or field = 3)

Yes, I do know that.

Thanks for the reply

in message Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the
clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Jeff Dillon]

You didn't answer my question

Hi Jeff,

I was too tired when I wrote the question, so I forgot to give all the
parameters on the Add method.

The complete code listings that works is:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?, ?, ?)";
int ids[] = {1, 3, 7 };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add( "@bar", OleDbType.Integer).Value = ids[ i ];
...
Given you have more than 7 records in the table and 'bar' is the only key
and incrementing from 1.

What I really want to archieve is to avoid the string concatenation I
normally have to do when using queries like this.
In a perfect world, it would be possible to pass the ids array to
Parameters...

Do you mean for each value within the IN clause list? Multiple records
could certainly be returned for where field in (2) for example. Unless the
IN contained discreet primary key values.

Haven't tried it, but I assume you tried a single parameter as a comma
separated list of values?

cmd.Parameters.Add().Value = "1,2,3"

It's also common practice (using server-side solutions) to add these values
to a permanent or #temp table, and perform a join instead of IN.

Or use sp_executesql on a string variable (dynamic sql).

set @str = "select * from mytable where id in (" + myinlist + ")"
sp_executesql @str

Jeff

in message news:[email protected]...

wrote
in message Hi all,

Just for fun, I was investigating wheither I could use

OleDbParameters
in

queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the
clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Bernie Yaeger]

Hi Tor,

Actually, I've found that the best way to do this is indeed to pass an
array. Now I know you can't, but you can, in effect, if you create the sp
'on the fly'. What I do is I first delete the sp; then I create a
bulkstring to contain my array - '12345', '34225', '957433', etc. Then I
construct the sp, create it from the vb code, and run it. Below is an
example of one such function I use.

HTH,

Bernie Yaeger
Public Function createa_rsummary(ByVal marraylist As ArrayList, ByVal oconn
As SqlConnection) As Integer

createa_rsummary = 0

Dim i As Integer

Dim bulkstring As String

Dim acount As Integer = 0

For i = 0 To marraylist.Count - 1

acount += 1

bulkstring += Chr(39) & Trim(marraylist(i)) & Chr(39) & ","

If i <> marraylist.Count - 1 Then ' ie, it isn't the last item

If acount > 20 Then

acount = 0

bulkstring += vbCrLf

End If

End If

Next

bulkstring = Mid(bulkstring, 1, bulkstring.Length - 1) & ")"

Dim dcmd As New SqlCommand

dcmd = New SqlCommand("sp_dropsp_createa_rsummary", oconn) ' drop the sp

dcmd.CommandType = CommandType.StoredProcedure

Try

dcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim creationstring As String

creationstring = "CREATE PROCEDURE sp_createa_rsummary AS " _

& "if exists (select * from information_schema.tables where table_name = " _

& "'a_rsumtable')" & vbCrLf & "drop table a_rsumtable" & vbCrLf _

& "select imcacct, pubcode, invnum, inv_dt, brname, " _

& "(case when inv_dt + 31 > getdate() then balance else 0 end) as under31, "
_

& "(case when inv_dt + 61 > getdate() and inv_dt + 31 <= getdate() then
balance else 0 end) as over30, " _

& "(case when inv_dt + 91 > getdate() and inv_dt + 61 <= getdate() then
balance else 0 end) as over60, " _

& "(case when inv_dt + 121 > getdate() and inv_dt + 91 <= getdate() then
balance else 0 end) as over90, " _

& "(case when inv_dt + 121 <= getdate() then balance else 0 end) as over120"
_

& " into a_rsumtable from a_r where imcacct" _

& " in (" & bulkstring & vbCrLf _

& "order by pubcode, imcacct"

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery() ' create the sp

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim ocmd As New SqlCommand

ocmd = New SqlCommand("sp_createa_rsummary", oconn) ' execute the sp

ocmd.CommandType = CommandType.StoredProcedure

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim acmd As New SqlCommand

acmd = New SqlCommand("sp_altera_rsumtable", oconn) ' give new table a pk

acmd.CommandType = CommandType.StoredProcedure

Try

acmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

' we now have a_rsumtable for only the selected accounts

createa_rsummary = 1

End Function

Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the
clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Dave Fancher]

Tor:
To build a parameterized IN clause using a dynamic set of values, you won't
be able to get around the concatenation. If you're concerned about
performance thought you may consider using a StringBuilder to manage the SQL
statement and call its ToString() method when you're ready to use it.

Keep in mind that SQL is a query language, and as such, it is not aware of
complex data types such as arrays. You could acheive a similar effect
through something such as the following snippet. This example uses
StringBuilders to dynamically generate the IN clause.

[Note that I have not tested this and it only serves as an example. You'd
probably want more functionality and overloads than provided by the
BuildInClause() method below if you were to use this in a production
environment though it may work for some solutions.]

public class TestApp()
{
public static void Main()
{
// Code to initialize command and connection...
System.Text.StringBuilder sql = new System.Text.StringBuilder("SELECT
title, txt FROM tblFoo WHERE ");

int ids[] = {1, 3, 7};
sql.Append(BuildInClause(ids, cmd));

cmd.CommandText = sql.ToString();
// Code to execute your command...
}

public static string BuildInClause(int[] values, SqlCommand cmd)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder("IN (");
for(int i=0;i<values.Length;i++)
{
sb.Append("?");
cmd.Parameters.Add(new SqlParameter(String.Format("@param{0}", i),
OleDbType.Int)).Value = values;
if(i!=values.Length-1)
sb.Append(", ");
}
sb.Append(")");

return sb.ToString();
}
}

Hi Jeff,

I was too tired when I wrote the question, so I forgot to give all the
parameters on the Add method.

The complete code listings that works is:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?, ?, ?)";
int ids[] = {1, 3, 7 };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add( "@bar", OleDbType.Integer).Value = ids[ i ];
...
Given you have more than 7 records in the table and 'bar' is the only key
and incrementing from 1.

What I really want to archieve is to avoid the string concatenation I
normally have to do when using queries like this.
In a perfect world, it would be possible to pass the ids array to
Parameters...

Do you mean for each value within the IN clause list? Multiple records
could certainly be returned for where field in (2) for example. Unless
the
IN contained discreet primary key values.

Haven't tried it, but I assume you tried a single parameter as a comma
separated list of values?

cmd.Parameters.Add().Value = "1,2,3"

It's also common practice (using server-side solutions) to add these
values
to a permanent or #temp table, and perform a join instead of IN.

Or use sp_executesql on a string variable (dynamic sql).

set @str = "select * from mytable where id in (" + myinlist + ")"
sp_executesql @str

Jeff

"Tor Martin Halvorsen" <[email protected]>
wrote
in message news:[email protected]...

What do you mean "for every record ..to get"?
for each record I want ... uhm..


IN behaves as follows:
select fields from table where field in (1, 2, 3)
is equivalent to where (field = 1 or field = 2 or field = 3)
Yes, I do know that.

Thanks for the reply



in message Hi all,

Just for fun, I was investigating wheither I could use
OleDbParameters in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation
i
usually use
since i have to figure out how many question-marks are needed in
the
clause.

So is there a more elegant way to use OleDbParameters with IN
clauses?
Obviously passing an array to the parameter won't work, and a
single
question-mark will only use the first parameter.

 

[Dave Fancher]

Yikes!!!
The code you provided is just laced with performance killers. Dropping and
recreating stored procedures means updates to system tables. Additionally,
the SP is only ever being executed once. SPs are always compiled for their
first execution so on top of the hit from updating the system tables, you're
forcing a compile every time the SP executes.

I would strongly suggest reading
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
for some information about using SPs that execute dynamic SQL. Also note
that using the Prepare() method of the SqlCommand class has a similar
effect. Both of these methods avoid the constant updates to the system
tables and will improve the operation of your application.

On another, possibly moot point, this code doesn't apply to Tor's question.
This code is specific to SQL Server while he's asking about the classes
contained in System.Data.OleDb (OleDbCommand, OleDbParameter, etc...) and is
referring to an Access database.

Hi Tor,

Actually, I've found that the best way to do this is indeed to pass an
array. Now I know you can't, but you can, in effect, if you create the sp
'on the fly'. What I do is I first delete the sp; then I create a
bulkstring to contain my array - '12345', '34225', '957433', etc. Then I
construct the sp, create it from the vb code, and run it. Below is an
example of one such function I use.

HTH,

Bernie Yaeger
Public Function createa_rsummary(ByVal marraylist As ArrayList, ByVal
oconn As SqlConnection) As Integer

createa_rsummary = 0

Dim i As Integer

Dim bulkstring As String

Dim acount As Integer = 0

For i = 0 To marraylist.Count - 1

acount += 1

bulkstring += Chr(39) & Trim(marraylist(i)) & Chr(39) & ","

If i <> marraylist.Count - 1 Then ' ie, it isn't the last item

If acount > 20 Then

acount = 0

bulkstring += vbCrLf

End If

End If

Next

bulkstring = Mid(bulkstring, 1, bulkstring.Length - 1) & ")"

Dim dcmd As New SqlCommand

dcmd = New SqlCommand("sp_dropsp_createa_rsummary", oconn) ' drop the sp

dcmd.CommandType = CommandType.StoredProcedure

Try

dcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim creationstring As String

creationstring = "CREATE PROCEDURE sp_createa_rsummary AS " _

& "if exists (select * from information_schema.tables where table_name = "
_

& "'a_rsumtable')" & vbCrLf & "drop table a_rsumtable" & vbCrLf _

& "select imcacct, pubcode, invnum, inv_dt, brname, " _

& "(case when inv_dt + 31 > getdate() then balance else 0 end) as under31,
" _

& "(case when inv_dt + 61 > getdate() and inv_dt + 31 <= getdate() then
balance else 0 end) as over30, " _

& "(case when inv_dt + 91 > getdate() and inv_dt + 61 <= getdate() then
balance else 0 end) as over60, " _

& "(case when inv_dt + 121 > getdate() and inv_dt + 91 <= getdate() then
balance else 0 end) as over90, " _

& "(case when inv_dt + 121 <= getdate() then balance else 0 end) as
over120" _

& " into a_rsumtable from a_r where imcacct" _

& " in (" & bulkstring & vbCrLf _

& "order by pubcode, imcacct"

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery() ' create the sp

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim ocmd As New SqlCommand

ocmd = New SqlCommand("sp_createa_rsummary", oconn) ' execute the sp

ocmd.CommandType = CommandType.StoredProcedure

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim acmd As New SqlCommand

acmd = New SqlCommand("sp_altera_rsumtable", oconn) ' give new table a pk

acmd.CommandType = CommandType.StoredProcedure

Try

acmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

' we now have a_rsumtable for only the selected accounts

createa_rsummary = 1

End Function

"Tor Martin Halvorsen" <[email protected]>
wrote in message

Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the
clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Guest]

Do you mean for each value within the IN clause list?
Yes, I ment for each value in the IN clause list.



Tor Martin

You didn't answer my question

Hi Jeff,

I was too tired when I wrote the question, so I forgot to give all the
parameters on the Add method.

The complete code listings that works is:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?, ?, ?)";
int ids[] = {1, 3, 7 };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add( "@bar", OleDbType.Integer).Value = ids[ i ];
...
Given you have more than 7 records in the table and 'bar' is the only key
and incrementing from 1.

What I really want to archieve is to avoid the string concatenation I
normally have to do when using queries like this.
In a perfect world, it would be possible to pass the ids array to
Parameters...

Do you mean for each value within the IN clause list? Multiple records
could certainly be returned for where field in (2) for example. Unless the
IN contained discreet primary key values.

Haven't tried it, but I assume you tried a single parameter as a comma
separated list of values?

cmd.Parameters.Add().Value = "1,2,3"

It's also common practice (using server-side solutions) to add these values
to a permanent or #temp table, and perform a join instead of IN.

Or use sp_executesql on a string variable (dynamic sql).

set @str = "select * from mytable where id in (" + myinlist + ")"
sp_executesql @str

Jeff

in message What do you mean "for every record ..to get"?
for each record I want ... uhm..


IN behaves as follows:
select fields from table where field in (1, 2, 3)
is equivalent to where (field = 1 or field = 2 or field = 3)
Yes, I do know that.

Thanks for the reply



"Tor Martin Halvorsen"

wrote
in message Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters
in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for
every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the
clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Guest]

Hi Dave,

Tor:
To build a parameterized IN clause using a dynamic set of values, you won't
be able to get around the concatenation. If you're concerned about
performance thought you may consider using a StringBuilder to manage the SQL
statement and call its ToString() method when you're ready to use it.

I am not concernad about the performance at all since working with databases
is rather slow in nature.

Keep in mind that SQL is a query language, and as such, it is not aware of
complex data types such as arrays. You could acheive a similar effect
through something such as the following snippet. This example uses
StringBuilders to dynamically generate the IN clause.

I do know that the SQL itself is not aware of complex datatypes, in fact it
doesn't know of datatypes at all. But the database itself is aware of them,
and when I pass the values as parameters I *thought* it was passed directly
to database-engine.

[Note that I have not tested this and it only serves as an example. You'd
probably want more functionality and overloads than provided by the
BuildInClause() method below if you were to use this in a production
environment though it may work for some solutions.]

<snip>
This is the method I've been using for years.

 

[Frans Bouma [C# MVP]]

Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN() clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

The only other way is to create a subquery to read the values for the
IN clause. That's about it. Otherwise you're stuck with teh string
concatenation, which is not that hard if you use a stringbuilder object,
in there you add the questionmarks and comma's,then add that to the
query string and execute it.

Frans.

 

[Guest]

Hi Frans,

...Otherwise you're stuck with teh string
concatenation, which is not that hard if you use a stringbuilder object,
in there you add the questionmarks and comma's,then add that to the
query string and execute it.

So what it boils down to is that the easiest way is to concatenate the
actual values in the clause?

Thanks for the reply though.

Tor Martin

 

[Bernie Yaeger]

Hi Dave,

Yikes!!!
The code you provided is just laced with performance killers. Dropping
and recreating stored procedures means updates to system tables.

Yes, you're correct. I lose a full second on this! Damn!

the SP is only ever being executed once. SPs are always compiled for
their first execution so on top of the hit from updating the system
tables, you're forcing a compile every time the SP executes.

That's because the sp is different every time. This too costs me, after
all, as you correctly point out, it's not compiled yet. This also can cost
me anywhere from 2 - 10 seconds! Good God!

Dave, I hear this performance nonsense all the time. In the practical
world, a function like this runs in 15 seconds. Give me a break.

Bernie

Yikes!!!
The code you provided is just laced with performance killers. Dropping
and recreating stored procedures means updates to system tables.
Additionally, the SP is only ever being executed once. SPs are always
compiled for their first execution so on top of the hit from updating the
system tables, you're forcing a compile every time the SP executes.

I would strongly suggest reading
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
for some information about using SPs that execute dynamic SQL. Also note
that using the Prepare() method of the SqlCommand class has a similar
effect. Both of these methods avoid the constant updates to the system
tables and will improve the operation of your application.

On another, possibly moot point, this code doesn't apply to Tor's
question. This code is specific to SQL Server while he's asking about the
classes contained in System.Data.OleDb (OleDbCommand, OleDbParameter,
etc...) and is referring to an Access database.

Hi Tor,

Actually, I've found that the best way to do this is indeed to pass an
array. Now I know you can't, but you can, in effect, if you create the
sp 'on the fly'. What I do is I first delete the sp; then I create a
bulkstring to contain my array - '12345', '34225', '957433', etc. Then I
construct the sp, create it from the vb code, and run it. Below is an
example of one such function I use.

HTH,

Bernie Yaeger
Public Function createa_rsummary(ByVal marraylist As ArrayList, ByVal
oconn As SqlConnection) As Integer

createa_rsummary = 0

Dim i As Integer

Dim bulkstring As String

Dim acount As Integer = 0

For i = 0 To marraylist.Count - 1

acount += 1

bulkstring += Chr(39) & Trim(marraylist(i)) & Chr(39) & ","

If i <> marraylist.Count - 1 Then ' ie, it isn't the last item

If acount > 20 Then

acount = 0

bulkstring += vbCrLf

End If

End If

Next

bulkstring = Mid(bulkstring, 1, bulkstring.Length - 1) & ")"

Dim dcmd As New SqlCommand

dcmd = New SqlCommand("sp_dropsp_createa_rsummary", oconn) ' drop the sp

dcmd.CommandType = CommandType.StoredProcedure

Try

dcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim creationstring As String

creationstring = "CREATE PROCEDURE sp_createa_rsummary AS " _

& "if exists (select * from information_schema.tables where table_name =
" _

& "'a_rsumtable')" & vbCrLf & "drop table a_rsumtable" & vbCrLf _

& "select imcacct, pubcode, invnum, inv_dt, brname, " _

& "(case when inv_dt + 31 > getdate() then balance else 0 end) as
under31, " _

& "(case when inv_dt + 61 > getdate() and inv_dt + 31 <= getdate() then
balance else 0 end) as over30, " _

& "(case when inv_dt + 91 > getdate() and inv_dt + 61 <= getdate() then
balance else 0 end) as over60, " _

& "(case when inv_dt + 121 > getdate() and inv_dt + 91 <= getdate() then
balance else 0 end) as over90, " _

& "(case when inv_dt + 121 <= getdate() then balance else 0 end) as
over120" _

& " into a_rsumtable from a_r where imcacct" _

& " in (" & bulkstring & vbCrLf _

& "order by pubcode, imcacct"

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery() ' create the sp

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim ocmd As New SqlCommand

ocmd = New SqlCommand("sp_createa_rsummary", oconn) ' execute the sp

ocmd.CommandType = CommandType.StoredProcedure

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim acmd As New SqlCommand

acmd = New SqlCommand("sp_altera_rsumtable", oconn) ' give new table a pk

acmd.CommandType = CommandType.StoredProcedure

Try

acmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

' we now have a_rsumtable for only the selected accounts

createa_rsummary = 1

End Function

"Tor Martin Halvorsen" <[email protected]>
wrote in message

Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters
in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the
clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Jeff Dillon]

Wow, do you want help or not??

Please reread my previous post, and answer all questions. I don't ask them
to waste my time.

Do you mean for each value within the IN clause list?
Yes, I ment for each value in the IN clause list.



Tor Martin

You didn't answer my question

in message news:[email protected]...

Hi Jeff,

I was too tired when I wrote the question, so I forgot to give all the
parameters on the Add method.

The complete code listings that works is:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?, ?, ?)";
int ids[] = {1, 3, 7 };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add( "@bar", OleDbType.Integer).Value = ids[ i ];
...
Given you have more than 7 records in the table and 'bar' is the only key
and incrementing from 1.

What I really want to archieve is to avoid the string concatenation I
normally have to do when using queries like this.
In a perfect world, it would be possible to pass the ids array to
Parameters...




:

Do you mean for each value within the IN clause list? Multiple records
could certainly be returned for where field in (2) for example.

Unless
the

IN contained discreet primary key values.

Haven't tried it, but I assume you tried a single parameter as a comma
separated list of values?

cmd.Parameters.Add().Value = "1,2,3"

It's also common practice (using server-side solutions) to add these values
to a permanent or #temp table, and perform a join instead of IN.

Or use sp_executesql on a string variable (dynamic sql).

set @str = "select * from mytable where id in (" + myinlist + ")"
sp_executesql @str

Jeff

"Tor Martin Halvorsen"

wrote
in message What do you mean "for every record ..to get"?
for each record I want ... uhm..


IN behaves as follows:
select fields from table where field in (1, 2, 3)
is equivalent to where (field = 1 or field = 2 or field = 3)
Yes, I do know that.

Thanks for the reply



"Tor Martin Halvorsen"

wrote
in message Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters
in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for
every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed

in
the

clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Jeff Dillon]

Unless you try my suggestion from earlier.

Hi Frans,


So what it boils down to is that the easiest way is to concatenate the
actual values in the clause?

Thanks for the reply though.

Tor Martin

 

[Guest]

Do you mean for each value within the IN clause list? Multiple records

could certainly be returned for where field in (2) for example. Unless the
IN contained discreet primary key values.

I'm sorry I wasn't clear enough in my first post, but that is exactly what I
want to do.
yes, I have; heck, that is what I usually do.
The whole thing was about wheiter there was a more 'elegant' way to perform
such a query without string-concatenation.


I have used this approach too , but I'm a bit concerned about the
performance hit if there is any?

.... It was not in any way my intent to "waste" your time...

 

[Guest]

Since when does poor performance equate to good application design (did you
not say that your method is "the best way")? When did good application
design suddenly transform into nonsense?

Perhaps you hear about this "nonsense" "all the time" because you need to
learn some lessons about its importance in application design. In the
"practical world," 15 seconds of unnecessary wait time is needlessly wasted
time (for both the computer and the human) that could have been better spent
elsewhere. I'm not at all arguing that entirely eliminating wait time is
possible but it should always be minimized. (Also, don't forget to include
network latency in determining total wait time.)

Every time this code is executed, both your application and database server
are doing unnecessary work. It is obvious by the tone of your response that
you do not value your users' (see also: customer) time since you have no
problem with making them wait unnecessarily (e.g.: wasting their time)
because of poor application design. If this server is being used by other
applications, you are tying up resources that are probably better utilized by
other processes.

You say that your method forces a recompile because the SP is different
every time but in reality, this method does not force a recopile but rather
forces a compile because it isn't that the SP is different but is a different
SP altogether. (you dropped it, remember?) It may have a common name but it
is a different SP.

By posting this code in response to Tor's question, you [indirectly] claim
that this method allows for a variable parameter list, but your SP isn't
parameterized at all. Tor wanted to find an easy way to parameterize a
dynamically generated IN clause of a query against an Access database.
Completely ignoring the fact that your code is specific to SQL Server (and,
as such, completely irrelevant to the question), where in "CREATE PROCEDURE
sp_createa_rsummary AS ..." is a parameter list supplied? This method
doesn't allow for a variable parameter list at all since you're still relying
solely on string concatenation.

Of course, relying solely on string concatenation rather than an actual
parameterized query opens up potentially larger problems but I'll leave it up
to you to discover what they are...perhaps your code is immune to them
because this is "the best way?"

Another point is the issue of concurrency. On top of the added overhead
from additional executions, suppose you have two users that coincidentally
execute this code within a fraction of a second of each other. User A's
execution has just completed executing the code to create the procedure but
has not yet executed the code to execute it (the SP). While User A's
execution is in the state I just described, what happens when User B's code
executes the drop procedure code?

Since the code is in a try block, the catch code is executed and a message
box is displayed telling the user that the procedure couldn't be found then
continues to execute because you don't have any code to exit the function.
Following the execution path, the SP for altering the table executes and
throws another error which is caught and displays another error message. The
function then assumes that everything worked correctly (which it didn't) and
execution continues, most likely providing the user with incorrect results!

Is it unlikely for this scenario to occur? Sure, but it is conceivable.
This "solution" simply does not scale. As the user base grows, so does the
probability that this problem will surface.

Finally, why not use a view for the common part of the query? All that
you're dynamically generating is the IN clause.

Hi Dave,

Yikes!!!
The code you provided is just laced with performance killers. Dropping
and recreating stored procedures means updates to system tables.

Yes, you're correct. I lose a full second on this! Damn!

the SP is only ever being executed once. SPs are always compiled for
their first execution so on top of the hit from updating the system
tables, you're forcing a compile every time the SP executes.

That's because the sp is different every time. This too costs me, after
all, as you correctly point out, it's not compiled yet. This also can cost
me anywhere from 2 - 10 seconds! Good God!

Dave, I hear this performance nonsense all the time. In the practical
world, a function like this runs in 15 seconds. Give me a break.

Bernie

Yikes!!!
The code you provided is just laced with performance killers. Dropping
and recreating stored procedures means updates to system tables.
Additionally, the SP is only ever being executed once. SPs are always
compiled for their first execution so on top of the hit from updating the
system tables, you're forcing a compile every time the SP executes.

I would strongly suggest reading
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
for some information about using SPs that execute dynamic SQL. Also note
that using the Prepare() method of the SqlCommand class has a similar
effect. Both of these methods avoid the constant updates to the system
tables and will improve the operation of your application.

On another, possibly moot point, this code doesn't apply to Tor's
question. This code is specific to SQL Server while he's asking about the
classes contained in System.Data.OleDb (OleDbCommand, OleDbParameter,
etc...) and is referring to an Access database.

Hi Tor,

Actually, I've found that the best way to do this is indeed to pass an
array. Now I know you can't, but you can, in effect, if you create the
sp 'on the fly'. What I do is I first delete the sp; then I create a
bulkstring to contain my array - '12345', '34225', '957433', etc. Then I
construct the sp, create it from the vb code, and run it. Below is an
example of one such function I use.

HTH,

Bernie Yaeger
Public Function createa_rsummary(ByVal marraylist As ArrayList, ByVal
oconn As SqlConnection) As Integer

createa_rsummary = 0

Dim i As Integer

Dim bulkstring As String

Dim acount As Integer = 0

For i = 0 To marraylist.Count - 1

acount += 1

bulkstring += Chr(39) & Trim(marraylist(i)) & Chr(39) & ","

If i <> marraylist.Count - 1 Then ' ie, it isn't the last item

If acount > 20 Then

acount = 0

bulkstring += vbCrLf

End If

End If

Next

bulkstring = Mid(bulkstring, 1, bulkstring.Length - 1) & ")"

Dim dcmd As New SqlCommand

dcmd = New SqlCommand("sp_dropsp_createa_rsummary", oconn) ' drop the sp

dcmd.CommandType = CommandType.StoredProcedure

Try

dcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim creationstring As String

creationstring = "CREATE PROCEDURE sp_createa_rsummary AS " _

& "if exists (select * from information_schema.tables where table_name =
" _

& "'a_rsumtable')" & vbCrLf & "drop table a_rsumtable" & vbCrLf _

& "select imcacct, pubcode, invnum, inv_dt, brname, " _

& "(case when inv_dt + 31 > getdate() then balance else 0 end) as
under31, " _

& "(case when inv_dt + 61 > getdate() and inv_dt + 31 <= getdate() then
balance else 0 end) as over30, " _

& "(case when inv_dt + 91 > getdate() and inv_dt + 61 <= getdate() then
balance else 0 end) as over60, " _

& "(case when inv_dt + 121 > getdate() and inv_dt + 91 <= getdate() then
balance else 0 end) as over90, " _

& "(case when inv_dt + 121 <= getdate() then balance else 0 end) as
over120" _

& " into a_rsumtable from a_r where imcacct" _

& " in (" & bulkstring & vbCrLf _

& "order by pubcode, imcacct"

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery() ' create the sp

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim ocmd As New SqlCommand

ocmd = New SqlCommand("sp_createa_rsummary", oconn) ' execute the sp

ocmd.CommandType = CommandType.StoredProcedure

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim acmd As New SqlCommand

acmd = New SqlCommand("sp_altera_rsumtable", oconn) ' give new table a pk

acmd.CommandType = CommandType.StoredProcedure

Try

acmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

' we now have a_rsumtable for only the selected accounts

createa_rsummary = 1

End Function

"Tor Martin Halvorsen" <[email protected]>
wrote in message
Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters
in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the
clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Bernie Yaeger]

I don't know why I bother, but for a brief moment, I will.

1. The concept works equally well against ms access as it does against sql
server. I'm sure Tor would understand that.
2. I code in the real world, for real customers. I have made a very good
living doing it, and my customers wouldn't leave me for a second.
3. No (even though you haven't the courtesy to name it), I don't care about
SQL injection. It won't happen in my environment for reasons you can't
possibly understand.
4. The recompile is meaningless. My customers have no problem with the
daunting extra 10 seconds I cost them.
5. There is no concurrency issue, as they are locked out for those delicate
15 (total) seconds.
6.

Every time this code is executed, both your application and database
server
are doing unnecessary work. It is obvious by the tone of your response
that
you do not value your users' (see also: customer) time since you have no
problem with making them wait unnecessarily (e.g.: wasting their time)
because of poor application design. If this server is being used by other
applications, you are tying up resources that are probably better utilized
by
other processes.

Think about how academically insightful but practically stupid that is.
7. I imply nothing to Tor - I gave him a way of dealing with a common
problem.

Finally, you didn't have the brains to come up with the practical solution
that I did, so you have little else to do but debunk it.

Try the real world, Dave; you might have some success at it.

Since when does poor performance equate to good application design (did
you
not say that your method is "the best way")? When did good application
design suddenly transform into nonsense?

Perhaps you hear about this "nonsense" "all the time" because you need to
learn some lessons about its importance in application design. In the
"practical world," 15 seconds of unnecessary wait time is needlessly
wasted
time (for both the computer and the human) that could have been better
spent
elsewhere. I'm not at all arguing that entirely eliminating wait time is
possible but it should always be minimized. (Also, don't forget to
include
network latency in determining total wait time.)

Every time this code is executed, both your application and database
server
are doing unnecessary work. It is obvious by the tone of your response
that
you do not value your users' (see also: customer) time since you have no
problem with making them wait unnecessarily (e.g.: wasting their time)
because of poor application design. If this server is being used by other
applications, you are tying up resources that are probably better utilized
by
other processes.

You say that your method forces a recompile because the SP is different
every time but in reality, this method does not force a recopile but
rather
forces a compile because it isn't that the SP is different but is a
different
SP altogether. (you dropped it, remember?) It may have a common name but
it
is a different SP.

By posting this code in response to Tor's question, you [indirectly] claim
that this method allows for a variable parameter list, but your SP isn't
parameterized at all. Tor wanted to find an easy way to parameterize a
dynamically generated IN clause of a query against an Access database.
Completely ignoring the fact that your code is specific to SQL Server
(and,
as such, completely irrelevant to the question), where in "CREATE
PROCEDURE
sp_createa_rsummary AS ..." is a parameter list supplied? This method
doesn't allow for a variable parameter list at all since you're still
relying
solely on string concatenation.

Of course, relying solely on string concatenation rather than an actual
parameterized query opens up potentially larger problems but I'll leave it
up
to you to discover what they are...perhaps your code is immune to them
because this is "the best way?"

Another point is the issue of concurrency. On top of the added overhead
from additional executions, suppose you have two users that coincidentally
execute this code within a fraction of a second of each other. User A's
execution has just completed executing the code to create the procedure
but
has not yet executed the code to execute it (the SP). While User A's
execution is in the state I just described, what happens when User B's
code
executes the drop procedure code?

Since the code is in a try block, the catch code is executed and a message
box is displayed telling the user that the procedure couldn't be found
then
continues to execute because you don't have any code to exit the function.
Following the execution path, the SP for altering the table executes and
throws another error which is caught and displays another error message.
The
function then assumes that everything worked correctly (which it didn't)
and
execution continues, most likely providing the user with incorrect
results!

Is it unlikely for this scenario to occur? Sure, but it is conceivable.
This "solution" simply does not scale. As the user base grows, so does
the
probability that this problem will surface.

Finally, why not use a view for the common part of the query? All that
you're dynamically generating is the IN clause.

Hi Dave,

Yikes!!!
The code you provided is just laced with performance killers. Dropping
and recreating stored procedures means updates to system tables.

Yes, you're correct. I lose a full second on this! Damn!

the SP is only ever being executed once. SPs are always compiled for
their first execution so on top of the hit from updating the system
tables, you're forcing a compile every time the SP executes.

That's because the sp is different every time. This too costs me, after
all, as you correctly point out, it's not compiled yet. This also can
cost
me anywhere from 2 - 10 seconds! Good God!

Dave, I hear this performance nonsense all the time. In the practical
world, a function like this runs in 15 seconds. Give me a break.

Bernie

Yikes!!!
The code you provided is just laced with performance killers. Dropping
and recreating stored procedures means updates to system tables.
Additionally, the SP is only ever being executed once. SPs are always
compiled for their first execution so on top of the hit from updating
the
system tables, you're forcing a compile every time the SP executes.

I would strongly suggest reading
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
for some information about using SPs that execute dynamic SQL. Also
note
that using the Prepare() method of the SqlCommand class has a similar
effect. Both of these methods avoid the constant updates to the system
tables and will improve the operation of your application.

On another, possibly moot point, this code doesn't apply to Tor's
question. This code is specific to SQL Server while he's asking about
the
classes contained in System.Data.OleDb (OleDbCommand, OleDbParameter,
etc...) and is referring to an Access database.


Hi Tor,

Actually, I've found that the best way to do this is indeed to pass an
array. Now I know you can't, but you can, in effect, if you create
the
sp 'on the fly'. What I do is I first delete the sp; then I create a
bulkstring to contain my array - '12345', '34225', '957433', etc.
Then I
construct the sp, create it from the vb code, and run it. Below is an
example of one such function I use.

HTH,

Bernie Yaeger
Public Function createa_rsummary(ByVal marraylist As ArrayList, ByVal
oconn As SqlConnection) As Integer

createa_rsummary = 0

Dim i As Integer

Dim bulkstring As String

Dim acount As Integer = 0

For i = 0 To marraylist.Count - 1

acount += 1

bulkstring += Chr(39) & Trim(marraylist(i)) & Chr(39) & ","

If i <> marraylist.Count - 1 Then ' ie, it isn't the last item

If acount > 20 Then

acount = 0

bulkstring += vbCrLf

End If

End If

Next

bulkstring = Mid(bulkstring, 1, bulkstring.Length - 1) & ")"

Dim dcmd As New SqlCommand

dcmd = New SqlCommand("sp_dropsp_createa_rsummary", oconn) ' drop the
sp

dcmd.CommandType = CommandType.StoredProcedure

Try

dcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim creationstring As String

creationstring = "CREATE PROCEDURE sp_createa_rsummary AS " _

& "if exists (select * from information_schema.tables where table_name
=
" _

& "'a_rsumtable')" & vbCrLf & "drop table a_rsumtable" & vbCrLf _

& "select imcacct, pubcode, invnum, inv_dt, brname, " _

& "(case when inv_dt + 31 > getdate() then balance else 0 end) as
under31, " _

& "(case when inv_dt + 61 > getdate() and inv_dt + 31 <= getdate()
then
balance else 0 end) as over30, " _

& "(case when inv_dt + 91 > getdate() and inv_dt + 61 <= getdate()
then
balance else 0 end) as over60, " _

& "(case when inv_dt + 121 > getdate() and inv_dt + 91 <= getdate()
then
balance else 0 end) as over90, " _

& "(case when inv_dt + 121 <= getdate() then balance else 0 end) as
over120" _

& " into a_rsumtable from a_r where imcacct" _

& " in (" & bulkstring & vbCrLf _

& "order by pubcode, imcacct"

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery() ' create the sp

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim ocmd As New SqlCommand

ocmd = New SqlCommand("sp_createa_rsummary", oconn) ' execute the sp

ocmd.CommandType = CommandType.StoredProcedure

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim acmd As New SqlCommand

acmd = New SqlCommand("sp_altera_rsumtable", oconn) ' give new table a
pk

acmd.CommandType = CommandType.StoredProcedure

Try

acmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

' we now have a_rsumtable for only the selected accounts

createa_rsummary = 1

End Function

"Tor Martin Halvorsen" <[email protected]>
wrote in message
Hi all,

Just for fun, I was investigating wheither I could use
OleDbParameters
in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for
every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the
clause.

So is there a more elegant way to use OleDbParameters with IN
clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Jeff Dillon]

Yes a more elegant way would be to insert each "parameter" as new DataRows
in an in-memory DataTable, then replace your IN with a JOIN.

Jeff