Marc
Fri Mar 05 07:39:25 CST 2004
When I did this I received an error reading "Parameter _1 does not have a
default value". I know that all of the values for that parameter are NULL
for this table. I looked for a way to set the default value for a parameter
but only found the VALUE property.
I currently am populating the tables row by row. I have created functions
that build the DataTable objects based on the structure of the SQL table(s)
that the data is being drawn from. I then build the Access tables 'on the
fly' based on the structure of the DataTable. I then cycle through each row
of the data table, assign the values of that row to the parameter and run
the InsertCommand.ExecuteNonQuery(). It is only a few more steps because I
was able to write functions that loop through the columns of the data table
to create and populate the parameters generically.
If you know how to handle the error mentioned above that would be great. I
was able to get my code working taking a slightly longer route but I would
like to be able to handle this in the future. I am still pretty new to .NET
programming and want to learn everything that I can.
Thanks again for all of your help.
"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:KCPFIOmAEHA.600@cpmsftngxa06.phx.gbl...
> Hi Marc,
>
> Thank you for posting in the community!
>
> First of all, I would like to confirm my understanding of your issue. From
> your description, I understand that you need to put data from several SQL
> Server tables to an Access table. If there is any misunderstanding, please
> feel free to let me know.
>
> To achieve this, there are many ways. Here I listed some of them.
>
> 1. The simplest way to do this, is to use the Data Transformation Service
> (DTS) in SQL Server. DTS in SQL Server 2000 provides a set of graphical
> tools and programmable objects to help administrators and developers solve
> data movement problems, including the extraction, transformation, and
> consolidation of data from disparate sources to single or multiple
> destinations. Sets of tasks, workflow operations, and constraints can be
> collected as DTS packages that can be scheduled to run periodically or
when
> certain events occur. This white paper introduces DTS, shows some of the
> components and services that can be used to create DTS solutions,
> illustrates the use of DTS Designer to implement DTS solutions, and
> introduces DTS application development.
>
> Here is a link for more information about DTS.
>
>
http://msdn.microsoft.com/data/technologyinfo/sqlserver/SQLKeyTechs/default.
> aspx?pull=/library/en-us/dnsql2k/html/dts_overview.asp
>
> 2. Since you have 5 tables to merge, you can also use 5 SqlDataAdapters to
> fill the data into one DataTable and use another OleDbDataAdapter to
update
> the data in the DataTable to the Access file. When filling the DataTable,
> please make sure to set the AcceptChangesDuringFill property of the
> SqlDataAdapter to false. It will make all the filled rows' RowState
> property to Added, then the update method will work properly.
>
> Here is a code snippet. However it can only be used when there is no
> primary key in the table.
>
> sda1.AcceptChangesDuringFill = False
> sda2.AcceptChangesDuringFill = False
> sda3.AcceptChangesDuringFill = False
> sda4.AcceptChangesDuringFill = False
> sda5.AcceptChangesDuringFill = False
>
> sda1.Fill(dtHeader) 'Put all the data in a single table.
> sda2.Fill(dtHeader)
> sda3.Fill(dtHeader)
> sda4.Fill(dtHeader)
> sda5.Fill(dtHeader)
>
> Dim conOLE As New Data.OleDb.OleDbConnection
> Dim odaROW As New OleDb.OleDbDataAdapter
>
> conOLE.ConnectionString = sConString
>
> odaROW.InsertCommand = New OleDb.OleDbCommand
> odaROW.InsertCommand.Connection = conOLE
> odaROW.InsertCommand.CommandText = "INSERT INTO header (guid,
> parentguid) VALUES (?,?)"
>
>
> odaROW.InsertCommand.Parameters.Add("guid", OleDb.OleDbType.Guid,
> 64, "guid")
> odaROW.InsertCommand.Parameters.Add("parentguid",
> OleDb.OleDbType.Guid, 64, "parentguid")
>
> odaROW.Update(dtHeader)
>
> For more information about OleDbDataAdapter.Update method, please check
the
> following link for reference.
>
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
> frlrfsystemdatacommondbdataadapterclassupdatetopic.asp
>
> HTH. Does this answer your question? If anything is unclear, please feel
> free to reply to the post.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>