Problem with data in 2 forms: Please help

[Vivista Eastbourne]

Hi Nicole,
Thanks again.

The Database is an Access database called userdetails.mdb
The table is called UserDetails
The field is called Username and is unique

SELECT * FROM Userdetails WHERE username = strUsername

Is this correct?

Also, in part 2 of your last response, how would I use the ADO command set?

In part 3, how would I code this?

Thanks


"Nicole Calinoiu" <nicolec@somewhere.net> wrote in message
news:Oa0elEojDHA.2512@TK2MSFTNGP09.phx.gbl...

> Andy,
>
> For this, you don't need to query the db to find out if a row for the user
> name already exists since you need the data for the user anyway if it

does.

> Since you haven't mentioned which database platform you're using, it'a wee
> bit difficult to give any details of the querying approach. However,

here's

> some general guidelines:
>
> 1. Set up a parameterized stored query (Access) or a stored procedure

(SQL

> Server) in the following form:
> SELECT <the columns you want to display on the form>
> FROM <your users table>
> WHERE <the user name column> = <the user name parameter>
> 2. User the ADO Command object in your user details page to execute this
> stored query/procedure, passing the user name received from the URL query
> string as the value the user name parameter, and returning an ADO

Recordset

> object.
> 3. Assuming you have a uniqueness contraint on the user name column in

your

> users table, the recordset returned from step #2 will contain either one

row

> (the matching user) or no rows (indicating that it's a new user name). If
> the recordset does contain a row, use the values from the recordset fields
> to pre-populate the input elements on your form. If it doesn't contain a
> row, leave the inputs blank or give them appropriate default values.
>
> If you need help with the details of any particular aspect of this, please
> specify what exactly.
>
> HTH,
> Nicole
>
>
>
>
> "Vivista Eastbourne" <a@a.a> wrote in message
> news:3f8583b0$0$122$7b0f0fd3@mistral.news.newnet.c o.uk...

> > Hi Nicole,
> >
> > Thanks for that, but how would I have the asp query the databases

username

> > field in all the records and determine if that record existed?
> > Thanks Again for all your help
> > I have been racking my brains with this part of the project.
> > Cheers
> >
> > Andy G
> >
> >
> > "Nicole Calinoiu" <nicolec@somewhere.net> wrote in message
> > news:u23rUenjDHA.1488@TK2MSFTNGP12.phx.gbl...

> > > Andy,
> > >
> > > In that case, instead of a link on the SCR page, you need to redirect

to

> > the

> > > user details page after completing the save from the server-side code

of

> > the

> > > SCR page. Let's say that by the time of the sucessful save in the SCR

> > page,

> > > the user name is stored in a variable named strUserName. After

> completing

> > > the save and performing any necessary clean-up activity in the SCR

page,

> > > just use the following line of code to redirect to the user details

> page:

> > >
> > > Response.Redirect "UserDetails.asp?UserName=" & strUserName
> > >
> > > This will cause a client-side redirection to the user details page,

with

> > the

> > > target user name included in the query string.
> > >
> > > HTH,
> > > Nicole
> > >
> > >
> > >
> > >
> > > "Vivista Eastbourne" <a@a.a> wrote in message
> > > news:3f83bc15$0$125$7b0f0fd3@mistral.news.newnet.c o.uk...
> > > > Hi Nicole
> > > >
> > > > There is an add buttin on the SCR form which, when pressed, should

add

> > the

> > > > SCR record to the database, then redirect to the userdetails form

and

> > > check
> > > > the 'username' from the SCR form in the userdetails table, if the

name

> > > > exists in a record, bring up that record for updating, otherwise

bring

> > up

> > > > the form for a new record.
> > > >
> > > > Thanks
> > > >
> > > > Andy G
> > > >
> > > > "Nicole Calinoiu" <nicolec@somewhere.net> wrote in message
> > > > news:eLDtnlOjDHA.2076@TK2MSFTNGP09.phx.gbl...
> > > > > Andy,
> > > > >
> > > > > I think I may have understood your problem incorrectly. When do

you

> > > want
> > > > to
> > > > > launch the user details form? From a link on the SCR form? When

> the

> > > SCR
> > > > > form data is saved? Some other trigger point?
> > > > >
> > > > > Nicole
> > > > >
> > > > >
> > > > > "Vivista Eastbourne" <a@a.a> wrote in message
> > > > > news:3f82da5f$0$122$7b0f0fd3@mistral.news.newnet.c o.uk...
> > > > > > Hi Nicole,
> > > > > > Thanks for responding
> > > > > > I am fairly new to ASP and not fully up to speed.
> > > > > >
> > > > > > Currently after the add button is pressed, the followiung is the
> > > submit
> > > > > > code:
> > > > > > <FORM name=frmDetail method=post
> > > > > > action="SCR_detail.asp<%=GetQueryString("Detail", Empty, Empty,

> > Empty,

> > > > > > Empty)%>">
> > > > > > to follow your convention of <a
> > > > > > href="userdetails.asp?username=whatever">...</a>, I presume the

