Wont Add record

[+FarmerPickles]

ok. I am trying to write an asp page that will add records to a DB. I am
trying to get away from using FP for forms and DB's. This is access DB.
Instead of adding the new record to the DB, it just comes back to the
register page and doesn't add it. here is the code:

<%
Dim Conn

If Request.Form("action") <> "Save Form Data" Then

Response.Redirect("register.asp")

Else
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
Server.MapPath("/tourney/fpdb/tourney.mdb")

var1 = Request.Form("username")
var2 = Request.Form("password")
var3 = Request.Form("email")

strSQL = "INSERT rounds SET " & var1 & " " & var2 & " " & var3 & ""

Conn.Execute (strSQL)

Conn.Close
Set Conn = Nothing

response.redirect "players.asp"

End If

%>

here is the register page.. try it. it is supposed to direct to the players
page located here: [url]http://www.gig-golf.com/Tourney/players.asp[/url]
can you see something wrong here? I thought I had the script correct, but
something isn't working.
thanks
Jeff

[url]http://www.gig-golf.com/Tourney/data/register.asp[/url] when you register, it
SHOULD take you to

[+FarmerPickles]

and before someone asks.. the register page IS posting to the asp page

"+FarmerPickles" <gig_bam_takemeout_@verizon.net> wrote in message
news:eviScHqUEHA.1152@TK2MSFTNGP09.phx.gbl...

> ok. I am trying to write an asp page that will add records to a DB. I am
> trying to get away from using FP for forms and DB's. This is access DB.
> Instead of adding the new record to the DB, it just comes back to the
> register page and doesn't add it. here is the code:
>
> <%
> Dim Conn
>
> If Request.Form("action") <> "Save Form Data" Then
>
> Response.Redirect("register.asp")
>
> Else
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
> Server.MapPath("/tourney/fpdb/tourney.mdb")
>
> var1 = Request.Form("username")
> var2 = Request.Form("password")
> var3 = Request.Form("email")
>
> strSQL = "INSERT rounds SET " & var1 & " " & var2 & " " & var3 & ""
>
> Conn.Execute (strSQL)
>
> Conn.Close
> Set Conn = Nothing
>
> response.redirect "players.asp"
>
> End If
>
> %>
>
> here is the register page.. try it. it is supposed to direct to the

players

> page located here: [url]http://www.gig-golf.com/Tourney/players.asp[/url]
> can you see something wrong here? I thought I had the script correct, but
> something isn't working.
> thanks
> Jeff
>
> [url]http://www.gig-golf.com/Tourney/data/register.asp[/url] when you register, it
> SHOULD take you to
>
>

[+FarmerPickles]

OK.. let me ask this. i dont need to have a value for each field in the DB
do i? i need all the other fields remain blank. i have an id field that is
an auto number, so that shouldnt' matter i dont think.
Jeff

"+FarmerPickles" <gig_bam_takemeout_@verizon.net> wrote in message
news:eBSesLqUEHA.1296@TK2MSFTNGP10.phx.gbl...

> and before someone asks.. the register page IS posting to the asp page
>
> "+FarmerPickles" <gig_bam_takemeout_@verizon.net> wrote in message
> news:eviScHqUEHA.1152@TK2MSFTNGP09.phx.gbl...

> > ok. I am trying to write an asp page that will add records to a DB. I am
> > trying to get away from using FP for forms and DB's. This is access DB.
> > Instead of adding the new record to the DB, it just comes back to the
> > register page and doesn't add it. here is the code:
> >
> > <%
> > Dim Conn
> >
> > If Request.Form("action") <> "Save Form Data" Then
> >
> > Response.Redirect("register.asp")
> >
> > Else
> > Set Conn = Server.CreateObject("ADODB.Connection")
> > Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
> > Server.MapPath("/tourney/fpdb/tourney.mdb")
> >
> > var1 = Request.Form("username")
> > var2 = Request.Form("password")
> > var3 = Request.Form("email")
> >
> > strSQL = "INSERT rounds SET " & var1 & " " & var2 & " " & var3 & ""
> >
> > Conn.Execute (strSQL)
> >
> > Conn.Close
> > Set Conn = Nothing
> >
> > response.redirect "players.asp"
> >
> > End If
> >
> > %>
> >
> > here is the register page.. try it. it is supposed to direct to the

> players

> > page located here: [url]http://www.gig-golf.com/Tourney/players.asp[/url]
> > can you see something wrong here? I thought I had the script correct,

but

> > something isn't working.
> > thanks
> > Jeff
> >
> > [url]http://www.gig-golf.com/Tourney/data/register.asp[/url] when you register, it
> > SHOULD take you to
> >
> >

>
>

[Bob Barrows [MVP]]

The first step in troubleshooting a non-working query is to see what the
query is:
+FarmerPickles wrote:
<snip>

> strSQL = "INSERT rounds SET " & var1 & " " & var2 & " " & var3 & ""

