INSERT INTO issue

[tyleri]

This is not working... I have an MDB MSAccess 2000 file called database.mdb. I have a table called news_items, and the two fields i am pulling from the form are "date" and "news".

here is the asp code that generates a HTTP 500 Internal Error

<%

Dim date, news, data_source, con, sql_insert


Function ChkString(string)
If string = &quot;&quot; Then string = &quot; &quot;
ChkString = Replace(string, &quot;'&quot;, &quot;''&quot
End Function


date = ChkString(Request.Form(&quot;date&quot)
news = ChkString(Request.Form(&quot;news&quot)


data_source = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;database.mdb&quot
sql_insert = &quot;insert into news_items (date, news) values ('&quot; & date & &quot;', '&quot; & news & &quot;')&quot;


Set con = Server.CreateObject(&quot;ADODB.Connection&quot
con.Open data_source
con.Execute sql_insert

con.Close
Set con = Nothing
%>

 

[AncientTiger]

Tyler, it looks like your checkstr function MAY be killing your sql string. You're replacing ticks with double ticks, but this &quot;null field&quot; may be rejected by your database...depending on how you've got your tables set up. If a specific column is set to NOT accept null entries, and you're feeding it a null value, it may be puking at that.

Also, I'd recommend taking a peek at the sql string your code is generating. You can do this by:
==========================================================
data_source = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;database.mdb&quot
sql_insert = &quot;insert into news_items (date, news) values ('&quot; & date & &quot;', '&quot; & news & &quot;')&quot;

Response.Write(&quot;<br>sql_insert = &quot;+sql_insert) ' Writes out the string value it's trying to execute in ADO
Response.End 'Stops execution of the page so you can take a peek at the string value

Set con = Server.CreateObject(&quot;ADODB.Connection&quot
==========================================================
This way you can see what your script is trying to send to the ADO connection. There may be some obvious syntax errors you can see right off with this method....

Another technique I'd recommend is inserting an error handler so that you won't get the ever ambiguous &quot;HTTP 500 Server Error&quot; page the never tells you what you need to know: WHAT THE HECK HAPPENED?!?!?!?

One way to do this is:
==========================================================
Dim date, news, data_source, con, sql_insert


Function ChkString(string)
If string = &quot;&quot; Then string = &quot; &quot;
ChkString = Replace(string, &quot;'&quot;, &quot;''&quot
End Function


date = ChkString(Request.Form(&quot;date&quot)
news = ChkString(Request.Form(&quot;news&quot)


data_source = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;database.mdb&quot
sql_insert = &quot;insert into news_items (date, news) values ('&quot; & date & &quot;', '&quot; & news & &quot;')&quot;


Set con = Server.CreateObject(&quot;ADODB.Connection&quot

on error resume next 'Tells the interpreter to continue with execution even if there's an error***

con.Open data_source
con.Execute sql_insert

con.Close
Set con = Nothing

if err <> 0 then 'If the err object numeric value is 0, everything's fine. if not, there's been an error

Response.Write(&quot;<br>An error occured&quot
Response.Write(&quot;<br>Error Description: &quot;+err.description) 'writes out the string value in the err description property
Response.Write(&quot;<br>Error Source: &quot;+err.source ' writes out the source of the error
err.clear 'clears the error

end if
==========================================================

This is a technique I use, and it has always served me very well. Don't know about you, but I HATE the 500 Server Error page. Us of the feeble minded species need more info than THAT!

Hope this helps
AT

***One thing to note is that if there's more than one error that occurs between your &quot;on error resume next&quot; statement, and the script that handles the error, you're going to get the 500 Server Error page. ASP can only handle one error at a time. If you need to have a broad based error handling routine, I'd recommend setting up a error handling function...