converting sql from access to SQL server, have to rewrite complex SQL queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

converting sql from access to SQL server, have to rewrite complex SQL queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-08-25 : 19:01:28

ark writes "Hi,

I'm moving the datasource from access mdb file to SQL server, I get this error:

Cannot insert the value NULL into column 'idOrder', table 'commerce-now-demo.com.dbo.Carthead'; column does not allow nulls. INSERT fails.

when executing this query:

connTemp.BeginTrans
'response.write "here "&idOrder&" !"
'If no cart exists, create new cart and session.
if isNull(idOrder) then
set rsTemp = openRSopen("cartHead",adUseServer,adOpenKeySet,adLockOptimistic,adCmdTable,0)
rsTemp.AddNew
'Update standard fields
rsTemp("idCust") = 0
rsTemp("orderDate") = now()
rsTemp("orderDateInt") = dateInt(now())
rsTemp("orderStatus") = "U"
rsTemp("auditInfo") = Request.ServerVariables("REMOTE_ADDR") & "|" & Request.ServerVariables("REMOTE_USER")
'Update Private Comments field
application.lock
if trim(application("session_privateComments"&sessionidstr)) <> "" then
rsTemp("privateComments")= trim(application("session_privateComments"&sessionidstr)&"") & chr(10)
end if
application.unlock
'Update Affiliate fields
application.lock
if isNumeric(application("session_idAffiliate"&sessionidstr)) _
and isNumeric(application("session_commPerc"&sessionidstr)) _
and trim(application("session_idAffiliate"&sessionidstr)) <> "" _
and trim(application("session_commPerc"&sessionidstr)) <> "" then
rsTemp("idAffiliate") = CInt(application("session_idAffiliate"&sessionidstr))
rsTemp("commPerc") = CDbl(application("session_commPerc"&sessionidstr))
end if
application.unlock
rsTemp.Update
'Put order ID into session object
application.lock
application("session_idOrder"&sessionidstr) = rsTemp("idOrder") '@@identity
application.unlock
idOrder = rsTemp("idOrder")
call closeRS(rsTemp)

Help please =)"

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-25 : 19:25:06

Two options:
1. Change the column in question to allow NULLs (you can do this in EM table design)
2. Modify the query to eliminate the NULLs from the values being entered.

maydo
Starting Member

20 Posts

Posted - 2004-08-25 : 23:50:01

You probably moved the table(Carthead) from access to SQLServer and in Access it had the autonumber type for the column 'idOrder'.
In SQLServer there isn't autonumber as datatype and this column has an int datatype and allows NULL.
You can fix it by altering the column as folows:

ALTER TABLE Carthead
ALTER COLUMN [idOrder] [int] IDENTITY (1, 1) NOT NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources