Working with a database

**Cor Ligthert [MVP]** · Dec 2 '06, 12:45 PM

Re: Working with a database

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

"jimmy" <james.herringt on@tiscali.co.u kschreef in bericht
news:1165057905 .385265.133850@ 80g2000cwy.goog legroups.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 cnnRestaurantMa nager As SqlConnection
Dim traRestaurantMa nager As SqlTransaction
Dim cmmRestaurantMa nager As New SqlCommand
Dim drRestaurantMan ager As SqlDataReader
>
Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
'Initiate the connection
cnnRestaurantMa nager = New SqlConnection(" Data
Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\Restaur antManager.mdf; Integrated
Security=True;C onnect Timeout=30;User Instance=True")
'Open the connection
Try
cnnRestaurantMa nager.Open()
Catch ex As Exception
MsgBox("Startup failed, could not connect to SQL server")
Exit Sub
End Try
>
Try
'Start transaction
traRestaurantMa nager =
cnnRestaurantMa nager.BeginTran saction("Reserv ations")
'Initiate the command
cmmRestaurantMa nager.Connectio n = cnnRestaurantMa nager
cmmRestaurantMa nager.Transacti on = traRestaurantMa nager
'Perform Query
cmmRestaurantMa nager.CommandTe xt = "SELECT
reservationsTbl .ReservationID, customerTbl.Fir stName,
customerTbl.Las tName, reservationsTbl .Time, reservationsTbl .PartySize,
reservationsTbl .Date FROM customerTbl INNER JOIN reservationsTbl ON
customerTbl.Cus tomerID = reservationsTbl .CustomerID"
drRestaurantMan ager = cmmRestaurantMa nager.ExecuteRe ader()
Catch ex As Exception
MsgBox("An error occurred whilst rying to query the
database")
End Try
>
'Setup DataGridView1
With ReservationGrid
.ColumnCount = 6
.Columns(0).Nam e = "ID"
.Columns(0).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(1).Nam e = "First Name"
.Columns(1).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(2).Nam e = "Surname"
.Columns(2).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(3).Nam e = "TOA"
.Columns(3).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(4).Nam e = "Party Size"
.Columns(4).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(5).Nam e = "Date"
.Columns(5).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
End With
>
'Read data from datareader and output to ReservationGrid
While drRestaurantMan ager.Read()
>
ReservationGrid .Rows.Add(drRes taurantManager. GetSqlInt32(0),
drRestaurantMan ager.GetString( 1), drRestaurantMan ager.GetString( 2),
drRestaurantMan ager.GetString( 3), drRestaurantMan ager.GetString( 4),
drRestaurantMan ager.GetSqlDate Time(5).ToStrin g())
End While
'Close datareader
drRestaurantMan ager.Close()
'Close Transaction
traRestaurantMa nager.Commit()
End Sub
>

**RobinS** · Dec 2 '06, 04:25 PM

Re: Working with a database

Here's an example:

Dim ds As DataSet
'open the connection
Using cnn As New SqlConnection(M y.Settings.Prod uctConnectionSt ring)
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("Prod uct").Rows
Dim ProductID As Integer = CType(dr.Item(" ProductID"), Integer)
Dim ProductName As String = dr.Item("Produc tName").ToStrin g
Dim ProductNumber As String = dr.Item("Produc tNumber").ToStr ing
Dim Description As String = dr.Item("Descri ption").ToStrin g
Console.WriteLi ne(String.Forma t("ProductID {0}, " & _
"ProductNam e {1}, {2}ProductNumbe r {3}, " & _
"Descriptio n {4}", ProductID, ProductName, _
ControlChars.Cr Lf, ProductNumber, Description))
Next

Robin S.
-----------------------------------
"jimmy" <james.herringt on@tiscali.co.u kwrote in message
news:1165057905 .385265.133850@ 80g2000cwy.goog legroups.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 cnnRestaurantMa nager As SqlConnection
Dim traRestaurantMa nager As SqlTransaction
Dim cmmRestaurantMa nager As New SqlCommand
Dim drRestaurantMan ager As SqlDataReader
>
Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
'Initiate the connection
cnnRestaurantMa nager = New SqlConnection(" Data
Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\Restaur antManager.mdf; Integrated
Security=True;C onnect Timeout=30;User Instance=True")
'Open the connection
Try
cnnRestaurantMa nager.Open()
Catch ex As Exception
MsgBox("Startup failed, could not connect to SQL server")
Exit Sub
End Try
>
Try
'Start transaction
traRestaurantMa nager =
cnnRestaurantMa nager.BeginTran saction("Reserv ations")
'Initiate the command
cmmRestaurantMa nager.Connectio n = cnnRestaurantMa nager
cmmRestaurantMa nager.Transacti on = traRestaurantMa nager
'Perform Query
cmmRestaurantMa nager.CommandTe xt = "SELECT
reservationsTbl .ReservationID, customerTbl.Fir stName,
customerTbl.Las tName, reservationsTbl .Time, reservationsTbl .PartySize,
reservationsTbl .Date FROM customerTbl INNER JOIN reservationsTbl ON
customerTbl.Cus tomerID = reservationsTbl .CustomerID"
drRestaurantMan ager = cmmRestaurantMa nager.ExecuteRe ader()
Catch ex As Exception
MsgBox("An error occurred whilst rying to query the
database")
End Try
>
'Setup DataGridView1
With ReservationGrid
.ColumnCount = 6
.Columns(0).Nam e = "ID"
.Columns(0).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(1).Nam e = "First Name"
.Columns(1).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(2).Nam e = "Surname"
.Columns(2).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(3).Nam e = "TOA"
.Columns(3).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(4).Nam e = "Party Size"
.Columns(4).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
.Columns(5).Nam e = "Date"
.Columns(5).Aut oSizeMode =
DataGridViewAut oSizeColumnMode .AllCells
End With
>
'Read data from datareader and output to ReservationGrid
While drRestaurantMan ager.Read()
>
ReservationGrid .Rows.Add(drRes taurantManager. GetSqlInt32(0),
drRestaurantMan ager.GetString( 1), drRestaurantMan ager.GetString( 2),
drRestaurantMan ager.GetString( 3), drRestaurantMan ager.GetString( 4),
drRestaurantMan ager.GetSqlDate Time(5).ToStrin g())
End While
'Close datareader
drRestaurantMan ager.Close()
'Close Transaction
traRestaurantMa nager.Commit()
End Sub
>

Working with a database

Working with a database

Comment

Comment