Solved

Display data from SQL Database in to  Data Grid

Posted on 2004-08-14
7
220 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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