I am still trying to replace a NULL or "" in a Numeric field with a Zero

The first part check out it seems

<% pregroup = rs("pregroup")
 if pregroup <> "" then
 pregroup = pregroup
 else
   pregroup ="0"
 end if
 response.write pregroup
 %>

The response.write gives me my zero when the field is empty or whatever was in the field if it was not empty

However I then want to insert the Numeric value I have just seen into a form where I am updating all the other fields using

<form method="post" action="lmadmin3.asp?Actionvar=update">
<label for="br_no">BR.Number</label>
<input id="br_no" name="br_no" value=" <% =rs("br_no") %>" />
<br />
<label for="other_no">LMS No.</label>
<input id="other_no" name="other_no" value=" <% =rs("other_no") %>" />
<br />
<label for="pregroup">Pre-Group No.</label>

This next line I cannot get right

  <input id="Pregroup" name="Pregroup" />
  <br />

I have also tried variations on this attempt

<input id="other_no" name="other_no" value=" <% =rs("other_no") %>"

Some guidence as to what I need here to get the result in the Form so I can see if it has a valid number or is a Zero, and allow an update of the record at the end

Please someone ...

Topshed

I am not clear what is exactly needed, but if you want to check for Pregroup is a valid number or not then you can use isnumeric() function.

If you want to check for null, then u can use isnull() function.





Om Prakash

Yup, I am aware of the options, but my Problem is then getting the result to show in my Form after I have checked and manipulated it.
I want the 0 or the existing number is to show in this form below so i can update to the new value on submit


<p>Update</p>
<% pregroup = rs("pregroup")
 if pregroup <> "" then
 pregroup = pregroup
 else
   pregroup ="0"
 end if
 response.write pregroup
 %>
<form method="post" action="lmadmin3.asp?Actionvar=update">
  <label for="lclas">Class 1</label>
  <input id="lclas" name="lclas" value=" <% =rs("lclas") %>" />
  <br />
  <label for="class2">Class 2</label>
  <input id="class2" name="class2" value=" <% =rs("class2") %>" />
  <br />

*****This bit does not weork ******************

  <label for="pregroup">Pre-Group No.</label>
  <input id="pregroup" name="pregroup" value=" <% =rs("pregroup") %>" />
  <br />

************************************************** *********************
  <label for="br_no">BR.Number</label>
  <input id="br_no" name="br_no" value=" <% =rs("br_no") %>" />
  <br />
  <label for="other_no">LMS No.</label>
  <input id="other_no" name="other_no" value=" <% =rs("other_no") %>" />
  <br />
  <label for="wheel">Wheel Arrangement</label>
  <input id="wheel" name="wheel" value=" <% =rs("wheel") %>" />
  <br />
  <label for="cme">CME</label>
  <input id="cme" name="cme" value=" <% =rs("cme") %>" />
  <br />
  <label for="loco_name">Loco Name</label>
  <input id="loco_name" name="loco_name" value=" <% =rs("loco_name") %>" />
  <br />
  <label for="builder">Builder</label>
  <input id="builder" name="builder" value=" <% =rs("builder") %>" />
  <br />
  <label for="build_no">Build Number</label>
  <input id="build_no" name="build_no" value=" <% =rs("build_no") %>" />
  <br />
  <label for="mm_build">Build Month</label>
  <input id="mm_build" name="mm_build" value=" <% =rs("mm_build") %>" />
  <br />
  <label for="yy_build">Build Year</label>
  <input id="yy_build" name="yy_build" value=" <% =rs("yy_build") %>" />
  <br />
  <label for="mm_wdn">Withdrawn Month</label>
  <input id="mm_wdn" name="mm_wdn" value=" <% =rs("mm_wdn") %>" />
  <br />
  <label for="yy_wdn">Withdrawn Year</label>
  <input id="yy_wdn" name="yy_wdn" value=" <% =rs("yy_wdn") %>" />
  <br />
  <label for="code">Shed Code</label>
  <input id="code" name="code" value=" <% =rs("code") %>" />
  <br />
  <label for="last_shed">Last Shed</label>
  <input id="last_shed" name="last_shed" value=" <% =rs("last_shed") %>" />
  <br />
  <label for="mm_cut">Month Cut</label>
  <input id="mm_cut" name="mm_cut" value=" <% =rs("mm_cut") %>" />
  <br />
  <label for="yy_cut">Year Cut</label>
  <input id="yy_cut" name="yy_cut" value=" <% =rs("yy_cut") %>" />
  <br />
  <label for="">Cut by / Fate</label>
  <input id="cut" name="cut" value=" <% =rs("cut") %>" />
  <br />
  <label for="notes">Notes</label>
  <input id="notes" name="notes" value=" <% =rs("notes") %>" />
  <br />
  <input type="hidden" name="flag" value="2" />
  <input type="hidden" name="Recordid" value="<%=rs("ID")%>" />
  <br />
  <label for="submit">label</label>
  <input type="submit" value="Submit Change" />
