OleDb stuff

[fkheng]

i declared

 

dim reader as oledbdatareader

 

i then used it to obtain values for rowsets...

 

however, i do not know wat i need to do after reading the value, i wish to overwrite that value with a desired value of mine which is in a string i created called "temp".

 

How do I overwrite it since this is just for reading?

[jfackler]

fkheng,

I was going to use this to reply to your previous post.... I'll put it here instead.

 

Have you read through this MSDN resource yet? Might be helpful.

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaccessingdatawithadonet.asp

 

Jon

[fkheng]

hey, thanx a lot, informative link...

i still have anothewr quesiton...

i plan to loop thru records in a table...

using a for loop, however, i do not know the syntax of a similar command in vb6 - rs.recordcount.

 

wat is the equivalent of accompliushing this outcome in VB.nET?

 

also, how do i move to the next record, like rs.movenext?

[jfackler]

Take a look here:

 

http://ms-help://MS.VSCC/MS.MSDNVS/vbcon/html/vbconADOPreviousVersionsOfADO.htm

 

edit: I don't suppose this explicitly answered the question other than "is there a way to do this in .net", write some code and if you then need some input, post it and we'll do what we can.

 

 

Jon

Edited May 26, 2003 by jfackler

[fkheng]

er.laiyo so sorry, my msdn is an old one, does not have this, i jsut looked it up and i don't htink it contains this document, could u help me by informing me on wat would suit me?

[jfackler]

Ok, the link boils down to this:

 

In ADO you scan sequentially through the rows of the recordset using the ADO MoveNext method. In ADO.NET, rows are represented as collections, so you can loop through a table as you would through any collection, or access particular rows via ordinal or primary key index.

 

Do you know how to loop through a collection?

 

Jon

[fkheng]

er.........so sorry, i don't know wat a collection is as well...could u help me out?

[jfackler]

Lets start with some basics;

 

1. What data base are you reading data from?

2. Is the db on the same machine on which you are writing code?

3. Do you want to only read data or do you need to write back to the db?

 

There are lots of other considerations after these basics. I need some idea of what we're trying to do as well... i.e. what's our basic goal, and, this isn't a school assignment is it (check the forum guidelines)?

 

Jon

[fkheng]

1. I am reading from an MS Access db

2. The db exists on my PC

3. i want to perform both read and write operations

 

i just want to loop through a database, check for data, and modify the data according to my conditions. That's all.

[jfackler]

Next series of assumptions, sorry I haven't worked with you before so I don't know what you know...we'll do better as time goes on.

 

1. Do you know how to create a dataadapter?

1.5. Do you know the basic differences (advantages/disadvantages) between a dataset and datareader?

2. Do you know how to establish a dataset? (the right choice if you want to read and write)

3. Do you have a good working knowledge of SQL?

 

We'll move on to writing vb.net code (the loop) once we establish the ado.net basics.

 

Jon

[fkheng]

yeah, i have read some tutorials, i know the basics of how to create one...don't really know wat it is for...

 

and sorry, i really don't know the differences between dataset and datareader.

 

i know how to declare a dataset, but there are so many properties i dunno how to make use of...

 

my sql knowledge is alright, i have done many SQL statements, so i feel i can always handle that one...

 

thanx for helping me out here...

[fkheng]

why i'm resorting to a for loop is becoz this while loop with the read function won't work (reader is declared as a oledbdatareader)

 

if i exectue the "cmSQL.executenonquery" within this loop, i can't do this becoz they say that the connection (variable conn) is used by the reader (oledbreader), therefore i cannot use this while loop. I mainly wanna use this reader loop to read the value, and modify the value in the db within this loop. Is this confdusing? =Any ideas?

 

'Do While reader.Read()

'Dim temp As String = reader.GetString(0)

'sqlStr = "UPDATE LoginTable SET Username ='" & UCase(temp) & "' WHERE Username='temp'"

 

'cmSQL = New OleDbCommand(sqlStr, conn)

'cmSQL.ExecuteNonQuery()

 

 

'adapter = New OleDbDataAdapter()

'adapter.UpdateCommand = New OleDbCommand(sqlStr, conn)

MsgBox(reader.GetString(0) & " " & reader.GetString(1), MsgBoxStyle.Information, "Test")

'Loop

[jfackler]

You don't really need to know the specifics about how a dataadapter works to use it's functionality...kind of like not knowing how the phone system works just trust that it will appropriately send your commands to the appropriate db if you have set it up correctly. You'll learn more and understand more about how they work the more you use them.

 

From an old post I commented on:

The datareader is a read only forward only object. Fast...but the point of no return is when you say read.

 

You can interupt it, but you can't go back.

 

Have to use a dataset for that ability.

 

Quote from "Coding techniques for Visual Basic.Net" from Microsoft Press by Connell:

 

"While the DataReader perimits direct, high-performance access to the database, it provides only read-only and forward only access to the data.

The results it returns are not memory resident, and it can access only one record at a time.

While these limitations put less load on the server's memory requirements, you can use the DataReader only to display data."

 

This isn't a bad thing, since many apps need just this display of data.

 

If you have'nt already, create your form with controls that will display the results of our data manipulation. (Don't worry about how we wire them up just yet if you don't know how, we'll get to that later)

