Problems in loading data from an access database into an array

[Fabiyi Olawale]

Please i'm new in VB.net and i'm designing a Cinema booking and purchase
system..I need to load the seat ID from the database into an array.please
how do i go about it.. or can anyone give me an idea of how i can show seats
are availabele,booked or bought..i'll really appreciate input....
regards
wale fabiyi
Kuala Lumpur,Malaysia

 

[Guest]

Hi

Suppose your database has a table called [Screen] which has columns called
[ScreenID] and [SeatID].

Try this:

Public Sub GetSeatIDs(ByVal Screen As Int32, ByVal DBPath As String,
Optional ByVal DBPassword As String = "")


Dim oleConn As New OleDbConnection
Dim oleCSB As New OleDbConnectionStringBuilder
Dim oleAdapter As New OleDbDataAdapter
Dim dtSeatIDs As New DataTable
Dim arSeatIDs(0) As Int16

'build the connection string
With oleCSB
.Add("Provider", "Microsoft.Jet.OLEDB.4.0")
.Add("Data Source", DBPath)
.PersistSecurityInfo = True
If DBPassword <> "" Then
.Add("Jet OLEDBatabase Password", DBPassword)
End If
End With

'associate the connection string just built with the ole connection
oleConn.ConnectionString = oleCSB.ConnectionString

