Display data from SQL Database in to Data Grid

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

LVL 4
RamanaChoudaryAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AerosSagaConnect With a Mentor Commented:
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
 
AerosSagaCommented:
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
 
AerosSagaCommented:
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
 
realraelCommented:

what's the error? - rael
0
 
TrancedifiedCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.