SQL String question

[harry78]

hello all,
I have a form with multiple chechboxes. Depending upon what the user chooses
I need to make a select from a database with 6 tables. I pass the results
from the form to the SQL statement up to the moment I need to put in a where
request.form>0 clause. The big issue is that the request form may look like
str1,str2,str3,str4 if the user selects them all.

Maybe u can help because I'm completely lost.
Here is the code:
------FORM------
<form action="processquery.asp" name="formfilter" Method="Post">
<table border="0" bordercolor="#0033CC" cellpadding="0" cellspacing="0"
width="100%">
<tr>
<td width="100%">Choose County
</td>
</tr>

<tr>
<td width="100%">
<input type="checkbox" name="Judet" value="Alba"> Alba &nbsp;
<input type="checkbox" name="Judet" value="Arges"> Arges &nbsp;
<input type="checkbox" name="Judet" value="Arad"> Arad &nbsp;
<input type="checkbox" name="Judet" value="Braila"> Braila &nbsp;
<input type="checkbox" name="Judet" value="Botosani"> Botosani &nbsp;
<input type="checkbox" name="Judet" value="Brasov"> Brasov &nbsp;
<input type="checkbox" name="Judet" value="Buzau"> Buzau &nbsp;
<input type="checkbox" name="Judet" value="Cluj"> Cluj &nbsp;
<input type="checkbox" name="Judet" value="Calarasi"> Calarasi &nbsp;
</td>
</tr>
<tr>
<td width="100%">Choose Culture
</td>
</tr>
<tr>
<td width="100%">
<input type="checkbox" name="Cultura" value="Sgrau"> Grau &nbsp;
<input type="checkbox" name="Cultura" value="Sporumb"> Porumb &nbsp;
<input type="checkbox" name="Cultura" value="Sfloare"> Floare &nbsp;
<input type="checkbox" name="Cultura" value="Srapita"> Rapita &nbsp;
<input type="checkbox" name="Cultura" value="Ssoia"> Soia &nbsp;
</td>
</tr>

<tr>
<td width="100%>Choose Source
</td>
</tr>
<tr>
<td width="100%">
<input type="checkbox" name="Sursa" value="Dir.Agricola"> Directia Agricola
&nbsp;
<input type="checkbox" name="Sursa" value="Simpozion"> Simpozion &nbsp;
<input type="checkbox" name="Sursa" value="APIA"> APIA &nbsp;
<input type="checkbox" name="Sursa" value="Altele"> Altele &nbsp;
</td>
</tr>

<tr>
<td width="100%" align="center" valign="middle">
<br /><BR /><input type="reset" name="Reset" value="Reset">&nbsp; <input
type="submit" name="Submit" value="Add Contact">
</form>

---------processquery.asp--------------
<%
dim strjudet,strcultura,strsursa

strjudet=request("Judet")
strjudet=Replace(strjudet, ",","','")
strjudet=Replace(strjudet, " ","")
strsursa=request("Sursa")
strsursa=Replace(strsursa, ",","','")
strsursa=Replace(strsursa, " ","")

strcultura=request("Cultura")
%>
<%
Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection = Conn

SQL = "SELECT * FROM Customer,Address,FarmDetails,InfoDetails WHERE Customer.
ID=Address.ID AND Customer.ID=FarmDetails.ID AND Customer.ID=InfoDetails.ID
AND Address.County IN ('"&strjudet&"') AND Source IN ('"&strsursa&"') AND
each Item in strcultura>0"????how do I go about that?
RS.Open SQL, Conn, 1, 3

If NOT (RS.BOF AND RS.EOF) Then%>

<% While (NOT RS.EOF)

%>

I thought about making strcultura an array and then do something like for each item in the array add an AND ArrayItem>0 to the sql string. I may be talking stupid here but I don't know much about arrays or how to correctly construct the SQL syntax for that

Thanks

[SheSaidImaPregy]

Well first thing about your SQL, you have an error near "AND SOURCE IN" because Source is a field but no table is representing that field (address, customer, farmdetails, etc.).

Second, before you run the query, have the webpage spit it out at you so you know exactly how it is being formatted. Just comment out the whole database thing and spit out the SQL statement to make sure. It's good debugging especially when you have it dynamic.

Third, don't use BOF and EOF together because you will create an error, whether or not it happens now or later on. Just stick with EOF.

To do the array you wish, you just post the request.form to a variable, then do the split function to split it at a character, which in this case it would be a comma. then you can do foreach x in arraystring thing exactly how you wish. Example below:

<%
dim strjudet,strcultura,strsursa
dim strculturaarr()

strjudet=request("Judet")
strjudet=Replace(strjudet, ",","','")
strjudet=Replace(strjudet, " ","")
strsursa=request("Sursa")
strsursa=Replace(strsursa, ",","','")
strsursa=Replace(strsursa, " ","")

strcultura=request("Cultura")
''I already declared the variable at the top. This creates the array:
strculturaarr = strcultura.split(",")
''Now to do the for each method BELOW your SQL statement
%>
<%
Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection = Conn

SQL = "SELECT * FROM Customer,Address,FarmDetails,InfoDetails WHERE Customer.
ID=Address.ID AND Customer.ID=FarmDetails.ID AND Customer.ID=InfoDetails.ID
AND Address.County IN ('"&strjudet&"') AND REPLACEWITHTABLENAME.Source IN ('"&strsursa&"')"

''Now for the for each method.
for each item in strculturaarr
  SQL = SQL & " AND " & item & ">0"
next
''this will create your SQL statement to have the chosen selection greater than zero.

RS.Open SQL, Conn, 1, 3

''removed the BOF. If you wish, keep it
''If NOT (RS.BOF AND RS.EOF) Then
If NOT RS.EOF Then
%>

<% While (NOT RS.EOF)

%>

[harry78]

Hello.
You're right about the AND SOURCE IN syntax, although it's funny because it works if I stop the query there. I modified it and now it looks AND InfoDetails.SOURCE IN.

The problem still remains at the array. I put the coode in and it gives me an error

"Microsoft VBScript runtime error '800a01a8'

Object required: 'Sgrau'

processquery.asp, line 31 ", line 31 being strculturaarr = strcultura.split(",")

If I select more than Sgrau in the form, for example Sgrau and Sfloare the error becomes :
Microsoft VBScript runtime error '800a01a8'

Object required: 'Sgrau, Sfloare'

processquery.asp, line 31
If I add Srapita to the selection the error becomes :

Microsoft VBScript runtime error '800a01a8'

Object required: 'Sgrau, Sfloare, Srap'

processquery.asp, line 31

I can't figure out why it says that. It is as if there is nothing in the array. I tried doing a response.write for each item in strculturaarr but it gives the same error.
Any clues?
Thanks.

[harry78]

I finally made it work. I defined the array this way:
strculturaarr=split(request.form("Cultura"), ", ")
and then used your code for the SQL query.

However I'm not sure what I've done, because it was done by trial and error. Could someone explain the difference between the two modes the array was given values?
strcultura=request("Cultura")
strculturaarr = strcultura.split(",")
versus
strculturaarr=split(request.form("Cultura"), ", ")

Also I would appreciate a link to some SQL Complex Syntax manual, because what I have found so far were tutorials for simple queries but my problems begin when dealing with multiple tables, multiple SELECT or INSERT and most of all with complex syntax, and replacing items like ', ", & and so on

Thank you.

[SheSaidImaPregy]

that was my problem, I haven't done classic asp in 3 years. string.split(",") is an ASP.NET method done by VB.NET, where split(..) is a VBScript method which Classic asp needs.

Just wrong syntax on my end.
string.split(char) is not a function in classic asp. The alike function is split(string, char)
now you could have done it with this as well:
strculturaarr=split(strcultura, ", ")

There was an added space that I saw in yours, which works as well. I forgot to add the following to eliminate the spaces:
strcultura=replace(strcultura, " ", "")

Your code is fine, you just fixed my error with an incorrect function.

So it works correctly?

For multiple statements in one query, research "Join" methods, "Union" methods (although, stray away from these), "IN" methods, and inner select statements.

You can have complex queries like:

"SELECT a.UserName, a.UserID, b.Address1, b.Address2, b.City, b.State, b.Zip, c.UserEmail FROM Users a, UserAddress b, UserEmail c WHERE a.UserID=" & strUserID & " OR c.UserEmail='" & strEmail & "' ORDER BY a.UserName DESC LIMIT 7"

This would be the same as:

"SELECT a.UserName, a.UserID, b.Address1, b.Address2, b.City, b.State, b.Zip, c.UserEmail FROM Users a JOIN UserAddress b ON a.UserID=b.UserID JOIN UserEmail c ON a.UserID=c.UserID WHERE a.UserID=" & strUserID & " OR c.UserEmail='" & strEmail & "' ORDER BY a.UserName DESC LIMIT 7"

For most SQL statements that are combined, the tables have to be related with unique data, like an ID that each record has and is unique. This is also called relational databases. Which basically means that each table is related by at least one column. If you do not have a related column, the query will fail or bring up exactly what you DON'T want!

There are 3 types of joins, Inner joins (aka Join), Left joins, and Right joins. Inner joins mean that both tables must have the data for the record to appear. Left joins means that the left table must have data to appear, while the additional table joined is optional. The right join means that the right table must have data and the previous table is optional. Referring to the code before...