'set up the data adapter with the sql to retrieve the data
oleAdapter.SelectCommand = New OleDbCommand("SELECT [SeatID] FROM
[Screen] WHERE [ScreenID]=" & Screen)

'associate the adapter with the connection to the database
oleAdapter.SelectCommand.Connection = oleConn

'open the connection
oleConn.Open()

'call the fill command to get the data into the datatable
oleAdapter.Fill(dtSeatIDs)

'copy the data into the array
'(although it might be easier to work with it still in the datatable)
dtSeatIDs.Rows.CopyTo(arSeatIDs, 0)

End Sub

hth

GC

 

[walataza]

Hi

Suppose your database has a table called [Screen] which has columns called
[ScreenID] and [SeatID].

Try this:

Public Sub GetSeatIDs(ByVal Screen As Int32, ByVal DBPath As String,
Optional ByVal DBPassword As String = "")

Dim oleConn As New OleDbConnection
Dim oleCSB As New OleDbConnectionStringBuilder
Dim oleAdapter As New OleDbDataAdapter
Dim dtSeatIDs As New DataTable
Dim arSeatIDs(0) As Int16

'build the connection string
With oleCSB
.Add("Provider", "Microsoft.Jet.OLEDB.4.0")
.Add("Data Source", DBPath)
.PersistSecurityInfo = True
If DBPassword <> "" Then
.Add("Jet OLEDBatabase Password", DBPassword)
End If
End With

'associate the connection string just built with the ole connection
oleConn.ConnectionString = oleCSB.ConnectionString

'set up the data adapter with the sql to retrieve the data
oleAdapter.SelectCommand = New OleDbCommand("SELECT [SeatID] FROM
[Screen] WHERE [ScreenID]=" & Screen)

'associate the adapter with the connection to the database
oleAdapter.SelectCommand.Connection = oleConn

'open the connection
oleConn.Open()

'call the fill command to get the data into the datatable
oleAdapter.Fill(dtSeatIDs)

'copy the data into the array
'(although it might be easier to work with it still in the datatable)
dtSeatIDs.Rows.CopyTo(arSeatIDs, 0)

End Sub

hth

GC

Please i'm new in VB.net and i'm designing a Cinema booking and purchase
system..I need to load the seat ID from the database into an array.please
how do i go about it.. or can anyone give me an idea of how i can show seats
are availabele,booked or bought..i'll really appreciate input....
regards
wale fabiyi
Kuala Lumpur,Malaysia- Hide quoted text -

- Show quoted text -

Thanks so much for this...it really helped...so now how do i update
the data i worked with into to array and then write it back finally
into the array

 

[walataza]

Hi

Suppose your database has a table called [Screen] which has columns called
[ScreenID] and [SeatID].

Try this:

Public Sub GetSeatIDs(ByVal Screen As Int32, ByVal DBPath As String,
Optional ByVal DBPassword As String = "")

Dim oleConn As New OleDbConnection
Dim oleCSB As New OleDbConnectionStringBuilder
Dim oleAdapter As New OleDbDataAdapter
Dim dtSeatIDs As New DataTable
Dim arSeatIDs(0) As Int16

'build the connection string
With oleCSB
.Add("Provider", "Microsoft.Jet.OLEDB.4.0")
.Add("Data Source", DBPath)
.PersistSecurityInfo = True
If DBPassword <> "" Then
.Add("Jet OLEDBatabase Password", DBPassword)
End If
End With

'associate the connection string just built with the ole connection
oleConn.ConnectionString = oleCSB.ConnectionString

'set up the data adapter with the sql to retrieve the data
oleAdapter.SelectCommand = New OleDbCommand("SELECT [SeatID] FROM
[Screen] WHERE [ScreenID]=" & Screen)

'associate the adapter with the connection to the database
oleAdapter.SelectCommand.Connection = oleConn

'open the connection
oleConn.Open()

'call the fill command to get the data into the datatable
oleAdapter.Fill(dtSeatIDs)

'copy the data into the array
'(although it might be easier to work with it still in the datatable)
dtSeatIDs.Rows.CopyTo(arSeatIDs, 0)

End Sub

hth

GC

Please i'm new in VB.net and i'm designing a Cinema booking and purchase
system..I need to load the seat ID from the database into an array.please
how do i go about it.. or can anyone give me an idea of how i can show seats
are availabele,booked or bought..i'll really appreciate input....
regards
wale fabiyi
Kuala Lumpur,Malaysia- Hide quoted text -

- Show quoted text -

thanks so much...sorry there was a problem wit my former post..what i
meant was after loading the data from the database into the array,then
how do i update the array and then finnally update the contents of the
array into the Database

 

[Guest]

Hi again

I suggest you leave the data in the datatable and don't bother with an
array. So, after

oleAdapter.Fill(dtSeatIDs)

you have a datatable called dtSeatIDs which is basically an in-memory
representation of your access table.

Referring to the columns and rows of a datatable is very easy. There is a
rows collection of the datatable, and to update a particular column of a
particular row, you would do this:

dtSeatIDs.Rows(RowNum)("ColumnName") = YourNewValue

So, after you have made a load of changes to the data in your datatable, you
would want to write it back to the database. To update the data to the
database table, using the same oleAdapter as before, try this:


dim builder as OleDbCommandBuilder(oleAdapter)

'insert the data into the database
With oleAdapter
.UpdateCommand = builder.GetUpdateCommand
Console.WriteLine("Updated " & .Update(dtSeatIDs) & " records")
End With

The update command created by the OleDbCommandBuilder is based on the select
statement that is associated with it's associated OleDbDataAdapter. So this
would create the sql "UPDATE [Screen] SET [SeatID]= @paramSeatID WHERE
[ScreenID] = @paramScreenID"

The adapter must have it's select statement set before you call
builder.GetUpdateCommand. The connection should also be set first.

The OleDbDataAdapter.Update(DataTable) method will update any rows in the
database table where the corresponding row in the datatable has

DataTable.Rows(RowNum).RowState = DataRowState.Modified

Hopefully you'll be able to piece something useful together from the above.
Remember, if in doubt, the help files usually have a few useful examples...

Good luck!

GC

Hi

Suppose your database has a table called [Screen] which has columns called
[ScreenID] and [SeatID].

Try this:

Public Sub GetSeatIDs(ByVal Screen As Int32, ByVal DBPath As String,
Optional ByVal DBPassword As String = "")

Dim oleConn As New OleDbConnection
Dim oleCSB As New OleDbConnectionStringBuilder
Dim oleAdapter As New OleDbDataAdapter
Dim dtSeatIDs As New DataTable
Dim arSeatIDs(0) As Int16

'build the connection string
With oleCSB
.Add("Provider", "Microsoft.Jet.OLEDB.4.0")
.Add("Data Source", DBPath)
.PersistSecurityInfo = True
If DBPassword <> "" Then
.Add("Jet OLEDBatabase Password", DBPassword)
End If
End With

'associate the connection string just built with the ole connection
oleConn.ConnectionString = oleCSB.ConnectionString

'set up the data adapter with the sql to retrieve the data
oleAdapter.SelectCommand = New OleDbCommand("SELECT [SeatID] FROM
[Screen] WHERE [ScreenID]=" & Screen)

'associate the adapter with the connection to the database
oleAdapter.SelectCommand.Connection = oleConn

'open the connection
oleConn.Open()

'call the fill command to get the data into the datatable
oleAdapter.Fill(dtSeatIDs)

'copy the data into the array
'(although it might be easier to work with it still in the datatable)
dtSeatIDs.Rows.CopyTo(arSeatIDs, 0)

End Sub

hth

GC

Please i'm new in VB.net and i'm designing a Cinema booking and purchase
system..I need to load the seat ID from the database into an array.please
how do i go about it.. or can anyone give me an idea of how i can show seats
are availabele,booked or bought..i'll really appreciate input....
regards
wale fabiyi
Kuala Lumpur,Malaysia- Hide quoted text -

- Show quoted text -

thanks so much...sorry there was a problem wit my former post..what i
meant was after loading the data from the database into the array,then
how do i update the array and then finnally update the contents of the
array into the Database