I am trying to ADD a record and the code appears to just fall thru, no error messages

<code starting the last bit of the form which sets the flag for what follows>

<input type="hidden" name="flag" value="2" />
<label for="submit"></label>
<input type="submit" value="ADD this Model" />
</form>

<%
'If the QueryString has the Actionvar = update then generatethe page for updating items

  ELSEIF Request.Form("flag")="2" THEN
    SQLstmt = ""
    SQLstmt = SQLstmt & "INSERT INTO aefe"'
    SQLstmt = SQLstmt & "(model,suffix,chassis,body,rego,fleet,route,ad_de st,livery,Rdate,gsnum)" & vbCrLf
    rdate = Request.Form("rdate") //* a date var set up earlier
    SQLstmt = SQLstmt & "VALUES ("
    SQLstmt = SQLstmt & request.form("model") & ","
    SQLstmt = SQLstmt & request.form("suffix")& "','"
    SQLstmt = SQLstmt & request.form("chassis")& "','"
    SQLstmt = SQLstmt & request.form("body")& "','"
    SQLstmt = SQLstmt & request.form("rego")& "','"
    SQLstmt = SQLstmt & request.form("fleet")& "','"
    SQLstmt = SQLstmt & request.form("route")& "','"
    SQLstmt = SQLstmt & request.form("ad_dest")& "','"
    SQLstmt = SQLstmt & request.form("livery")& "','"
    SQLstmt = SQLstmt & request.form("rdate")& "','"
    SQLstmt = SQLstmt & request.form("gsnum")& "'"
    SQLstmt = SQLstmt & ");"

%>

SQL statement: <%=SQLstmt%>
<%
    conn.execute(SQLstmt)
    Response.Write "Operation Complete<br /><a href=""Eadmin.asp"">Home</a>"
  END IF
</code>

the response write at the bottom never gets written and the record does not get written

I can find nothing wrong with the code and it dont throw an error

AND ITS DRIVING ME NUTS !

Please help ?

Topshed

The first thing I see is:

rdate = Request.Form("rdate") //* a date var set up earlier

// is not a comment in asp (' is a comment) this will cause you problems.

You should comment out the execute line to write the query to the browser, change:
conn.execute(SQLstmt)
to:
'conn.execute(SQLstmt)

FYI you should use the trim function around all your:
trim(request.form("model"))

