Inspiring

Answered

Simple DB Insert Not Working

Forum|Forum|17 years ago
October 24, 2008
4 replies
1297 views

Why isn't the following code working? Everything gets inserted into the database except the userID. I've tried enclosing that in both " " and ' ', to no avail. What the heck am I missing here?

Thanks,
GwenH

This topic has been closed for replies.

Correct answer GwenH

I am using MS Access 2007, but saving it in an 1997-2003 format. Yes, the userID is supposed to be unique. It is comprised of the year, month, day, hour, and minute a user submits a form, PLUS their zip code and the last four digits of their phone number.

I need the userID to be unique, and using the combination outlined above, it will be. How do I get Access to accommodate a 19 to 20-digit number?

Never mind, I found the answer. Access can only handle up to 10 digits in a number field. So, I have changed my userID to consist only of the YMMDDHHMM at which a user submitted a form.

Thanks for your help and your patience!

GwenH

G

GwenHAuthor

Inspiring

Isn't toNumber a "standard" function in ColdFusion? If not, how do I convert a text string to a number?

-

-__cfSearching__-

Inspiring

GwenH wrote:
> Isn't toNumber a "standard" function in ColdFusion?

The best way to answer that question is to look at the list of functions in documentation:
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions-pt0_01.html

> If not, how do I convert a text string to a number?

That depends on the format of the string. A starting place would be to look at the Val() function
http://livedocs.adobe.com/coldfusion/8/htmldocs/functions-pt0_18.html#1099887

G

GwenHAuthor

Inspiring

Here's the code I have now for the line in question, and here's the error message I am getting. The attached code is the only two lines I've changed since my last reply, and line one has a very slight modification only.

Error message: "Invalid data 81024121032539638 for CFSQLTYPE CF_SQL_INTEGER. "

Thanks,
GwenH

D

Dan_Bracuk

Inspiring

Or you can use cfquery instead of cfinsert. There are other advantages to this approach that justify the extra work, at least for me.

-

-__cfSearching__-

Inspiring

quote:

Originally posted by: Dan Bracuk
Or you can use cfquery instead of cfinsert. There are other advantages to this approach that justify the extra work, at least for me.

+1

G

GwenHAuthor

Inspiring

Okay, so how do I get this to work? I can set the date as a hidden field on the form, so that cfinsert recognizes that value at least. But how do I take the first five numbers of the zip code form field and the last four numbers of the phone number form field, then concatenate them with the hidden date field from the form?

Thanks,
GwenH

-

-__cfSearching__-

Inspiring

FORM is structure. So you can add or remove keys from it as with regular structures.

<cfset FORM.myNewVariable = "whatever">

> <cfset date = dateFormat(now(), 'YYMMDDHHMM')>

You may experience problems if the table column name is actually "Date". "Date" is often a reserved keyword. Also, DateFormat returns a string. The value may not be interpreted correctly if you are inserting into a date/time column.

G

GwenHAuthor

Inspiring

Here's the code I have now. I am getting an error message on it: "variable toNumber not defined". Why is ColdFusion seeing the toNumber() function as a variable in this code?

Thanks,
GwenH

D

Dan_Bracuk

Inspiring

cfinsert only recognizes form fields and userid is a local variable.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded