How to get value from col 3 instead from col 2

[Lupo]

Hi,

I would like to make a database driven stylesheet incl. maintenance option
and I have a problem. The maintenance option works perfect. But the stylesheet
means SQL statement gives me some headaches.

The database contains a table named "tbl_colors". The layout of the table
"tbl_colors" see below.

tbl_colors

ColID ¦ CSSName ¦ ColCode ¦ Description
-----------------------------------------
1 ¦ SideNav ¦ 967E85 ¦ Navi left (nl)
2 ¦ TopNav ¦ B5A6AB ¦ Navi top (nt)
3 ¦ Head1 ¦ CCCC9A ¦ Header (h1)
etc. ¦ etc. ¦ etc. | etc.


The stylesheet is setup as follows:


+ ---- start style.inc --- +
[tt]
<%
'Dimension variables
Dim adoCon 'Database Connection Variable
Dim rsCode 'Holds the recordset
Dim strCon 'Holds the db driver, path and name of db
Dim strSQL 'Holds the SQL query for the database
dim strSideNav 'Font color SideNav
dim strTopNav 'Font color TopNav
dim strHead1 'Font color Header

'Create database connection
'Create a connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Connection String
strCon = "Driver={Microsoft Access Driver (*.mdb)};DBQ="&Server.MapPath("database/layout.mdb")

'Set an active connection to the Connection object
adoCon.Open strCon

'Intialise the ADO recordset object
Set rsCode = Server.CreateObject("ADODB.Recordset")

'SQL statement to get datas from the database
strSQL = "SELECT * FROM tbl_colors"

'Query the database
rsCode.Open strSQL, strCon

'If there is config deatils in the recordset then read them in
If NOT rsCode.EOF Then

'Read in details from the recordset
strHead1 = rsCode("Head1")
strSideNav = rsCode("SideNav")
strTopNav = rsCode("TopNav")

End If

%>

<style>
h1 { color: #<% = strHead1 %>; }
a:hover.nl { color: #<% = strSideNav %>; }
a:hover.nt { color: #<% = strTopNav %>; }
</style>

%>

[/tt]
+ ---- end style.inc --- +


My problem:

How do I get the values of the column *ColCode" into the file style.inc. The only thing I get are values from column "CSSName". :-(( What do i have to do?

Thanks in advance for any solutions or alternative suggestions.

rgds Lupo
WEB Designer & WEB Developer (as per my working contract) and

 

[vbkris]

>>How do I get the values of the column *ColCode" into the file style.inc

i dont get it, u could use <%=rs("ColHeads")%>

Known is handfull, Unknown is worldfull

 

[MoLaker]

Yeah, you've got us all confused, I think. Your code makes no sense.

'Read in details from the recordset
    strHead1 = rsCode("Head1")
    strSideNav = rsCode("SideNav")
    strTopNav = rsCode("TopNav")

"Head1", "SideNav" & "TopNav" are not column names. They are column values. There is no such field as rsCode("Head1"), etc. You must use rsCode("CSSName"), rsCode("ColCode"), etc. to store the value of the record columns in your variables.

 

[Lupo]

I know my code makes really no sense....

You are right, "Head1", "SideNav" & "TopNav" are not column names. The colum names are "ColID", "CSSName", "ColCode" & "Description".

You must use rsCode("CSSName"), rsCode("ColCode"), etc. to store the value of the record columns in your variables.

hmmm, but how and where? I think I lost my brain. Can you write a sample of storing the value of the record column? Please! Thanks.


rgds Lupo
WEB Designer & WEB Developer (as per my working contract) and

 

[vbkris]

sorry, i am still confused, can u explain once more ur requirement???

Known is handfull, Unknown is worldfull

 

[MoLaker]

At this stage of your skills development, I highly recommend you become a little more familiar with database access before proceeding. The reason I say this is because the best way to do what you are attempting is fairly complex even after you extract the data from the database. Consequently, this could become an instructional undertaking bigger than we should approach in this thread.

But, the idea is to look at each record - one at a time - and store the pertinent data for each. That will require a different variable for each record or will require you to complete your <style> assignments within the confines of stepping through the recordset. Perhaps storing the data in an array would work best.

The following code has been modified to look at each record and store the color information in an array. (code is untested)

'SQL statement to get data from the database
strSQL = "SELECT * FROM tbl_colors ORDER BY ColID"

'Query the database
rsCode.Open strSQL, strCon

'dimension an array with the number of records in recordset
Recs = rsCode.recordcount
Dim ColorArray(Recs + 1,1)
' ColorArray([i]ColID,ColCode[/i])
n = 1
' Read the records one at a time
Do While NOT rsCode.EOF

'Read in details from the recordset
   ColorArray(n,0) = rsCode("ColID")
   ColorArray(n,1) = rsCode("ColCode")
   n = n + 1
   rsCode.MoveNext
Loop
%>
<style>
    h1 { color: #<% = ColorArray(3,1) %>; }
    a:hover.nl { color: #<% = ColorArray(1,1) %>; }
    a:hover.nt { color: #<% = ColorArray(2,1) %>; }
</style>

Actually, this needs to be carried further since you need a way to find a specific style area within the array rather than just depending upon the recordset order and ColID being sequential.

 

[Genimuse]

Here's bascially what your code needs to look like, using your existing code as a base, with the new/changed parts in bold:

<%
'Dimension variables
Dim adoCon                    'Database Connection Variable
Dim rsCode                    'Holds the recordset
Dim strCon                    'Holds the db driver, path and name of db
Dim strSQL                    'Holds the SQL query for the database
dim strSideNav                'Font color SideNav
dim strTopNav                 'Font color TopNav
dim strHead1                  'Font color Header

'Create database connection
'Create a connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Connection String
strCon = "Driver={Microsoft Access Driver (*.mdb)};DBQ="&Server.MapPath("database/layout.mdb")

'Set an active connection to the Connection object
adoCon.Open strCon

'Intialise the ADO recordset object
Set rsCode = Server.CreateObject("ADODB.Recordset")

'SQL statement to get datas from the database
strSQL = "SELECT * FROM tbl_colors"

'Query the database
rsCode.Open strSQL, strCon

'If there is config deatils in the recordset then read them in
[b]
While NOT rsCode.EOF
    Select Case rsCode("CSSCode")
        Case "Head1"
            strHead1 = rsCode("ColCode")
        Case "SideNav"
            strSideNav = rsCode("ColCode")
        Case "TopNav"
            strTopNav = rsCode("ColCode")
        'More cases here if you need them
    End Select

    rsCode.MoveNext
Wend
[/b]
%>

<style>
    h1 { color: #<% = strHead1 %>; }
    a:hover.nl { color: #<% = strSideNav %>; }
    a:hover.nt { color: #<% = strTopNav %>; }
</style>

 

[Lupo]

@ MoLaker

At this stage of your skills development, I highly recommend you become
little more familiar with database access before proceeding.

I fully agree. It is absolutely essential for me to become more familiar with database
access before proceeding. And by the way not just a little. ;-) I try to learn and understand.
I will do my best.

Thanks for your help.


@ Genimuse

I've just tried quickly the code with the new/changed parts you have sent.
The code works perfect!!

I think it is also necessary for me to become more familiar with SQL and
the possibilities of SQL.

Thank for your help.


rgds Lupo
WEB Designer & WEB Developer (as per my working contract) and

 

[Genimuse]

I recommend the ASP and ADO tutorials at

http://www.w3schools.com

for a good overview.