</form>
<br />
<%
      rs.MoveNext
      rs.Close
    END IF
  ELSEIF Request.Form("flag")="2" THEN
'pre_var = rs("pregroup")
'if isnull(rs("pregroup")) then
' rs("pregroup") = 0
' response.write rs("pregroup")
'else
' prs("pregroup")=rs("pregroup")
' response.write rs("pregroup")
'end if
'response.end
   SQLstmt = "UPDATE lms SET "
   SQLstmt = SQLstmt & "lclas='" & TRIM(Request.Form("lclas")) & "', "
   SQLstmt = SQLstmt & "class2='" & TRIM(Request.Form("class2")) & "', "
   SQLstmt = SQLstmt & "pregroup='" & TRIM(Request.form("pregroup")) & "', "
   SQLstmt = SQLstmt & "br_no='" & TRIM(Request.Form("br_no")) & "', "
   SQLstmt = SQLstmt & "other_no='" & TRIM(Request.Form("other_no")) & "', "
   SQLstmt = SQLstmt & "wheel='" & TRIM(Request.Form("wheel")) & "', "
   SQLstmt = SQLstmt & "cme='" & TRIM(Request.Form("cme")) & "', "
   SQLstmt = SQLstmt & "loco_name='" & TRIM(Request.Form("loco_name")) & "', "
   SQLstmt = SQLstmt & "builder='" & TRIM(Request.Form("builder")) & "', "
   SQLstmt = SQLstmt & "build_no='" & TRIM(Request.Form("build_no")) & "', "
   SQLstmt = SQLstmt & "mm_build='" & TRIM(Request.Form("mm_build")) & "', "
   SQLstmt = SQLstmt & "yy_build='" & TRIM(Request.Form("yy_build")) & "', "
   SQLstmt = SQLstmt & "mm_wdn='" & TRIM(Request.Form("mm_wdn")) & "', "
   SQLstmt = SQLstmt & "yy_wdn='" & TRIM(Request.Form("yy_wdn")) & "', "
   SQLstmt = SQLstmt & "code='" & TRIM(Request.Form("code")) & "', "
   SQLstmt = SQLstmt & "last_shed='" & TRIM(Request.Form("last_shed")) & "', "
   SQLstmt = SQLstmt & "mm_cut='" & TRIM(Request.Form("mm_cut")) & "', "
   SQLstmt = SQLstmt & "yy_cut='" & TRIM(Request.Form("yy_cut")) & "', "
   SQLstmt = SQLstmt & "cut='" & TRIM(Request.Form("cut")) & "', "
   SQLstmt = SQLstmt & "notes='" & TRIM(Request.Form("notes")) & "' "
   SQLstmt = SQLstmt & " WHERE ID=" & TRIM(Request.Form("Recordid"))
    %>
SQL statement: <%=SQLstmt%>
<%
    Conn.Execute (SQLstmt)
    Response.Write "Operation Complete<br /><A HREF=""lmadmin3.asp"">Home</A>"
  END IF </i>

If it is a number you should use a numeric datatype in your db table, then you could never get "" in there. but anyway, you want
hth
Phil

Thank you both so much for your help, The number is a numeric field
I was stufing around trying all sorts to come up with an answer.
Final code I am using that works great...

<% pregroup = rs("pregroup")
 if isnull(pregroup) then
   pregroup = 0
 else
   pregroup = pregroup
 end if
>
>
>
<input id="pregroup" name="pregroup" value="<%=pregroup%>" />

ect;...

Thanks Topshed

The easiest way is to do the replacing of NULL to 0 in your SQL, a stored procedure would be best.