calling SQL inside ASP [Archive]

View Full Version : calling SQL inside ASP

whyiswyh

03-29-2009, 06:25 PM

Is there a syntax error or is it illegal to declare in such a way.
Plus is it hard to use just if else statements alone in retrieving from the database. I think there is a better way.My question is how because there involves quite a lot of data?

if categories<>"" then
24 sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'"
set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn
...
elseif races<>"" then
...
else
....

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/demo/submit.asp, line 24

brazenskies

03-29-2009, 06:38 PM

please post the full code and highlight line 24.

also, as ever, make sure you write the sql to the screen to help debug

whyiswyh

03-30-2009, 01:54 AM

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/demo/submit.asp, line 24

<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.provider="Microsoft.Jet.OLEDB.4.0"
conn.open "C:\Inetpub\wwwroot\Demo\data\demo.mdb"

if categories<>"" then
sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'"
elseif races<>"" then
sql="SELECT * FROM tblGuestBook WHERE races='" & races & "'"
else
sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'"
end if
set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
rs.MoveNext%>
<%loop
rs.close
conn.close%>
</table>
</body>

I am also planning to add some additional functions such as an advance search with seeking All Words and Phrases as well as boolean searches(AND,NOT,OR)

Old Pedant

03-30-2009, 04:15 AM

That is *NOT* the full code.

In the code you show, the line in red is only line 8, give or take a line.

If there truly is an error on *THAT* line, it has to be in the use of the variable categories, and you don't show us where that comes from.

Now... Occasionally, ASP lies about the line number when issuing an error message. Usually not by more than one or maybe 2 lines.

But this code is so simple that I don't think that's the problem. So...I'm really suspicious of just WHAT categories is. So show us the first 16 or so lines of the file, the ones you chopped off before.

Oh...and if they #include a file, be prepared to show it, as well.

whyiswyh

03-30-2009, 07:50 AM

I got this empty table after fixing the problem that you said earlier when I run it in the localhost.

Submitted change to Database
no categories races occasion email homepage dateadded

I got this instead if I used a html+asp combo:

<% set conn=Server.CreateObject("ADODB.Connection") conn.provider="Microsoft.Jet.OLEDB.4.0" conn.open "C:\Inetpub\wwwroot\Demo\data\demo.mdb" if categories<>"" then sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'" elseif races<>"" then sql="SELECT * FROM tblGuestBook WHERE races='" & races & "'" else sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'" end if set rs=Server.CreateObject("ADODB.Recordset") rs.Open sql,conn %> <%for each x in rs.Fields response.write("") next%> <%do until rs.EOF%> <%for each x in rs.Fields%> <%next rs.MoveNext%> <%loop rs.close conn.close%>
" & x.name & "
<%Response.Write(x.value)%>

Is it a fault if I selected the wrong type of query that is an if statement if my cases is AND only.Should I add an AND to the search?

Spudhead

03-30-2009, 06:23 PM

Ok. This:

no categories races occasion email homepage dateadded

... isn't a sentence.

I don't know what the code you posted is supposed to be doing, but in its current state it's not going to do much at all. What, exactly, is the nature of the problem?

whyiswyh

03-31-2009, 07:42 AM

This is a table an empty table bcoz I cannot draw the table
Supposeably it can find it from the database it will look like this

no categories races occasion email homepage dateadded
1 Male Chinese
2 Female Indian

This is perhaps a suspicion because I used the wrong logical analogy
inside this SQL coding error as it is displayed if I never opened my localhost:
<% set conn=Server.CreateObject("ADODB.Connection")
conn.provider="Microsoft.Jet.OLEDB.4.0"
conn.open "C:\Inetpub\wwwroot\Demo\data\demo.mdb"
if categories<>"" then sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'"
elseif races<>"" then sql="SELECT * FROM tblGuestBook WHERE races='" & races & "'"
else sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'"
end if
set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn %>
<%for each x in rs.Fields response.write("") next%>
<%do until rs.EOF%>
<%for each x in rs.Fields%>
<%next rs.MoveNext%>
<%loop rs.close conn.close%>

Spudhead

03-31-2009, 05:15 PM

Nope, I'm still lost. What's it SUPPOSED to do, and what IS it doing?

