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

Re: Retriving data from DB

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

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"

Re: Retriving data from DB

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

Neven


"Bob Barrows [MVP]" wrote in message
news:uaFTD1NvEHA.1520@TK2MSFTNGP11.phx.gbl...
> Neven Klofutar wrote:
> > Hi,
> >
> > Is it possible to retrive data from Recordset using this syntax
> >
> > rsRecordset("t_Person.s_Name")
> >
> > insted of
> >
> > rsRecordset("s_Name")
> >
> >
> > thanx, Neven
>
> 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"
>
>

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:
> Thanx, but it's not so easy in my case ...
>
> Neven
>
>
> "Bob Barrows [MVP]" wrote in message
> news:uaFTD1NvEHA.1520@TK2MSFTNGP11.phx.gbl...
>> Neven Klofutar wrote:
>>> Hi,
>>>
>>> Is it possible to retrive data from Recordset using this syntax
>>>
>>> rsRecordset("t_Person.s_Name")
>>>
>>> insted of
>>>
>>> rsRecordset("s_Name")
>>>
>>>
>>> thanx, Neven
>>
>> 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"

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

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]" wrote in message
news:uADKnmOvEHA.2624@TK2MSFTNGP11.phx.gbl...
> 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:
> > Thanx, but it's not so easy in my case ...
> >
> > Neven
> >
> >
> > "Bob Barrows [MVP]" wrote in message
> > news:uaFTD1NvEHA.1520@TK2MSFTNGP11.phx.gbl...
> >> Neven Klofutar wrote:
> >>> Hi,
> >>>
> >>> Is it possible to retrive data from Recordset using this syntax
> >>>
> >>> rsRecordset("t_Person.s_Name")
> >>>
> >>> insted of
> >>>
> >>> rsRecordset("s_Name")
> >>>
> >>>
> >>> thanx, Neven
> >>
> >> 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"
>
> --
> 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.
>
>

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:
> 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]" wrote in message
> news:uADKnmOvEHA.2624@TK2MSFTNGP11.phx.gbl...
>> 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:
>>> Thanx, but it's not so easy in my case ...
>>>
>>> Neven
>>>
>>>
>>> "Bob Barrows [MVP]" wrote in message
>>> news:uaFTD1NvEHA.1520@TK2MSFTNGP11.phx.gbl...
>>>> Neven Klofutar wrote:
>>>>> Hi,
>>>>>
>>>>> Is it possible to retrive data from Recordset using this syntax
>>>>>
>>>>> rsRecordset("t_Person.s_Name")
>>>>>
>>>>> insted of
>>>>>
>>>>> rsRecordset("s_Name")
>>>>>
>>>>>
>>>>> thanx, Neven
>>>>
>>>> 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"
>>
>> --
>> 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.

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

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

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]" wrote in message
news:u0q5%23HPvEHA.2804@TK2MSFTNGP14.phx.gbl...
> 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:
> > 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]" wrote in message
> > news:uADKnmOvEHA.2624@TK2MSFTNGP11.phx.gbl...
> >> 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:
> >>> Thanx, but it's not so easy in my case ...
> >>>
> >>> Neven
> >>>
> >>>
> >>> "Bob Barrows [MVP]" wrote in message
> >>> news:uaFTD1NvEHA.1520@TK2MSFTNGP11.phx.gbl...
> >>>> Neven Klofutar wrote:
> >>>>> Hi,
> >>>>>
> >>>>> Is it possible to retrive data from Recordset using this syntax
> >>>>>
> >>>>> rsRecordset("t_Person.s_Name")
> >>>>>
> >>>>> insted of
> >>>>>
> >>>>> rsRecordset("s_Name")
> >>>>>
> >>>>>
> >>>>> thanx, Neven
> >>>>
> >>>> 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"
> >>
> >> --
> >> 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.
>
> --
> 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.
>
>

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]" wrote in message
news:u0q5%23HPvEHA.2804@TK2MSFTNGP14.phx.gbl...
> 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:
> > 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]" wrote in message
> > news:uADKnmOvEHA.2624@TK2MSFTNGP11.phx.gbl...
> >> 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:
> >>> Thanx, but it's not so easy in my case ...
> >>>
> >>> Neven
> >>>
> >>>
> >>> "Bob Barrows [MVP]" wrote in message
> >>> news:uaFTD1NvEHA.1520@TK2MSFTNGP11.phx.gbl...
> >>>> Neven Klofutar wrote:
> >>>>> Hi,
> >>>>>
> >>>>> Is it possible to retrive data from Recordset using this syntax
> >>>>>
> >>>>> rsRecordset("t_Person.s_Name")
> >>>>>
> >>>>> insted of
> >>>>>
> >>>>> rsRecordset("s_Name")
> >>>>>
> >>>>>
> >>>>> thanx, Neven
> >>>>
> >>>> 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"
> >>
> >> --
> >> 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.
>
> --
> 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.
>
>

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]" wrote in message
news:OWUvhOPvEHA.3840@TK2MSFTNGP12.phx.gbl...
> 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:
> > 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:
> >> 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
> >>
> --
> 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.
>
>

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:
> 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]" wrote in message
> news:u0q5%23HPvEHA.2804@TK2MSFTNGP14.phx.gbl...
>> 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:
>>> 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]" wrote in message
>>> news:uADKnmOvEHA.2624@TK2MSFTNGP11.phx.gbl...
>>>> 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:
>>>>> Thanx, but it's not so easy in my case ...
>>>>>
>>>>> Neven
>>>>>
>>>>>
>>>>> "Bob Barrows [MVP]" wrote in message
>>>>> news:uaFTD1NvEHA.1520@TK2MSFTNGP11.phx.gbl...
>>>>>> Neven Klofutar wrote:
>>>>>>> Hi,
>>>>>>>
>>>>>>> Is it possible to retrive data from Recordset using this syntax
>>>>>>>
>>>>>>> rsRecordset("t_Person.s_Name")
>>>>>>>
>>>>>>> insted of
>>>>>>>
>>>>>>> rsRecordset("s_Name")
>>>>>>>
>>>>>>>
>>>>>>> thanx, Neven
>>>>>>
>>>>>> 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"
>>>>
>>>> --
>>>> 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.
>>
>> --
>> 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.

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

