[Microsoft][ODBC SQL Server Driver]COUNT field incorrect

12 Aug 2005 1:32 PM

tjonsek

I have code that sends emails to individuals based on values in a field
union_name. It builds the body text of the email based on the recordset
information. It is possible that the email would actually display
information for multiple records. For the email going to a specific
department, it adds a line to the body, allowing them to follow a link
and update information pertaining to that record or set of records.
This worked until I added the last part which adds the link to the body
of the email and sends it.
I have run the actual xp_sendmail directly against the database through
Query Analyzer and it works fine. I'm suspecting the problem might have
to do with one of my loops but I am unable to find the problem.

Please look over this and tell me if you can help me find what may be
causing my Count field incorrect error.
Thank you!

if not rst.EOF then
    if rst("union_name") = "1327" then
        strHRRecip = "madcbrow" 'production
    else
        strHRRecip = "tlhender" 'maintenance/clerical
    end if
    dt_filed = rst("dt_filed")
    g_desc = rst("g_desc")
    union_name = rst("union_name")
    griev_num = rst("griev_num")
    if left(griev_num,1) = "I" then 'check to see if informal action or
grievance
        grievType = "In Lieu of Grievance"
    else
        grievType = "For Grievance"
    end if
    g_type = rst("g_type")

    body = "The following payout is requested " & grievType & ": " &
griev_num & ". " & vbCrLf &_
    "Date Prepared: " & dt_filed & vbCrLf &_
    "Union: " & union_name & vbCrLf &_
    "Reason: " & g_type & vbCrLf & "For the following employee(s):" &
vbCrLf & vbCrLf
    'build body of email
    sql = "select emp_name, clock_num, dept, title, supervisor,hours, rate
from vw03_EmployeePayouts where g_key = " & session("g_key")
    cmd.CommandText = sql
    set rst = cmd.Execute()
    if not rst.EOF then
        do while not rst.EOF
            body = body + "Employee Name: " & rst("emp_name") & vbCrLf &_
            "Clock Number: " & rst("clock_num") & vbCrLf &_
            "Department: " & rst("dept") & vbCrLf &_
            "Job Title: " & rst("title") & vbCrLf &_
            "Rate of Pay: " & rst("rate") & vbCrLf &_
            "Hours to be Paid: " & rst("hours") & vbCrLf &_
            "Authorized Signature: " & rst("supervisor") & vbCrLf & vbCrLf &
vbCrLf
            rst.MoveNext
        loop
    else
        body = body + "There was an error retrieving employee information.
Please contact IT."
        strError = "1"
    end if

    if strError <> "1" then 'if there were no errors
        bodyPR = body + vbCrLf + "To mark this incident as paid, please click
<a href='http://ashdev/GIS/inf_CheckPaid1.asp?g_key=" &
session("g_key") &_
                "&griev_num=" & griev_num & "'> here.</a>"
    end if
    if len(body) > 0 then 'if the body message was built
        dim cnnEmail
        dim rstEmail
        dim cmdEmail

        set cnnEmail = server.createobject("adodb.connection")
        set rstEmail = server.createobject("adodb.recordset")
        set cmdEmail = server.createobject("adodb.command")

        cnnEmail.open Application("cnnEmail_ConnectionString")
        set cmdEmail.ActiveConnection = cnnEmail
        'email to HR
        sql = "Exec xp_sendmail @recipients='tmjonsek;" & strHRRecip &
"',@message='" & body & "', @subject='" & grievType & ": " & griev_num
& "'" &_
        " Exec xp_sendmail
@recipients='tmjonsek;rgtatom;lcharlst',@message='" & bodyPR & "',
@subject='" & grievType & ": " & griev_num & "'"
        cmdEmail.CommandText = sql
        'Response.Write sql
        'Response.Flush
        set rstEmail = cmdEmail.Execute()
        'email to payroll
        'sql = "Exec xp_sendmail
@recipients='tmjonsek;rgtatom;lcharlst',@message='" & bodyPR & "',
@subject='" & grievType & ": " & griev_num & "'"
        'cmdEmail.CommandText = sql
        'Response.Write sql
        'Response.Flush
        'set rstEmail = cmdEmail.Execute()
    else
        Response.Write "There were problems retrieving the record from the
database. Please contact IT. "
        Response.End
    end if

else
    Response.Write "There were problems retrieving the record from the
database. Please contact IT. "
    Response.End
end if

12 Aug 2005 2:39 PM

tjonsek

I solved my own problem. But in case anyone ever has this trouble, I'll
post what I did wrong.
When building the text for the second email, I was in html mode, not
outlook email mode.
I first coded:
bodyPR = body + vbCrLf + "To mark this incident as paid, please click
<a href='http://ashdev/GIS/inf_CheckPaid1.asp?g_key=" &
session("g_key") &_
"&griev_num=" & griev_num & "'>
here.</a>"
What should have been coded was:
bodyPR = body + vbCrLf + "To mark this incident as paid, please click
here: http://ashdev/GIS/inf_CheckPaid1.asp?g_key=" & session("g_key")
&_
"&griev_num=" & griev_num

The link should have been written directly rather than attempting to
put it into an <a href> tag. The question mark in the link for passing
parameters was interpreted incorrectly.