OleDb stuff

F

fkheng

Well-known member
Joined
May 8, 2003
Messages
155
Location
Malaysia
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?
 
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?
 
er.laiyo so sorry, my msdn is an old one, does not have this, i jsut looked it up and i dont htink it contains this document, could u help me by informing me on wat would suit me?
 
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
 
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 were trying to do as well... i.e. whats our basic goal, and, this isnt a school assignment is it (check the forum guidelines)?

Jon
 
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. Thats all.
 
Next series of assumptions, sorry I havent worked with you before so I dont know what you know...well 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?

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

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

and sorry, i really dont 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...
 
why im resorting to a for loop is becoz this while loop with the read function wont work (reader is declared as a oledbdatareader)

if i exectue the "cmSQL.executenonquery" within this loop, i cant 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
 
You dont really need to know the specifics about how a dataadapter works to use its 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. Youll 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 cant 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 servers memory requirements, you can use the DataReader only to display data."

This isnt a bad thing, since many apps need just this display of data.

If you havent already, create your form with controls that will display the results of our data manipulation. (Dont worry about how we wire them up just yet if you dont know how, well get to that later)
Write your code:
1. Create the dataadapter.
2. Establish and populate your dataset.

Once youve got that done, post the code you generate (wont seem like much) and well work from there.

Jon
 
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, thats 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 ill have to use somnething else right?
er........wat else can i use without having to display the data?
 
i have read ur article, but i still do not know how to apply it into my situation as im not sure if ive explained to u my situation clearly enough, i think ill attach my project here, maybe you could better help me if u could see my error, thanx...
 
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 im 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 dojnt 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 ill learn about the database concepts later, as i find it too confusing, dont really understand...
 

Attachments

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?
 
Back
Top