Professional Web Applications Themes

Please help with data in 2 tables - ASP Database

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 ...

  1. #1

    Default Please help with data in 2 tables

    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


    Vivista Eastbourne Guest

  2. #2

    Default Re: Please help with data in 2 tables

    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" <aa.a> wrote in message
    news:3f82b41f$0$130$7b0f0fd3mistral.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 Guest

  3. #3

    Default Re: Please help with data in 2 tables

    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" <nicolecsomewhere.net> wrote in message
    news:uE58CVNjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > news:3f82b41f$0$130$7b0f0fd3mistral.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
    > >
    > >
    >
    >

    Vivista Eastbourne Guest

  4. #4

    Default Re: Please help with data in 2 tables

    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" <aa.a> wrote in message
    news:3f82da5f$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > news:uE58CVNjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > news:3f82b41f$0$130$7b0f0fd3mistral.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 Guest

  5. #5

    Default Re: Please help with data in 2 tables

    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" <nicolecsomewhere.net> wrote in message
    news:eLDtnlOjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > news:3f82da5f$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > news:uE58CVNjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > news:3f82b41f$0$130$7b0f0fd3mistral.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
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Vivista Eastbourne Guest

  6. #6

    Default Re: Please help with data in 2 tables

    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" <aa.a> wrote in message
    news:3f83bc15$0$125$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > news:eLDtnlOjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > news:3f82da5f$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > news:uE58CVNjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > news:3f82b41f$0$130$7b0f0fd3mistral.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 Guest

  7. #7

    Default Re: Please help with data in 2 tables

    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" <nicolecsomewhere.net> wrote in message
    news:u23rUenjDHA.1488TK2MSFTNGP12.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" <aa.a> wrote in message
    > news:3f83bc15$0$125$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > news:eLDtnlOjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > news:3f82da5f$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > > news:uE58CVNjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > > news:3f82b41f$0$130$7b0f0fd3mistral.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
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Vivista Eastbourne Guest

  8. #8

    Default Re: Please help with data in 2 tables

    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" <aa.a> wrote in message
    news:3f8583b0$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > news:u23rUenjDHA.1488TK2MSFTNGP12.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" <aa.a> wrote in message
    > > news:3f83bc15$0$125$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > news:eLDtnlOjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > news:3f82da5f$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > > > news:uE58CVNjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > > > news:3f82b41f$0$130$7b0f0fd3mistral.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 Guest

  9. #9

    Default Re: Please help with data in 2 tables

    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" <nicolecsomewhere.net> wrote in message
    news:Oa0elEojDHA.2512TK2MSFTNGP09.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" <aa.a> wrote in message
    > news:3f8583b0$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > news:u23rUenjDHA.1488TK2MSFTNGP12.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" <aa.a> wrote in message
    > > > news:3f83bc15$0$125$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > > news:eLDtnlOjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > > news:3f82da5f$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > > > > news:uE58CVNjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > > > > news:3f82b41f$0$130$7b0f0fd3mistral.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
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Vivista Eastbourne Guest

  10. #10

    Default Re: Please help with data in 2 tables

    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" <nicolecsomewhere.net> wrote in message
    news:Oa0elEojDHA.2512TK2MSFTNGP09.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" <aa.a> wrote in message
    > news:3f8583b0$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > news:u23rUenjDHA.1488TK2MSFTNGP12.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" <aa.a> wrote in message
    > > > news:3f83bc15$0$125$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > > news:eLDtnlOjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > > news:3f82da5f$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > > > > news:uE58CVNjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > > > > news:3f82b41f$0$130$7b0f0fd3mistral.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
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >


    Vivista Eastbourne Guest

  11. #11

    Default Please help with data in 2 tables

    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" <nicolecsomewhere.net> wrote in message
    news:Oa0elEojDHA.2512TK2MSFTNGP09.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" <aa.a> wrote in message
    > news:3f8583b0$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > news:u23rUenjDHA.1488TK2MSFTNGP12.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" <aa.a> wrote in message
    > > > news:3f83bc15$0$125$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > > news:eLDtnlOjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > > news:3f82da5f$0$122$7b0f0fd3mistral.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" <nicolecsomewhere.net> wrote in message
    > > > > > > news:uE58CVNjDHA.2076TK2MSFTNGP09.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" <aa.a> wrote in message
    > > > > > > > news:3f82b41f$0$130$7b0f0fd3mistral.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
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >



    Vivista Eastbourne Guest

Similar Threads

  1. Empty data tables - Check
    By CF_starter in forum Coldfusion Database Access
    Replies: 5
    Last Post: September 14th, 04:23 PM
  2. Querying data that matches in two different tables
    By weswhite7 in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 28th, 03:53 PM
  3. one-to-many - how to display data from two tables?
    By Nathon Jones in forum Dreamweaver AppDev
    Replies: 5
    Last Post: February 23rd, 06:09 PM
  4. Queries on tables from different data sources
    By John in forum ASP.NET Web Services
    Replies: 1
    Last Post: June 28th, 11:23 PM
  5. Replies: 1
    Last Post: July 10th, 08:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139