brazenskies

03-31-2009, 05:43 PM

Nope, I'm still lost. What's it SUPPOSED to do, and what IS it doing?

you've actually turned into Old Pedant :p

Old Pedant

03-31-2009, 09:07 PM

ROTFLMFAO!

Okay, JUST for that, let me see if I can get him to make any sense.

whyiswyh: You seem to be saying that your table that SHOULD have 7 fields per record actually only has 3 fields per record.

Or, at least, you only see 3 fields when you use the ASP code you have shown us to display the records.

IS THAT CORRECT?

If so, then are you surprised? That is, do you think you DO have 7 fields per record in the table?

If you think so, how did you you verify that?

Did you look in the actual database, *WITHOUT* using ASP?

If you are not surprised, then what is the problem? What are you trying to fix???

We know English is not your native language, but you simply are not using enough words for us to understand you. Sorry.

whyiswyh

04-01-2009, 05:40 AM

Nope. I mean I am not able to display the table correctly even though I have all seven fields running in my database.I only used 3 forms that is races,occasion and categories to ensure the search can be implemented with 7 or more fields to test my database. Soon I will be collecting others data such as description,type,etc. But now it is displaying empty data although all my database seems to be perfect though I created random strings into it. It just won't display any of the search results.

Old Pedant

04-01-2009, 08:33 AM

Hmmm...so did you try it WITHOUT filtering at all?

set conn=Server.CreateObject("ADODB.Connection")
conn.provider="Microsoft.Jet.OLEDB.4.0"
conn.open "C:\Inetpub\wwwroot\Demo\data\demo.mdb"

sql="SELECT * FROM tblGuestBook"
Set rs = conn.Execute( sql )
rs.Open sql,conn
%>
<table border="1" width="100%">
<tr>
<%
for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next
%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%=x.value%></td>
<% next
rs.MoveNext
loop
rs.close
conn.close
%>
</table>
</body>

???

Can you copy/past what that shows here? Wrap the copy/paste in [ code ] .... [ /code ] tags (without the spaces).

If that still doesn't show all the fields, then is your occasion field a MEMO field, by any chance???
If so, you *might* try doing:

sql="SELECT no, categories, races, email, homepage, dateadded, occasion FROM tblGuestBook"

Sometimes, putting a MEMO field last helps. See that ASPFAQs (link is center above) for some FAQs on this.

Oh...one more thing: Since this is an Access database, can you use Access to display the table and then copy/paste from Access to here??

whyiswyh

04-01-2009, 02:59 PM

sql="SELECT * FROM tblGuestBook"
Set rs = conn.Execute( sql )
rs.Open sql,conn

I got some sort of error without this line:
Set rs = conn.Execute( sql )

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/demo/submit.asp, line varX

and another different error with that particular line:
cannot submit while opening...
I tried to all the variables but it is the same. However, 1 thing is different in the displayed is instead of no in the field it gave me a garbage value.

whyiswyh

04-01-2009, 03:11 PM

Access data:
No Memo used,
All of it is text except no which is an (Auto Number)
no categories races occasion email homepage dateadded
4 Woman Chinese Normal Day fggdf fd 3/28/2009 12:34:10 PM
no categories races occasion email homepage dateadded
5 Girl Malay Wedding gf GF 3/28/2009 12:34:24 PM
no categories races occasion email homepage dateadded
6 Man Indian Funeral w@w.com AAAA.com 3/30/2009 12:07:59 AM
no categories races occasion email homepage dateadded
7 Man Others Wedding fjdfdlk dfdf 3/28/2009 12:39:49 PM
no categories races occasion email homepage dateadded
8 Boy Chinese Normal Day 3vd@ dfdf
no categories races occasion email homepage dateadded
9 Man Malay Normal Day Baju Melayu fdsf

The funny thing is without the search function it can do well. With the search function it has some hiccups.I did a if...else statement it doesn't display the if(..."male"....) instead it shows only the else(..."female") so that's why I suspect I couldn't use the if else statement.Either it was me or it is illogical to use the if...else statement in the first place.

Old Pedant

04-01-2009, 11:45 PM

When you code it THIS way:

sql="SELECT * FROM tblGuestBook"
Set rs = conn.Execute( sql )

