request.dsn and error

[Guest]

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">

[bhakala]

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.

[Guest]

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

[bhakala]

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.

[Guest]

YES! the Request.DSN2 is what I was looking for. I knew there had to be something like that. Simple but hard to find the info online if you dont know what to call what your looking for. Thanks!!

I am trying to understand the second issue I was having. I don't understand why everything works on my testing server but not on the remote server??

[Newsgroup_User]

fyi, and to follow up on adam's post, request.dsn2 is just a name. it
means a variable called dsn2 in the request scope. you could have called
it whatever you want! it is not any sort of pre-set cf variable name
that you must use, no no no.

as adam said, naming your two variables with more meaningful names, i.e.
with one conveying a dsn's purpose, may be a good idea, though with just
2 dsns, one of which is used in only one other page, it is not that
paramount and having your vars named dsn and dsn2 will not cause you
much confusion

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

[Newsgroup_User]

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

[Guest]

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>

[Newsgroup_User]

1) what exactly is the error thrown?
2) which database are you using? (ms access, mysql, etc)

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

b) similarly, not all databases will allow you to explicitly insert a
NULL value into a field...

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

[Guest]

Thanks for your reply. I am using MS Access :)


Syntax error in INSERT INTO statement.

The error occurred in D:\Hosting\admin\add_member.cfm: line 46

44 : <cfelse>
45 : ''
46 : </cfif>
47 : )
48 : </cfquery>

SQL INSERT INTO Users (UserName, Password, FirstName, LastName, UserTypeID, Email) VALUES ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , '' )
DATASOURCE thedatabasename
VENDORERRORCODE 3092
SQLSTATE  

[Newsgroup_User]

and which db are you using?


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

[Guest]

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..

[cf_dev2 · Accepted solution]

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 .... )

[Guest]

Just putting the brackets around the word password in my add_user.cfm corrected it.

INSERT INTO Users (UserName, [Password], FirstName, LastName, UserTypeID, Email)

I want to thank all of you who provided input. Its great to know there are people out there willing to help. Hopefully I will become as knowledgeable as all of you so that I may pass that favor forward :)

Is it common for a page to work on the local testing server only to pop up an error on the remote server? This is my first coldfusion site that I am constructing and I would like to know for future endeavors. If this kind of thing is more or less common I need to build it into my time frame for site completion.

[Newsgroup_User]

there are a lot of possible reasons. different db drivers, differences
in the webserver and cf server setups, etc etc etc. if you are serious
about your development, it may be a good idea to get at least a
semi-dedicated server. with a shared hosting look on the company's
website for your plan's specs (in terms of versions of app and db
servers they use) and make sure your local specs meet those + contact
tech support and get as much other details about the setup from them as
you can, i mean the stuff they do not publish in the hosting plan's
specs on their site, like various cf settings, db driver versions in
use, etc etc

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

[Guest]

Thanks Azadi. That is helpful to know 🙂

[Newsgroup_User]

> 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