copying records

[Jeff Brooks]

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

[Cor Ligthert [MVP]]

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/lib...enonquery.aspx

I hope this helps,

Cor

"Jeff Brooks" <Je********@dis cussions.micros oft.com> schreef in bericht
news:7B******** *************** ***********@mic rosoft.com...

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 Brooks]

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
"Cor Ligthert [MVP]" wrote:

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/lib...enonquery.aspx

I hope this helps,

Cor

"Jeff Brooks" <Je********@dis cussions.micros oft.com> schreef in bericht
news:7B******** *************** ***********@mic rosoft.com...

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

[Cor Ligthert [MVP]]

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/de...chematopic.asp

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

dim cmb = oledbcommandbui lder(TheDataAda pter)

And than the update.

I hope this helps,

Cor

"Jeff Brooks" <Je********@dis cussions.micros oft.com> schreef in bericht
news:CF******** *************** ***********@mic rosoft.com...

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
"Cor Ligthert [MVP]" wrote:

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/lib...enonquery.aspx

I hope this helps,

Cor

"Jeff Brooks" <Je********@dis cussions.micros oft.com> schreef in bericht
news:7B******** *************** ***********@mic rosoft.com...

>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
>

[Göran Andersson]

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.

Jeff Brooks wrote:

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 Brooks]

I was hoping I could get away from that. It will be a long insert statement.

"Göran Andersson" wrote:

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.

Jeff Brooks wrote:

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

[Miro]

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.
"Jeff Brooks" <Je********@dis cussions.micros oft.com> wrote in message
news:7B******** *************** ***********@mic rosoft.com...

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