5 {{ upvoteCount | shortNum }}
5 {{ upvoteCount | shortNum }}

Display SQL DB data in VB scripted page... HELP!

by localbandpost
3 replies
I have my connection set up to the db I created a class to do so:


Imports Microsoft.VisualBasic
Imports System.Data
Imports MySql.Data.MySqlClient

Public Class DBAccess
Dim connectionstring As String = "Server=localhost;Database=geustdb;Uid=*********** ;Pwd=***********"

Public Function SQLCommandNoResults(ByVal command As MySql.Data.MySqlClient.MySqlCommand) As Boolean
Dim connection As New MySql.Data.MySqlClient.MySqlConnection
connection.ConnectionString = connectionstring
connection.Open()
command.Connection = connection

command.ExecuteNonQuery()
Return True
End Function

End Class
----------------------------------------------------------------------
I have my data going to the table:

Protected Sub infosubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles infosubmit.Click
'Dim connection As New MySql.Data.MySqlClient.MySqlConnection
'connection.ConnectionString = "Server=localhost;Database=geustdb;Uid=*********** ;Pwd=***********"
'connection.Open()
Dim dba As New DBAccess
Dim command As New MySql.Data.MySqlClient.MySqlCommand

command.CommandText = "INSERT INTO guest_info (firstname, lastname, email, msgdate, poll, msgbody) VALUES (@firstname, @lastname, @email, @msgdate, @poll, @msgbody)"
command.Parameters.AddWithValue("firstname", firstnamebox.Text)
command.Parameters.AddWithValue("lastname", lastnamebox.Text)
command.Parameters.AddWithValue("email", emailbox.Text)
command.Parameters.AddWithValue("msgdate", DateTime.Now())
command.Parameters.AddWithValue("poll", Poll.SelectedValue)
command.Parameters.AddWithValue("msgbody", commentbox.Text)

Dim A As Boolean = dba.SQLCommandNoResults(command)
Response.Redirect("Default2.aspx")



------------------------------------------------------------------------
I BELIEVE I am pulling the data back out into the MySqlDataAdapter:



Public Function SQLCommandWithResults(ByVal command As MySql.Data.MySqlClient.MySqlCommand) As DataTable
Dim connection As New MySql.Data.MySqlClient.MySqlConnection
Dim da As MySqlDataAdapter
Dim dt As New DataTable

connection.ConnectionString = connectionstring
connection.Open()
command.Connection = connection

da = New MySqlDataAdapter(command)
da.Fill(dt)

Return dt

End Function

-----------------------------------------------------------------------
But now I dont know how to get it to display in Default2.aspx in the
**
<aspanel ID="pnlGuest" runat="server">

</aspanel>
**
tag... Or anywhere else for that matter... I have looked everywhere and can find nothing to help me get the data onto the page. It is a guestbook which contains rows in the database that I do not want displayed on the webpage. But I just dont know what Im doing. This is my first ever project on anything other than HTML so I am learning the hard way. ANY HELP WOULD BE GREATLY APPRECIATED!!!
#data #display #page #scripted #sql
  • Profile picture of the author BrianLeanza
    Hey localbandpost

    there is several ways to get your data onto the page.

    One way is to iterate through the datatable you have and add the items to a list (btw, you probably want to double check syntax, method- and attribute names, since I am doing this out of my head here and in c#):

    Assuming you use a ListBox that you have in the .aspx page (<asp:ListBox runat="server" id="listboxData" ....> :

    Code:
    function populate(DataTable dt)
{
   foreach(DataRow row in dt.Rows)
   {
     ListItem item = new ListItem();
     item.Text = row["columname"].ToString();
     listboxData.Items.Add(item);
   }
}
    A more straightforward way is to use a Datagrid
    Code:
    <asp:DataGrid runat="server" id="datagridSomething" />
    and in code behind:
    Code:
    datagridSomething.DataSource = dt; // dt being the datatable
datagridSomething.DataBind();
    Even better: use a DataSource in the .aspx page, like so

    Code:
    <asp:DataGrid runat="server" id="myDataGrid" DataSourceId="myDataSource" />
<asp:MySqlDataSource runat="server" id="myDataSource" ConnectionString="....." SelectCommand="Select * FROM TheTable WHERE ...." />

    Hopefully this was of help. If not, PM me or head over to www.asp.net ...
    Cheers!
    {{ DiscussionBoard.errors[1637538].message }}
  • Profile picture of the author Mr. Enthusiastic
    Originally Posted by localbandpost View Post

    This is my first ever project on anything other than HTML so I am learning the hard way.
    My, you're ambitious! Did you learn to swim by jumping into the shark-infested Bering Sea?

    I recommend you look for a tutorial about the concept of data binding, using data bound controls. Some of the ASP.Net controls can automatically show information from a database. I think this would be a far easier way to start than having to write all the code yourself to walk through the data and put it onto the page. This might help:

    ASP.NET QuickStart Tutorials

    I also suggest that you look for some existing sample sites you can download and browse through the code. It might be easier to modify something like DotNetNuke (you can always re-download it if you break it) then to start from scratch.

    I admire your desire to learn!

    Chris
    {{ DiscussionBoard.errors[1638499].message }}
  • Profile picture of the author localbandpost
    I did what Brian said and it worked!!! And I also took your advice mr. enthusiastic, You guys were a great help!!! Have some Kudos!!!!!!!!!!!!!!!!!!
    {{ DiscussionBoard.errors[1714188].message }}

Trending Topics