Solved

Display data from SQL Database in to  Data Grid

Posted on 2004-08-14
7
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…

749 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