|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
copying recordsI 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 Show quoteHide quote "Jeff Brooks" <JeffBro***@discussions.microsoft.com> schreef in bericht news:7B0F615D-8970-4E6B-820D-452E95B513F9@microsoft.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,
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 Show quoteHide quote "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/library/system.data.oledb.oledbcommand.executenonquery.aspx > > I hope this helps, > > Cor > > "Jeff Brooks" <JeffBro***@discussions.microsoft.com> schreef in bericht > news:7B0F615D-8970-4E6B-820D-452E95B513F9@microsoft.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,
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/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondbdataadapterclassfillschematopic.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 Show quoteHide quote "Jeff Brooks" <JeffBro***@discussions.microsoft.com> schreef in bericht news:CF3A313D-79E6-4365-82E8-D143E8ECDCB9@microsoft.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/library/system.data.oledb.oledbcommand.executenonquery.aspx >> >> I hope this helps, >> >> Cor >> >> "Jeff Brooks" <JeffBro***@discussions.microsoft.com> schreef in bericht >> news:7B0F615D-8970-4E6B-820D-452E95B513F9@microsoft.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 >> > >> >> >> 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: Show quoteHide quote > 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 > I was hoping I could get away from that. It will be a long insert statement.
Show quoteHide quote "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 > > > 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. Show quoteHide quote "Jeff Brooks" <JeffBro***@discussions.microsoft.com> wrote in message news:7B0F615D-8970-4E6B-820D-452E95B513F9@microsoft.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 > 
Other interesting topics
Do nothing but wait loop..
User permission problems. sqlconnection On the fly formatting of textbox contents How can I embedd graphics in Outlook Email without using MAPI Register DLL for COM interop and use it in ASP Declaration Expected how to prevent the controls in a form and form itself to scroll? Strip chars from a string Reference count? VB script to VB.Net back to VB script |
|||||||||||||||||||||||
