Home All Groups Group Topic Archive Search About

Working with a database

microsoft.public.dotnet.languages.vb
Author
2 Dec 2006 11:11 AM
jimmy
Hi,

I currently have the following code in my program which loads data into
a data reader and then adds it to a DataGridView. Using the datareader
i cant however delete and modify the data because it is read only. How
can i do the same as what im doing except with a DataSet. Please not in
the query there are some joins which i have not been able to get to
work with a dataset so far.

    'Declare variables
    Dim cnnRestaurantManager As SqlConnection
    Dim traRestaurantManager As SqlTransaction
    Dim cmmRestaurantManager As New SqlCommand
    Dim drRestaurantManager As SqlDataReader

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
        'Initiate the connection
        cnnRestaurantManager = New SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RestaurantManager.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=True")
        'Open the connection
        Try
            cnnRestaurantManager.Open()
        Catch ex As Exception
            MsgBox("Startup failed, could not connect to SQL server")
            Exit Sub
        End Try

        Try
            'Start transaction
            traRestaurantManager =
cnnRestaurantManager.BeginTransaction("Reservations")
            'Initiate the command
            cmmRestaurantManager.Connection = cnnRestaurantManager
            cmmRestaurantManager.Transaction = traRestaurantManager
            'Perform Query
            cmmRestaurantManager.CommandText = "SELECT
