Problems with SQL server & asp

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)
 
K

Kevin Spencer

Considering you're posting an ASP question to an ASP.Net newsgroup, I'm not
surprised you're having problems! Try posting on
microsoft.public.inetserver.asp.general instead.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

Sunny said:
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)
 
G

Guest

This is a .NET newsgroup, so you might want to consider posting elsewhere.

Just eyeballing the code, I don't see what the problem is. That said, I
would not recommend using text as the datatype in most of the places where
you're using it.

Sunny said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,989
Messages
2,570,207
Members
46,783
Latest member
RickeyDort

Latest Threads

Top