Solved

Display data from SQL Database in to  Data Grid

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

632 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