Solved

Display data from SQL Database in to  Data Grid

Posted on 2004-08-14
7
211 Views
Last Modified: 2010-04-23
Hai,
 i am new for vb.net. i want Display one table data into  Data grid.how? i wrote this code.
But i am getting an Error.

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim CON As New ADODB.Connection()
    Dim RS As New ADODB.Recordset()
   
   
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load)

        CON = New ADODB.Connection()   'SQL CONNECTION
        CON.Open("DSN=ABS;USERID=;PWD=;")

        RS = New ADODB.Recordset()
        RS.Open("SELECT * FROM DEPT", CON, 1, 2)

        DataGrid1.DataSource = RS

    End Sub
End Class

0
Comment
Question by:RamanaChoudary
  • 3
7 Comments
 
LVL 17

Accepted Solution

by:
AerosSaga earned 125 total points
ID: 11802637
Private Sub LoadProductData()
        Dim cnn As New ByteFX.Data.MySqlClient.MySqlConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New ByteFX.Data.MySqlClient.MySqlCommand
        Dim da As New ByteFX.Data.MySqlClient.MySqlDataAdapter(cmd)
        Dim ds As New DataSet
        Dim Query As String
        Query = "SELECT * FROM Products ORDER BY ItemName ASC"
        cmd.CommandText = Query
        cmd.Connection = cnn
        cnn.Open()
        da.SelectCommand = cmd
        da.Fill(ds, "Products")
        dg.DataSource = ds.Tables("Products").DefaultView
        dg.DataKeyField = "ProductID"
        dg.DataBind()
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
    End Sub

Regards,

Aeros
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11802648
I define my connection string in my web.config file liike so:

<appSettings>
<add key="SiteDB" value="Persist Security Info=False;database=Mydb;server=63.174.***.**;user id=root;pwd=****"></add>
      </appSettings>
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11802653
or if you prefer a datareader example:

 Private Sub LoadProductData(ByVal ProductID As Integer)
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim dr As OleDb.OleDbDataReader
        Dim decTempPrice As Decimal
        cmd.CommandType = CommandType.Text
        cmd.CommandText = _
"SELECT ProductID, Products.Name, Products.Description, Products.Tier1ID, Products.Price," & _
    "Products.Tier2ID, Products.ImageSrc, Products.Weight, Width, Depth, Height, Tier3ID FROM Tier2 " & _
"RIGHT JOIN (Tier1 INNER JOIN Products ON Products.Tier1ID = Tier1.Tier1ID )" & _
"ON Tier2.Tier2ID = Products.Tier2ID " & _
"WHERE ProductID = " & ProductID.ToString() & " ORDER BY Products.Name"
        cmd.Connection = cnn
        cnn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        dr.Read()
        Image1.ImageUrl = "~/Pictures/Products/" & (CStr(dr("ImageSrc")))
        lblProduct.Text = CStr(dr("Name"))
        lblDescription.Text = CStr(dr("Description"))
        lblWidth.Text = CStr(dr("Width"))
        lblDepth.Text = CStr(dr("Depth"))
        lblHeight.Text = CStr(dr("Height"))
        decTempPrice = CDec(CStr(dr("Price")))
        decTempPrice = CDec(decTempPrice.ToString("$ ##.00"))
        lblPrice.Text = CStr(decTempPrice).ToString
        lblProductID.Text = CInt(dr("ProductID")).ToString
        lblWeight.Text = CStr(dr("Weight")).ToString
        GetTierNames(ProductID)
        cnn.Close()
        dr.Close()
        cmd.Dispose()
        cnn.Dispose()

    End Sub

Regards,

Aeros
0
 
LVL 3

Expert Comment

by:realrael
ID: 11807247

what's the error? - rael
0
 
LVL 1

Expert Comment

by:Trancedified
ID: 11856348
This is for SQL Server 2000 database.

-Create a new project
-add a datagrid onto your form
-Add a button to your form

Imports System.Data.SqlClient

Public Class frmMain
    Inherits System.Windows.Forms.Form

    Private da As SqlDataAdapter
    Private ds As DataSet
    Private dtSource As DataTable

   Public Sub cmdOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOpen.Click
        'Build connection string
        Dim strSQL As String
        Dim objConn As SqlConnection = New SqlConnection( _
            "Initial Catalog=Mydatabase;" & _
            "Data Source=myServerName;Integrated Security=SSPI;")

             objConn.Open()
        strSQL = "SELECT * FROM MyTable"

        da = New SqlDataAdapter(strSQL, objConn)
        ds = New DataSet

        'Fill the DataSet.
        da.Fill(ds, "mytable")

        'Added code remove if not working
        DataGrid1.DataSource = ds.Tables("mytable")

        ' Set the source table.
        dtSource = ds.Tables("mytable")

       LoadPage()
   End Sub

    'Loads datagrid
    Private Sub LoadPage()
        Dim i As Integer
        Dim startRec As Integer
        Dim endRec As Integer
        Dim dtTemp As DataTable
        Dim dr As DataRow

        'Duplicate or clone the source table to create the temporary table.
        dtTemp = dtSource.Clone

        If currentPage = PageCount Then
            endRec = maxRec
        Else
            endRec = pageSize * currentPage
        End If

        startRec = recNo

        'Copy the rows from the source table to fill the temporary table.
        For i = startRec To endRec - 1
            dtTemp.ImportRow(dtSource.Rows(i))
            recNo = recNo + 1
        Next

        DataGrid1.DataSource = dtTemp  
    End Sub
End Class

Let me know if this works?

Chris
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now