Hi, I had to convert my Access database to MS SQL 2008 and now none of my insert statements work. Don't suppose anyone knows why?

Here is my code:

<!--#include file="connection.asp" --><%

'Dimension variables Dim adoCon 'Holds the Database Connection Object Dim rsAddEntry 'Holds the recordset for the records in the database Dim strSQL 'Holds the SQL query for the database Dim file_exists Dim IDCheck Dim pTitle Dim pContent Dim pSub

pTitle = request.form ("Headline") pSub = request.form("SubHead") pContent = request.form("Content")

'This makes it so people cant inject SQL code and/or cause some unwanted errors pTitle = Replace(pTitle,"'", "''", 1, -1, 1) pContent = Replace(pContent,"'", "''", 1, -1, 1)
pSub = Replace(pSub,"'", "''", 1, -1, 1)

response.Write(pTitle) response.Write(pContent) response.Write(pSub)

'Create an ADO connection object Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection adoCon.Open Database_Path

'Create an ADO recordset object Set rsAddEntry = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database strSQL = "SELECT * FROM News WHERE News.Headline = '" & pTitle & "';" 'Open the recordset with the SQL query rsAddEntry.Open strSQL, adoCon 'If there is a record returned from the database then the username is already used file_exists = NOT rsAddEntry.EOF rsAddEntry.Close If file_exists = FALSE then

'Initialise the strSQL variable with an SQL statement to query the database strSQL = "SELECT * FROM News;"

'Set the cursor type we are using so we can navigate through the recordset rsAddEntry.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated rsAddEntry.LockType = 3

'Open the recordset with the SQL query rsAddEntry.Open strSQL, adoCon

'Tell the recordset we are adding a new record to it rsAddEntry.AddNew

'Update the record in the recordset rsAddEntry.Fields("Headline") = pTitle rsAddEntry.Fields("SubHead") = pSub rsAddEntry.Fields("Content") = pContent

'Write the updated recordset to the database rsAddEntry.Update

'Reset server objects rsAddEntry.Close

Set rsAddEntry = Nothing

Set adoCon = Nothing

Response.Redirect "news_add3.asp?ref=" & pTitle & ""

else

Response.Redirect "news_add.asp?Content=exists"

end if %>

The error I'm getting is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e2f'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'NewsDate', table 'aiicoplc_www.dbo.News'; column does not allow nulls. INSERT fails.

/admin/news_add2.asp, line 113

If anyone could let me know in the next hour or so that would be fantastic!

Thanks, Suzy

Apparently it was just because the database didn't allow NULL's. The problem was my date field in access was automatically done by setting the value to Now() which doesn't happen now the database has been converted.

hey you sorted it within the hour too!

Burning the midnight oil a bit there weren't you Suzy?

Big style at the moment - trying to learn on the go and survive on about 4 hours sleep a night :(

At least I'm being forced to change my database now though and finally move away from Access. Shame I've got very little time to get my head round the differences between them, though they are only subtle!