Createobject of '(null)' caused exception C0000005

Discussion in 'ASP General' started by wk6pack, Sep 29, 2004.

  1. wk6pack

    wk6pack Guest

    Hi,

    I'm getting this error on my asp page intermittently. One day it is fine,
    another day, it crashes a lot. I have searched the web and microsoft on
    this and they say it is a recordset assigned to a session variable.

    I dont assign any record sets to session variables in my code. I assign
    values to the session variables though.
    The place it seems to crash is Set AccessConn =
    Server.CreateObject("ADODB.Connection")

    code:

    connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    Server.mapPath("EmployeeTransactions.mdb")
    Set AccessConn = Server.CreateObject("ADODB.Connection")
    AccessConn.open connstr

    'get session("loc") to see if they are allowed to update records
    lloc = session("sploctype")
    loccode = session("splocationdesc")

    'Determining the ability to access update records
    if lloc = "School" then
    accessupdate = 0
    else
    accessupdate = 1
    end if

    Set rs = Server.CreateObject("ADODB.Recordset")
    sql = "select emprecid, employeeno, employeelastname, employeefirstname,
    iif(isnull(hrstatus),'&nbsp',hrstatus) as hrstatus,
    iif(isnull(hrdate),'&nbsp',hrdate) as hrdate,
    iif(isnull(budgetstatus),'&nbsp',budgetstatus) as budgetstatus,
    iif(isnull(budgetdate),'&nbsp',budgetdate) as budgetdate," & _
    " iif(isnull(payrollstatus),'&nbsp',payrollstatus) as payrollstatus,
    iif(isnull(payrolldate),'&nbsp',payrolldate) as payrolldate,
    iif(isnull(benefitdate),'&nbsp',benefitdate) as benefitdate,
    iif(isnull(createdby),'&nbsp',createdby) as createdby,
    iif(isnull(processstatus),'&nbsp',processstatus) as processstatus,
    employeetype, location from employeetransaction "

    'we only display the records for the school location otherwise, we display
    all for departments
    if lloc="School" then
    sql = sql & " where location = '" & loccode & "' and
    ((EmployeeTransaction.PayrollDate >=(date()-30)) OR
    (Isnull([EmployeeTransaction].[PayrollDate])<> FALSE)) order by emprecid
    desc "
    else
    sql = sql & " where ((EmployeeTransaction.PayrollDate >= (date()-30)) OR
    (Isnull([EmployeeTransaction].[PayrollDate])<>FALSE)) order by emprecid desc
    "
    end if
    set rs = AccessConn.execute(sql)

    Any ideas??
    thanks,
    Will
    wk6pack, Sep 29, 2004
    #1
    1. Advertising

  2. Several suggestions.

    (a) don't use Server.CreateObject, just CreateObject

    (b) what is set rs = Server.CreateObject("ADODB.Recordset") for? You
    override it later with set rs = AccessConn.execute(sql) ... I also changed
    "AccessConn" to a more standard "Conn"...

    (c) don't do presentation things (replace null with "&nbsp;") in the query.
    Do that where it belongs, in the presentation tier! Wherever you have a
    value coming back from the resultset that needs to be at least "&nbsp;", use
    the function I wrote below called sb (showblanks). Or, just response.write
    rs("column") & "&nbsp;" ... it makes your query much easier to read, doesn't
    it?

    (d) make sure you close and destroy both the recordset object and the
    connection object after you're done.


    connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    Server.mapPath("EmployeeTransactions.mdb")
    Set Conn = CreateObject("ADODB.Connection")
    Conn.open connstr

    'get session("loc") to see if they are allowed to update records
    lloc = session("sploctype")
    loccode = session("splocationdesc")

    'Determining the ability to access update records
    accessupdate = 1
    if lloc = "School" then accessupdate = 0

    sql = "SELECT emprecid, employeeno, employeelastname, employeefirstname,
    hrstatus, hrdate, budgetstatus, budgetdate, payrollstatus, payrolldate,
    benefitdate, createdby, processstatus, employeetype, location FROM
    employeetransaction "

    ' we only display the records for the school location
    ' otherwise, we display all for departments

    if lloc="School" then
    sql = sql & " where location = '" & loccode & "' and
    ((EmployeeTransaction.PayrollDate >=(date()-30)) OR
    (Isnull([EmployeeTransaction].[PayrollDate])<> FALSE)) order by emprecid
    desc "
    else
    sql = sql & " where ((EmployeeTransaction.PayrollDate >= (date()-30)) OR
    (Isnull([EmployeeTransaction].[PayrollDate])<>FALSE)) order by emprecid desc
    "
    end if

    set rs = Conn.execute(sql)

    ....
    response.write sb(rs("hrstatus"))

    function sb(s)
    sb = s : if len(trim(sb)) = 0 then sb = "&nbsp;"
    end function

    rs.close: set rs = nothing
    conn.close: set conn = nothing


    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "wk6pack" <> wrote in message
    news:...
    > Hi,
    >
    > I'm getting this error on my asp page intermittently. One day it is fine,
    > another day, it crashes a lot. I have searched the web and microsoft on
    > this and they say it is a recordset assigned to a session variable.
    >
    > I dont assign any record sets to session variables in my code. I assign
    > values to the session variables though.
    > The place it seems to crash is Set AccessConn =
    > Server.CreateObject("ADODB.Connection")
    >
    > code:
    >
    > connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > Server.mapPath("EmployeeTransactions.mdb")
    > Set AccessConn = Server.CreateObject("ADODB.Connection")
    > AccessConn.open connstr
    >
    > 'get session("loc") to see if they are allowed to update records
    > lloc = session("sploctype")
    > loccode = session("splocationdesc")
    >
    > 'Determining the ability to access update records
    > if lloc = "School" then
    > accessupdate = 0
    > else
    > accessupdate = 1
    > end if
    >
    > Set rs = Server.CreateObject("ADODB.Recordset")
    > sql = "select emprecid, employeeno, employeelastname, employeefirstname,
    > iif(isnull(hrstatus),'&nbsp',hrstatus) as hrstatus,
    > iif(isnull(hrdate),'&nbsp',hrdate) as hrdate,
    > iif(isnull(budgetstatus),'&nbsp',budgetstatus) as budgetstatus,
    > iif(isnull(budgetdate),'&nbsp',budgetdate) as budgetdate," & _
    > " iif(isnull(payrollstatus),'&nbsp',payrollstatus) as payrollstatus,
    > iif(isnull(payrolldate),'&nbsp',payrolldate) as payrolldate,
    > iif(isnull(benefitdate),'&nbsp',benefitdate) as benefitdate,
    > iif(isnull(createdby),'&nbsp',createdby) as createdby,
    > iif(isnull(processstatus),'&nbsp',processstatus) as processstatus,
    > employeetype, location from employeetransaction "
    >
    > 'we only display the records for the school location otherwise, we display
    > all for departments
    > if lloc="School" then
    > sql = sql & " where location = '" & loccode & "' and
    > ((EmployeeTransaction.PayrollDate >=(date()-30)) OR
    > (Isnull([EmployeeTransaction].[PayrollDate])<> FALSE)) order by emprecid
    > desc "
    > else
    > sql = sql & " where ((EmployeeTransaction.PayrollDate >= (date()-30))

    OR
    > (Isnull([EmployeeTransaction].[PayrollDate])<>FALSE)) order by emprecid

    desc
    > "
    > end if
    > set rs = AccessConn.execute(sql)
    >
    > Any ideas??
    > thanks,
    > Will
    >
    >
    Aaron [SQL Server MVP], Sep 29, 2004
    #2
    1. Advertising

  3. Aaron [SQL Server MVP] wrote:
    > Several suggestions.
    >
    > (a) don't use Server.CreateObject, just CreateObject
    >
    > (b) what is set rs = Server.CreateObject("ADODB.Recordset") for? You
    > override it later with set rs = AccessConn.execute(sql) ... I also
    > changed "AccessConn" to a more standard "Conn"...
    >
    > (c) don't do presentation things (replace null with "&nbsp;") in the
    > query. Do that where it belongs, in the presentation tier!


    I do this in my query when I want to use GetString.

    Bob Barrows
    --
    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 Barrows [MVP], Sep 29, 2004
    #3
  4. How many people are hitting the site? Any time there are "intermittent"
    errors that are Access database related, I'd say that they are often related
    to concurrency issues and Access not being able/meant to handle lots of
    connections.

    As far as the code you included below, I'd drop the Set rs =
    Server.CreateObject("ADODB.Recordset") line. What you're doing is creating
    an empty recordset there, and then on the Set rs = AccessConn.Execute(sql)
    line, you're destroying the recordset and creating a new one. So, it's a
    microsecond of extra processing time there.

    Also, instead of doing all that IsNull stuff in your query, I suggest
    dropping all of that. What you can do instead is:

    ....
    sql = "select emprecid, employeeno, employeelastname,
    employeefirstname,hrstatus,
    hrdate,budgetstatus,budgetdate,payrollstatus,payrolldate,benefitdate,createdby,,processstatus,
    employeetype, location from employeetransaction "
    ....

    And then, after you get your recordset back, use .GetRows to dump the
    recordset into an array. (Then quickly close and destroy your recordset and
    ado connection!) With the GetRows method, you can convert all the nulls to
    &nbsp; as such:

    Set rs = AccessConn.Execute(sSQL)
    If Not rs.EOF Then aData = rs.GetRows(2,,,"&nbsp;")


    If you aren't sure what to do with the aData array then or haven't used
    GetRows before and would like to know more about it, just post back here.

    Ray at work


    "wk6pack" <> wrote in message
    news:...
    >
    >
    > Set rs = Server.CreateObject("ADODB.Recordset")
    > sql = "select emprecid, employeeno, employeelastname, employeefirstname,
    > iif(isnull(hrstatus),'&nbsp',hrstatus) as hrstatus,
    > iif(isnull(hrdate),'&nbsp',hrdate) as hrdate,
    > iif(isnull(budgetstatus),'&nbsp',budgetstatus) as budgetstatus,
    > iif(isnull(budgetdate),'&nbsp',budgetdate) as budgetdate," & _
    > " iif(isnull(payrollstatus),'&nbsp',payrollstatus) as payrollstatus,
    > iif(isnull(payrolldate),'&nbsp',payrolldate) as payrolldate,
    > iif(isnull(benefitdate),'&nbsp',benefitdate) as benefitdate,
    > iif(isnull(createdby),'&nbsp',createdby) as createdby,
    > iif(isnull(processstatus),'&nbsp',processstatus) as processstatus,
    > employeetype, location from employeetransaction "
    >
    > 'we only display the records for the school location otherwise, we display
    > all for departments
    > if lloc="School" then
    > sql = sql & " where location = '" & loccode & "' and
    > ((EmployeeTransaction.PayrollDate >=(date()-30)) OR
    > (Isnull([EmployeeTransaction].[PayrollDate])<> FALSE)) order by emprecid
    > desc "
    > else
    > sql = sql & " where ((EmployeeTransaction.PayrollDate >= (date()-30))
    > OR
    > (Isnull([EmployeeTransaction].[PayrollDate])<>FALSE)) order by emprecid
    > desc
    > "
    > end if
    > set rs = AccessConn.execute(sql)
    >
    Ray Costanzo [MVP], Sep 29, 2004
    #4
  5. Aaron [SQL Server MVP], Sep 29, 2004
    #5
  6. wk6pack

    wk6pack Guest

    thanks for the great suggestions. I'll try them and see what happens.

    Will
    "Aaron [SQL Server MVP]" <> wrote in message
    news:...
    > Several suggestions.
    >
    > (a) don't use Server.CreateObject, just CreateObject
    >
    > (b) what is set rs = Server.CreateObject("ADODB.Recordset") for? You
    > override it later with set rs = AccessConn.execute(sql) ... I also changed
    > "AccessConn" to a more standard "Conn"...
    >
    > (c) don't do presentation things (replace null with "&nbsp;") in the

    query.
    > Do that where it belongs, in the presentation tier! Wherever you have a
    > value coming back from the resultset that needs to be at least "&nbsp;",

    use
    > the function I wrote below called sb (showblanks). Or, just

    response.write
    > rs("column") & "&nbsp;" ... it makes your query much easier to read,

    doesn't
    > it?
    >
    > (d) make sure you close and destroy both the recordset object and the
    > connection object after you're done.
    >
    >
    > connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > Server.mapPath("EmployeeTransactions.mdb")
    > Set Conn = CreateObject("ADODB.Connection")
    > Conn.open connstr
    >
    > 'get session("loc") to see if they are allowed to update records
    > lloc = session("sploctype")
    > loccode = session("splocationdesc")
    >
    > 'Determining the ability to access update records
    > accessupdate = 1
    > if lloc = "School" then accessupdate = 0
    >
    > sql = "SELECT emprecid, employeeno, employeelastname, employeefirstname,
    > hrstatus, hrdate, budgetstatus, budgetdate, payrollstatus, payrolldate,
    > benefitdate, createdby, processstatus, employeetype, location FROM
    > employeetransaction "
    >
    > ' we only display the records for the school location
    > ' otherwise, we display all for departments
    >
    > if lloc="School" then
    > sql = sql & " where location = '" & loccode & "' and
    > ((EmployeeTransaction.PayrollDate >=(date()-30)) OR
    > (Isnull([EmployeeTransaction].[PayrollDate])<> FALSE)) order by emprecid
    > desc "
    > else
    > sql = sql & " where ((EmployeeTransaction.PayrollDate >= (date()-30))

    OR
    > (Isnull([EmployeeTransaction].[PayrollDate])<>FALSE)) order by emprecid

    desc
    > "
    > end if
    >
    > set rs = Conn.execute(sql)
    >
    > ...
    > response.write sb(rs("hrstatus"))
    >
    > function sb(s)
    > sb = s : if len(trim(sb)) = 0 then sb = "&nbsp;"
    > end function
    >
    > rs.close: set rs = nothing
    > conn.close: set conn = nothing
    >
    >
    > --
    > http://www.aspfaq.com/
    > (Reverse address to reply.)
    >
    >
    >
    >
    > "wk6pack" <> wrote in message
    > news:...
    > > Hi,
    > >
    > > I'm getting this error on my asp page intermittently. One day it is

    fine,
    > > another day, it crashes a lot. I have searched the web and microsoft on
    > > this and they say it is a recordset assigned to a session variable.
    > >
    > > I dont assign any record sets to session variables in my code. I assign
    > > values to the session variables though.
    > > The place it seems to crash is Set AccessConn =
    > > Server.CreateObject("ADODB.Connection")
    > >
    > > code:
    > >
    > > connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > > Server.mapPath("EmployeeTransactions.mdb")
    > > Set AccessConn = Server.CreateObject("ADODB.Connection")
    > > AccessConn.open connstr
    > >
    > > 'get session("loc") to see if they are allowed to update records
    > > lloc = session("sploctype")
    > > loccode = session("splocationdesc")
    > >
    > > 'Determining the ability to access update records
    > > if lloc = "School" then
    > > accessupdate = 0
    > > else
    > > accessupdate = 1
    > > end if
    > >
    > > Set rs = Server.CreateObject("ADODB.Recordset")
    > > sql = "select emprecid, employeeno, employeelastname, employeefirstname,
    > > iif(isnull(hrstatus),'&nbsp',hrstatus) as hrstatus,
    > > iif(isnull(hrdate),'&nbsp',hrdate) as hrdate,
    > > iif(isnull(budgetstatus),'&nbsp',budgetstatus) as budgetstatus,
    > > iif(isnull(budgetdate),'&nbsp',budgetdate) as budgetdate," & _
    > > " iif(isnull(payrollstatus),'&nbsp',payrollstatus) as payrollstatus,
    > > iif(isnull(payrolldate),'&nbsp',payrolldate) as payrolldate,
    > > iif(isnull(benefitdate),'&nbsp',benefitdate) as benefitdate,
    > > iif(isnull(createdby),'&nbsp',createdby) as createdby,
    > > iif(isnull(processstatus),'&nbsp',processstatus) as processstatus,
    > > employeetype, location from employeetransaction "
    > >
    > > 'we only display the records for the school location otherwise, we

    display
    > > all for departments
    > > if lloc="School" then
    > > sql = sql & " where location = '" & loccode & "' and
    > > ((EmployeeTransaction.PayrollDate >=(date()-30)) OR
    > > (Isnull([EmployeeTransaction].[PayrollDate])<> FALSE)) order by emprecid
    > > desc "
    > > else
    > > sql = sql & " where ((EmployeeTransaction.PayrollDate >=

    (date()-30))
    > OR
    > > (Isnull([EmployeeTransaction].[PayrollDate])<>FALSE)) order by emprecid

    > desc
    > > "
    > > end if
    > > set rs = AccessConn.execute(sql)
    > >
    > > Any ideas??
    > > thanks,
    > > Will
    > >
    > >

    >
    >
    wk6pack, Sep 29, 2004
    #6
  7. wk6pack

    wk6pack Guest

    Hi Ray,

    I've got at least 30 people hitting the site to enter data into the Access
    database.

    I've never used .GetRows before. I would like to know more about it.

    thanks,
    Will


    "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
    message news:...
    > How many people are hitting the site? Any time there are "intermittent"
    > errors that are Access database related, I'd say that they are often

    related
    > to concurrency issues and Access not being able/meant to handle lots of
    > connections.
    >
    > As far as the code you included below, I'd drop the Set rs =
    > Server.CreateObject("ADODB.Recordset") line. What you're doing is

    creating
    > an empty recordset there, and then on the Set rs = AccessConn.Execute(sql)
    > line, you're destroying the recordset and creating a new one. So, it's a
    > microsecond of extra processing time there.
    >
    > Also, instead of doing all that IsNull stuff in your query, I suggest
    > dropping all of that. What you can do instead is:
    >
    > ...
    > sql = "select emprecid, employeeno, employeelastname,
    > employeefirstname,hrstatus,
    >

    hrdate,budgetstatus,budgetdate,payrollstatus,payrolldate,benefitdate,created
    by,,processstatus,
    > employeetype, location from employeetransaction "
    > ...
    >
    > And then, after you get your recordset back, use .GetRows to dump the
    > recordset into an array. (Then quickly close and destroy your recordset

    and
    > ado connection!) With the GetRows method, you can convert all the nulls

    to
    > &nbsp; as such:
    >
    > Set rs = AccessConn.Execute(sSQL)
    > If Not rs.EOF Then aData = rs.GetRows(2,,,"&nbsp;")
    >
    >
    > If you aren't sure what to do with the aData array then or haven't used
    > GetRows before and would like to know more about it, just post back here.
    >
    > Ray at work
    >
    >
    > "wk6pack" <> wrote in message
    > news:...
    > >
    > >
    > > Set rs = Server.CreateObject("ADODB.Recordset")
    > > sql = "select emprecid, employeeno, employeelastname, employeefirstname,
    > > iif(isnull(hrstatus),'&nbsp',hrstatus) as hrstatus,
    > > iif(isnull(hrdate),'&nbsp',hrdate) as hrdate,
    > > iif(isnull(budgetstatus),'&nbsp',budgetstatus) as budgetstatus,
    > > iif(isnull(budgetdate),'&nbsp',budgetdate) as budgetdate," & _
    > > " iif(isnull(payrollstatus),'&nbsp',payrollstatus) as payrollstatus,
    > > iif(isnull(payrolldate),'&nbsp',payrolldate) as payrolldate,
    > > iif(isnull(benefitdate),'&nbsp',benefitdate) as benefitdate,
    > > iif(isnull(createdby),'&nbsp',createdby) as createdby,
    > > iif(isnull(processstatus),'&nbsp',processstatus) as processstatus,
    > > employeetype, location from employeetransaction "
    > >
    > > 'we only display the records for the school location otherwise, we

    display
    > > all for departments
    > > if lloc="School" then
    > > sql = sql & " where location = '" & loccode & "' and
    > > ((EmployeeTransaction.PayrollDate >=(date()-30)) OR
    > > (Isnull([EmployeeTransaction].[PayrollDate])<> FALSE)) order by emprecid
    > > desc "
    > > else
    > > sql = sql & " where ((EmployeeTransaction.PayrollDate >= (date()-30))
    > > OR
    > > (Isnull([EmployeeTransaction].[PayrollDate])<>FALSE)) order by emprecid
    > > desc
    > > "
    > > end if
    > > set rs = AccessConn.execute(sql)
    > >

    >
    wk6pack, Sep 29, 2004
    #7
  8. http://www.aspfaq.com/2467

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "wk6pack" <> wrote in message
    news:#...
    > Hi Ray,
    >
    > I've got at least 30 people hitting the site to enter data into the Access
    > database.
    >
    > I've never used .GetRows before. I would like to know more about it.
    >
    > thanks,
    > Will
    >
    >
    > "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
    > message news:...
    > > How many people are hitting the site? Any time there are "intermittent"
    > > errors that are Access database related, I'd say that they are often

    > related
    > > to concurrency issues and Access not being able/meant to handle lots of
    > > connections.
    > >
    > > As far as the code you included below, I'd drop the Set rs =
    > > Server.CreateObject("ADODB.Recordset") line. What you're doing is

    > creating
    > > an empty recordset there, and then on the Set rs =

    AccessConn.Execute(sql)
    > > line, you're destroying the recordset and creating a new one. So, it's

    a
    > > microsecond of extra processing time there.
    > >
    > > Also, instead of doing all that IsNull stuff in your query, I suggest
    > > dropping all of that. What you can do instead is:
    > >
    > > ...
    > > sql = "select emprecid, employeeno, employeelastname,
    > > employeefirstname,hrstatus,
    > >

    >

    hrdate,budgetstatus,budgetdate,payrollstatus,payrolldate,benefitdate,created
    > by,,processstatus,
    > > employeetype, location from employeetransaction "
    > > ...
    > >
    > > And then, after you get your recordset back, use .GetRows to dump the
    > > recordset into an array. (Then quickly close and destroy your recordset

    > and
    > > ado connection!) With the GetRows method, you can convert all the nulls

    > to
    > > &nbsp; as such:
    > >
    > > Set rs = AccessConn.Execute(sSQL)
    > > If Not rs.EOF Then aData = rs.GetRows(2,,,"&nbsp;")
    > >
    > >
    > > If you aren't sure what to do with the aData array then or haven't used
    > > GetRows before and would like to know more about it, just post back

    here.
    > >
    > > Ray at work
    > >
    > >
    > > "wk6pack" <> wrote in message
    > > news:...
    > > >
    > > >
    > > > Set rs = Server.CreateObject("ADODB.Recordset")
    > > > sql = "select emprecid, employeeno, employeelastname,

    employeefirstname,
    > > > iif(isnull(hrstatus),'&nbsp',hrstatus) as hrstatus,
    > > > iif(isnull(hrdate),'&nbsp',hrdate) as hrdate,
    > > > iif(isnull(budgetstatus),'&nbsp',budgetstatus) as budgetstatus,
    > > > iif(isnull(budgetdate),'&nbsp',budgetdate) as budgetdate," & _
    > > > " iif(isnull(payrollstatus),'&nbsp',payrollstatus) as payrollstatus,
    > > > iif(isnull(payrolldate),'&nbsp',payrolldate) as payrolldate,
    > > > iif(isnull(benefitdate),'&nbsp',benefitdate) as benefitdate,
    > > > iif(isnull(createdby),'&nbsp',createdby) as createdby,
    > > > iif(isnull(processstatus),'&nbsp',processstatus) as processstatus,
    > > > employeetype, location from employeetransaction "
    > > >
    > > > 'we only display the records for the school location otherwise, we

    > display
    > > > all for departments
    > > > if lloc="School" then
    > > > sql = sql & " where location = '" & loccode & "' and
    > > > ((EmployeeTransaction.PayrollDate >=(date()-30)) OR
    > > > (Isnull([EmployeeTransaction].[PayrollDate])<> FALSE)) order by

    emprecid
    > > > desc "
    > > > else
    > > > sql = sql & " where ((EmployeeTransaction.PayrollDate >=

    (date()-30))
    > > > OR
    > > > (Isnull([EmployeeTransaction].[PayrollDate])<>FALSE)) order by

    emprecid
    > > > desc
    > > > "
    > > > end if
    > > > set rs = AccessConn.execute(sql)
    > > >

    > >

    >
    >
    Aaron [SQL Server MVP], Sep 29, 2004
    #8
  9. Aaron [SQL Server MVP] wrote:
    >> I do this in my query when I want to use GetString.

    >
    > Why? GetString() supports NullExpr (which allow you to specify
    > "&nbsp;" or
    > something else for any value that is Null).
    >

    http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetstringmethod(recordset)ado.asp

    It's not to replace Nulls.

    In one instance, I do it pad hard spaces between pieces of data to simulate
    columns in a SELECT. (so it will display say company number and company name
    in columns in the SELECT) - I also concatenate all the OPTION tags in the
    query as well (gasp!)

    There's nothing like a simple

    Response.Write rs.GetString

    to reduce the amount of code on a page. This also cuts down on the number of
    include files. This dropdown box is used in quite a few pages in which I
    would have to #include the asp page to create the same option list that is
    coming from my stored procedure. I see no point to doing this merely to
    satisfy a theoretical "presentation layer-data layer" division. Using such a
    division is supposed to improve my codes maintainability, not reduce it.

    Bob Barrows

    --
    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 Barrows [MVP], Sep 29, 2004
    #9
  10. GetRows returns a two-dimensional array, where the first dimension
    represents the columns in the recordset, and the second dimension represents
    the rows, so to speak. So, if you had a query like:

    "select firstname,lastname from people"

    And you dumped that to an array with .GetRows, you'd iterate through it as
    such:

    <%

    For i = 0 To UBound(aData, 2) '<-- the UBound of the second dimension
    '''since the rows are in the second dimension
    '''you want to loop through the number of times
    '''as there are "records" in it.
    Response.Write "Firstname = " & aData(0,i) & "<br>"
    Response.Write "Lastname = " & aData(1,i) & "<hr>"
    Next
    %>


    So, if you're used to working with arrays, it's just like having an array
    like:

    Dim someArray(1,5)
    someArray(0,0) = "Bob" : someArray(1,0) = "Smith"
    someArray(0,1) = "Ted" : someArray(1,1) = "Jones"
    someArray(0,2) = "Jen" : someArray(1,2) = "Giles"
    someArray(0,3) = "Kim" : someArray(1,3) = "Baron"
    someArray(0,4) = "Ned" : someArray(1,4) = "Heinz"
    someArray(0,5) = "Gus" : someArray(1,5) = "Baker"


    Ray at work





    "wk6pack" <> wrote in message
    news:%...
    > Hi Ray,
    >
    > I've got at least 30 people hitting the site to enter data into the Access
    > database.
    >
    > I've never used .GetRows before. I would like to know more about it.
    >
    > thanks,
    > Will
    Ray Costanzo [MVP], Sep 29, 2004
    #10
  11. > coming from my stored procedure. I see no point to doing this merely to
    > satisfy a theoretical "presentation layer-data layer" division.


    I think a lot of people on the database side would disagree with you. Some
    of them vehemently and maybe even violently. ;-)

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
    Aaron [SQL Server MVP], Sep 29, 2004
    #11
  12. wk6pack

    wk6pack Guest

    thanks Ray. I'll give it a try.

    Will
    "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
    message news:...
    > GetRows returns a two-dimensional array, where the first dimension
    > represents the columns in the recordset, and the second dimension

    represents
    > the rows, so to speak. So, if you had a query like:
    >
    > "select firstname,lastname from people"
    >
    > And you dumped that to an array with .GetRows, you'd iterate through it as
    > such:
    >
    > <%
    >
    > For i = 0 To UBound(aData, 2) '<-- the UBound of the second dimension
    > '''since the rows are in the second dimension
    > '''you want to loop through the number of times
    > '''as there are "records" in it.
    > Response.Write "Firstname = " & aData(0,i) & "<br>"
    > Response.Write "Lastname = " & aData(1,i) & "<hr>"
    > Next
    > %>
    >
    >
    > So, if you're used to working with arrays, it's just like having an array
    > like:
    >
    > Dim someArray(1,5)
    > someArray(0,0) = "Bob" : someArray(1,0) = "Smith"
    > someArray(0,1) = "Ted" : someArray(1,1) = "Jones"
    > someArray(0,2) = "Jen" : someArray(1,2) = "Giles"
    > someArray(0,3) = "Kim" : someArray(1,3) = "Baron"
    > someArray(0,4) = "Ned" : someArray(1,4) = "Heinz"
    > someArray(0,5) = "Gus" : someArray(1,5) = "Baker"
    >
    >
    > Ray at work
    >
    >
    >
    >
    >
    > "wk6pack" <> wrote in message
    > news:%...
    > > Hi Ray,
    > >
    > > I've got at least 30 people hitting the site to enter data into the

    Access
    > > database.
    > >
    > > I've never used .GetRows before. I would like to know more about it.
    > >
    > > thanks,
    > > Will

    >
    >
    wk6pack, Sep 29, 2004
    #12
  13. Aaron [SQL Server MVP] wrote:
    >> coming from my stored procedure. I see no point to doing this merely
    >> to satisfy a theoretical "presentation layer-data layer" division.

    >
    > I think a lot of people on the database side would disagree with you.
    > Some of them vehemently and maybe even violently. ;-)


    Probably. But why give us the string functions and then tell us not to use
    them? :)

    This is not to say that I'm a total rebel on this point. There are things
    that I prefer to handle in the "presentation" tier that could be handled
    with more or less effort or performance impact in the database tier. I just
    don't follow this philosophy blindly. If I did, I would use much fewer
    stored procedures and triggers (business logic should be in the business
    layer, right?)
    --
    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 Barrows [MVP], Sep 29, 2004
    #13
  14. > don't follow this philosophy blindly. If I did, I would use much fewer
    > stored procedures and triggers (business logic should be in the business
    > layer, right?)


    In reality (at least at my workplace and in all my contract situations), the
    "business layer" is either disappearing, or never existed. There is no more
    COM and interop and all that crap. Why should I build COM or .NET
    components that need to be compiled and are a pain in the ass to (re-)deploy
    when (not if) specs change? Why use three tiers when I only need two?

    Plus, let's face it, some of your data validation you want to do on the
    client side, and some of it you have to do in the database.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
    Aaron [SQL Server MVP], Sep 29, 2004
    #14
  15. Aaron [SQL Server MVP] wrote:
    >> don't follow this philosophy blindly. If I did, I would use much
    >> fewer stored procedures and triggers (business logic should be in
    >> the business layer, right?)

    >
    > In reality (at least at my workplace and in all my contract
    > situations), the "business layer" is either disappearing, or never
    > existed. There is no more COM and interop and all that crap. Why
    > should I build COM or .NET components that need to be compiled and
    > are a pain in the ass to (re-)deploy when (not if) specs change? Why
    > use three tiers when I only need two?
    >
    > Plus, let's face it, some of your data validation you want to do on
    > the client side, and some of it you have to do in the database.


    Yes, my point exactly


    --
    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 Barrows [MVP], Sep 30, 2004
    #15
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Raúl Martín
    Replies:
    1
    Views:
    10,803
    Natty Gur
    May 13, 2004
  2. Som
    Replies:
    2
    Views:
    601
    Aaron Bertrand [MVP]
    Mar 6, 2004
  3. David Lozzi

    ScriptEngine threw exception 'C0000005'

    David Lozzi, Aug 23, 2005, in forum: ASP General
    Replies:
    0
    Views:
    252
    David Lozzi
    Aug 23, 2005
  4. ashish
    Replies:
    1
    Views:
    435
    ashish
    Aug 14, 2007
  5. Sagar

    Server.CreateObject Vs CreateObject

    Sagar, Jan 15, 2008, in forum: ASP General
    Replies:
    2
    Views:
    332
    Sagar
    Jan 15, 2008
Loading...

Share This Page