locked
Asp.net noob database question RRS feed

  • Question

  • User-935210368 posted

    I am trying to figure out the easiest way to get some data from my db.  I know c# and .net well.  with asp I would do something like...

     <%
     DIM adoConn, rsitems, szHTML
     
     Set adoConn = server.CreateObject("ADODB.Connection")
     adoConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.MapPath("../mydb.mdb")

     Set rsitems = adoConn.Execute("SELECT * From Brands")

     DO WHILE NOT rsitems.EOF

      szHTML = szHTML & "<li><a href=""#"">" & rsitems("Name") & "</a></li>"

      rsitems.movenext
     Loop

     Response.Write(szHTML)

     adoConn.Close
    %>

     however i am having trouble with asp .net...I have a site hosted with network solutions and a sql server db.  can anyone tell me the easiest way to just connect and pull some items out of a db and write them dynamically in the html?

     

    Wednesday, October 29, 2008 5:06 PM

Answers

  • User1396828672 posted

    A pure conversion would be something like this:

     Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=c:\AccessDB\MyDB.mdb;")
    Dim cmd As New OleDbCommand("select * from Brands", cn)
    
    Dim reader As New OleDbReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    Dim szHTML As String = ""
    
    While reader.Read()
    szHTML &= "<li><a href='#'>" & reader("name") & "</a></li>"
    End While        
    reader.Close()
    
    Response.Write(szHTML)
    cn.Close()
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 29, 2008 10:00 PM
  • User-821857111 posted

    Unfortunately, the previous suggestion is a mixture of C# and VB syntax (which will blow up), and makes use of Access. Nor does it open the Connection. An accurate conversion to C# and SQL Server would be:

    using(SqlConnection cn = new SqlConnection(<Your connection string>))
    {
        SqlCommand cmd = new SqlCommand("select [name] from Brands", cn);
        cn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        StringBuilder sb = new StringBuilder();
        while (rdr.Read())
        {
            sb.Append("<li><a href=\"#\">" + rdr["name"].ToString() + "</a></li>";
        }   
        rdr.Close();
        Literal1.Text = sb.ToString();
    }

    Drag an <asp:Literal> control wherever you want to the list to appear.  The last line sets its Text property to the value of the string you build up.  In ASP.NET Response.Write doesn't work in the same way as classic ASP.  It injects the output right at the top of the page, before the <html> tags.  The Literal control acts as a placeholder for text output.  You can check www.connectionstrings.com for the correct connections string to use for your database, although you will probably need to check the Support pages or desk at your web host for the actual Sql Server name.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 30, 2008 3:28 AM

All replies

  • User1396828672 posted

    A pure conversion would be something like this:

     Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=c:\AccessDB\MyDB.mdb;")
    Dim cmd As New OleDbCommand("select * from Brands", cn)
    
    Dim reader As New OleDbReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    Dim szHTML As String = ""
    
    While reader.Read()
    szHTML &= "<li><a href='#'>" & reader("name") & "</a></li>"
    End While        
    reader.Close()
    
    Response.Write(szHTML)
    cn.Close()
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 29, 2008 10:00 PM
  • User-821857111 posted

    Unfortunately, the previous suggestion is a mixture of C# and VB syntax (which will blow up), and makes use of Access. Nor does it open the Connection. An accurate conversion to C# and SQL Server would be:

    using(SqlConnection cn = new SqlConnection(<Your connection string>))
    {
        SqlCommand cmd = new SqlCommand("select [name] from Brands", cn);
        cn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        StringBuilder sb = new StringBuilder();
        while (rdr.Read())
        {
            sb.Append("<li><a href=\"#\">" + rdr["name"].ToString() + "</a></li>";
        }   
        rdr.Close();
        Literal1.Text = sb.ToString();
    }

    Drag an <asp:Literal> control wherever you want to the list to appear.  The last line sets its Text property to the value of the string you build up.  In ASP.NET Response.Write doesn't work in the same way as classic ASP.  It injects the output right at the top of the page, before the <html> tags.  The Literal control acts as a placeholder for text output.  You can check www.connectionstrings.com for the correct connections string to use for your database, although you will probably need to check the Support pages or desk at your web host for the actual Sql Server name.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 30, 2008 3:28 AM