Gossamer Forum: General: Databases and SQL: Update to SQL DB using ASP

Hello,

I'm doing updates to an SQL db from an ASp file. I can do the updates, but when I do the displays the field are truncated after the first space, If I do a display (response.write) the contents of the field are displayed, when data is entered in the field with spaces the db gets updated properly. I don't know what I'm doing wrong. Here is the code.

Thanks,

Dolores

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN//4.0">
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<html>
<HEAD>
<center>
<TITLE>Project Information</TITLE>
<LINK REL="stylesheet" HREF="/css/style_sheet.css" type="text/css">
</HEAD>
<h1>Casa Linda Homes - Property Management</h1>
<h3 class="important">Update Project</h3>

<%
Server.ScriptTimeout = 300
dim intcompany
dim intlender
dim intcity
dim intcounty

set conn = Server.CreateObject("ADODB.recordset")
conn="DSN=ODBCBuilder;uid=BSE;pwd=password;"

ProjKey=Request.form("Project_Key")



if Request.form("Loan_No")="" then
set rsproject = Server.CreateObject("ADODB.recordset")
sql0="SELECT ProjectName FROM Projects WHERE Projects.Project_Key='" & ProjKey & "'"
rsproject.Open sql0,conn
end if

if Request.form("Loan_No")="" then
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT CLH_Projects.Project_Key, CLH_Projects.Lender_NAF_Key, CLH_Projects.Loan_No, CLH_Projects.Legal1,

CLH_Projects.Legal2, CLH_Projects.Address1, CLH_Projects.Address2, CLH_Projects.City, CLH_Projects.County,

CLH_Projects.Company_Key FROM CLH_PROJECTS WHERE CLH_Projects.Project_Key='" & ProjKey & "'"
conn="DSN=ODBCBuilder;uid=BSE;pwd=password;"
rs.Open sql,conn

%>



<% response.write("<b>" & rsproject.fields("ProjectName") & "</b>")%>

<form method="post" action="CLH_projupda.asp">
<table class="input" border="1" cellpadding="2" cellspacing="0">
<%
do until rs.EOF
response.write("</tr><tr bgcolor='#EAF3F7'>")
response.write("<th width=180>Key</th>")
response.write("<td><input name=Project_Key size=4 maxlength=4 value=" & rs.fields("Project_Key") & "></td>")
response.write("</tr><tr bgcolor='#EAF3F7'>")
response.write("<th width=180>Loan Number</th>")
response.write("<td><input name=Loan_No size=10 maxlength=10 value=" & rs.fields("Loan_No") & "></td>")
response.write("</tr><tr bgcolor='#EAF3F7'>")

response.write("<th width=180>Legal Note 1</th>")
response.write("<td><input name=Legal1 size=25 maxlength=25 value=" & rs.fields("Legal1") & "></td>")
response.write("</tr><tr bgcolor='#EAF3F7'>")
response.write("<th width=180>Legal Note 2</th>")
response.write("<td><input name=Legal2 size=25 maxlength=25 value=" & rs.fields("Legal2") & "></td>")
response.write("</tr><tr bgcolor='#EAF3F7'>")

response.write("<th width=180>Address 1</th>")
response.write("<td><input name=Address1 size=50 maxlength=50 value=" & rs.fields("Address1") & "></td>")
response.write("</tr><tr bgcolor='#EAF3F7'>")
response.write("<th width=180>Address 2</th>")
response.write("<td><input name=Address2 size=50 maxlength=50 value=" & rs.fields("Address2") & "></td>")
response.write("</tr><tr bgcolor='#EAF3F7'>")
response.write(rs.fields("address1"))
holdcity=("" & rs.fields("city") &"")
intcity=CInt(holdcity)
if intcity<>"" then
set rscity = Server.CreateObject("ADODB.recordset")
sql2="SELECT city_Key, City_Name FROM CLH_city ORDER BY City_Name"
rscity.Open sql2,conn

response.write("<th width=180>City</th>")
response.write("<td><select name=city_Key value=" & rscity.fields("city_Key") & ">")
do until rscity.EOF
response.write("<option")
if rscity.fields("city_Key")=rs.fields("city") then
response.write(" selected")
end if
response.write(" value=")
response.write(rscity.fields("city_Key"))
response.write(">")
response.write(rscity.fields("City_Name"))
rscity.MoveNext
loop
end if

rscity.close
set rscity=nothing

response.write("</tr><tr bgcolor='#EAF3F7'>")

holdcounty=("" & rs.fields("county") &"")
intcounty=CInt(holdcounty)
if intcounty<>"" then
set rscounty = Server.CreateObject("ADODB.recordset")
sql2="SELECT county_Key, county_Name FROM CLH_county ORDER BY county_Name"
rscounty.Open sql2,conn

response.write("<th width=180>county</th>")
response.write("<td><select name=county_Key value=" & rscounty.fields("county_Key") & ">")
do until rscounty.EOF
response.write("<option")
if rscounty.fields("county_Key")=rs.fields("county") then
response.write(" selected")
end if
response.write(" value=")
response.write(rscounty.fields("county_Key"))
response.write(">")
response.write(rscounty.fields("county_Name"))
rscounty.MoveNext
loop
end if
response.write("</tr><tr bgcolor='#EAF3F7'>")

holdcompany=("" & rs.fields("Company_Key") &"")
intcompany=CInt(holdcompany)
if intcompany<>"" then
set rscompany = Server.CreateObject("ADODB.recordset")
sql2="SELECT Company_Key, LegalName FROM Company ORDER BY LegalName"
rscompany.Open sql2,conn

response.write("<th width=180>Company that Owns Project</th>")
response.write("<td><select name=Company_Key value=" & rscompany.fields("Company_Key") & ">")
do until rscompany.EOF
response.write("<option")
if rscompany.fields("Company_Key")=rs.fields("Company_Key") then
response.write(" selected")
end if
response.write(" value=")
response.write(rscompany.fields("Company_Key"))
response.write(">")
response.write(rscompany.fields("LegalName"))
rscompany.MoveNext
loop
end if
response.write("</td></tr>")
response.write("</table>")
response.write("<br><br>")
response.write("<input type='submit' value='Update Project'>")
response.write("</form>")
rs.MoveNext
loop

else

set rs9 = Server.CreateObject("ADODB.recordset")
sql="SELECT CLH_Projects.Project_Key, CLH_Projects.Lender_NAF_Key, CLH_Projects.Loan_No, CLH_Projects.Legal1,

CLH_Projects.Legal2, CLH_Projects.Address1, CLH_Projects.Address2, CLH_Projects.City, CLH_Projects.County,

CLH_Projects.Company_Key FROM CLH_PROJECTS WHERE CLH_Projects.Project_Key='" & ProjKey & "'"
conn="DSN=ODBCBuilder;uid=BSE;pwd=password;"

rs9.Open sql,conn,3,3

rs9("Loan_No") = Request.Form("Loan_No")
rs9("Legal1")= Request.Form("Legal1")
rs9("Legal2") = Request.Form("Legal2")
rs9("Address1") = Request.Form("Address1")
rs9("Address2") = Request.Form("Address2")
rs9("City") = Request.Form("City_key")
rs9("County") = Request.Form("County_key")
rs9("Company_Key") = Request.Form("Company_Key")

rs9.update
rs9.close

set rs9 = nothing

if err<>0 then
response.write("No update permissions!")
else
response.write("Record " & ProjKey & " was updated!")
end if

end if

%>

<form method="post" action="clh_projedit.asp">
</select>
<input type="submit" value="Return to Update Project" >
</center>
</body>
</html>