Problems with asp & SQL server
From: Sunny (Sunny_at_discussions.microsoft.com)
Date: 08/16/04
- Next message: Justin: "caching table data"
- Previous message: Beren: "Re: Downloading a file from above the /www directory"
- Next in thread: Aaron [SQL Server MVP]: "Re: Problems with asp & SQL server"
- Reply: Aaron [SQL Server MVP]: "Re: Problems with asp & SQL server"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 16 Aug 2004 08:23:02 -0700
I am using an asp page to access data stored by Microsoft SQL in a SQL server
database. I cannot get all values to return, some display as blanks.
I am using IIS v5, Microsoft SQL Server 2000, running on Windows Advanced
Server 2000 SP4.
Here is my asp code, SQL table create statements, CSV example data and
output. As can be seen by the output, the pc_model and manufacturer fields
display blank although these fields hold text data in my test database. Has
anyone else encountered similar problems/ know of any issues/ got any info at
all to share on this one?
=====================================================
ASP CODE:
<%@ Language=VBScript %>
<html>
<head>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
</head>
<body>
</body>
</html>
<%
'Script reads values from a Microsoft SQL database & displays them
Dim conn
Dim dbPcModel 'PC Model
Dim dbPcManufacturer 'PC Make
Dim dbPcSpeed 'Processor speed
Dim dbSN 'PC Serial Number
Dim dbRam 'Memory (MHz)
Dim dbHdd 'Disk Capacity
Dim dbCpu 'Processor type
Dim dbOs 'Operating System
Dim dbType 'PC Type (desktop/laptop)
Dim dbCost 'Cost Centre
QueryDatabase()
'
' Method: QueryDatabase
'
' Author: Iain Worlock
' Description: This subroutine retrieves the users asset record from the
database
' Date of last update: 16/08/2004
'
Sub QueryDatabase
Dim dbrs
Dim results
Dim ownerEmail
ownerEmail = "iain.worlock@hp.com"
' Open database query
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=asset_tracker"
Set dbrs = conn.Execute("select * from test2 where email like '" +
ownerEmail + "'")
Set results = dbrs.Fields
dbSN = results.Item("serial_number").Value
dbPcModel = results.Item("pc_model").Value
dbPcManufacturer = results.Item("manufacturer").Value
dbPcSpeed = results.Item("cpu_mhz").Value
dbRam = results.Item("ram_mb").Value
dbHdd = results.Item("hdd_gb").Value
dbCpu = results.Item("processor").Value
dbOs = results.Item("os").Value
dbType = results.Item("type").Value
dbCost = results.Item("cost_centre").Value
response.Write("serial is: " + dbSN +"<BR>")
response.Write("model is: " + dbPcModel +"<BR>")
response.Write("make is: " + dbPcManufacturer +"<BR>")
response.Write("speed is: " + CStr(dbPcSpeed) +"<BR>")
response.Write("mem is: " + CStr(dbRam) +"<BR>")
response.Write("storage is: " + CStr(dbHdd) +"<BR>")
response.Write("processor speed is: " + CStr(dbCpu) +"<BR>")
response.Write("OS is: " + dbOs +"<BR>")
response.Write("processor type is: " + dbType +"<BR>")
response.Write("cost centre is: " + dbCost +"<BR>")
End Sub
%>
MICROSOFT SQL TABLE:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[test2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test2]
GO
CREATE TABLE [dbo].[test2] (
[pkindex] [int] NOT NULL ,
[email] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pc_model] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[manufacturer] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[serial_number] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cpu_mhz] [int] NULL ,
[ram_mb] [int] NULL ,
[hdd_gb] [int] NULL ,
[processor] [int] NULL ,
[os] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cost_centre] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
DATA (CSV):
1, iain.worlock@hp.com, model=evo, make=compaq, serial=2345, 1800, 384, 40,
9999, os=win, type=p3, cost=null
OUTPUT:
<html>
<head>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
</head>
<body>
serial is: serial=2345<BR>
model is: <BR>
make is: <BR>
speed is: 1800<BR>
mem is: 384<BR>
storage is: 40<BR>
processor speed is: 9999<BR>
OS is: os=win<BR>
processor type is: type=p3<BR>
cost centre is: cost=null<BR>
</body>
</html>
=====================================================
- Next message: Justin: "caching table data"
- Previous message: Beren: "Re: Downloading a file from above the /www directory"
- Next in thread: Aaron [SQL Server MVP]: "Re: Problems with asp & SQL server"
- Reply: Aaron [SQL Server MVP]: "Re: Problems with asp & SQL server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