Write your code:

1. Create the dataadapter.

2. Establish and populate your dataset.

 

Once you've got that done, post the code you generate (won't seem like much) and we'll work from there.

 

Jon

[fkheng]

oh, but wat i want to achieve is not to display the data, just to read the data without having to display it, and then merely modify the value of each field of each record in the database, that's all actually...but my problem was that the reader was taking up the connection item, therefore i could not execute another sql statement becoz of htis...so as u said, if the data reader is read only, then i'll have to use somnething else right?

er........wat else can i use without having to display the data?

[jfackler]

You can use a data command to execute a database procedure that returns a read-only result set � that is, you can execute a SQL Select statement or a stored procedure that contains a Select statement. This is similar to what happens when you fill a dataset using a data adapter, except that the result set is returned directly to you.

 

If you don't have other reasons to use a dataset (for example, you want to share the data with another component or application), it can be faster to use a data command to get a result set directly rather than load it into the dataset.

 

A result set is returned to you in a data reader (an object of type OleDbDataReader Class or SqlDataReader Class). You can then loop through this object, extracting individual records from the result set. Because the data reader provides forward-only, read-only access to the data, it is very fast.

 

The ExecuteReader method allows you to pass a parameter indicating command behavior, including options to close the connection immediately, return only a single record, and return keys only.

 

Dim myReader As OleDbDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
   While myReader.Read()
       Console.WriteLine(myReader.GetString(0))
   End While
   myReader.Close()

 

If you are using a dataset, you do not need to use a separate data command to execute database updates. Instead, you use the data adapter to update the database. However, if you are not using a dataset, you can send update commands directly to the database. To execute a command to update a database:

 

1. Add a data command to your form or component and configure it with the SQL statement or stored procedure to execute.

2. If the command takes parameters, configure them.

3. If there are parameters, add code to set their values.

4. Add code to open the connection associated with the data command.

5. Call the command's ExecuteNonQuery Method.

6. Close the connection.

 

The following example shows how to use a data command to update a database by inserting a new record into the Authors table of the SQL Server Pubs database. In this instance, the command calls a stored procedure named "NewAuthor" that is assumed to contain an Insert Into statement with nine values for the new author record. The data command OleDbCommand2 has already been configured to have a Parameters collection with nine parameters in it, one each for the parameters being passed to the stored procedure. The code sets the parameters values based on text boxes in a form, opens the connection, calls the ExecuteNonQuery method, and then closes the connection.

 

Dim cmdresults As Integer
' The following two property settings can also me done 
' in the Properties window, but are shown here for completeness.
OleDbcommand2.CommandText = "NewAuthor"
OleDbCommand2.CommandType = CommandType.StoredProcedure

' Set parameter values. In this case, all parameter values
' are strings.
OleDbCommand2.Parameters("au_id").Value = TextBox1.Text
OleDbCommand2.Parameters("au_lname").Value = TextBox2.Text
OleDbCommand2.Parameters("au_fname").Value = TextBox3.Text
OleDbCommand2.Parameters("phone").Value = TextBox4.Text
OleDbCommand2.Parameters("address").Value = TextBox5.Text
OleDbCommand2.Parameters("city").Value = TextBox6.Text
OleDbCommand2.Parameters("st").Value = TextBox7.Text
OleDbCommand2.Parameters("zip").Value = TextBox8.Text
OleDbCommand2.Parameters("contract").Value = CheckBox1.Checked

OleDbConnection2.Open()
Try
  cmdresults = OleDbcommand2.ExecuteNonQuery()
Catch ex as Exception
  MessageBox.Show("Failed to execute command, err = " & ex.Message)
End Try
OleDbConnection2.Close()
MessageBox.Show("Number of records inserted = " & cmdresults.ToString)

 

 

I know this is a load of a post but I think it will give you lots of fodder for contemplation.

 

Jon

[fkheng]

i see, okok, give3 me some time to digest this first, thanx a lot for the tremendous effort to help me...

[fkheng]

i have read ur article, but i still do not know how to apply it into my situation as i'm not sure if i've explained to u my situation clearly enough, i think i'll attach my project here, maybe you could better help me if u could see my error, thanx...

[fkheng]

wat i mainly want to do is to loop thru records in a table and then to update each record in the table to UPPER case no matter wat even if it is alreayd in upper case...

 

but since i'm using the data reader to loop thru, when i want to update, i get an error msg coz the data reader is already using the connection item...

 

i have read ur short tutorial above...i dojn't really get it...

 

could u help me out? probably just directly give me an idea on wat code or algorithm i should employ, i think i'll learn about the database concepts later, as i find it too confusing, don't really understand...

windowsapplication1.zip

[fkheng]

i would like to apologise if the file is too big...

[fkheng]

hm......given the situation, the onli way out seems to be to create 2 separate connections so that i can use both the data reader and update the datrabase at the same time without conflicts of the using the same connection item...is there a way i could still use the same connection item?

[fkheng]

hey, jsut have another question, i know it is after a long time since u last posted in this thread...well, is it proper to reuse a same oledbconnection over and over again, being defined in a main module so that it can be shared by forms which need it at a particular time? instead of creating new connection objects all over? are there a ny issues involved here?