> code

> > > > would
> > > > > > look like this:
> > > > > > <FORM name=frmDetail method=post
> > > > > >
> > > > >
> > > >
> > >

> >

>

action="userdetails_detail.asp?vusername=vusername <%=GetQueryString("Detail"

> > > > > > , Empty, Empty, Empty, Empty)%>">
> > > > > > where vusername is the variable used on both forms in asp but in
> > > > different
> > > > > > database tables, is this correct?
> > > > > >
> > > > > > Also, what code would be required in the userdetails_detail.asp

> page

> > > to
> > > > > > check if the username exists in that table?
> > > > > >
> > > > > > Thanks Again
> > > > > >
> > > > > > Andy G
> > > > > >
> > > > > > "Nicole Calinoiu" <nicolec@somewhere.net> wrote in message
> > > > > > news:uE58CVNjDHA.2076@TK2MSFTNGP09.phx.gbl...
> > > > > > > Andy,
> > > > > > >
> > > > > > > Put all the logic for determining whether your dealing with

new

> or

> > > > > > existing
> > > > > > > user data in the user details page. In the SCR page, just use

a

> > > link
> > > > to
> > > > > > the
> > > > > > > user details page that passes the SCR user name in the query

> > string.

> > > > > > e.g.:
> > > > > > >
> > > > > > > <a href="userdetails.asp?username=whatever">...</a>
> > > > > > >
> > > > > > > In ther user details page, query the db based for data on the
> > > > specified
> > > > > > > user. If you get back any data, pre-populate all the controls

> > with

> > > > data
> > > > > > on
> > > > > > > the user. If you don't get back data from the db, leave all

the

> > > > > controls
> > > > > > > blank except for the user name (which you can populate with

the

> > > value
> > > > > > > received from the query string).
> > > > > > >
> > > > > > > Don't bother flagging the row as an insert or update at the

time

> > of

> > > > the
> > > > > > > initial read from the db. Even if it's a new row when the

page

> is

> > > > > > initially
> > > > > > > loaded, it might not be by the time it's saved (i.e.: another

> user

> > > may
> > > > > > have
> > > > > > > added data against the same user name in the interim). Just

add

> > > logic
> > > > > to
> > > > > > > the code that saves the edited data to the db to update/insert

> as

> > > > > > necessary
> > > > > > > at the time of the actual save.
> > > > > > >
> > > > > > > HTH,
> > > > > > > Nicole
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Vivista Eastbourne" <a@a.a> wrote in message
> > > > > > > news:3f82b41f$0$130$7b0f0fd3@mistral.news.newnet.c o.uk...
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > I have 2 tables, one called SCR and one called userdetails
> > > > > > > > SCR table contains details of a change
> > > > > > > > Userdetails contains information about a user
> > > > > > > >
> > > > > > > > All the data is entered via asp on our intranet
> > > > > > > >
> > > > > > > > One of the SCR fields is Username
> > > > > > > > One of the Userdetails fields is also Username
> > > > > > > >
> > > > > > > > Both tables currently have a seperate asp form (by request

> from

> > > > above)
> > > > > > > >
> > > > > > > > How can I check if the value entered for username on the SCR