Response.Write strSQL
Response.End

Run your page and examine the sql statement printed to the browser window.
If that does not help you, copy it to the clipboard, open your database in
Access, go to the Queries tab, create a new query in design view, close the
Choose Tables dialog without selecting a table, switch to SQL View, paste in
the sql statement and attempt to run it. You should get a more informative
error message, given that you've constructed the sql statement incorrectly.

The correct syntax for an INSERT statement that inserts values into a single
new row is:

INSERT (col1,...,colN) VALUES (val1, ..., valN)

Your Response.Written sql statement should conform to that syntax. In
addition, you need to quote-delimit string values in the VALUES list, and
#-delimit date values. I prefer to avoid this hassle by using saved
parameter queries instead of dynamic sql. A Google search should provide
several posts I've made on this subject.

I usually advise people to always use the Access Query Builder to create and
test your queries before attempting to run them from ASP. In this case,
however, the Query Builder can only be used for testing, because it does not
do a good job of creating INSERT...VALUES queries. You have to write the sql
for those queries yourself.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Roland Hall]

"+FarmerPickles" <gig_bam_takemeout_@verizon.net> wrote in message
news:eviScHqUEHA.1152@TK2MSFTNGP09.phx.gbl...

: Set Conn = Server.CreateObject("ADODB.Connection")
: Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
: Server.MapPath("/tourney/fpdb/tourney.mdb")

To add...

Considering changing your driver:

[url]http://able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet[/url]

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]

[+FarmerPickles]

Ok. I got it to work. was a problem in the sql statement. Anyway.. since
this is a tournament site... i would like to know how to make it only show
the most recent entry? i have an autonumber id set, so the latest entry
would have the higest number.... how can i tell it this? otherwise.. it will
show all of them.
Thanks
Jeff

i know it would be SELECT * FROM Results Where id is something.. but i am
not sure :(



"Roland Hall" <nobody@nowhere> wrote in message
news:Ospt8BvUEHA.2908@TK2MSFTNGP10.phx.gbl...

> "+FarmerPickles" <gig_bam_takemeout_@verizon.net> wrote in message
> news:eviScHqUEHA.1152@TK2MSFTNGP09.phx.gbl...
>
> : Set Conn = Server.CreateObject("ADODB.Connection")
> : Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
> : Server.MapPath("/tourney/fpdb/tourney.mdb")
>
> To add...
>
> Considering changing your driver:
>
>

[url]http://able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet[/url]

>
> --
> Roland Hall
> /* This information is distributed in the hope that it will be useful, but
> without any warranty; without even the implied warranty of merchantability
> or fitness for a particular purpose. */
> Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
> WSH 5.6 Documentation -

[url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]

> MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]
>
>

[Bob Barrows [MVP]]

+FarmerPickles wrote:

> Ok. I got it to work. was a problem in the sql statement. Anyway..
> since
> this is a tournament site... i would like to know how to make it only
> show
> the most recent entry? i have an autonumber id set, so the latest
> entry
> would have the higest number.... how can i tell it this? otherwise..
> it will
> show all of them.
> Thanks
> Jeff
>
> i know it would be SELECT * FROM Results Where id is something.. but
> i am
> not sure :(
>
>

SELECT col1, ..., colN FROM Results
WHERE ID = (SELECT MAX(ID) FROM Results)

Don't be lazy: selstar (Select *) should not be used in production code. It
degrades performance by forcing ADO to make an extra trip to the database to
translate * into an actual list of columns, and it potentially increases
network traffic by causing your query to return data that you have no
intention of using.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

+FarmerPickles wrote:

> Ok. I got it to work. was a problem in the sql statement. Anyway..
> since
> this is a tournament site... i would like to know how to make it only
> show
> the most recent entry? i have an autonumber id set, so the latest
> entry
> would have the higest number.... how can i tell it this? otherwise..
> it will
> show all of them.
> Thanks
> Jeff
>
> i know it would be SELECT * FROM Results Where id is something.. but
> i am
> not sure :(
>

Another way of doing it would be this:

SELECT TOP 1 col1, ..., colN FROM Results ORDER BY Id DESC

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[+FarmerPickles]

Thx a bunch Bob... got it working... you rock!!
Jeff



"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OmDK8O5UEHA.716@TK2MSFTNGP11.phx.gbl...

> +FarmerPickles wrote:

> > Ok. I got it to work. was a problem in the sql statement. Anyway..
> > since
> > this is a tournament site... i would like to know how to make it only
> > show
> > the most recent entry? i have an autonumber id set, so the latest
> > entry
> > would have the higest number.... how can i tell it this? otherwise..
> > it will
> > show all of them.
> > Thanks
> > Jeff
> >
> > i know it would be SELECT * FROM Results Where id is something.. but
> > i am
> > not sure :(
> >

>
> Another way of doing it would be this:
>
> SELECT TOP 1 col1, ..., colN FROM Results ORDER BY Id DESC
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>