UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Asp Page Failed To Display My Db Record    
 
   
smyeong
post Jan 15 2018, 04:08 AM
Post#1



Posts: 404
Joined: 6-November 02
From: Malaysia


Hi, I was writing a asp page to show my db 's record. But I failed to code it using asp.
Kindly help check below asp coding is correct ?

code as below :

hello my name is Yeong im learning ASP now
<hr>

Opening connection to db
To display all the vehicle number
<br>
<%
Cstring = "Provider=SQLOLEDB;Server=TCIMBI01\SQLEXPRESS;Database=datadb;UID=sa;PWD=passw@rd1234"
Query = "SELECT distinct trname,trdate, cardno,(select departmentID_ from CardDB where cardno = tblTransaction.cardno ) as DepartmentID, trtime, "transaction" from tblTransaction where (select departmentID_ from CardDB where cardno = tblTransaction.cardno ) = 17 "

Response.Write Query
Set content = GetRecordSet(Cstring, Query)

%>


<tr bgcolor="#FFFFFF">

<td bgcolor="<%= TMPBGCOLOR %>" class="contentedit"><%= content("TRDATE") %></td>
<td BGCOLOR="<%= TMPBGCOLOR %>" class="contentedit"><%= content("DepartmentID")%></td>
<td BGCOLOR="<%= TMPBGCOLOR %>" class="contentedit"><%= content("CardNo") %></td>
<td BGCOLOR="<%= TMPBGCOLOR %>" class="contentedit"><%= content("Trname") %></td>
<td BGCOLOR="<%= TMPBGCOLOR %>" class="contentedit"><%= content("TrTime") %></td>
<td BGCOLOR="<%= TMPBGCOLOR %>" class="contentedit"><%= content("'Transaction'") %></td>

</tr>

---------------- end of code -----------------------

when I refresh my asp page, it would show as below :
Microsoft VBScript compilation error '800a0401'

Expected end of statement

/attd/yeong.asp, line 9

Query = "SELECT distinct trname,trdate, cardno,(select departmentID_ from CardDB where cardno = tblTransaction.cardno ) as DepartmentID, trtime, "transaction" from tblTransaction where (select departmentID_ from CardDB where cardno = tblTransaction.cardno ) = 17 "
--------------------------------------------------------------------------------------------------------------------------------------------------^

FYI, i did test the SQL query into MS sql2005, and display no problem in query screen

Tq very much

Go to the top of the page
 
cheekybuddha
post Jan 15 2018, 06:54 AM
Post#2


UtterAccess VIP
Posts: 11,469
Joined: 6-December 03
From: Telegraph Hill


Hi Yeong,

>> FYI, i did test the SQL query into MS sql2005, and display no problem in query screen <<

Are you sure?

Your SQL looks wrong:
CODE
SELECT distinct
  trname,
  trdate,
  cardno,
  (
    select
      departmentID_
      from CardDB
      where cardno = tblTransaction.cardno
  ) as DepartmentID,
  trtime,
  "transaction"
from tblTransaction
where (select departmentID_ from CardDB where cardno = tblTransaction.cardno ) = 17

I think you need to alias the fields in your subquery field for DepartmentID

But you would be better off writing the query as:
CODE
SELECT DISTINCT
  t.trname,
  t.trdate,
  t.cardno,
  c.departmentID_ AS DepartmentID
  t.trtime,
  'transaction' AS 'Description'
FROM tblTransaction t
INNER JOIN CardDB c
        ON t.cardno = c.cardno
WHERE c.departmentID_ = 17
;


hth,

d
Go to the top of the page
 
smyeong
post Jan 15 2018, 11:00 PM
Post#3



Posts: 404
Joined: 6-November 02
From: Malaysia


hi, cheekybuddha

--------------revised code ------------------------------

hello my name is Yeong im learning ASP now
<hr>

Opening connection to db
To display all the vehicle number
<br>
<%
Cstring = "Provider=SQLOLEDB;Server=TCIMBI01\SQLEXPRESS;Database=datadb;UID=sa;PWD=passw@rd1234"

Query = ""

Query = Query & "SELECT DISTINCT t.trname, t.trdate, t.cardno, c.departmentID_ AS DepartmentID t.trtime, 'transaction' AS 'Description' FROM tblTransaction t INNER JOIN CardDB c ON t.cardno = c.cardno WHERE c.departmentID_ = 17"

Response.Write Query

Set content = GetRecordSet(Cstring, Query)

%>


<%= content("TRDATE") %><%= content("Description") %><%= content("CardNo") %><%= content("TName") %><%= content("TrTime") %>

------------------ end of code ---------------

I have revised the code as advised. Now the page would show as below :
hello my name is Yeong im learning ASP now
--------------------------------------------------------------------------------
Opening connection to db To display all the vehicle number
SELECT DISTINCT t.trname, t.trdate, t.cardno, c.departmentID_ AS DepartmentID t.trtime, 'transaction' AS 'Description' FROM tblTransaction t INNER JOIN CardDB c ON t.cardno = c.cardno WHERE c.departmentID_ = 17
Microsoft VBScript runtime error '800a000d'

Type mismatch: 'GetRecordSet'

/attd/yeong.asp, line 16



---------------
kindly guide me on how to code asp for displaying the mentioned query on my asp page..
TQ

Go to the top of the page
 
cheekybuddha
post Jan 16 2018, 05:44 AM
Post#4


UtterAccess VIP
Posts: 11,469
Joined: 6-December 03
From: Telegraph Hill


Hi Yeong,

I have never coded in ASP, but my guess is that GetRecordSet() is a custom function.

Where have you based your code from? Check it again for a declaration of a function called GetRecordSet().

You will need to include it in your code somewhere.

I suspect it will look something like:
CODE
Function GetRecordSet(cnString, strSQL)

  Set cn=Server.Createobject("ADODB.Connection")
  cn.ConnsctionString = cnString
  cn.Open
  Set GetRecordSet = cn.Execute(strSQL)
  cn.Close

End Function


hth,

d
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2018, 08:21 AM
Post#5


UA Admin
Posts: 35,668
Joined: 20-June 02
From: Newcastle, WA


Is this supposed to be VB.Net? Not C#?

I'm not sure as I write C#, but I "believe" code lines should be terminated with the semicolon ;

That's the case in C#, at any rate.
Go to the top of the page
 
cheekybuddha
post Jan 16 2018, 08:24 AM
Post#6


UtterAccess VIP
Posts: 11,469
Joined: 6-December 03
From: Telegraph Hill


Looks like classic ASP to me - like vbs in a web page.

I didn't know that anyone bothered with it any more!!!
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2018, 08:47 AM
Post#7


UA Admin
Posts: 35,668
Joined: 20-June 02
From: Newcastle, WA


Oh, yeah. Prior even to VB.Net?
Go to the top of the page
 
cheekybuddha
post Jan 16 2018, 08:51 AM
Post#8


UtterAccess VIP
Posts: 11,469
Joined: 6-December 03
From: Telegraph Hill


ohyeah.gif

Classic ASP - wikipedia history
VB.net - wikipedia history

It's sort of what Access DAP's were built on, and works in a similar way to php
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2018, 10:59 AM
Post#9


UA Admin
Posts: 35,668
Joined: 20-June 02
From: Newcastle, WA


I have a book on my bookshelf; it's kind of dusty by now.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th September 2019 - 05:31 AM