Hi Nadia, I have a similar setup and use the following to update the db.

Case 1 is if there are optional boxes in the form:

strchg = "update tblfolks set name='"&replace( request.form("newname"), "'",
"''")&"'"

bln=true  ' to test if optional entry is the last and thus doesn't need the
comma before it

if len(request.form("phone"))>0 then
 if bln=true then
strchg=strchg&" phone='"&cstr(request.form("newphone"))&"'"
 else
 strchg=strchg&", phone='"&cstr(request.form("newphone"))&"'"
 end if
bln=false
end if

if bln=true then
 strchg=strchg&" chgper = '"&chgper&"'"
 else
 strchg=strchg&", chgper = '"&chgper&"'"
end if

strchg=strchg&" where pw = '"&cstr(request.form("pw"))&"'"
objcon.execute(strchg)


Case 2 is simpler (all form boxes required):

strchg="update tbldrink set drinkstat=0 where drinkid="&vardrinkid
objcon.execute(strchg)

hth,

Mike


----- Original Message -----
From: Nadia Hamza <hamzan@h...>
To: sql language <sql_language@p...>
Sent: Saturday, March 17, 2001 5:52 PM
Subject: [sql_language] Re: Dynamic Sql Statements


> Hi,
>
> I'm doing queries from an ASP page so I'm using VB Script with Access. Any
> help is greatly appreciated.
>
> Nadia
>
>
> > Nadia,
> >
> > Most database packages allow you to prepare sql queries inadvance and
> pass
> > or "bind" the variable parameters when you execute the query.
> >
> > The exact syntax you need will depend on what dbms you are using.  I
> will
> > also depend on how you are connecting to your database.  Are you using
> VBA
> > with access, JDBC with Java ???
> >
> > I do this all the time in PERL, and have done it using the JDBC and
> using
> > VBA with MS Access, and I'm sure it's supported by just about every
> other
> > dbms.
> >
> > Perhaps if you tell me what package you are using I'll be able to help.
> >
> > Also, I've never SQL syntax like your update example.  Usually an update
> > is done like this:
> >
> > update table set (col1, col2, col3) = (val1, val2, val3)
> >
> >
> >
> > > Hi,
> > >
> > > I've got an array of 4 items which correspond to 4 columns in a table.
> > > ex:Paper1, Paper2, Paper3, Paper3
> > >
> > > I want to create an update SQL statement that will allow me to have
> > > variables.
> > >
> > > ie: Update tRegistrations327 Set Paper1 = item(i), Paper2 = item(i+1),
> > > Paper3 = item(i+2), Paper3(i+3)
> > >
> > > What is the best way of doing this?
> > >
> > > Another possibility might be doing a loop and updating one at a time.
> I
> > > know I can work around this problem.  I just want to know what would
> be
> > > the easiest and most efficient way of doing this.  Thanks!
> > >
> > > Nadia
> >
>
>