reservationsTbl.ReservationID, customerTbl.FirstName,
customerTbl.LastName, reservationsTbl.Time, reservationsTbl.PartySize,
reservationsTbl.Date FROM customerTbl INNER JOIN reservationsTbl ON
customerTbl.CustomerID = reservationsTbl.CustomerID"
            drRestaurantManager = cmmRestaurantManager.ExecuteReader()
        Catch ex As Exception
            MsgBox("An error occurred whilst rying to query the
database")
        End Try

        'Setup DataGridView1
        With ReservationGrid
            .ColumnCount = 6
            .Columns(0).Name = "ID"
            .Columns(0).AutoSizeMode =
DataGridViewAutoSizeColumnMode.AllCells
            .Columns(1).Name = "First Name"
            .Columns(1).AutoSizeMode =
DataGridViewAutoSizeColumnMode.AllCells
            .Columns(2).Name = "Surname"
            .Columns(2).AutoSizeMode =
DataGridViewAutoSizeColumnMode.AllCells
            .Columns(3).Name = "TOA"
            .Columns(3).AutoSizeMode =
DataGridViewAutoSizeColumnMode.AllCells
            .Columns(4).Name = "Party Size"
            .Columns(4).AutoSizeMode =
DataGridViewAutoSizeColumnMode.AllCells
            .Columns(5).Name = "Date"
            .Columns(5).AutoSizeMode =
DataGridViewAutoSizeColumnMode.AllCells
        End With

        'Read data from datareader and output to ReservationGrid
        While drRestaurantManager.Read()

ReservationGrid.Rows.Add(drRestaurantManager.GetSqlInt32(0),
drRestaurantManager.GetString(1), drRestaurantManager.GetString(2),
drRestaurantManager.GetString(3), drRestaurantManager.GetString(4),
drRestaurantManager.GetSqlDateTime(5).ToString())
        End While
        'Close datareader
        drRestaurantManager.Close()
        'Close Transaction
        traRestaurantManager.Commit()
    End Sub

Author
2 Dec 2006 1:10 PM
Cor Ligthert [MVP]
Jimmy,

Probably are you coming from the ASP side, where the datareader is often
used to populate the grids.

In the windowforms way is for that the datatable, which fullfils that 1:1
and has everything around it in dotNet to do things as by instance updating.

To see a simple sample how to use that, see this page.

http://www.vb-tips.com/dbpages.aspx?ID=1139f14a-c236-4ad7-8882-b1ed16424252

I hope this helps,

Cor


Show quoteHide quote
"jimmy" <james.herring***@tiscali.co.uk> schreef in bericht
news:1165057905.385265.133850@80g2000cwy.googlegroups.com...
> Hi,
>
> I currently have the following code in my program which loads data into
> a data reader and then adds it to a DataGridView. Using the datareader
> i cant however delete and modify the data because it is read only. How
> can i do the same as what im doing except with a DataSet. Please not in
> the query there are some joins which i have not been able to get to
> work with a dataset so far.
>
>    'Declare variables
>    Dim cnnRestaurantManager As SqlConnection
>    Dim traRestaurantManager As SqlTransaction
>    Dim cmmRestaurantManager As New SqlCommand
>    Dim drRestaurantManager As SqlDataReader
>
>    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
>        'Initiate the connection
>        cnnRestaurantManager = New SqlConnection("Data
> Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RestaurantManager.mdf;Integrated
> Security=True;Connect Timeout=30;User Instance=True")
>        'Open the connection
>        Try
>            cnnRestaurantManager.Open()
>        Catch ex As Exception
>            MsgBox("Startup failed, could not connect to SQL server")
>            Exit Sub
>        End Try
>
>        Try
>            'Start transaction
>            traRestaurantManager =
> cnnRestaurantManager.BeginTransaction("Reservations")
>            'Initiate the command
>            cmmRestaurantManager.Connection = cnnRestaurantManager
>            cmmRestaurantManager.Transaction = traRestaurantManager
>            'Perform Query
>            cmmRestaurantManager.CommandText = "SELECT
> reservationsTbl.ReservationID, customerTbl.FirstName,
> customerTbl.LastName, reservationsTbl.Time, reservationsTbl.PartySize,
> reservationsTbl.Date FROM customerTbl INNER JOIN reservationsTbl ON
> customerTbl.CustomerID = reservationsTbl.CustomerID"
>            drRestaurantManager = cmmRestaurantManager.ExecuteReader()
>        Catch ex As Exception
>            MsgBox("An error occurred whilst rying to query the
> database")
>        End Try
>
>        'Setup DataGridView1
>        With ReservationGrid
>            .ColumnCount = 6
>            .Columns(0).Name = "ID"
>            .Columns(0).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(1).Name = "First Name"
>            .Columns(1).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(2).Name = "Surname"
>            .Columns(2).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(3).Name = "TOA"
>            .Columns(3).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(4).Name = "Party Size"
>            .Columns(4).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(5).Name = "Date"
>            .Columns(5).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>        End With
>
>        'Read data from datareader and output to ReservationGrid
>        While drRestaurantManager.Read()
>
> ReservationGrid.Rows.Add(drRestaurantManager.GetSqlInt32(0),
> drRestaurantManager.GetString(1), drRestaurantManager.GetString(2),
> drRestaurantManager.GetString(3), drRestaurantManager.GetString(4),
> drRestaurantManager.GetSqlDateTime(5).ToString())
>        End While
>        'Close datareader
>        drRestaurantManager.Close()
>        'Close Transaction
>        traRestaurantManager.Commit()
>    End Sub
>
Author
2 Dec 2006 4:49 PM
RobinS
Here's an example:

Dim ds As DataSet
'open the connection
Using cnn As New SqlConnection(My.Settings.ProductConnectionString)
    cnn.Open()
    'define the command
    Dim cmd As New SqlCommand
    cmd.Connection = cnn
    cmd.CommandText = "SELECT * FROM Product"
    'define the data adapter and fill the data table
    Dim da As New SqlDataAdapter(cmd)
    ds = New DataSet
    da.Fill(ds, "Product")
End Using

For Each dr As DataRow In ds.Tables("Product").Rows
    Dim ProductID As Integer = CType(dr.Item("ProductID"), Integer)
    Dim ProductName As String = dr.Item("ProductName").ToString
    Dim ProductNumber As String = dr.Item("ProductNumber").ToString
    Dim Description As String = dr.Item("Description").ToString
    Console.WriteLine(String.Format("ProductID {0}, " & _
      "ProductName {1}, {2}ProductNumber {3}, " & _
      "Description {4}", ProductID, ProductName, _
      ControlChars.CrLf, ProductNumber, Description))
Next


Robin S.
-----------------------------------
Show quoteHide quote
"jimmy" <james.herring***@tiscali.co.uk> wrote in message
news:1165057905.385265.133850@80g2000cwy.googlegroups.com...
> Hi,
>
> I currently have the following code in my program which loads data into
> a data reader and then adds it to a DataGridView. Using the datareader
> i cant however delete and modify the data because it is read only. How
> can i do the same as what im doing except with a DataSet. Please not in
> the query there are some joins which i have not been able to get to
> work with a dataset so far.
>
>    'Declare variables
>    Dim cnnRestaurantManager As SqlConnection
>    Dim traRestaurantManager As SqlTransaction
>    Dim cmmRestaurantManager As New SqlCommand
>    Dim drRestaurantManager As SqlDataReader
>
>    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
>        'Initiate the connection
>        cnnRestaurantManager = New SqlConnection("Data
> Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RestaurantManager.mdf;Integrated
> Security=True;Connect Timeout=30;User Instance=True")
>        'Open the connection
>        Try
>            cnnRestaurantManager.Open()
>        Catch ex As Exception
>            MsgBox("Startup failed, could not connect to SQL server")
>            Exit Sub
>        End Try
>
>        Try
>            'Start transaction
>            traRestaurantManager =
> cnnRestaurantManager.BeginTransaction("Reservations")
>            'Initiate the command
>            cmmRestaurantManager.Connection = cnnRestaurantManager
>            cmmRestaurantManager.Transaction = traRestaurantManager
>            'Perform Query
>            cmmRestaurantManager.CommandText = "SELECT
> reservationsTbl.ReservationID, customerTbl.FirstName,
> customerTbl.LastName, reservationsTbl.Time, reservationsTbl.PartySize,
> reservationsTbl.Date FROM customerTbl INNER JOIN reservationsTbl ON
> customerTbl.CustomerID = reservationsTbl.CustomerID"
>            drRestaurantManager = cmmRestaurantManager.ExecuteReader()
>        Catch ex As Exception
>            MsgBox("An error occurred whilst rying to query the
> database")
>        End Try
>
>        'Setup DataGridView1
>        With ReservationGrid
>            .ColumnCount = 6
>            .Columns(0).Name = "ID"
>            .Columns(0).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(1).Name = "First Name"
>            .Columns(1).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(2).Name = "Surname"
>            .Columns(2).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(3).Name = "TOA"
>            .Columns(3).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(4).Name = "Party Size"
>            .Columns(4).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>            .Columns(5).Name = "Date"
>            .Columns(5).AutoSizeMode =
> DataGridViewAutoSizeColumnMode.AllCells
>        End With
>
>        'Read data from datareader and output to ReservationGrid
>        While drRestaurantManager.Read()
>
> ReservationGrid.Rows.Add(drRestaurantManager.GetSqlInt32(0),
> drRestaurantManager.GetString(1), drRestaurantManager.GetString(2),
> drRestaurantManager.GetString(3), drRestaurantManager.GetString(4),
> drRestaurantManager.GetSqlDateTime(5).ToString())
>        End While
>        'Close datareader
>        drRestaurantManager.Close()
>        'Close Transaction
>        traRestaurantManager.Commit()
>    End Sub
>



Post Other interesting topics
best way to learn Windows Forms
Upgrading Access 97 application to dotNet
For loop variable date
Merging two tables in Dataset? I only want to get the matching info based on the two key fields
How to set an objects property using Reflection
How do you start an assembly dynamically with constructors
Enter Key vs. Tab Key
Q: VS2005
Drag and Drop controls during runtime in Windows App
Magnetic Cursor