SQL 2000 Database INSERT INTO Problem

[MinalMaurice]

When using MS Access the following code works

<cfset #UKHour# = #DateAdd("h",5,Now())#>
<cfquery name="addpage" datasource="#AcDNS#">
INSERT INTO tblCounter (visitdate, visittime, browser, ip_address)
VALUES ('#dateformat("#UKHour#", 'dd/mmm/yyyy') #', '#timeformat("#UKHour#", 'HH:mm:ss')#', '#cgi.http_user_agent#', '#remote_addr#')
</cfquery>

With SQL 2000 the new code used is

<cfquery name="addpage" datasource="#AcDNS#" username="#AcName#" password="#AcPW#">
INSERT INTO tblCounter (visitdate, visittime, browser, ip_address)
VALUES (<cfqueryparam value="#Now()#" cfsqltype="CF_SQL_DATE">, <cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIME">, '#cgi.http_user_agent#', '#remote_addr#')
</cfquery>

The first problem is that an error comes up saying that a NULL value can't be entered into the ID field, which means that the index field is not being automatically incremented.

The second problem is that because the database is in another country I added 5 hours via #DateAdd("h",5,Now())#>
- is there any way that can be done with the cfqueryparam.

How should the code be changed to overcome those 2 problems. Does a Trigger need to be set within SQL 2000 for the automatic indexing.

Thanks in advance.

 

[ecobb]

If the SQL Server isn't automatically incrementing, whoever set it up doesn't know what they're doing and does not need to be messing with the SQL Server. Period. Sounds to me like someone just tried to upgrade an Access database and never bothered to check anything on the SQL Server end. This isn't a ColdFusion problem, you'll need to go to the SQL Server forum for help on that.

On your second question, you should be able to put the DateAdd inside your cfqueryparm like this:

<cfqueryparam value="#DateAdd("h",5,Now())#" cfsqltype="CF_SQL_DATE">

Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher