By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

copying records

P: n/a
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

May 5 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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********@discussions.microsoft.com> schreef in bericht
news:7B**********************************@microsof t.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

May 5 '06 #2

P: n/a
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********@discussions.microsoft.com> schreef in bericht
news:7B**********************************@microsof t.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


May 5 '06 #3

P: n/a
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 = oledbcommandbuilder(TheDataAdapter)

And than the update.

I hope this helps,

Cor

"Jeff Brooks" <Je********@discussions.microsoft.com> schreef in bericht
news:CF**********************************@microsof t.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********@discussions.microsoft.com> schreef in bericht
news:7B**********************************@microsof t.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
>


May 5 '06 #4

P: n/a
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

May 5 '06 #5

P: n/a
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

May 5 '06 #6

P: n/a
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********@discussions.microsoft.com> wrote in message
news:7B**********************************@microsof t.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

May 8 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.