Problem displaying database info on pageRSS

17 replies

Last post Sep 24, 2005 05:32 PM by jforward5

  • Problem displaying database info on page

    Aug 13, 2005 02:06 PM|jforward5|LINK

    I am having problems getting the information in my sql database to display on my webpage.  I can't use the SQLDataAdapter wizard to link up to my data because my web host doesn't allow remote access in that way.  So I created a recordset connection object and used it to connect to the database.  This works fine in manipulating the information and validating it, but i can't get it to display the information I pick with the SELECT command.  Everytime I try to call the info from the recordset to the page it shows the name and type of object i have created instead of the information I selected.  Here is an example of the code I am using to make the connection:

    Dim oConn, oRs
    Dim qry, connectstr
    Dim db_name, db_username, db_userpassword
    Dim db_server
    Dim fieldname, tablename

    db_server = "myserver_Location/name_here"
    db_name = "my_dbname"
    db_username = "my_dbusername"
    db_userpassword = "my_dbpassword"
    fieldname = "my_field"
    tablename = "my_table"

    connectstr = "Driver={SQL Server};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword

    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open connectstr

    qry = "SELECT * FROM " & tablename

    Set oRS = oConn.Execute(qry)

    if not oRS.EOF then
    while not oRS.EOF
    response.write ucase(fieldname) & ": " & oRs.Fields(fieldname) & "
    "
    oRS.movenext
    wend
    oRS.close
    end if

    Set oRs = nothing
    Set oConn = nothing


    The response line is supposed to show me the information i selected, however it doesn't.  I am using vb to program this.  Can some one please help me with this?[:)]

    Thanks
    http://www.crackerjacksoftware.com
    CrackerJack Software, we solve computer problems!
  • Re: Problem displaying database info on page

    Aug 14, 2005 11:03 PM|jeff@zina.com|LINK

    jforward5

    Everytime I try to call the info from the recordset to the page it shows the name and type of object i have created instead of the information I selected.
    Can you post an example of this output?  What database?  Does the query produce anything at all?  Have you tried in Query Analyzer (Or Access, or whatever)?

    Jeff
    Have you Binged a solution before posting?
  • Re: Problem displaying database info on page

    Aug 14, 2005 11:51 PM|jforward5|LINK

    This is an example of the output that posts to the top of the page when it runs

    ComObjectSystem.__ComObjectSystem

    Thanx
    http://www.crackerjacksoftware.com
    CrackerJack Software, we solve computer problems!
  • Re: Problem displaying database info on page

    Aug 15, 2005 08:11 AM|jeff@zina.com|LINK

    That doesn't make sense given the code you post.  Is this the correct code?  At the least, your code should be displaying:

    MY_FIELD:

    Give uis the database, and a sample of your table and data.

    Jeff
    Have you Binged a solution before posting?
  • Re: Problem displaying database info on page

    Aug 15, 2005 02:29 PM|jforward5|LINK

    Im creating a website for a basketball league.  I am using that code to call all the information in the SQL Server on a table i have named after a basketball team, say Atlanta Hawks.  In the table I have stored all the player names, the positions they play and their jersey numbers.  So the output Im looking for should be something like this:

      <script language=JavaScript type=text/javascript> </script> 0 T. Delk SG
      <script language=JavaScript type=text/javascript> </script> 1 J. Childress SG
      <script language=JavaScript type=text/javascript> </script> 2 C. Akyol SG
      <script language=JavaScript type=text/javascript> </script> 3 A. Harrington PF
    <script language=JavaScript type=text/javascript> </script>   5 J. Smith SF
      <script language=JavaScript type=text/javascript> </script> 7 T. Gugliotta PF
      <script language=JavaScript type=text/javascript> </script> 10 T. Lue PG
      <script language=JavaScript type=text/javascript> </script> 13 B. Diaw SG
      <script language=JavaScript type=text/javascript> </script> 15 D. Smith SG
      <script language=JavaScript type=text/javascript> </script> 20 S. Stoudamire PG
      <script language=JavaScript type=text/javascript> </script> 24 M. Williams PF
      <script language=JavaScript type=text/javascript> </script> 36 R. Ivey PG
      <script language=JavaScript type=text/javascript> </script> 40 J. Collier C
      <script language=JavaScript type=text/javascript> </script> 42 K. Willis C
      <script language=JavaScript type=text/javascript> </script> 44 M. Stewart C

    These are the results im looking for.  How can I acheive them? 

    http://www.crackerjacksoftware.com
    CrackerJack Software, we solve computer problems!
  • Re: Problem displaying database info on page

    Aug 15, 2005 02:59 PM|tomkmvp|LINK

    Here's one way:

    qry = "SELECT jersey_number, player_name, position FROM " & tablename

    Set oRS = oConn.Execute(qry)
    response.write "<table>"
    Do While not oRS.EOF
    response.write "<tr><td>" & oRs(0) & "</td>"
    response.write "<td>" & oRs(1) & "</td>"
    response.write "<td>" & oRs(2) & "</td></tr>"
    oRS.movenext
    Loop
    oRS.close

  • Re: Problem displaying database info on page

    Aug 15, 2005 03:19 PM|jforward5|LINK

    I get an invalid cast error when I try that.
    InvalidCastException: Cast from type 'Field' to type 'String' is not valid
    http://www.crackerjacksoftware.com
    CrackerJack Software, we solve computer problems!
  • Re: Problem displaying database info on page

    Aug 15, 2005 03:40 PM|jforward5|LINK

    Ok I fixed the cast error.  Here is what my results look like on the page.

    <form id=Form1 name=Form1 action=atlanta.aspx method=post>   <input id=TextBox1 style="Z-INDEX: 101; LEFT: 608px; WIDTH: 112px; POSITION: absolute; TOP: 8px" readOnly name=TextBox1> <div id=Panel1 style="FONT-SIZE: 15pt; Z-INDEX: 103; LEFT: 8px; WIDTH: 320px; COLOR: white; FONT-FAMILY: Stencil BT; POSITION: absolute; TOP: 176px; HEIGHT: 400px" align=center>

     

    Trades

    Roster Changes

    </div></form>
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject
    System.__ComObject System.__ComObject System.__ComObject

    http://www.crackerjacksoftware.com
    CrackerJack Software, we solve computer problems!
  • Re: Problem displaying database info on page

    Aug 15, 2005 05:54 PM|jforward5|LINK

    would creating an xml file with my table info work?  And changing the xml everytime the table changes or is there another way to get my results to the page?
    http://www.crackerjacksoftware.com
    CrackerJack Software, we solve computer problems!
  • Re: Problem displaying database info on page

    Aug 16, 2005 08:50 AM|tomkmvp|LINK

    Perhaps, but what we've been trying should work.  Very strange results.  Try this for your connection string instead:
    http://www.aspfaq.com/show.asp?id=2126

    Just to confirm, this is classic ASP we're talking about right?  Can you show all of your code?
  • Re: Problem displaying database info on page

    Aug 16, 2005 12:11 PM|jeff@zina.com|LINK

    The code should work as written.  Can you check and see what data is actually in your database?

    Jeff
    Have you Binged a solution before posting?
  • Re: Problem displaying database info on page

    Aug 22, 2005 04:43 PM|jforward5|LINK

    no this is the asp.net included in visual basic 7.0 i think it is asp.net 1.1.  but i figured that the code i used would work but i guess not.  here is my code:

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim oConn, oRs

    Dim qry, qry2, connectstr

    Dim db_name, db_username, db_userpassword

    Dim db_server

    Dim fieldname, tablename

    db_server = "whsql-v02.prod.mesa1.secureserver.net"

    db_name = "db_14122"

    db_username = "jforward5"

    db_userpassword = "jan1077"

    tablename = "users"

    connectstr = "Driver={SQL Server};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword

    oConn = Server.CreateObject("ADODB.Connection")

    oConn.Open(connectstr)

    qry = "select * from atlanta"

    oRs.oconn.execute(qry)

    If Not oRs.EOF Then

    While Not oRs.EOF

    Response.Write(oRs(0))

    Response.Write(oRs(1))

    Response.Write(oRs(2))

    oRs.movenext()

    End While

    oRs.close()

    End If

    oRs = Nothing

    oConn = Nothing

     

     

    End Sub

    what do i need to do to make it show the information in my db.  I know there is information in my db because i put it there manually.

    http://www.crackerjacksoftware.com
    CrackerJack Software, we solve computer problems!
  • Re: Problem displaying database info on page

    Aug 23, 2005 12:01 AM|tomkmvp|LINK

    Very strange.  Since this forum is for issues with classic ASP you might get better help in an ASP.NET forum ...
  • Re: Problem displaying database info on page

    Sep 19, 2005 11:53 AM|hemalrathod|LINK

    oRs.Fields(fieldname).Value should do the trick. Try it out, I had the same problem and I just rectified it.
  • Re: Problem displaying database info on page

    Sep 19, 2005 02:19 PM|jforward5|LINK

    i got tired of trying to make it work with my host so i just built my own server and that fixed all the problems i was having.  thank you for the good information though.
    check out what i have done so far at www.officialnbaseasoncenter.com!!
    http://www.crackerjacksoftware.com
    CrackerJack Software, we solve computer problems!
  • Re: Problem displaying database info on page

    Sep 22, 2005 03:48 PM|michaelhoullis|LINK

    jforward5,

    I was having exactly the same problem (System.__ComObject) and searched for
    over three days straight, and finally found a snippet in a note on Microsoft's site:

    <%= rs.Fields.Item("Name").Value %>

    You have to use that exact format instead of

    <%= rs.Fields("Name") %>

    They called the first one "verbose" and said it's form was required when accessing
    data from within an asp.net page to prevent the return of the object type instead of
    the text or character or number you are trying to retrieve.  I changed it in mine and
    it worked instantly.

    MH
  • Re: Problem displaying database info on page

    Sep 22, 2005 04:31 PM|jeff@zina.com|LINK

    michaelhoullis

    I was having exactly the same problem (System.__ComObject) and searched for
    over three days straight, and finally found a snippet in a note on Microsoft's site:

    <%= rs.Fields.Item("Name").Value %>

    You have to use that exact format instead of

    <%= rs.Fields("Name") %>

    They called the first one "verbose" and said it's form was required when accessing
    data from within an asp.net page to prevent the return of the object type instead of
    the text or character or number you are trying to retrieve.  I changed it in mine and
    it worked instantly.
    This is because of the way recordsets work.  You should always be precise in your code, specify exactly what you want.  Otherwise, you may get unpredictable results. 

    Jeff
    Have you Binged a solution before posting?
  • Re: Problem displaying database info on page

    Sep 24, 2005 05:32 PM|jforward5|LINK

    thanx for all the info on this i will use it in the future when i build a site for someone on a remote web server.  For now tho i have built a server at my house and made my site on it.  I have had far less problems this way and i don't have to pay for it either!  if you would like to see what i have done so far check it out at http://www.officialnbaseasoncenter.com.

    thanx again for all your help guys [:)]
    http://www.crackerjacksoftware.com
    CrackerJack Software, we solve computer problems!