Re: Retriving data from DB

Rename the tables instead of modifying the ASP code?

Bob Barrows
Neven Klofutar wrote:
> 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]" wrote in message
> news:OWUvhOPvEHA.3840@TK2MSFTNGP12.phx.gbl...
>> 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:
>>> 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:
>>>> 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
>>>>
>> --
>> 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.

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

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 & """
"
i=i+1
next

HTH,
Bob Barrows

Neven Klofutar wrote:
> 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
>

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

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]" wrote in message
news:%23qN9BYPvEHA.3908@TK2MSFTNGP12.phx.gbl...
> Rename the tables instead of modifying the ASP code?
>
> Bob Barrows
> Neven Klofutar wrote:
> > 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]" wrote in message
> > news:OWUvhOPvEHA.3840@TK2MSFTNGP12.phx.gbl...
> >> 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:
> >>> 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:
> >>>> 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
> >>>>
> >> --
> >> 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.
>
> --
> 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.
>
>

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

Re: Retriving data from DB

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

Lant wrote:
> I am trying to connect to Access database using DAO.

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


> So I have db = OpenDatabase(path to database) in my
> ThisDocument.opened code The db is set as Public variable.

Public? Are you talking about asp?

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

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

> 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"
> ThisDocument.db.Execute strString
> Gives an error.

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"

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:

> You should start a new thread (conversation) instead of replying to an old
> one.
>
> Lant wrote:
> > I am trying to connect to Access database using DAO.
>
> That's your first mistake. DAO does not work well in asp. It works, but not
> well ADO is what you should be using.
>
>
> > So I have db = OpenDatabase(path to database) in my
> > ThisDocument.opened code The db is set as Public variable.
>
> Public? Are you talking about asp?
>
> >
> > 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.
>
> 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
>
> > 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"
> > ThisDocument.db.Execute strString
> > Gives an error.
>
> 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"
>
>
>

Re: Retriving data from DB

<<
Is it possible to retrive data from Recordset using this syntax

rsRecordset("t_Person.s_Name")

insted of

rsRecordset("s_Name")
>>

Well don't forget that you can rename fields in your SQL statement:

strSQL = "SELECT tblClient.LName AS ClientLName, tblStudent.LName AS
StudentLName ... "

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!