request.dsn and error

Forum|Forum|18 years ago
July 28, 2007
3 replies
2484 views

I am working on a site and I have two databases. I used the Request.DSN in the application.cfm for the first data base...but how can I do something similar for the second? I would rather not have to go back and rename everywhere the database is listed in the site to make it match the server after upload.

Also, my second question. I am trying to allow users to update there information. The site works perfectly on my computer but after I upload it ...one of my update forms doesnt work. All the others do..however this one uses the username from a session variable.Why would it work on my computer but not when its uploaded to the remote server? See below.

Thanks

Error Executing Database Query.
Syntax error in UPDATE statement.

The error occurred in D:\Hosting\members\update_member.cfm: line 38

36 : ''
37 : </cfif>
38 : WHERE UserName=<cfqueryparam value="#FORM.UserName#" cfsqltype="cf_sql_clob" maxlength="50">
39 : </cfquery>
40 : <cflocation url="index.cfm">

Database access

This topic has been closed for replies.

Correct answer cf_dev2

Thanks for your reply and time. I am using MS Access via Request.DSN and the users table.

I tried not using the password, I tried on a blank page... I tried to simplify it any way I could. The code from what I can tell is exactly Identical except field names to a form I used at another place on the site....just at a loss..

quote:

Originally posted by: Azadi
possible causes:

a) in ms access "password" is a reserved word, so if a field/column in
your table has that name you should surround it with [ ] in your sql
statements. if i am not mistaken, unicode and non-unicode ms access
drivers may tret this issue differently, and if on your dev server and
production server different drivers are used this may explain why you
see error only on production server

Based on the error message, I suspect Azadi's suggestion is correct. "Password" is likely a reserved word.

Try using square brackets around the column name. If that eliminates the error, consider renaming the column. Its best to avoid reserved words when possible.

INSERT INTO Users (UserName, [Password], FirstName, LastName, UserTypeID, Email)
VALUES ( ... rest of code .... )

N

Newsgroup_User

Inspiring

> I am working on a site and I have two databases. I used the Request.DSN in the
> application.cfm for the first data base...but how can I do something similar
> for the second?

A data source name is just a string... there's nothing special about it,
and "request.dsn" is just a variable in which you put the name of the data
source. So if you have a second datasource, you can use another variable
to refer to that data source.

I see that other people have suggested "request.dsn2" as a variable name.
I'm not so sure how helpful that is. Your two datasources obviously ahve
different purposes, so I would suggest using a variable name that reflects
those purposes.

request.dsnStockSystem, request.dsnPersonnel, etc.

To troubleshoot the UPDATE error you're getting... it would be helpful if
you posted the ENTIRETY of the query that is erroring.

It's always a good idea to think through what information might be helpful
to those you're asking, when asking for help.

Sean Corfield posted a relevant blog entry the other day. It might be
helpful to give that a read:
http://corfield.org/blog/index.cfm/do/blog.entry/entry/How_To_Ask_Questions_The_Smart_Way

--
Adam

N

Newsgroup_User

Inspiring

1) how about just defining request.dsn2 in your application.cfm ?

2) CLOB data type for a 50 char long field??? do you really need that?
TEXT/VARCHAR(50) would sufficy.
but that is unlikely the problem... pls post the full code of your query
that throws the error - it is very likely that the problem is on a
different line, like in your cfif statement...

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

A

Anonymous

Thanks for all your help regarding the request.dsn question. I found all your responses very helpful.

I used dreamweaver for my forms...might be why I cant understand what is wrong with it. The insert record is coming up with an error on the remote server but not the testing server. I have created various other insert record forms that are working just fine...but this one is the last that is not working... I recreated the edit user form from scratch and it fixed the problem.. but no a problem migrated to the insert new user page...lool my luck ....and below is code for it.

Thanks

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<cfquery datasource="#Request.DSN#">
INSERT INTO Users (UserName, Password, FirstName, LastName, UserTypeID, Email)
VALUES (<cfif IsDefined("FORM.UserName") AND #FORM.UserName# NEQ "">
<cfqueryparam value="#FORM.UserName#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.Password") AND #FORM.Password# NEQ "">
<cfqueryparam value="#FORM.Password#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.FirstName") AND #FORM.FirstName# NEQ "">
<cfqueryparam value="#FORM.FirstName#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.LastName") AND #FORM.LastName# NEQ "">
<cfqueryparam value="#FORM.LastName#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.UserTypeID") AND #FORM.UserTypeID# NEQ "">
<cfqueryparam value="#FORM.UserTypeID#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, <cfif IsDefined("FORM.Email") AND #FORM.Email# NEQ "">
<cfqueryparam value="#FORM.Email#" cfsqltype="cf_sql_clob" maxlength="100">
<cfelse>
''
</cfif>
)
</cfquery>
<cflocation url="membership.cfm">
</cfif>
<cfquery name="Recordset1" datasource="#Request.DSN#">
SELECT *
FROM Users
</cfquery>

And the form

<form action="<cfoutput>#CurrentPage#</cfoutput>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td nowrap="nowrap" align="right">UserName:</td>
<td><input type="text" name="UserName" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Password:</td>
<td><input type="text" name="Password" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">FirstName:</td>
<td><input type="text" name="FirstName" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">LastName:</td>
<td><input type="text" name="LastName" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">UserTypeID:</td>
<td><input type="text" name="UserTypeID" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Email:</td>
<td><input type="text" name="Email" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"> </td>
<td><input type="submit" value="Insert record" /></td>
</tr>
</table>
<input type="hidden" name="MM_InsertRecord" value="form1" />
</form>

B

bhakala

Participating Frequently

quote:

Originally posted by: dwmart
I am working on a site and I have two databases. I used the Request.DSN in the application.cfm for the first data base...but how can I do something similar for the second? I would rather not have to go back and rename everywhere the database is listed in the site to make it match the server after upload.

I can help on this, but I guess I am a bit confused as to:

A. Are you trying to use two databases, thus two datasources defined via CF administration for same website?

OR

B. Just reusing the same database on two different sites? (i.e. completed it for one site and now want to reuse the app pages)

Let me know.

A

Anonymous

Yes, the two databases are to be used on the same website. There is only one page on the website that uses both to draw information, but both would run within the same website.

The website hosting doesnt really have any coldfusion admin area....they just have a place to assign the DSN.
I am trying to avoid renaming the datasource on all the pages that contain the second database. I used the Request.DSN for the first database on the site.. but what can I do about the second database?

Thanks

B

bhakala

Participating Frequently

I would agree with why not just redefining the request.dsn2 in your application.cfm for your second database?

Also if you only are really only pulling in the second database into one page, why would you even be concerned with having to change up your queries all over the site?

The only page of concern should be the ones where you are pulling in both databases, which you could easily resolve by making your second query to the second database to use:

<cfquery name="query_name" datasource="#request.dsn2#">

Am I missing something else here?

On the second issue for updating member info, I assume you are making them login first beforehand and creating some sort of session.Username in your application.cfm?

If so then it is easy enough of:

WHERE UserName = "#session.Username#"

I would be confused otherwise where you would use the code you have, if that code could even work, of:

WHERE UserName=<cfqueryparam value="#FORM.UserName#">

Let me know if any of this helps.

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