which is the most efficient way to get a recordset, then you DO NOT then do

rs.Open sql,conn ' NO! Zap this!

***************

You've got me as to why it didn't work.

Can you show the *COMPLETE* code, one more time?

But *DO* also try that code of mine, that will dump all rows without filtering.

whyiswyh

04-02-2009, 02:43 PM

It did work for both the ways.One of them is your way declaring it as a conn.execute.
However when it come to the if...else with select function it didn't work.I will show you the whole code that didn't work for you suggestion. Yes it did function, thanks.

<html>
<head>
<title>Submit DataBase Record</title>
</head>
<body>
<h2>Submitted change to Database</h2>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.provider="Microsoft.Jet.OLEDB.4.0"
conn.open "C:\Inetpub\wwwroot\Demo\data\demo.mdb"

if categories<>"" then
sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'"
elseif races<>"" then
sql="SELECT * FROM tblGuestBook WHERE races='" & races & "'"
else
sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'"
end if

Set rs = conn.Execute( sql )
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
rs.MoveNext%>
<%loop
rs.close
conn.close%>
</table>

</body>
</html>

Could it be because my first page is in test.html then I used action="submit.asp"?

Old Pedant

04-02-2009, 10:30 PM

That is your ENTIRE PAGE???

Well OF COURSE it doesn't work!!!!

You NEVER assign *ANY VALUE* to *ANY* of those 3 variables:

if categories<>"" then
sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'"
elseif races<>"" then
sql="SELECT * FROM tblGuestBook WHERE races='" & races & "'"
else
sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'"
end if

Since NONE of those have ANY value, you will end up doing the third query:

sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'"

But since even occasion has no value, you are essentially saying "show only the records where the occasion field has no value."

You *MUST* have a value in *ONE* of those three variables for that code to work. Period.

whyiswyh

04-03-2009, 07:56 AM

That is your ENTIRE PAGE???

Well OF COURSE it doesn't work!!!!

You NEVER assign *ANY VALUE* to *ANY* of those 3 variables:
Code:

if categories<>"" then
sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'"
elseif races<>"" then
sql="SELECT * FROM tblGuestBook WHERE races='" & races & "'"
else
sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'"
end if

Since NONE of those have ANY value, you will end up doing the third query:
Code:

sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'"

But since even occasion has no value, you are essentially saying "show only the records where the occasion field has no value."

You *MUST* have a value in *ONE* of those three variables for that code to work. Period.

Even the occasion displays empty values I gave them values.I gave all the three values except like you said the string must not be null.

if categories<>"" then
sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'"
However my reasoning is this:

I want to call all types of categories that is: Man,Woman,Boy,Girl
and races:Malay,Chinese,Indians,Others
and occasion:Normal Day,Wedding,Funeral

I did suspect it was the if else statement but I can't think of a way to replace the double quote that is red in color like above

Old Pedant

04-03-2009, 10:30 PM

You just need to add another ELSEIF:

if categories<>"" then
sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'"
elseif races<>"" then
sql="SELECT * FROM tblGuestBook WHERE races='" & races & "'"
elseif occasions <> "" then
sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'"
else
sql="SELECT * FROM tblGuestBook"
end if

But what you SHOULD be doing *BEFORE* that code is something like:

categories = Trim(Request("categories"))
races = Trim(Request("races"))
occasion = Trim(Request("occasion"))

So then, if the <FORM> on the prior page passes any one of those three, *THEN* your IF code will cause the right one to be used.

A somewhat better way to do this, so that the user of the <FORM> can specify 1 or 2 or ALL THREE of the values, would be:

WHERE = " 1 = 1 "

categories = Trim(Request("categories"))
If categories <> "" Then WHERE = WHERE & " AND categories = '" & Replace(categories,"'","''") & "' "

races = Trim(Request("races"))
If races <> "" Then WHERE = WHERE & " AND races = '" & Replace(races,"'","''") & "' "

occasion = Trim(Request("occasion"))
If occasion <> "" Then WHERE = WHERE & " AND occasion = '" & Replace(occasion,"'","''") & "' "

SQL = "SELECT * FROM tblGuestBook " & WHERE
...

Now, if all 3 are given, the SQL could end up looking like:

SELECT * FROM tbleGuestBook WHERE 1=1 AND categories='Woman' AND races='Malay' AND occasion='Wedding'

Do you understand that?

whyiswyh

04-04-2009, 01:05 PM

I tried your advice where you used this coding.Whether I used 3 end if statement anywhere appropriately or just a single end if.It gives this error:
Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/demo/submit.asp, line 26
end if
If I didn't use the end if:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause.
/demo/submit.asp, line 27
WHERE = " 1 = 1 "

categories = Trim(Request("categories"))
If categories <> "" Then WHERE = WHERE & " AND categories = '" & Replace(categories,"'","''") & "' "

races = Trim(Request("races"))
If races <> "" Then WHERE = WHERE & " AND races = '" & Replace(races,"'","''") & "' "

occasion = Trim(Request("occasion"))
If occasion <> "" Then WHERE = WHERE & " AND occasion = '" & Replace(occasion,"'","''") & "' "

SQL = "SELECT * FROM tblGuestBook " & WHERE

end if
For the first instance it gives me the full database without filtering:

Code:

if categories<>"" then
sql="SELECT * FROM tblGuestBook WHERE categories='" & categories & "'"
elseif races<>"" then
sql="SELECT * FROM tblGuestBook WHERE races='" & races & "'"
elseif occasions <> "" then
sql="SELECT * FROM tblGuestBook WHERE occasion='" & occasion & "'"
else
sql="SELECT * FROM tblGuestBook"
end if

But what you SHOULD be doing *BEFORE* that code is something like:
Code:

categories = Trim(Request("categories"))
races = Trim(Request("races"))
occasion = Trim(Request("occasion"))

And I thought about giving it the full WHERE statement with the following partial code for defining SQL:

SQL = "SELECT * FROM tblGuestBook WHERE 1=1 AND categories='" & categories & "' AND races='" & races "' AND occasion='" & occasion

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/demo/submit.asp, line 24, column 102
SQL = "SELECT * FROM tblGuestBook WHERE 1=1 AND categories='" & categories & "' AND races='" & races "' AND occasion='" & occasion

Old Pedant

04-04-2009, 10:51 PM

I just accidentally omitted the WHERE keyword here:

WHERE = " 1 = 1 "

categories = Trim(Request("categories"))
If categories <> "" Then WHERE = WHERE & " AND categories = '" & Replace(categories,"'","''") & "' "

races = Trim(Request("races"))
If races <> "" Then WHERE = WHERE & " AND races = '" & Replace(races,"'","''") & "' "

occasion = Trim(Request("occasion"))
If occasion <> "" Then WHERE = WHERE & " AND occasion = '" & Replace(occasion,"'","''") & "' "

SQL = "SELECT * FROM tblGuestBook WHERE " & WHERE
...

whyiswyh

04-05-2009, 06:01 AM

Thanks for your support.
If I never use the end if statement it does not have any effect on the results.
If I used a single/three end if it has compilation error:
Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/demo/submit.asp, line 25
end if

After checking one of the site(classicasp.aspfaq.com)
Why do I get errors in the 800A0400 -> 800A041F range?
Microsoft VBScript compilation (0x800A0400)
Expected statement

This is usually due to using a With statement on a server with an older version of the script engines. Make sure you have the most recent script engines on your server; specifically, With requires version 5.0. See Article #2151 for information about getting the most recent updates.
Does it mean that I need some additional software

Old Pedant

04-05-2009, 06:25 AM

That is a completely WRONG WRONG WRONG piece of bad advice from that FAQ!

That may be the silliest explanation for that error I've ever seen!

