Professional Web Applications Themes

Retriving data from DB - ASP Database

Hi, Is it possible to retrive data from Recordset using this syntax rsRecordset("t_Person.s_Name") insted of rsRecordset("s_Name") thanx, Neven...

  1. #1

    Default Retriving data from DB

    Hi,

    Is it possible to retrive data from Recordset using this syntax

    rsRecordset("t_Person.s_Name")

    insted of

    rsRecordset("s_Name")


    thanx, Neven


    Neven Guest

  2. #2

    Default Re: Retriving data from DB

    Neven Klofutar wrote: 

    No. The Field object's Name property contains only the unqualified column
    name, or the alias assigned to the column in your sql statement. If you have
    two columns with the same name, you need to use the AS keyword to assign an
    alias to one of them.

    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 Guest

  3. #3

    Default Re: Retriving data from DB

    Thanx, but it's not so easy in my case ...

    Neven


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    >
    > No. The Field object's Name property contains only the unqualified column
    > name, or the alias assigned to the column in your sql statement. If you[/ref]
    have 
    an 


    Neven Guest

  4. #4

    Default Re: Retriving data from DB

    Maybe not, but it's the only solution, outside of returning two recordsets.
    Perhaps if you could explain why it's not easy in your case, we could
    suggest some workarounds.

    I'm making the assumption that the two columns with the same name do not
    contain the same data. Otherwise I would be criticizing you for returning
    the same data twice. :-) This is always due to the inappropriate use of
    selstar (select *).

    Bob Barrows

    Neven Klofutar wrote: 
    >>
    >> No. The Field object's Name property contains only the unqualified
    >> column name, or the alias assigned to the column in your sql
    >> statement. If you have two columns with the same name, you need to
    >> use the AS keyword to assign an alias to one of them.
    >>
    >> 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"[/ref][/ref]

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  5. #5

    Default Re: Retriving data from DB

    Well,

    I'm working on an upgrade of web aplication my precesor created some 2-3
    years ago ...
    It's an aplication created for educational company to keep records of
    seminars and atendants.

    The main problem is that is was probably first application my precesor ever
    created, so it has some huge problem in DB design.

    There are 2 tables that represent schedule for all seminars throught the
    year ...
    tblSchedule and tlbScheduleAdd

    tblSchedule
    *********
    Date (datetime)
    Day (nvarchar)
    (now comes the fun part :))
    Course11
    Time11
    Instructor11
    Course12
    Time12
    Instructor12
    ....
    this continus throught the whoe page until Course105
    let me explain this code:
    Course11 - it's a field containing course name for the classroom 1 in
    the 1st time period
    Course21 - it's a field containing course name for the classroom 2 in
    the 1st time period
    Course35 - it's a field containing course name for the classroom 3 in
    the 5st time period

    table tblScheduleAdd is almost the same
    ******************************
    Date (datetime)
    Day (nvarchar)
    Color11
    Reservations11
    Time11
    Color12
    Reservations12
    Time12
    ....

    Now, when I try to find a specific Course I have to loop throught some wierd
    recordset connection this 2 tables, and as you can see both tables have some
    fields named the same ...
    You probly think I'm insane, that this is a joke, but this is my reality for
    quite some time, and my boss won't let me create a new application.
    (explanation: It would take loooooong time to create something new, and it's
    better to keep patching this one :((()

    thanx for listening, Neven



    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    recordsets. [/ref]
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    Neven Guest

  6. #6

    Default Re: Retriving data from DB

    Ugh! Not only bad design, but he used reserved keywords for his column
    names! My condolences.

    You can use a union query to normalize this design. What database are you
    using? In Access, you would create a saved query (call it qSchedule) with
    this sql:

    Select [Date],[Day],1 As ClassRoom, 1 As Period,
    [Course 11] As Course, [Time 11] As [Time],
    [Instructor 11] As Instructor FROM tblSchedule
    UNION ALL
    Select [Date],[Day],1 As ClassRoom, 2 As Period,
    [Course 12] As Course, [Time 12] As [Time],
    [Instructor 12] As Instructor FROM tblSchedule
    UNION ALL
    Select [Date],[Day],1 As ClassRoom, 3 As Period,
    [Course 13] As Course, [Time 13] As [Time],
    [Instructor 13] As Instructor FROM tblSchedule
    etc.

    In SQL Server, you could use virtually the same sql statement to create a
    View.

    Now, finding a specific course will simply be:

    SELECT [Date],[Day],Period,Course,[Time],Instructor
    FROM qSchedule
    WHERE Course = 'Some Course'

    You could probably create a subroutine in an Access module to loop through
    the fields in your table and generate this sql statement for you (and even
    automate creating the saved query), if you know enough Access VBA. If not,
    post to an Access newsgroup for help.

    HTH,
    Bob Barrows


    Neven Klofutar wrote: 
    >>
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.[/ref][/ref]

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  7. #7

    Default Re: Retriving data from DB

    The problem with this solution of course is that the saved query/view will
    not be indexed, so searches may be slow. You should consider creating a new
    table. Since you need to create the saved union query regardless, you can
    use it to create a new table*:

    Select * INTO tblScheduleNormalized
    FROM qSchedule

    Then open tblScheduleNormalized in Design View, create a primary key using
    the Date,Classroom, and Period fields, and create an index on the Course
    field.

    Depending on how often the tblSchedule table is updated, you can either
    create a scheduled task to refresh the data in tblScheduleNormalized, or
    make the refresh of the table part of the update of tblSchedule. You would
    use this to refresh it:

    After deleting all the records in tblScheduleNormalized, do this

    INSERT INTO tblScheduleNormalized
    Select * FROM qSchedule

    Bob Barrows

    *Even though I used "tblScheduleNormalized" for the name of this table, I am
    aware that the table is not fully normalized. Hey! I had to call it
    something! :-)


    Bob Barrows [MVP] wrote: [/ref]
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  8. #8

    Default Re: Retriving data from DB

    Thanx Bob,

    I'm using MS SQL Server, I'll try to create view, I just use ASP to
    dinamicaly create SQL query using 2 nested FOR loops.

    Thanx again, Neven


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:u0q5%phx.gbl... [/ref]
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    Neven Guest

  9. #9

    Default Re: Retriving data from DB

    One more question on this subject ...

    If I use:

    rsRecordset("Name"), which name would I get from the recordset, the first
    one or the second, or random or ?

    Thanx, Neven


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:u0q5%phx.gbl... [/ref]
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    Neven Guest

  10. #10

    Default Re: Retriving data from DB

    I thought of something similar, but the problem is:
    I have way too many code to be absolutely certain that I can find every ASP
    page that is working with those 2 tables, and rewrite those pages ...

    Neven


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    new 
    am [/ref]
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    Neven Guest

  11. #11

    Default Re: Retriving data from DB

    In that case, you can use a T-SQL script to generate the sql (see
    ..sqlserver.programming for help). An you can use either a scheduled task or
    a trigger to refresh the "normalized" table I suggested in my second post.

    Bob Barrows
    Neven Klofutar wrote: 
    >>
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.[/ref][/ref]

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  12. #12

    Default Re: Retriving data from DB

    Rename the tables instead of modifying the ASP code?

    Bob Barrows
    Neven Klofutar wrote: 
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.[/ref][/ref]

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  13. #13

    Default Re: Retriving data from DB

    Whatever order they appear in the sql statement. It will not be random.

    Here's some quick code to help you determine this order:

    dim i
    i=1
    for each fld in rs.Fields
    response.write i & " - " & fld.name & ": """ & fld.value & """<BR>"
    i=i+1
    next

    HTH,
    Bob Barrows

    Neven Klofutar wrote: 

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  14. #14

    Default Re: Retriving data from DB

    Yup, but I would still have to be shure I got every page that works with
    those two pages ... it could be painfull ...

    Bob, thanx alot !!!

    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:%phx.gbl... [/ref]
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    Neven Guest

  15. #15

    Default Re: Retriving data from DB

    I am trying to connect to Access database using DAO.
    So I have db = OpenDatabase(path to database) in my ThisDoent.opened code
    The db is set as Public variable.

    On a user form I have a comboBox and a TextBox. I need to populate the
    combobox with values from a table in the database and populate the
    details(from the table) in the textbox based on the selection in the
    combobox.

    I have been using a Select statement to get the list for the ComboBox and
    using the Update Event of the combobox to query into a record Set the Item
    details based on the user selectionin the combobox. This seems to work but
    would involve querying the database on every change in the combobox.

    Fromthe posts on this site I figured there is a way to associate the
    rowSource of the combo box with a query and display details in the Textbox as
    the user selects an item in the combobox. I tried assigning the Select
    statement as the rowSource for the ComboBox in the Initialize event of the
    user form like this:

    strString = "SELECT distinct table_Items.Item_name FROM table_Items"
    ThisDoent.db.Execute strString
    Gives an error.

    I do not make any other database connections in my code. Would someone
    please tell me how I can get the Textbox details tied to the ComboBox and any
    mistakes in my database connections.

    Lant Guest

  16. #16

    Default Re: Retriving data from DB

    You should start a new thread (conversation) instead of replying to an old
    one.

    Lant wrote: 

    That's your first mistake. DAO does not work well in asp. It works, but not
    well ADO is what you should be using.

     

    Public? Are you talking about asp?
     

    Not really. At least no way that does not involve a trip to the server every
    time a new item in the box is selected.

    With client-side code however, there are options. But that is off-topic for
    this list
     

    It seems to me that you are not talking about asp. I suggest you try an
    Access newsgroup


    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 Guest

  17. #17

    Default Re: Retriving data from DB

    Thank You Bob.
    Yes, I am talking about Access.
    Just figured out that the 'Post New Question' box was not showing up as I had
    pop-ups blocked. Shall try a new post.

    Lant

    "Bob Barrows [MVP]" wrote:
     
    >
    > That's your first mistake. DAO does not work well in asp. It works, but not
    > well ADO is what you should be using.
    >

    >
    > Public? Are you talking about asp?

    >
    > Not really. At least no way that does not involve a trip to the server every
    > time a new item in the box is selected.
    >
    > With client-side code however, there are options. But that is off-topic for
    > this list

    >
    > It seems to me that you are not talking about asp. I suggest you try an
    > Access newsgroup
    >
    >
    > 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"
    >
    >
    >[/ref]
    Lant Guest

Similar Threads

  1. Storing and Retriving Data Locally
    By The Other White Meat in forum Macromedia Flash Data Integration
    Replies: 2
    Last Post: June 20th, 05:26 PM
  2. retriving data from memo data type of msaccess to asp.net
    By Aneesh a in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 7th, 01:11 AM
  3. setting and retriving the correct text member height!
    By garwspeed in forum Macromedia Director Lingo
    Replies: 2
    Last Post: November 19th, 09:17 PM
  4. Putting/retriving files into a database
    By Eric Kincl in forum PHP Development
    Replies: 4
    Last Post: November 10th, 06:05 PM
  5. Access - retriving primary key ...
    By Neven Klofutar in forum ASP Database
    Replies: 1
    Last Post: July 21st, 10:40 AM

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