copying records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to copy a record one access record into the same table. I just need
to modify a couple fields that the user will change. Im sure there is an
easy way to do it. I have started just reading the data and writing a SQL
insert command, but there are a ton of fields. It would take forever to get
the datatypes correct and the SQL formatted properly. Anyone know how to
just copy a record and make a couple changes to it?

Thanks
Jeff
 
Jeff,

If is an easy question however has not a simple answer. If the keys of those
records are for sure completely unique, than it is simple.

Create a SQL Insertcommand with parameters and let it run by using an
ExecuteNonQuery

Because almost all text for OleDb parameters on MSDN are wrong. I give you
this, which is in my opinion one of the correct once. (OleDB uses no named
parameters which is consequent used on MSDN).

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery.aspx

I hope this helps,

Cor
 
Cor,

Thanks for the reply. I have used the execnonquery before. It works great,
but I was really hoping to get away from having to build a large insert
command. I was hoping to just copy the dataset and modify the fields I need
modified.


Jeff
 
Jeff,

You can and when you have less than 100 fields you can even use a
commanbuilder to build the insert.

First you have than to set a schema in a new dataset using the Fileschema
Than you create a dataadapter

http://msdn.microsoft.com/library/d...tacommondbdataadapterclassfillschematopic.asp

Fill that with your data in the way you wish.
and use than the commanbuilder

dim cmb = oledbcommandbuilder(TheDataAdapter)

And than the update.

I hope this helps,

Cor
 
You can use the insert command to read from the table. Example:

insert into table (field1, field2, field3)
select field1, field2, 42 from table where field3 = 18

This way you ony have to supply the values that you want to change, and
just copy the unchanged values.
 
I am extremely new to vb but i do something in my other language i program
in that i will soon try to re-create in vb.

Basically we have a file such as dbf with 100+ fields.

We have a function that we call and it Loops through the file defenition (
knowing now how many fields there are in a record ).
We call this "Scatter() ""
Scatter looks at the record you are sitting on, loops through the field
names ( that you know from the dbf defenition ) and then
writes each field into an array. So a Record that has 115 fields would have
115 dimension arrays.

So now this function returns the array of all the values in the field.

Now we have another function called " Gather() " and this loops through the
array copying all the values in the array to
the same field # in the database.

In between calling Scatter and Gather, you can change values in an array.
You just have to know which array# you are changin,
which is the field # in the db.

Once i get to this stage ill try to write it in vb. See me in a month or
two ;)
I had a big enough problem last night trying to call one form from another.



Miro.
 
Back
Top