Also i would suggest dealing with ' etc characters by running a function around ALL free text areas. Here is a function:

  Function StoreText(theText)
    StoreText = ""
    on error resume next
    StoreText = CStr(theText)
    if (len(StoreText) > 0) Then
      StoreText = Replace(StoreText, """", "&quot;", 1, -1, 1)
      StoreText = Replace(StoreText, "'", "''", 1, -1, 1)
      StoreText = Replace(StoreText, vbCrLf, "<BR>", 1, -1, 1)
    end if
  End Function

To use it you should:
trim(storeText(request.form("model")))

Otherwise users will crash your page. Statements are weak without these simple steps.


Wind is your friend
Matt

Hi Matt,

Thanks for that info I have implemented it all in my script
The comment I put in was not in my script just what I posted

I love the little function Thank you
<code now is- >
'conn.execute(SQLstmt) ELSEIF Request.Form("flag")="2" THEN
    SQLstmt = ""
    SQLstmt = SQLstmt & "INSERT INTO aefe"'
    SQLstmt = SQLstmt & "(model,suffix,chassis,body,rego,fleet,route,ad_de st,livery,Rdate,gsnum)" & vbCrLf
    rdate = Request.Form("rdate")
    SQLstmt = SQLstmt & "VALUES ("
    SQLstmt = SQLstmt & trim(storeText(Request.Form("model")))
    SQLstmt = SQLstmt & trim(storeText(request.form("suffix")))
    SQLstmt = SQLstmt & trim(storeText(request.form("chassis")))
    SQLstmt = SQLstmt & trim(storeText(request.form("body")))
    SQLstmt = SQLstmt & trim(storeText(request.form("rego")))
    SQLstmt = SQLstmt & trim(storeText(request.form("fleet")))
    SQLstmt = SQLstmt & trim(storeText(request.form("route")))
    SQLstmt = SQLstmt & trim(storeText(request.form("ad_dest")))
    SQLstmt = SQLstmt & trim(storeText(request.form("livery")))
    SQLstmt = SQLstmt & trim(storeText(request.form("rdate")))
    SQLstmt = SQLstmt &trim(storeText( request.form("gsnum")))
    SQLstmt = SQLstmt & ");"

%>

SQL statement: <%=SQLstmt%>
<%
    'conn.execute(SQLstmt)
    Response.Write "Operation Complete<br /><a href=""Eadmin.asp"">Home</a>"

</code>

but the thing does not wrtie to my screen and still apparantly drops thru and the response.write does not appear

Regards

Topshed

Opps changed Num field ("model")to

SQLstmt = SQLstmt & request.form("model") & ","

Topshed

Still struggling with this
any help most welcome

Topshed

Hi Topshed,

If the sql statement is not being written to your screen, it sounds like the other half of your if statement may be getting executed (the part above the ELSEIF) or the Request.Form("flag") is not returning "2".

Is the form using method="post"?

Does a Response.Write return anything in the other part of the if statement?

Cheers,

Chris

Thanks for the help, I am using "post" although I seem to get mote meaningfull error messages with "get"

but I now have it working with this :-
<code>
ELSEIF Request.Form("flag")="2" THEN
    SQLstmt = ""
    SQLstmt = SQLstmt & "INSERT INTO aefe (model,suffix,chassis,body,rego,fleet,route,ad_des t,livery,rdate,gsnum) "
    rdate = Request.Form("rdate")
    SQLstmt = SQLstmt & "VALUES ("
    SQLstmt = SQLstmt & trim(storeText(Request.Form("model")))&",' "
    SQLstmt = SQLstmt & trim(storeText(request.form("suffix")))&"','"
    SQLstmt = SQLstmt & trim(storeText(request.form("chassis")))&"','"
    SQLstmt = SQLstmt & trim(storeText(request.form("body")))&"','"
    SQLstmt = SQLstmt & trim(storeText(request.form("rego")))&"','"
    SQLstmt = SQLstmt & trim(storeText(request.form("fleet")))&"','"
    SQLstmt = SQLstmt & trim(storeText(request.form("route")))&"','"
    SQLstmt = SQLstmt & trim(storeText(request.form("ad_dest")))&"','"
    SQLstmt = SQLstmt & trim(storeText(request.form("livery")))&"','"
    SQLstmt = SQLstmt & trim(storeText(request.form("rdate")))&"'"
    SQLstmt = SQLstmt & trim(storeText(request.form("gsnum")))&"'"
    SQLstmt = SQLstmt & ");"&vbCrLf
%>
SQL statement: <%=SQLstmt%>
<%
JXIsoDate(rdate)
    conn.execute(SQLstmt)
</code>

the date seemed to be a lot of the problem, and the small function to convert it to an ISOdate fixed it up
see below
Function JXIsoDate(dteDate)
'Version 1.0
   If IsDate(dteDate) = True Then
      DIM dteDay, dteMonth, dteYear
      dteDay = Day(dteDate)
      dteMonth = Month(dteDate)
      dteYear = Year(dteDate)
      JXIsoDate = dteYear & _
         "-" & Right(Cstr(dteMonth + 100),2) & _
         "-" & Right(Cstr(dteDay + 100),2)
   Else
      JXIsoDate = Null
   End If
End Function

only thing left to sort out now is how to trap the error when there is no date entered and still save the record without errors popping up

I would default it to now() but the date relates to an issue date and can be anywhere from 1989 onwards

Thanks
Roy