Working with a database

[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

 

[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

 

[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.
-----------------------------------