> > form

> > > > > exists
> > > > > > > as
> > > > > > > > a username value in the Userdetails form, and if it does,

> bring

> > up

> > > > the
> > > > > > > > record for that username, or if not, open up the blank form.
> > > > > > > >
> > > > > > > > Bear in mind that the forms already exist, just really need
> > > pointers
> > > > > on
> > > > > > > the
> > > > > > > > code.
> > > > > > > >
> > > > > > > > Any help is (as always) greatly appreciated
> > > > > > > >
> > > > > > > > Many Thanks if you can help (you were all newbies once upon

a

> > > time)
> > > > > > > >
> > > > > > > > Andy G
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Nicole Calinoiu]

See inline...

"Vivista Eastbourne" <a@a.a> wrote in message
news:3f8d4ec1$0$127$7b0f0fd3@mistral.news.newnet.c o.uk...

> Hi Nicole,
> Thanks again.
>
> The Database is an Access database called userdetails.mdb
> The table is called UserDetails
> The field is called Username and is unique
>
> SELECT * FROM Userdetails WHERE username = strUsername
>
> Is this correct?
>

Not quite. First, create a stored parameter query with a SQL statement like
the following:

PARAMETERS pUserName Text ( <length of your UserName column in the
UserDetails table> );
SELECT <the fields you need for the form>
FROM UserDetails
WHERE UserName = pUserName;

> Also, in part 2 of your last response, how would I use the ADO command

set?

There is an example with quite a bit of explanation at
[url]http://www.webconcerns.co.uk/asp/accessqueries/accessqueries.asp[/url]. You might
also want to take a look as the ADO documentation in the MSDN library. If
you don't have a local copy, see
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdaobj01.asp[/url].

>
> In part 3, how would I code this?

Once you've got your recordset open, it'll have either no records or a
single record. If it has a record, transfer the field values into variables
for later use. If it doesn't, set the variable values to the appropriate
default values. e.g.:

with rstUser
if .eof and .bof then
strFirstName = ""
strLastName = ""
'and so on for the rest of the variables you need...
else
strFirstName = .fields("FirstName").value
strLastName = .fields("LastName").value
'...
end if
.close 'since you shouldn't need it any more once you've captured all
the available data.
end with


When the form gets rendered, you simply write the variable values (with
appropriate HTML-encoding) into the HTML tags for your input elements.
e.g.:

If your HTML is hard-coded:
<input type=text name=FirstName value="<% Response.Write
Server.HtmlEncode(strFirstName) %>">

