CODEWITHSUNDEEP
sqldatabasecoldfusion
user1926837
user1926837

Reputation:

Cannot get data from MS SQL database using ColdFusion

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

Answers (2)

Der U
Der U

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

Matt Busche
Matt Busche

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

Related Questions