An "expected statement" error can come from DOZENS of reasons (yes, including the one they give, but it's a VERY unlikely reason).

Post your *FULL CODE* for that "submit.asp" page here, again. AT LEAST as far as line 30 or so.

whyiswyh

04-05-2009, 07:33 AM

<html>
<head>
<title>ADO - Submit DataBase Record</title>
</head>
<body>
<h2>Submitted change to Database</h2>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.provider="Microsoft.Jet.OLEDB.4.0"
conn.open "C:\Inetpub\wwwroot\Demo\data\demo.mdb"

WHERE = " 1 = 1 "

categories = Trim(Request("categories"))
If categories <> "" Then WHERE = WHERE & " AND categories = '" & Replace(categories,"'","''") & "' "

races = Trim(Request("races"))
If races <> "" Then WHERE = WHERE & " AND races = '" & Replace(races,"'","''") & "' "

occasion = Trim(Request("occasion"))
If occasion <> "" Then WHERE = WHERE & " AND occasion = '" & Replace(occasion,"'","''") & "' "

SQL = "SELECT * FROM tblGuestBook WHERE" & WHERE
end if
set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
rs.MoveNext%>
<%loop
rs.close
conn.close%>
</table>

</body>
</html>

Old Pedant

04-05-2009, 09:08 AM

You have a BOGUS extra line in there:

...
SQL = "SELECT * FROM tblGuestBook WHERE" & WHERE
end if
set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn
...

REMOVE the line in RED !!!

See what I mean about a POOR FAQ?? In this case, because you gave VBScript an "END IF" when it wasn't expecting one, it was saying to you "I expected some statement before the END IF and didn't find one." Has nothing to do with USING. Isn't it amazing how much BAD advice you can find with Google??

whyiswyh

04-05-2009, 11:11 AM

Agree with your comments on googling something.
Well quite true I did some modifying b4 and after you said about the end if in fact throw out that end if statement but it still does not display correctly if selected
categories:Man
races:Malay
occasion:Normal Day
where it displays all instead of the selected value.Could it be the form that has the problem.I will post the code here.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">

</style>
</head>

<body>
<table width="1250" height="533" background="main4.jpg" border="0">
<tr>
<td height="50" colspan="2"><MARQUEE BEHAVIOR=ALTERNATE><img src="HOME.png" width="200" height="50" /><img src="SEARCH.png" width="200" height="50" /><img src="LINKS.png" width="200" height="50" /><img src="ABOUT.png" width="200" height="50" /><img src="CONTACT.png" width="200" height="50" /></MARQUEE></td>
</tr>
<tr>
<td width="992" align="center"><table width="589" height="230" border="0">
<tr>
<td colspan="2" class="style4"><div align="center">Basic search</div></td>
</tr>
<tr>
<td width="105" class="style3"><div align="center" class="style3">
<div align="left" class="style1">Races</div>
</div></td>
<td width="329"><label>
<form id="races" name="races" method="post" action="">
<select name="races" class="style2">
<option value="Malay">Malay</option>
<option value="Chinese">Chinese</option>
<option value="Indian">Indian</option>
<option value="Others">Others</option>
</select></form>
</label></td>
</tr>
<tr>

<td class="style1">Categories</td>
<td>
<form id="categories" name="categories" method="post" action="">
<select name="categories" class="style2">
<option value="Man">Man</option>
<option value="Woman">Woman</option>
<option value="Boy">Boy</option>
<option value="Girl">Girl</option>
</select></form></td>
</tr>
<tr>
<td><div align="left" class="style1">Occasion</div></td>
<td>
<form id="ocassion" name="ocassion" method="post" action="">
<select name="ocassion" class="style2">
<option value="Normal Day">Normal Day</option>
<option value="Wedding">Wedding</option>
<option value="Funeral">Funeral</option>
</select></form></td>
</tr>
<tr>
<td> </td>
<td><form id="form1" name="form1" method="post" action="submit.asp">
<label>
<input type="submit" name="Submit" value="Search" />
</label>
<label>
<input type="reset" name="Submit2" value="Cancel" />
</label>
</form></td>
</tr>
</table></td>
<td width="242"><marquee height="500" behavior="scroll" direction="up" scrollamount="2"><p><span class="style1">Did you know?</span></p>
<p class="style3">In Malaysia, the early colonial censuses listed separate ethnic groups such as "Malays, Boyanese, Achinese, Javanese, Bugis, Manilamen and Siamese". The 1891 census merged these ethnic groups into the three racial categories used in modern Malaysia – Chinese, ‘Tamils and other natives of India’, and ‘Malays and other Natives of the Archipelago’.
<p> <span class="style1">Malaysian Indian</span></p><p class="style3">This was based upon the European view at the time that race was a biologically based scientific category. For the 1901 census, the government advised the word "race" should replace "nationality" wherever it occurs.</p><p class="style3">Malaysian Indians are a group of Malaysians largely descended from those who migrated from southern India during the British colonization of Malaya. Prior to British colonization, Tamils had been conspicuous in the archipelago much earlier, especially since the period of the powerful South India kingdom of the Cholas in the 11th century. By that time, Tamils were among the most important trading peoples of maritime Asia.</p><p class="style1">Malaysian Chinese</p><p class="style3">Malaysian Chinese is a Malaysian of Chinese origin. Most are descendants of Chinese who arrived between the fifteenth and the mid-twentieth centuries. Within Malaysia, they are usually simply referred to as "Chinese" in all languages. The term Chinese Malaysian is also sometimes used to refer to this community.</p>
</marquee></td>
</tr>
</table>
</body>
</html>

whyiswyh

04-05-2009, 11:41 AM

I think I forgot to declare the "Save" button when I submit but it display's a blank database and I can't use end if after the loop.I give you the code which I modified.Which I think the end if statement should occur earlier but I can use the end if b4 the %> it gives that argument in conflict.

<html>
<head>
<title>ADO - Submit DataBase Record</title>
</head>
<body>
<h2>Submitted change to Database</h2>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.provider="Microsoft.Jet.OLEDB.4.0"
conn.open "C:\Inetpub\wwwroot\Demo\data\demo.mdb"

if Request.form("action")="Search" then
WHERE = " 1 = 1 "

categories = Trim(Request("categories"))
If categories <> "" Then WHERE = WHERE & " AND categories = '" & Replace(categories,"'","''") & "' "

races = Trim(Request("races"))
If races <> "" Then WHERE = WHERE & " AND races = '" & Replace(races,"'","''") & "' "

occasion = Trim(Request("occasion"))
If occasion <> "" Then WHERE = WHERE & " AND occasion = '" & Replace(occasion,"'","''") & "' "

SQL = "SELECT * FROM tblGuestBook WHERE" & WHERE

set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
rs.MoveNext%>
<%loop
rs.close
conn.close
end if
%>
</table>

</body>

Old Pedant

04-05-2009, 09:54 PM

NO NO NO!!!

*ONLY* form fields that are *BETWEEN* your <FORM> and </FORM> tags are sent to the next page!!!!

And you can ONLY SUBMIT ONE <FORM> PER PAGE!!!
(You can have multiple <FORM>s, but only one can be submitted...You should consider multiple <FORM>s to be an advanced feature that you will NOT USE for a LONG TIME!)

YOU MUST MOVE YOUR <FORM> tag!! You MUST have ONLY ONE <form> tag!

I *strongly* suggest that you put <FORM> right after <BODY> and </FORM> right before </BODY>. Thus:

<body>
<form name="races" method="post" action="submit.asp">
...
<select name="races" class="style2">
<option value="Malay">Malay</option>
<option value="Chinese">Chinese</option>
<option value="Indian">Indian</option>
<option value="Others">Others</option>
</select>
...
<select name="categories" class="style2">
<option value="Man">Man</option>
<option value="Woman">Woman</option>
<option value="Boy">Boy</option>
<option value="Girl">Girl</option>
</select>
...
<select name="ocassion" class="style2">
<option value="Normal Day">Normal Day</option>
<option value="Wedding">Wedding</option>
<option value="Funeral">Funeral</option>
</select>
...
<input type="submit" name="Submit" value="Search" />
<input type="reset" name="Submit2" value="Cancel" />
...
</table>
</form>

I don't know where you learned ASP from, but I think you need to forget a lot of what you learned and go find a *GOOD* book or good web site, instead of whatever you have been using.

I don't really think there are any good web sites for ASP, so I think a book would be better. But one site you can start from is http://www.LearnASP.com/Learn

Old Pedant

04-05-2009, 09:56 PM

And you do NOT need that IF...END IF on the search. It's not a bad idea, but it's not needed. If you ever get to the "submit.asp" page without any <SELECT>s being chosen, the worst that happens is you see all records.

whyiswyh

04-06-2009, 09:06 AM

Thanks Old Pendant :) I didn't realize it was the coding structure now it is running already. It always works when people give me some extra info and I normally buck up afterwards.I really appreciate the help *thumbs up*