Error: divide by Zero VB.net SQL text

[WordTechinc]

How to modify within full cmd.commandtext.
I need to able to pass this full cmd.commandtext to SQL.

This error occurs while SQL text thru vb.net because ProsRNCount is zero.


This is the part of cmd.commandtext.

"ISNULL(cast((RespRNCount/" & _LowestSelectedCityCounter & " ) / ProsRNCount *100 as numeric(9,3)),0) as [RNPercResponse]" _



This is full cmd.commandtext from vb.net

Cmd.CommandText = "select ZipCode as [Zip Code],ProsPect_Count as [Prospective count], " _
& " Responder_Count as [Responder count], " _
& " cast((Responder_Count/" & _LowestSelectedCityCounter & " ) as numeric(9,2)) as [Res.cnt/Sem], " _
& " Cast((Responder_Count/" & _LowestSelectedCityCounter & " )/ ProsPect_Count *100 as numeric(9,3))as [PercResponse], " _
& " ProsDentCount,RespDentCount, ProsNonDentCount, RespNonDentCount, " _
& " ProsRNCount as [RN Prospective count], " _
& " RespRNCount as [RN Responder count], " _
& " cast((RespRNCount/" & _LowestSelectedCityCounter & " ) as numeric(9, 2)) AS [RNRes.cnt/Sem], " _
& " ISNULL(cast((RespRNCount/" & _LowestSelectedCityCounter & " ) / ProsRNCount *100 as numeric(9,3)),0) as [RNPercResponse], " _
& " ProsNonRNCount as [NonRN Prospective count], " _
& " RespNonRNCount as [NonRN Responder count], " _
& " cast((RespNonRNCount/" & _LowestSelectedCityCounter & " ) as numeric(9, 2)) AS [NonRNRes.cnt/Sem], " _
& " cast((RespNonRNCount/" & _LowestSelectedCityCounter & " ) / ProsRNCount *100 as numeric(9,3)) as [NonRNPercResponse], " _
& " RNind,NonRNind,RNNonRNind " _
& " from dbo.INR_PLDetails where PLID in (Select PLID from INR_PLMAster where " _
& " JobCode='" & JobNo.ToString() & "')"

Dadapter.SelectCommand = Cmd
Dadapter.Fill(DtabProspPlusRespo)
Cmd.CommandText = "select ClassKey from INR_PLClassKeys where JobNo='" & JobNo & "'"
Dadapter.Fill(dtClassKeys)

 

[RiverGuy]

Why are you posting this another time? You have two replies to your previous question on this topic.

 

[WordTechinc]

I wasn't sure how to ask queation to replier. I need to able to modify within full SQL PASS cmd.commandtext.

Could you give me any advide where to insert the following?
[RNPercResponse] =
CASE
WHEN ProsRNCount = 0 THEN 0
ELSE ISNULL(cast((RespRNCount/" & _LowestSelectedCityCounter & " ) / ProsRNCount *100 as numeric(9,3)),0)
END