G
Guest
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 = "(e-mail address removed)"
' 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 ,
(e-mail address removed)
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 = "(e-mail address removed)"
' 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 ,
(e-mail address removed)