Prev: Bluefish mp3 ringtones
Next: CDOSYS executes but takes TOO TOO long?
From: Ang on 20 Feb 2008 04:41


Dear all,
I'm a newbie of ASP, and I found that my code cannot output the data
when the DATA type set to TEXT or CHAR, it can only show int & varchar
in MSSQL, what can I do to show TEXT & CHAR data on my page? Many
thanks.

My code is here.

<%
' Use vid to fetch information from server for the video
Sub showMovie(VID)
Dim width, height ' width and height of video
Dim ConnString, SQL, Connection, Recordset

width = "300"
height = "200"
ConnString = "DRIVER={SQL
Server};SERVER=serverName;UID=login;PWD=password;DATABASE=video"
SQL = "SELECT * FROM table_video WHERE vid = " & VID

' Create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

Connection.Open ConnString ' Open the connection to the database
Recordset.Open SQL,Connection ' Open the recordset object executing the
SQL statement and return records

' First of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'show record information
' "description" is TEXT data type, "grade" is CHAR data type
%>
<h1><%=Recordset("title")%></h1>
<p>Category: <%=Recordset("category")%></p>
<p>Time: <%=Recordset("dateadd")%></p>
<p>User: <%=Recordset("uid")%>, Grade: <%=Recordset("grade")%></p>
<embed src="<%=Recordset("filename")%>" width=<%=width%>
height=<%=height%> />
<p>Description:<br /><%=Recordset("description")%></p>
<%
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
End Sub

' vid (video id) numeric checking and validation (if empty)
If Len(Request.Querystring("vid")) > 0 AND
IsNumeric(Request.Querystring("vid")) Then
call showMovie(Request.Querystring("vid"))
Else
Response.write ("Invalid Video ID.") ' error message
End If
%>


*** Sent via Developersdex http://www.developersdex.com ***
From: Bob Barrows [MVP] on 20 Feb 2008 06:44
Ang wrote:
> Dear all,
> I'm a newbie of ASP, and I found that my code cannot output the data
> when the DATA type set to TEXT or CHAR, it can only show int & varchar
> in MSSQL, what can I do to show TEXT & CHAR data on my page? Many
> thanks.

My first thought was that you were running into a very old bug caused by
using the obsolete ODBC driver instead of the native SQL Server OLE DB
provider. Sure enough ...
>
> ConnString = "DRIVER={SQL Server}
See http://www.aspfaq.com/show.asp?id=2126
> SQL = "SELECT * FROM table_video WHERE vid = " & VID

See these two articles:

http://www.aspfaq.com/show.asp?id=2096
http://databases.aspfaq.com/database/how-do-i-deal-with-memo-text-hyperlink-and-currency-columns.html


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


From: McKirahan on 20 Feb 2008 07:53
"Ang" <nospam(a)gmail.com> wrote in message
news:uS4THT6cIHA.4844(a)TK2MSFTNGP04.phx.gbl...
>
>
> Dear all,
> I'm a newbie of ASP, and I found that my code cannot output the data
> when the DATA type set to TEXT or CHAR, it can only show int & varchar
> in MSSQL, what can I do to show TEXT & CHAR data on my page? Many
> thanks.

Try adapting the following script for your database. Watch for word-wrap.

<% Option Explicit
'****
'* Dump all field names and values for a single databse table row.
'****
'*
Const cMDB = "Northwind.mdb"
Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Const cSQL = "SELECT * FROM Customers WHERE CustomerID = 'FRANK'"
'*
Dim intCOL
Dim strNAM
Dim strROW
Dim strVAL
'*
Dim objRST
Set objRST = CreateObject("ADODB.Recordset")
objRST.Open cSQL, cDSN & cMDB
'*
On Error Resume Next
If Not objRST.EOF Then
For intCOL = 0 To 99
strNAM = objRST(intCOL).Name
If Err <> 0 Then Exit For
strVAL = objRST(intCOL).Value
If isNull(strVAL) Then strVAL = ""
strROW = strROW & "<li>" & strNAM & " : " & strVAL
Next
End If
'*
Set objRST = Nothing
Response.Write strSQL & "<hr>" & strROW
%>

The above uses an MS-Access database.
For MS-SQL the declaration and sage of cMDB would be removed.

Perhaps the value for your constants is; (watch for word-wrap):

Const cDSN = "DRIVER={SQL
Server};SERVER=serverName;UID=login;PWD=password;DATABASE=video"

Const cSQL = "SELECT * FROM table_video WHERE vid = 1"

Your DSN may be old as I use something like this for MS-SQL:

Const cDSN = "DSN=serverName;UID=login;PWD=password;"


 | 
Pages: 1
Prev: Bluefish mp3 ringtones
Next: CDOSYS executes but takes TOO TOO long?