Type Mismatch Error 5

[charbrad]

I am getting a "Type Mismatch" error...and can use another set of eyes. What I am trying to accomplish is if LBS = 0 then the report should not print. I have hightlighted in bold where I think my problem is coming from.

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) 

LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT 

Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
[b]if NOT rs.EOF and rs("LBSProduced") > 0 then
p=rs.GetRows()
End if
rs.close[/b]

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) 

LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT 

Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE Outsourced = 'No' AND CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
pIH=rs.GetRows()
rs.close

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) 

LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT 

Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE Outsourced = 'Yes' AND CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
pOS=rs.GetRows()
rs.close

 

[Sheco]

Suppose you have this logic in VB:[tt]
IF False AND True Then
DoSomething(1)
END IF[/tt]


In C or Java or other "curly brace" language it would be:
[tt]if (false && true) {
DoSomething(1);
}[/tt]


So the C version is going to read the line and see "false and" and then it will stop right there because it already knows the answer is false, because false and true is false, and false is false is false.


The VB version is going to go ahead and try to do logic on the entire statement.

So the problem is, VB is trying to figure out [tt]rs("LBSProduced") > 0[/tt] even after it has already determined that [tt]NOT rs.EOF[/tt] is false.


Another problem could be that [tt]rs("LBSProduced")[/tt] contains non-numeric data, possibly a Null value.

 

[Sheco]

Nevermind about that last null bit in my post above, I see your SQL is using the ISNULL() function.

 

[Sheco]

To really be safe about it, split it up somethng like this:

'Is the recordset open?
if rs.State = 1 then  
  'Is the recordset empty?
  if NOT rs.EOF 
    'Does the field contain a number?
    if IsNumeric(rs("LBSProduced") & "") then
      'Is the number larger than zero?
      if rs("LBSProduced") > 0 then
        p = rs.GetRows()
      end if
    end if
  end if
end if

rs.close

 

[charbrad]

I am still getting the Type Mismatch error on line 26 which is : if NOT rs.EOF

 

[Chopstik]

Not sure which is line 26. Can you re-post the relevant code (assuming you took Sheco's earlier advice, it will be changed). What you have in your most recent post is missing the "then" part of your statement but that would not explain your particular error message.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[charbrad]

Here is is

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) 

LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT 

Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
if rs.State = 1 then  
    if NOT rs.EOF then
      if IsNumeric(rs("LBSProduced") & "") then
            if rs("LBSProduced") > 0 then
        p = rs.GetRows()
      end if
    end if
  end if
end if
rs.close

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) 

LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT 

Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE Outsourced = 'No' AND CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
pIH=rs.GetRows()
rs.close

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) 

LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT 

Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE Outsourced = 'Yes' AND CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
pOS=rs.GetRows()
rs.close

 

[Turkbear]

Hi,
Maybe try ( although I do not know why it would matter)

if rs.State = 1 then  
    Do while not rs.EOF 
      if IsNumeric(rs("LBSProduced") & "") then
            if rs("LBSProduced") > 0 then
        p = rs.GetRows()
      end if
    end if
  loop
end if
rs.close

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[charbrad]

Nothing changed....

 

[monksnake]

You could alternatively check the recordcount.

If rs.RecordCount > 0 Then

Without looking too deep into what has already been posted, I'm not sure why not rs.EOF isn't working

Put some Response.Write() and Response.End within the code to see what values are getting set:

rs.Open SQLQuery, MyConn 
Response.Write(rs.State)
if rs.State = 1 then  
Response.Write(rs.EOF)
Response.End
    if NOT rs.EOF then
      if IsNumeric(rs("LBSProduced") & "") then
            if rs("LBSProduced") > 0 then
        p = rs.GetRows()
      end if
    end if
  end if
end if

<.

 

[Chopstik]

Along the same lines that Turkbear and monksnake are going, are you sure that your recordset is actually returning records? How are you creating the connection and recordset itself? It almost seems like the issue is with the recordset at this point...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[charbrad]

I am new to ASP (given this task a week ago), but this report runs without the EOF statment. I was asked to have this report not print when LBS = 0.

I took Monksnake's advise and added the

"Response.Write(rs.EOF)
Response.End"

It returned "FALSE"

 

[monksnake]

Well you can try

If rs.EOF = false Then

If that doesn't work, then we know the problem lies elsewhere.

Though not rs.EOF should be the same thing.


<.

 

[charbrad]

Thanks to all of you for your help. I still do not know why I am getting this error, but with the advice given I at least have a place to start. Thanks again.

 

[CamaroLT]

rs.Open SQLQuery, MyConn
if NOT rs.EOF and rs("LBSProduced") > 0 then
p=rs.GetRows()
End if
rs.close

Verify that LBSProduced is coming back as an integer and not something else. What you could do is change 0 to "0" and see if that changes anything.

I would also change the IF to something like

if (not rs.eof) and (rs("LBSProduced") > 0) then

Some languages would do the check on

not rs.eof and rs("LBSProduced")

Convert that to a boolean, then compare it to integer value of 0 which is invalid because the interpreter is expecting TRUE or FALSE, not 1 or 0.


-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[Hans8823]

You're all foccusing at the wrong line! The error message is quit clear what is wrong:
'Data type mistmatch in citeria expression'

The error occurs in the line:

rs.Open SQLQuery, MyConn

So the code stops there, the rest of the code will not be executed. You have to look in the WHERE clause of the SQL statement.

I think that the problem is in:

WHERE Outsourced = 'No' AND CloseDate = '" & ReportDate & "'"

CloseDate is obviously date type, and is assigened a string value.

the correct syntax should be:

WHERE Outsourced = 'No' AND CloseDate = #" & ReportDate & "#"

Are you sure that Outsourced is sting value? If it is a boolean than it should be:

WHERE Outsourced = false AND CloseDate = #" & ReportDate & "#"

 

[Sheco]

Hans8823: There has been no mention of any criteria expression. Where did you get that?

 

[Hans8823]

The first line in the original post

 

[gmmastros]

Hans8823:

Because the query uses IsNull, which is NOT ANSI compliant, I can deduce that this is Microsoft SQL Server. With Access, you need to delimit your dates with the # symbol, but in SQL Server, you delimit dates with the single-quote symbol.

I see nothing wrong with the query.

Well... unless you consider the integer math that could cause problems with the yield column.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Hans8823]

Oh, I'm sorry, it says only type mismatch...

However, I think this is still the problem, I've never had a type mismtach error or something by executing the .EOF function!