November 7, 2003 at 7:56 pm
Hello everyone. I'm brand new to SQL and I've been asked to move a ASP/SQL website from one ISP to another. Using DTS I was able to make a copy of the DB and everything seems to be working.
Except for one thing. There's some shopping cart code that adds an item to the cart when it's run. When I try to run it on the new ISP, I get this error:
"Cannot insert the value NULL into column 'createdate', table 'sawbonesdb.sawbones2.prl_shopping_cart'; column does not allow nulls. INSERT fails. "
I did some digging and that column should be automatically filling itself with the current date because the default schema says (getdate()) for that row.
I'm wondering where I should start looking for an issue like this. Is there a way to install the getdate function if it's not already present? Am I barking up the wrong tree entirely? Any thoughts would be greatly appreciated.
Thanks!
Jon
November 8, 2003 at 4:13 am
You are probably inserting null into the field rather than omitting it from the insert list and allowing it to default.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
November 8, 2003 at 9:28 am
Thanks for the response. I thought the same thing, but the code doesn't say anything about date. Plus, the it works on the old server. Here it is:
oCmd.Parameters.append oCmd.CreateParameter("@userid",adBigInt,adParamInput,,iUserID)
oCmd.Parameters.append oCmd.CreateParameter("@productid",adInteger,adParamInput,4, iProdID)
oCmd.Parameters.append oCmd.CreateParameter("@category",adVarChar,adParamInput,30,sCat)
oCmd.Parameters.append oCmd.CreateParameter("@qty",adInteger,adParamInput,4,iQty)
oCmd.Parameters.append oCmd.CreateParameter("@bUser",adChar,adParamInput,1,bUser)
oCmd.Parameters.append oCmd.CreateParameter("@retval",adInteger,adParamOutput)
I added a line for the date:
oCmd.Parameters.append oCmd.CreateParameter("@orderdate",adDBTimeStamp,adParamOutput,7)
And now I get an error that tells me there are "too many arguments specified."
I'm not sure where else to look to modify/fix this code.
November 8, 2003 at 9:40 am
I take it this is calling an SP in which case the column can't be referenced.
Yoyu copied the database usnig dts - have you checked that the default was also copied for that column.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
November 8, 2003 at 9:52 am
Hm, I'm not sure what "SP" stands for. I'm pretty new to SQL.
I did go into the schema, and the date column says (getdate()). I also verified it was identical to the old ISP which works fine.
November 8, 2003 at 12:56 pm
SP = Stored Procedure
What does the rest of that insert code look like - does it set the command type to adCmdStoredProc?
In query analyser try an insert statement
begin tran
insert prl_shopping_cart(col1, col2, col3)
select 1,1,1
rollback tran
Don't know what the columns are but something like that - and see if you get the CreateDate error
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
November 8, 2003 at 1:05 pm
Good timing -- I had just realized you were referring to stored procedures, so I went and added the getdate() function. Problem solved. I really appreciate your help.
But now I'm getting a new error because the code is not passing an ID either. The new database, rather than auto-incrementing based on the last ID, is choking because ID can't be NULL.
I'm sure it's very simple to simply add the next ID with a special call like nextID() or something, but I haven't found anything yet from some searching. Any thoughts?
November 8, 2003 at 1:10 pm
I'm pretty sure this is because of the way you created the database.
I suspect this is an identity in the old database but not in the new (and that the createdate column doesn't really have a default - in spite of what you are seeing).
Try backing up the old database, copying the backup file and restoring it.
This will get all the database objects and the only thing you may have problems with are users.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
November 10, 2003 at 8:51 am
Look at the table definition (from Enterprise Manager, right click on the table, select aALll tasks, Generate SQL Script. Make sure the id field is like:
CategoryID int IDENTITY (1, 1) NOT NULL ,
and not
CategoryID int NOT NULL
If you missed this check for missing indexes etc. There may be more wron with this database than a missing IDENTITY field. Using DTS to copy the database assumes you have used the correct table and Index definitions on the new database to begin with. You may want to rescript the entire database and recopy.
Francis
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply