473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL query... is there a better way?

Hi,

I am using following code to read and generate SQL query based on values
stored in ArrayList. But I am not sure if it is the best way... all
suggestions are
welcome!

Thank you!
Scott
Dim SqlQuery As String
Dim SqlCity As String

If Not Cities.Count = 0 Then
For i As Integer = 0 To Cities.Count - 1
If i = 0 Then
SqlCity = "(City =" + Cities(i) + ")"
Else
SqlCity = SqlCity + " Or (City =" + Cities(i) + ")"
End If
Next
SqlCity = "(" & SqlCity & ")"
End If

SqlQuery = "Select * FROM MyTable WHERE " & SqlCity

Nov 19 '05 #1
9 1103
It is better for you to use parameter instead of passing in the value
directly. You can achieve what you wanted with String.Join method though.

Tu-Thach

"Scott Reynolds" wrote:
Hi,

I am using following code to read and generate SQL query based on values
stored in ArrayList. But I am not sure if it is the best way... all
suggestions are
welcome!

Thank you!
Scott
Dim SqlQuery As String
Dim SqlCity As String

If Not Cities.Count = 0 Then
For i As Integer = 0 To Cities.Count - 1
If i = 0 Then
SqlCity = "(City =" + Cities(i) + ")"
Else
SqlCity = SqlCity + " Or (City =" + Cities(i) + ")"
End If
Next
SqlCity = "(" & SqlCity & ")"
End If

SqlQuery = "Select * FROM MyTable WHERE " & SqlCity

Nov 19 '05 #2
Well, I do have one suggestion. First, TURN OPTION STRICT ON!!!!

It really would help you to learn about data types. If you can understand
data types, you can understand how data is stored, and what exactly it is.
This prevents a lot of errors, and increases your programming power
personally. It will also help your app run faster.

An ArrayList is a Collection of objects. As such, it is not typed data per
se (everything inherits from object). This means that you will either have
to cast the proper data type, or have Option Strict turned off in order to
compile your app. When Option Strict is turned off, and you use untyped
data, "Late Binding" occurs at run-time. This means that the .Net platform
has to query the data to find out what it is, which will slow down your
app's performance. In addition, as you can add ANY type of object to the
Collection, it is possible, for example, to assign a DataReader to one of
the values in your ArrayList, which would throw an exception at run-time,
although it would compile quite nicely. I have seen classes that defined
private fields as Integers, and exposed them as public string properties.
Not a problem, unless, of course, something tries to assign "foo" to the
value. "foo" is indeed a string, but it cannot be parsed into a number.
Hence, difficult-to-identify errors can pop up in your code at run-time.

The fastest enumerated type to use in this case would be an array of
strings. Of course, an array is of fixed length, so you need to either know
how many elements are in your array when you declare it, or you have to use
a Collection. You can certainly create a safely-typed Collection, by
inheriting CollectionBase in a class, and adding your own typed
implementations of the Add, Remove, etc. methods.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

"Scott Reynolds" <sc***@it-xenius.co.uk> wrote in message
news:uB**************@TK2MSFTNGP12.phx.gbl...
Hi,

I am using following code to read and generate SQL query based on values
stored in ArrayList. But I am not sure if it is the best way... all
suggestions are
welcome!

Thank you!
Scott
Dim SqlQuery As String
Dim SqlCity As String

If Not Cities.Count = 0 Then
For i As Integer = 0 To Cities.Count - 1
If i = 0 Then
SqlCity = "(City =" + Cities(i) + ")"
Else
SqlCity = SqlCity + " Or (City =" + Cities(i) + ")"
End If
Next
SqlCity = "(" & SqlCity & ")"
End If

SqlQuery = "Select * FROM MyTable WHERE " & SqlCity

Nov 19 '05 #3
Hi!

Could you please provide me an example how to use parameter?

Scott
"Tu-Thach" <Tu*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...
It is better for you to use parameter instead of passing in the value
directly. You can achieve what you wanted with String.Join method though.

Tu-Thach

"Scott Reynolds" wrote:

Nov 19 '05 #4
For your case you might use this in your loop (C#, not VB):

SqlCommand command = new SqlCommand();
....
for loop..
....
SqlParameter param = new SqlParameter("@City" + i, Cities[i]);
command.Parameters.Add(param);
...
end foor loop
execute your command to retrieve result.
"Scott Reynolds" wrote:
Hi!

Could you please provide me an example how to use parameter?

Scott
"Tu-Thach" <Tu*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...
It is better for you to use parameter instead of passing in the value
directly. You can achieve what you wanted with String.Join method though.

Tu-Thach

"Scott Reynolds" wrote:


Nov 19 '05 #5
Thank you Kevin! I'll turn Option Strict On.

Scott

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:u$**************@TK2MSFTNGP09.phx.gbl...
Well, I do have one suggestion. First, TURN OPTION STRICT ON!!!!

It really would help you to learn about data types. If you can understand
data types, you can understand how data is stored, and what exactly it is.
This prevents a lot of errors, and increases your programming power
personally. It will also help your app run faster.

An ArrayList is a Collection of objects. As such, it is not typed data per
se (everything inherits from object). This means that you will either have
to cast the proper data type, or have Option Strict turned off in order to
compile your app. When Option Strict is turned off, and you use untyped
data, "Late Binding" occurs at run-time. This means that the .Net platform
has to query the data to find out what it is, which will slow down your
app's performance. In addition, as you can add ANY type of object to the
Collection, it is possible, for example, to assign a DataReader to one of
the values in your ArrayList, which would throw an exception at run-time,
although it would compile quite nicely. I have seen classes that defined
private fields as Integers, and exposed them as public string properties.
Not a problem, unless, of course, something tries to assign "foo" to the
value. "foo" is indeed a string, but it cannot be parsed into a number.
Hence, difficult-to-identify errors can pop up in your code at run-time.

The fastest enumerated type to use in this case would be an array of
strings. Of course, an array is of fixed length, so you need to either
know how many elements are in your array when you declare it, or you have
to use a Collection. You can certainly create a safely-typed Collection,
by inheriting CollectionBase in a class, and adding your own typed
implementations of the Add, Remove, etc. methods.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.

"Scott Reynolds" <sc***@it-xenius.co.uk> wrote in message
news:uB**************@TK2MSFTNGP12.phx.gbl...
Hi,

I am using following code to read and generate SQL query based on values
stored in ArrayList. But I am not sure if it is the best way... all
suggestions are
welcome!

Thank you!
Scott
Dim SqlQuery As String
Dim SqlCity As String

If Not Cities.Count = 0 Then
For i As Integer = 0 To Cities.Count - 1
If i = 0 Then
SqlCity = "(City =" + Cities(i) + ")"
Else
SqlCity = SqlCity + " Or (City =" + Cities(i) + ")"
End If
Next
SqlCity = "(" & SqlCity & ")"
End If

SqlQuery = "Select * FROM MyTable WHERE " & SqlCity

Nov 19 '05 #6
I don't know if your solution works for me, because I need to achieve
following...

SELECT * FROM Cities WHERE City = 'City1' OR City = 'City2' OR City =
'City4'

depends on what values are stored in array. What do you think?

Scott

"Tu-Thach" <Tu*****@discussions.microsoft.com> wrote in message
news:5C**********************************@microsof t.com...
For your case you might use this in your loop (C#, not VB):

SqlCommand command = new SqlCommand();
...
for loop..
....
SqlParameter param = new SqlParameter("@City" + i, Cities[i]);
command.Parameters.Add(param);
...
end foor loop
execute your command to retrieve result.
"Scott Reynolds" wrote:
Hi!

Could you please provide me an example how to use parameter?

Scott
"Tu-Thach" <Tu*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...
> It is better for you to use parameter instead of passing in the value
> directly. You can achieve what you wanted with String.Join method
> though.
>
> Tu-Thach
>
> "Scott Reynolds" wrote:


Nov 19 '05 #7
Kevin,

What if I store a ListItem in ArrayList? Is this OK?

Scott
It really would help you to learn about data types. If you can understand
data types, you can understand how data is stored, and what exactly it is.
This prevents a lot of errors, and increases your programming power
personally. It will also help your app run faster.

An ArrayList is a Collection of objects. As such, it is not typed data per
se (everything inherits from object). This means that you will either have
to cast the proper data type, or have Option Strict turned off in order to
compile your app. When Option Strict is turned off, and you use untyped
data, "Late Binding" occurs at run-time. This means that the .Net platform
has to query the data to find out what it is, which will slow down your
app's performance. In addition, as you can add ANY type of object to the
Collection, it is possible, for example, to assign a DataReader to one of
the values in your ArrayList, which would throw an exception at run-time,
although it would compile quite nicely. I have seen classes that defined
private fields as Integers, and exposed them as public string properties.
Not a problem, unless, of course, something tries to assign "foo" to the
value. "foo" is indeed a string, but it cannot be parsed into a number.
Hence, difficult-to-identify errors can pop up in your code at run-time.

The fastest enumerated type to use in this case would be an array of
strings. Of course, an array is of fixed length, so you need to either
know how many elements are in your array when you declare it, or you have
to use a Collection. You can certainly create a safely-typed Collection,
by inheriting CollectionBase in a class, and adding your own typed
implementations of the Add, Remove, etc. methods.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.

"Scott Reynolds" <sc***@it-xenius.co.uk> wrote in message
news:uB**************@TK2MSFTNGP12.phx.gbl...
Hi,

I am using following code to read and generate SQL query based on values
stored in ArrayList. But I am not sure if it is the best way... all
suggestions are
welcome!

Thank you!
Scott
Dim SqlQuery As String
Dim SqlCity As String

If Not Cities.Count = 0 Then
For i As Integer = 0 To Cities.Count - 1
If i = 0 Then
SqlCity = "(City =" + Cities(i) + ")"
Else
SqlCity = SqlCity + " Or (City =" + Cities(i) + ")"
End If
Next
SqlCity = "(" & SqlCity & ")"
End If

SqlQuery = "Select * FROM MyTable WHERE " & SqlCity


Nov 19 '05 #8
> What if I store a ListItem in ArrayList? Is this OK?

Not if you want your app to perform as best it can. It's really easy to
create a custom strongly-typed Collection. Here's a link to an article that
explains how:

http://msdn.microsoft.com/library/de...ctionclass.asp

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

"Scott Reynolds" <sc***@it-xenius.co.uk> wrote in message
news:ea**************@TK2MSFTNGP11.phx.gbl...
Kevin,

What if I store a ListItem in ArrayList? Is this OK?

Scott
It really would help you to learn about data types. If you can understand
data types, you can understand how data is stored, and what exactly it
is. This prevents a lot of errors, and increases your programming power
personally. It will also help your app run faster.

An ArrayList is a Collection of objects. As such, it is not typed data
per se (everything inherits from object). This means that you will either
have to cast the proper data type, or have Option Strict turned off in
order to compile your app. When Option Strict is turned off, and you use
untyped data, "Late Binding" occurs at run-time. This means that the .Net
platform has to query the data to find out what it is, which will slow
down your app's performance. In addition, as you can add ANY type of
object to the Collection, it is possible, for example, to assign a
DataReader to one of the values in your ArrayList, which would throw an
exception at run-time, although it would compile quite nicely. I have
seen classes that defined private fields as Integers, and exposed them as
public string properties. Not a problem, unless, of course, something
tries to assign "foo" to the value. "foo" is indeed a string, but it
cannot be parsed into a number. Hence, difficult-to-identify errors can
pop up in your code at run-time.

The fastest enumerated type to use in this case would be an array of
strings. Of course, an array is of fixed length, so you need to either
know how many elements are in your array when you declare it, or you have
to use a Collection. You can certainly create a safely-typed Collection,
by inheriting CollectionBase in a class, and adding your own typed
implementations of the Add, Remove, etc. methods.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.

"Scott Reynolds" <sc***@it-xenius.co.uk> wrote in message
news:uB**************@TK2MSFTNGP12.phx.gbl...
Hi,

I am using following code to read and generate SQL query based on values
stored in ArrayList. But I am not sure if it is the best way... all
suggestions are
welcome!

Thank you!
Scott
Dim SqlQuery As String
Dim SqlCity As String

If Not Cities.Count = 0 Then
For i As Integer = 0 To Cities.Count - 1
If i = 0 Then
SqlCity = "(City =" + Cities(i) + ")"
Else
SqlCity = SqlCity + " Or (City =" + Cities(i) + ")"
End If
Next
SqlCity = "(" & SqlCity & ")"
End If

SqlQuery = "Select * FROM MyTable WHERE " & SqlCity



Nov 19 '05 #9
You still have to build the SQL string, but you should be building so that it
looks like

SELECT * FROM Cities WHERE City = @City1 OR City = @City2 OR City = @City4
"Scott Reynolds" wrote:
I don't know if your solution works for me, because I need to achieve
following...

SELECT * FROM Cities WHERE City = 'City1' OR City = 'City2' OR City =
'City4'

depends on what values are stored in array. What do you think?

Scott

"Tu-Thach" <Tu*****@discussions.microsoft.com> wrote in message
news:5C**********************************@microsof t.com...
For your case you might use this in your loop (C#, not VB):

SqlCommand command = new SqlCommand();
...
for loop..
....
SqlParameter param = new SqlParameter("@City" + i, Cities[i]);
command.Parameters.Add(param);
...
end foor loop
execute your command to retrieve result.
"Scott Reynolds" wrote:
Hi!

Could you please provide me an example how to use parameter?

Scott
"Tu-Thach" <Tu*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...
> It is better for you to use parameter instead of passing in the value
> directly. You can achieve what you wanted with String.Join method
> though.
>
> Tu-Thach
>
> "Scott Reynolds" wrote:


Nov 19 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
0
by: John Macon | last post by:
------=_NextPart_000_04B4_01C36308.415CE100 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, Long time reader, first time poster, I hope...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
14
by: Bob | last post by:
Hi there, Need a little help with a certain query that's causing a lot of acid in my stomach... Have a table that stores sales measures for a given client. The sales measures are stored per...
10
by: Thomas R. Hummel | last post by:
I have a stored procedure that suddenly started performing horribly. The query plan didn't look right to me, so I copy/pasted the code and ran it (it's a single SELECT statement). That ran pretty...
4
by: jimh | last post by:
I'm not a SQL expert. I want to be able to write a stored procedure that will return 'people who bought this product also bought this...'. I have a user table that links to a transaction table...
22
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
2
by: paulmitchell507 | last post by:
I think I am attempting a simple procedure but I just can't figure out the correct syntax. My asp (classic) page runs a SELECT query to obtain dates and ID's from 2 tables uSQL = "SELECT...
3
by: bob laughland | last post by:
Hi All, I have a SQL query like this (I have tried to break the problem down to simplify it), select rowid from table where name in ('a', 'b', 'd') group by rowid Here is an example of data...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.