If you're rendering the HTML from script:
Response.Write "<input type=text name=FirstName value="""
Response.Write Server.HtmlEncode(strFirstName)
Response.Write """>"

HTH,
Nicole

>
> Thanks
>
>
> "Nicole Calinoiu" <nicolec@somewhere.net> wrote in message
> news:Oa0elEojDHA.2512@TK2MSFTNGP09.phx.gbl...

> > Andy,
> >
> > For this, you don't need to query the db to find out if a row for the

user

> > name already exists since you need the data for the user anyway if it

> does.

> > Since you haven't mentioned which database platform you're using, it'a

wee

> > bit difficult to give any details of the querying approach. However,

> here's

> > some general guidelines:
> >
> > 1. Set up a parameterized stored query (Access) or a stored procedure

> (SQL

> > Server) in the following form:
> > SELECT <the columns you want to display on the form>
> > FROM <your users table>
> > WHERE <the user name column> = <the user name parameter>
> > 2. User the ADO Command object in your user details page to execute

this

> > stored query/procedure, passing the user name received from the URL

query

> > string as the value the user name parameter, and returning an ADO

> Recordset

> > object.
> > 3. Assuming you have a uniqueness contraint on the user name column in

> your

> > users table, the recordset returned from step #2 will contain either one

> row

> > (the matching user) or no rows (indicating that it's a new user name).

If

> > the recordset does contain a row, use the values from the recordset

fields

> > to pre-populate the input elements on your form. If it doesn't contain

a

> > row, leave the inputs blank or give them appropriate default values.
> >
> > If you need help with the details of any particular aspect of this,

please

> > specify what exactly.
> >
> > HTH,
> > Nicole
> >
> >
> >
> >
> > "Vivista Eastbourne" <a@a.a> wrote in message
> > news:3f8583b0$0$122$7b0f0fd3@mistral.news.newnet.c o.uk...

> > > Hi Nicole,
> > >
> > > Thanks for that, but how would I have the asp query the databases

> username

> > > field in all the records and determine if that record existed?
> > > Thanks Again for all your help
> > > I have been racking my brains with this part of the project.
> > > Cheers
> > >
> > > Andy G
> > >
> > >
> > > "Nicole Calinoiu" <nicolec@somewhere.net> wrote in message
> > > news:u23rUenjDHA.1488@TK2MSFTNGP12.phx.gbl...
> > > > Andy,
> > > >
> > > > In that case, instead of a link on the SCR page, you need to

redirect

> to

> > > the
> > > > user details page after completing the save from the server-side

code

> of

> > > the
> > > > SCR page. Let's say that by the time of the sucessful save in the

SCR

> > > page,
> > > > the user name is stored in a variable named strUserName. After

> > completing

> > > > the save and performing any necessary clean-up activity in the SCR

> page,

> > > > just use the following line of code to redirect to the user details

> > page:

> > > >
> > > > Response.Redirect "UserDetails.asp?UserName=" & strUserName
> > > >
> > > > This will cause a client-side redirection to the user details page,

> with

> > > the
> > > > target user name included in the query string.
> > > >
> > > > HTH,
> > > > Nicole
> > > >
> > > >
> > > >
> > > >
> > > > "Vivista Eastbourne" <a@a.a> wrote in message
> > > > news:3f83bc15$0$125$7b0f0fd3@mistral.news.newnet.c o.uk...
> > > > > Hi Nicole
> > > > >
> > > > > There is an add buttin on the SCR form which, when pressed, should

> add

> > > the
> > > > > SCR record to the database, then redirect to the userdetails form

> and

> > > > check
> > > > > the 'username' from the SCR form in the userdetails table, if the

> name

> > > > > exists in a record, bring up that record for updating, otherwise

> bring

> > > up
> > > > > the form for a new record.
> > > > >
> > > > > Thanks
> > > > >
> > > > > Andy G
> > > > >
> > > > > "Nicole Calinoiu" <nicolec@somewhere.net> wrote in message
> > > > > news:eLDtnlOjDHA.2076@TK2MSFTNGP09.phx.gbl...
> > > > > > Andy,
> > > > > >
> > > > > > I think I may have understood your problem incorrectly. When do

> you

> > > > want
> > > > > to
> > > > > > launch the user details form? From a link on the SCR form?

When

> > the

> > > > SCR
> > > > > > form data is saved? Some other trigger point?
> > > > > >
> > > > > > Nicole
> > > > > >
> > > > > >
> > > > > > "Vivista Eastbourne" <a@a.a> wrote in message
> > > > > > news:3f82da5f$0$122$7b0f0fd3@mistral.news.newnet.c o.uk...
> > > > > > > Hi Nicole,
> > > > > > > Thanks for responding
> > > > > > > I am fairly new to ASP and not fully up to speed.
> > > > > > >
> > > > > > > Currently after the add button is pressed, the followiung is

the

> > > > submit
> > > > > > > code:
> > > > > > > <FORM name=frmDetail method=post
> > > > > > > action="SCR_detail.asp<%=GetQueryString("Detail", Empty,

Empty,

> > > Empty,
> > > > > > > Empty)%>">
> > > > > > > to follow your convention of <a
> > > > > > > href="userdetails.asp?username=whatever">...</a>, I presume

the

> > code

> > > > > would
> > > > > > > look like this:
> > > > > > > <FORM name=frmDetail method=post
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >

> >

>

action="userdetails_detail.asp?vusername=vusername <%=GetQueryString("Detail"

> > > > > > > , Empty, Empty, Empty, Empty)%>">
> > > > > > > where vusername is the variable used on both forms in asp but

in

> > > > > different
> > > > > > > database tables, is this correct?
> > > > > > >
> > > > > > > Also, what code would be required in the

userdetails_detail.asp

> > page

> > > > to
> > > > > > > check if the username exists in that table?
> > > > > > >
> > > > > > > Thanks Again
> > > > > > >
> > > > > > > Andy G
> > > > > > >
> > > > > > > "Nicole Calinoiu" <nicolec@somewhere.net> wrote in message
> > > > > > > news:uE58CVNjDHA.2076@TK2MSFTNGP09.phx.gbl...
> > > > > > > > Andy,
> > > > > > > >
> > > > > > > > Put all the logic for determining whether your dealing with

> new

> > or

> > > > > > > existing
> > > > > > > > user data in the user details page. In the SCR page, just

use

> a

> > > > link
> > > > > to
> > > > > > > the
> > > > > > > > user details page that passes the SCR user name in the query
> > > string.
> > > > > > > e.g.:
> > > > > > > >
> > > > > > > > <a href="userdetails.asp?username=whatever">...</a>
> > > > > > > >
> > > > > > > > In ther user details page, query the db based for data on

the

> > > > > specified
> > > > > > > > user. If you get back any data, pre-populate all the

controls

> > > with
> > > > > data
> > > > > > > on
> > > > > > > > the user. If you don't get back data from the db, leave all

> the

> > > > > > controls
> > > > > > > > blank except for the user name (which you can populate with

> the

> > > > value
> > > > > > > > received from the query string).
> > > > > > > >
> > > > > > > > Don't bother flagging the row as an insert or update at the

> time

> > > of
> > > > > the
> > > > > > > > initial read from the db. Even if it's a new row when the

> page

> > is

> > > > > > > initially
> > > > > > > > loaded, it might not be by the time it's saved (i.e.:

another

> > user

> > > > may
> > > > > > > have
> > > > > > > > added data against the same user name in the interim). Just

> add

> > > > logic
> > > > > > to
> > > > > > > > the code that saves the edited data to the db to

update/insert

> > as

> > > > > > > necessary
> > > > > > > > at the time of the actual save.
> > > > > > > >
> > > > > > > > HTH,
> > > > > > > > Nicole
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Vivista Eastbourne" <a@a.a> wrote in message
> > > > > > > > news:3f82b41f$0$130$7b0f0fd3@mistral.news.newnet.c o.uk...
> > > > > > > > > Hi
> > > > > > > > >
> > > > > > > > > I have 2 tables, one called SCR and one called userdetails
> > > > > > > > > SCR table contains details of a change
> > > > > > > > > Userdetails contains information about a user
> > > > > > > > >
> > > > > > > > > All the data is entered via asp on our intranet
> > > > > > > > >
> > > > > > > > > One of the SCR fields is Username
> > > > > > > > > One of the Userdetails fields is also Username
> > > > > > > > >
> > > > > > > > > Both tables currently have a seperate asp form (by request

> > from

> > > > > above)
> > > > > > > > >
> > > > > > > > > How can I check if the value entered for username on the

SCR

> > > form
> > > > > > exists
> > > > > > > > as
> > > > > > > > > a username value in the Userdetails form, and if it does,

> > bring

> > > up
> > > > > the
> > > > > > > > > record for that username, or if not, open up the blank

form.

> > > > > > > > >
> > > > > > > > > Bear in mind that the forms already exist, just really

need

> > > > pointers
> > > > > > on
> > > > > > > > the
> > > > > > > > > code.
> > > > > > > > >
> > > > > > > > > Any help is (as always) greatly appreciated
> > > > > > > > >
> > > > > > > > > Many Thanks if you can help (you were all newbies once

upon

> a

> > > > time)
> > > > > > > > >
> > > > > > > > > Andy G
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>
>
>