EDN Admin
Well-known member
Heres my scenario...
I have a table in Access with two key fields (lets call them Item and Cust). These key fields *combined* are the primary key. So each record must have a unique Item and User combination. It can have a duplicate Item with other records, and a duplicate Cust
with other records, but not a duplicate of both. Because my "primary key" is a combination of two fields, I cant make a primary key on the table in Access (it only supports one field as a primary key).<br/>
<br/>
I have another table in SQLServer which also has Item and Cust, but in addition, is has several descriptive fields that give more details about the Item and Cust.<br/>
<br/>
Im trying to use ADO.NET to bring in that SQL Server table and UPDATE some matching fields in Access with the descriptive fields.<br/>
<br/>
Here is the code Im currently using.<br/>
<br/>
<div style="color:Black;background-color:White; <pre>
<span style="color:Green; Open the connection to the db on SQLServer
conSQL = <span style="color:Blue; New SqlConnection(<span style="color:#A31515; "Data Source=SQLServer;Initial Catalog=Database;User Id=UserName;Password=Password")
conSQL.Open()
<span style="color:Green; Create a dataadapter that pulls in the necessary fields so we can update the descriptions...
daSQL = <span style="color:Blue; New SqlDataAdapter(<span style="color:#A31515; "SELECT [Description], Item, Cust FROM SQLTable", conSQL)
dt = <span style="color:Blue; New DataTable
daSQL.Fill(dt)
<span style="color:Blue; If <span style="color:Blue; Not dt <span style="color:Blue; Is <span style="color:Blue; Nothing <span style="color:Blue; Then
<span style="color:Green; Create DataAdapter for the Access table (the connection already exists at this time in the code)
da = <span style="color:Blue; New OleDbDataAdapter(<span style="color:#A31515; "SELECT [Description], Item, Cust FROM AccTable", con)
<span style="color:Green; Set the UpdateCommand of the Access table
da.UpdateCommand = <span style="color:Blue; New OleDbCommand(<span style="color:#A31515; "UPDATE AccTable SET [Description]=[Description] WHERE Item=Item AND Cust=Cust")
<span style="color:Green; Update the target DataAdapter using the DataTable that you filled from the source db
da.Update(dt)
<span style="color:Blue; End <span style="color:Blue; If
[/code]
<br/>
Now... what this *should* do is update the "Description" field in Access with the data in the "Description" field in SQL Server. The problem is, I have no idea how to write the UpdateCommand. I know the example above isnt correct. I just dont know what it
should be. Ive tried using question marks (Ive seen those in some examples of UpdateCommands) and Ive tried using Parameters by creating a command object and adding parameters (i.e. calling "Description" @Description and so on), but that doesnt appear
to do anything. However, Im not sure Im using the parameters properly. Since the column names are all the same, Im not sure that the parameter Im adding is referencing a column in the Access table or a column in the SQL Server.<br/>
<br/>
So if anyone knows how to use the UpdateCommand to update a table through a dataadapter using a datatable in memory, Id appreciate the know-how.<br/>
<br/>
Ultimately, I can do a "work around" for this by transferring the whole entire SQL table into Access and just doing an internal UPDATE statement with a JOIN, but Im trying to avoid that.
WATYF
View the full article
I have a table in Access with two key fields (lets call them Item and Cust). These key fields *combined* are the primary key. So each record must have a unique Item and User combination. It can have a duplicate Item with other records, and a duplicate Cust
with other records, but not a duplicate of both. Because my "primary key" is a combination of two fields, I cant make a primary key on the table in Access (it only supports one field as a primary key).<br/>
<br/>
I have another table in SQLServer which also has Item and Cust, but in addition, is has several descriptive fields that give more details about the Item and Cust.<br/>
<br/>
Im trying to use ADO.NET to bring in that SQL Server table and UPDATE some matching fields in Access with the descriptive fields.<br/>
<br/>
Here is the code Im currently using.<br/>
<br/>
<div style="color:Black;background-color:White; <pre>
<span style="color:Green; Open the connection to the db on SQLServer
conSQL = <span style="color:Blue; New SqlConnection(<span style="color:#A31515; "Data Source=SQLServer;Initial Catalog=Database;User Id=UserName;Password=Password")
conSQL.Open()
<span style="color:Green; Create a dataadapter that pulls in the necessary fields so we can update the descriptions...
daSQL = <span style="color:Blue; New SqlDataAdapter(<span style="color:#A31515; "SELECT [Description], Item, Cust FROM SQLTable", conSQL)
dt = <span style="color:Blue; New DataTable
daSQL.Fill(dt)
<span style="color:Blue; If <span style="color:Blue; Not dt <span style="color:Blue; Is <span style="color:Blue; Nothing <span style="color:Blue; Then
<span style="color:Green; Create DataAdapter for the Access table (the connection already exists at this time in the code)
da = <span style="color:Blue; New OleDbDataAdapter(<span style="color:#A31515; "SELECT [Description], Item, Cust FROM AccTable", con)
<span style="color:Green; Set the UpdateCommand of the Access table
da.UpdateCommand = <span style="color:Blue; New OleDbCommand(<span style="color:#A31515; "UPDATE AccTable SET [Description]=[Description] WHERE Item=Item AND Cust=Cust")
<span style="color:Green; Update the target DataAdapter using the DataTable that you filled from the source db
da.Update(dt)
<span style="color:Blue; End <span style="color:Blue; If
[/code]
<br/>
Now... what this *should* do is update the "Description" field in Access with the data in the "Description" field in SQL Server. The problem is, I have no idea how to write the UpdateCommand. I know the example above isnt correct. I just dont know what it
should be. Ive tried using question marks (Ive seen those in some examples of UpdateCommands) and Ive tried using Parameters by creating a command object and adding parameters (i.e. calling "Description" @Description and so on), but that doesnt appear
to do anything. However, Im not sure Im using the parameters properly. Since the column names are all the same, Im not sure that the parameter Im adding is referencing a column in the Access table or a column in the SQL Server.<br/>
<br/>
So if anyone knows how to use the UpdateCommand to update a table through a dataadapter using a datatable in memory, Id appreciate the know-how.<br/>
<br/>
Ultimately, I can do a "work around" for this by transferring the whole entire SQL table into Access and just doing an internal UPDATE statement with a JOIN, but Im trying to avoid that.
WATYF
View the full article