a little help with my sql

[TechBoyJK]

I'm moving over to MS SQL, and I need to update my sql (no pun intended) for MS SQL.

I'm going to google this, but if anybody has some quick pointers, it would be appreciated. Here is an example that pretty much shows how I wrote most of the code. I rely on Access autoincrement feature for new records. Does MS SQL not do that?

<CFQUERY NAME="create_account_a" DATASOURCE="members">

INSERT INTO tbl_member_core
(member_user_name,
member_type,
member_status,
member_password,
member_password_hint,
member_signup_date,
member_signup_time
)

VALUES
('#FORM.member_user_name#',
'#FORM.member_type#',
'new',
'#FORM.member_password#',
'#FORM.member_password_hint#',
'#DateFormat(Now(),'mm/dd/yy')#',
'#TimeFormat('00:00:01','HH:mm:ss')#'
)

;

</CFQUERY>

 

[UCJefe]

SQL Server does autoincrement no problem. I don't really know Access or ColdFusion at all so I'm not sure if all those funny characters are for ColdFusion's sake or Access' sake, but regardless you don't want them in SQL Server. I took the liberty of condesing your signup_date and signup_time into 1 field (datetime) which stores both. And really, you can set up a default on that column so you don't even need to pass it from your app. Anyway, this is what I did and everything worked fine. If you need more help, post the schema of your table so we don't have to guess at datatypes and try to isolate the query that is actually being performed from all the CF junk (although you may have done that already).

There is no DateFormat() and TimeFormat() in SQL Server, but again, maybe that's CF weirdness. Anyhow, here's my schema and query.

 

[TechBoyJK]

thanks. as I was suspecting, ms sql code looks to be smarter and cleaner.