Last post Jun 03, 2009 02:56 AM by tvb2727
Jun 02, 2009 11:23 PM|tvb2727|LINK
Is there a way that you can do muliple where clauses like in an array, but show it all in one grid view? sometimes I might need to use 1 where clause but other times i might need to use 10 where clauses. Basically I want to connect other id's to a user's
main id to show in a grid view to the user.
Select description from tablemain where userid = 1
or possibly 2 where clauses
Select description from tablemain where userid = 1 and userid = 2 and userid =3.
Jun 03, 2009 01:08 AM|gmmastros|LINK
Your query will never return any rows. Think of it this way.... for each row in your table, only return if userid = 1 AND userid = 2 AND userid = 3. This can never work because a single value (the userid) cannot be 1 at the same time that it is 2 or 3.
There are several ways you can write this query.
Select description from tablemain where userid = 1 OR userid = 2 OR userid =3.
Select description from tablemain where userid In(1,2,3)
Jun 03, 2009 01:16 AM|tvb2727|LINK
okay i understand. not sure why I was thinking AND would work.
Now, what if I have in(1,2,3,4,5) for one user, but the other user is only in(1,2). Can I write some vb code to piece together a SQL statement based on the number of userid's i need to return? Do a select statement as below. Is that the best and easiest
Dim sqlstring as string = ""
select case returnuserid
sqlstring = Select description from tablemain where userid In(1)
sqlstring = Select description from tablemain where userid In(1,2)
sqlstring = Select description from tablemain where userid In(1,2,3)
Jun 03, 2009 01:29 AM|gmmastros|LINK
To be honest.... I don't really understand (big picture) what you are trying to do. I'm really just throwing out some syntax to see if anything helps you out.
Based on your recent post, you may want to consider this syntax.
Select description from tablemain where userid between 1 and 3
Between is inclusive for the range, so the previous where will return exactly the same as
Where userid = 1 or userid = 2 or userid = 3
Where userid in (1,2,3)
So... you may actually want to use...
sqlstring = "Select Description From TableMain Where UserId Between 1 And " & returnuserid
Hope this helps.
Jun 03, 2009 01:37 AM|tvb2727|LINK
yes what you have put in the post will help me a lot I believe. I just have to try a couple of things and see how it plays out. I never knew about the IN() in a where clause. Been using it now in SQL server 2008 and its pretty neat. Thanks again.
Jun 03, 2009 02:56 AM|tvb2727|LINK
made an example to make an sql statement with the question I had above. VB code
Private Sub makesqlstring()
Dim x(100) As String
Dim xct As Integer = 0
For xct = 1 To 100
x(xct) = (xct).ToString
Dim ct As Integer
Dim ctfinal As Integer = 27
Dim sqlmain As String = "SELECT [autonumber], [value] from tblmerge1 WHERE [autonumber] IN("
For ct = 1 To ctfinal
If ct < ctfinal Then
sqlmain = sqlmain + x(ct) + ","
sqlmain = sqlmain + x(ct) + ")"
MsgBox("final: " + sqlmain)
SqlDataSource4.SelectCommand = sqlmain
Catch ex As Exception