''Example of Inner Join or Join:
'''Tables in RED must have the required data in the ON statement
'''and within the WHERE statement.
"SELECT a.UserName, a.UserID, b.Address1, b.Address2, b.City, b.State, b.Zip, c.UserEmail FROM Users a JOIN UserAddress b ON a.UserID=b.UserID JOIN UserEmail c ON a.UserID=c.UserID WHERE a.UserID=" & strUserID & " OR c.UserEmail='" & strEmail & "' ORDER BY a.UserName DESC LIMIT 7"
''' Now if all tables: Users or UserAddress or UserEmail do not contain the same unique strUserID or the same unique UserEmail, then your query will fail and no records will be displayed. All tables must have the UserID. Because the table is joined on the UserID's, all tables must have that same UserID. If all tables have 4 records each of the same UserID, 4 records will be displayed.


'''Example of Outer Join (Outer joins are Left joins or Right joins. Left join is by default)
'''If you use Outer Join instead of Left or Right join, Left Join will be default.
'''You may see this sometimes as "Left Join" or "Left Outer Join" or "Outer Join" which
'''all mean the same. Only "Right Join" or "Right Outer Join" mean the same.

'''Left Join, Left Outer Join, or Outer Join Example:
'''Tables in RED must have the required data in the ON statement:
'''Tables in Green are optional:
"SELECT a.UserName, a.UserID, b.Address1, b.Address2, b.City, b.State, b.Zip, c.UserEmail FROM Users a LEFT JOIN UserAddress b ON a.UserID=b.UserID LEFT JOIN UserEmail c ON a.UserID=c.UserID WHERE a.UserID=" & strUserID & " OR c.UserEmail='" & strEmail & "' ORDER BY a.UserName DESC LIMIT 7"
''' Now the only table that is required is the Users table. If there are no records in the users table, the other two tables won't display data either. If there is a record in the User table, then it relates a record to the UserAddress Table, if no records in the User Address Table, only the Users table will be displayed. If there is a record, both tables will be displayed. Now if both those tables have records that relate, then the third table is searched. If there are no records in UserEmail, then only Users and UserAddress will be displayed. If there are records in the UserEmail as well, all three tables will be displayed. Now keep in mind, that all tables being displayed have the same related data used in the ON statement which happens to be UserID. All tables have the same UserID for at least one record. This process is repeated for each record. With this type of join, Table1 will always have the same or more records than Table2, and Table2 will always have the same or less than Table1 and the same or more records than Table3. Ex:  Records:  5 5 1, or 5 5 5, or 5 4 3, or 5 5 0, or 5 0 0.

'''Right Join or Right Outer Join
'''Tables in Red must have the required data in the ON statement:
'''Tables in Green are optional:
"SELECT a.UserName, a.UserID, b.Address1, b.Address2, b.City, b.State, b.Zip, c.UserEmail FROM Users a RIGHT JOIN UserAddress b ON a.UserID=b.UserID RIGHT JOIN UserEmail c ON a.UserID=c.UserID WHERE c.UserID=" & strUserID & " OR c.UserEmail='" & strEmail & "' ORDER BY a.UserName DESC LIMIT 7"
''' Now make sure you see that the required WHERE statement changed as it is now c.UserID, not a.UserID. Now the only table required here is the UserEmail table. If there are no records within the UserEmail table with the supplied UserID, then no records will be shown. This is the same process as the Left Join but reversed and different tables are required. This method is much more confusing as everything is backwards, but this may be handy one day.


'''Union statements work the same way as joins, just separated queries. This requires more resources than a standard join, so learn joins over unions.
"SELECT UserName, UserID FROM Users WHERE UserID=" & strUserID & " UNION SELECT Address1, Address2, City, State, Zip FROM UserAddress WHERE UserID=" & strUserID & " UNION SELECT UserEmail FROM UserEmail WHERE UserID=" & strUserID & ""
''' Now the benefit of this is the fact that there is NO relation required. You can have many differen't tables that aren't related. It's a biotch to separate the info in the way you need, but it grabs it all in one query. The way the database handles UNIONS is separates it into all different requests then throws all the information at you at once. It's essentially the same as querying the database three times.


'''IN statements are used with the WHERE
"SELECT UserName FROM Users WHERE UserID IN (SELECT UserID FROM UserEmail WHERE NOT UserEmail IS NULL)"
'''What this code does is grabs all UserNames from Users where users don't have email address. The UserID from table Users equals the UserID from table UserEmail. This IN statement comes in handy so frequently, so learn it very well.

''The above query is essentially the same as the below query, but IN statements are faster and require less resources:
"SELECT a.UserName FROM Users a INNER JOIN UserAddress b ON a.UserID=b.UserID WHERE NOT b.UserEmail IS NULL"
''Oh, and IN statements are less confusing... no?

That should help you a little bit.

[harry78]

Thank you very much for your answers.

[SheSaidImaPregy]

you're welcome, I hope it helps.