Problem with Data.Read

[Markus Ellebrecht]

Hello,

I am new to this group and I hope someone can help me *g*.

I am good in VBS but now I want to switch to vb.net because of getting crazy while creating every form in VBS with InternetExplorer an HTML.

OK. Now here is my first question:

I have an Access-Database and I want to get some information out of it.

The code works and looks loke this:

----------------------------------------------------------------------------------------------------------
Module Main
Dim i As Integer
Dim strUser As String = Mid(System.Security.Principal.WindowsIdentity.GetCurrent().Name(), System.Security.Principal.WindowsIdentity.GetCurrent().Name().IndexOf("\") + 2).ToUpper
Dim objKategorien() As Kategorie
Const strDB As String = "D:\Dokumente und Einstellungen\Markus\Eigene Dateien\Visual Studio Projects\ResSolution-Zaehler\ResSolution-Zaehler.mdb"

Public Sub Main()
ReadDB()
End Sub

'Kategorien und akt. Zählerstand aus Access-Datenbank auslesen
Private Sub ReadDB()
Dim connDB1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB)
Dim connDB2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB)
Dim cmdDB1 As New OleDb.OleDbCommand("SELECT * from Kategorien", connDB1)
Dim cmdDB2 As New OleDb.OleDbCommand("SELECT Zaehlerstaende.Kategorie, Zaehlerstaende.Minuten, Zaehlerstaende.OK FROM(Zaehlerstaende) WHERE (Zaehlerstaende.[User-Kuerzel]='" & strUser & "')
and (Zaehlerstaende.Datum=Date())", connDB2)
Dim drDB1 As OleDb.OleDbDataReader
Dim drDB2 As OleDb.OleDbDataReader

connDB1.Open()
drDB1 = cmdDB1.ExecuteReader()
i = 0
Do While drDB1.Read()
ReDim Preserve objKategorien(i)
objKategorien(i) = New Kategorie
objKategorien(i).ID = drDB1.Item("ID")
objKategorien(i).Bezeichnung = drDB1.Item("Bezeichnung")
connDB2.Open()
drDB2 = cmdDB2.ExecuteReader()
Do While drDB2.Read
If (objKategorien(i).ID = drDB2.Item("Kategorie")) And (drDB2.Item("OK") = False) Then
objKategorien(i).Minuten = drDB2.Item("Minuten")
End If
Loop
drDB2.Close()
connDB2.Close()
i = i + 1
Loop
drDB1.Close()
connDB1.Close()
End Sub
End Module
----------------------------------------------------------------------------------------------
Public Class Kategorie
Public ID As Long
Public Bezeichnung As String
Public Minuten As Integer
End Class
----------------------------------------------------------------------------------------------

It is working very well but do I have to open and close the drDB2 every time?
And why is it not working only with connDB1? I have to Dim connDB2, too, because the compiler says: Dieser Verbindung ist bereits ein geöffneter DataReader zugeordnet, der zuerst geschlossen werden
muss. (The connection is already set to a DataReader witch has to be closed first).
Is there a better way to get the data. Is there something like a DataSet where I can store the result?

Please help me.

Thx, Markus Ellebrecht

P.S. I am german and my english might not be the best. So please foregive me my mistakes *g*

 

[Arne Janning]

Hi Markus!

'Kategorien und akt. Zählerstand aus Access-Datenbank auslesen

Oh my god! Everything in german ;-)

Is there a better way to get the data. Is there something like a DataSet
where I can store the result?

DataReader is read-only, forward-only. Have a look at System.Data.DataSet:

Tutorial (in german)
http://www.microsoft.com/germany/ms/msdnbiblio/show_all.asp?siteid=542640

Documentation (in german):
http://msdn.microsoft.com/library/deu/cpref/html/frlrfSystemDataDataSetClassTopic.asp

P.S. I am german and my english might not be the best. So please foregive
me my mistakes *g*

There is a german newsgroup as well:

for ADO.NET

Cheers

Arne Janning

 

[Cor Ligthert]

Markus,

First a question, are you using VBNet with a Visual.Studio Net IDE or with a
Notebook, the last can make it for you a lot more difficult however not
impossible.

For both is a very good page how to do it.
http://samples.gotdotnet.com/quickstart/

In VBNet (certainly with ASPNET) you can forget the module.

You connect your page to your code using a string on the page, however how
to do it depends very much if you use the IDE which does it automaticly or a
notebook or whatever and the VBC compiler. (For me it seems a very hard way
to go using that).

The way you see it in the quickstarts above is by the way again an other
method, because that is a kind of ASPX (VBNET) scripting.

Cor

 

[Markus Ellebrecht]

Hello Arne,

thank u for your answer.
I will switch to the german groups *g*

I will watch your links and i hope it will help...

Greets, Markus Ellebrecht

 

[Cor Ligthert]

Arne,

I did not see this, I will see the reaction of Herfried when this question
comes in his most own particular newsgroup.
)))

Cor

 

[Markus Ellebrecht]

Hello Cor,

thx for your message.

I am using Visual.Studio but i am working on a notebook, too. Is there a problem with notebooks?
I thinks there is no difference between a notebook and a desktop-PC.
The quickstart helps me a little bit. Thx.
What u mean with the module etc I did not understand.

But I have switched to the german groups, now. So I think I will understand a little bit more

Markus

 

[Cor Ligthert]

Markus,

I did mean notepad because your code did look like it was typed in a notepad
for me.

There is an ASPNET resourcekit in those is a lot of usefull information,
extra controls and quickstart, maybe you can take that as sample. Better
you take now I know you have Visual Studio Net that you take this one.

http://msdn.microsoft.com/asp.net/asprk/

With ASPNET you have to remember some things.

The response.redirect is as well used to get the next page
The Session is still and even more important (you eveb can put a dataset in
one time in it)
ASPNET is stateless, what means that nothing is saved between sending and
getting back unless you put it in a session between the send and the
postback

I wrote it the postback, that is important.

The load event is forever fired. In that you have to use the statement.

If Not Ispostback then

Else

End if

Beside the session is the viewstate (everything is sended everytime), a
shared class however that is for the application what means for all users
active on your server in a session and as well the cache what is almost the
same as a shared cache and therefore usefull, however wait untill you need
that.

I think I have give you for now enough information.

I hope this helps somehow?

Cor



I hope this helps a little bit?



Cor

 

[Arne Janning]

Hi Cor!

There is an ASPNET resourcekit in those is a lot of usefull information,
extra controls and quickstart, maybe you can take that as sample. Better
you take now I know you have Visual Studio Net that you take this one.

http://msdn.microsoft.com/asp.net/asprk/

With ASPNET you have to remember some things.

The response.redirect is as well used to get the next page

Keep in mind that may as well be used to get the previous page.

The Session is still and even more important (you eveb can put a dataset
in one time in it)

Actually every web server can have multiple sessions. So there is not "The
Session" there are "Sessions". I think this is to improve performance.

ASPNET is stateless, what means that nothing is saved between sending and
getting back unless you put it in a session between the send and the
postback

HTTP is stateless as it has been specified by a supra-natinal commitee.
ASP.NET has been built in building 42, Bellvue, Washington, USA and
therefore might not be stateless.

I wrote it the postback, that is important.

Full ACK.

The load event is forever fired. In that you have to use the statement.

If Not Ispostback then

Return ToSender

Beside the session is the viewstate (everything is sended everytime), a
shared class however that is for the application what means for all users
active on your server in a session and as well the cache what is almost
the same as a shared cache and therefore usefull, however wait untill you
need that.

I hope this helps a little bit?

A little bit, yes, thanks. You know, germans...

Cheers

Arne