Reputation:
I'm using ColdFusion and trying to get data from a MS SQL database. The problem is there should be only one line with details. At the same time if I'm trying to login as user or admin it works fine. The syntax is basically the same as for admin login. Maybe I've missed something? Here's the code:
<html>
<head>
<cfquery name='Query' datasource='MyDSN'>
<cfset "id" = #Form.user#/>
SELECT UserID, FName, SName, Phone, Address FROM users WHERE UserID = <cfqueryparam value="#FORM.user#" cfsqltype="cf_sql_varchar">
</cfquery>
</head>
<body>
<cfif #Query.recordCount# EQ 1>
<TABLE height="30px" CELLPADDING="5" CELLSPACING="3">
<TR BGCOLOR="888888" >
<TH>User ID </TH>
<TH>Name </TH>
<TH>Surname </TH>
<TH>Phone </TH>
<TH>Address </TH>
</TR>
<TD><cfoutput> #Query.UserID# </cfoutput></TD>
<TD><cfoutput> # Query.FName# </cfoutput></TD>
<TD><cfoutput> # Query.SName# </cfoutput></TD>
<TD><cfoutput> # Query.Phone# </cfoutput></TD>
<TD><cfoutput> # Query.Address# </cfoutput></TD></TR>
<cfelse>
<cfoutput> No such user...</cfoutput>
</cfif>
</body>
</html>
Upvotes: 0
Views: 518
Reputation: 3335
You didn't specify your problem. I assume the query doesn't return the proper results. Is your list of columnnames exactly what the asterisk *
gives back? (As a sidenote, one should never use asterisk in a select
.)
Also make sure that the data type of:
<cfqueryparam value= "#Form.user#">
matches the data type of the UserID
column.
Upvotes: 0
Reputation: 14333
Your original query and your error message are not from the same code. Your error message shows you're listing your column names in the FROM statement. This is the correct syntax.
<cfquery name='Query' datasource='MyDSN'>
SELECT UserID, FName, SName, Phone, Address
FROM users
WHERE UserID = <cfqueryparam value="#trim(FORM.user)#" cfsqltype="cf_sql_varchar">
</cfquery
From Leigh's chat - make sure you're not adding in leading/trailing zeroes when using cfqueryparam
.
Also, in your cfif statement, #
are not needed.
<cfif Query.recordCount EQ 1>
To fix the issue with your output not working
<cfif Query.recordCount EQ 1>
<cfoutput>
<TABLE height="30px" CELLPADDING="5" CELLSPACING="3">
<TR BGCOLOR="888888" >
<TH>User ID</TH>
<TH>Name</TH>
<TH>Surname</TH>
<TH>Phone</TH>
<TH>Address</TH>
</TR>
<TR>
<TD>#Query.UserID#</TD>
<TD>#Query.FName#</TD>
<TD>#Query.SName#</TD>
<TD>#Query.Phone#</TD>
<TD>#Query.Address#</TD>
</TR>
</cfoutput>
<cfelse>
<cfoutput> #Form.user#</cfoutput>
</cfif>
Upvotes: 3