TableAdapter SQL Update Help

J

jp2msft

I have 2 tables: EmployeeInfo and MGRSPV (Manager/Supervisor).

On the DataGridView, we modified the EmployeeInfo Table's Fill/GetData to
replace the EmployeeInfo.MGR value with the Manager/Supervisor's name from
MGRSPV.[MGR/SPVSR] as follows:

SELECT EmployeeInfo.FIRSTNAME, EmployeeInfo.LASTNAME, MGRSPV.[MGR/SPVSR]
FROM EmployeeInfo
INNER JOIN MGRSPV ON EmployeeInfo.MGR = MGRSPV.ID

It was working great, but crashed with an unhandled exception whenever I
tried to Save data on the 'AutoGenerated' line below. Now, VS refuses to
compile:

this.employeeInfoTableAdapter.Update(this.SqlSrvrDataSet.EmployeeInfo);
'EmployeeInfoTableAdapter' does not contain a definition for 'Update'

I guess this means I need to write a custom Update query for the
EmployeeInfo Table. Right?

How would I write the statement?

How would I later use the statement in my code? Do I need to manually read
in and supply parameters from my form? How would they be inserted? Using
SqlParameter with ParameterName/Value pairs seems like it would work, but
then I'd need an SqlCommand and have to find a way to update the TableAdapter.

The Wizard created everything on this except for the custom Fill/GetData SQL
piece, so I am left out in the cold!
 
J

Jignesh

if the query is based on joins, you will need to specify update sql clause
manually. if you are using declarative binding, open HTML source view and
specify UpdateCommand and update parameters.

eg.
UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName,
[UnitPrice] = @UnitPrice, [CategoryID] = @CategoryID WHERE [ProductID] =
@original_ProductID">
<UpdateParameters>
<asp:parameter Name="ProductName" Type="String" />
<asp:parameter Name="UnitPrice" Type="Decimal" />
<asp:parameter Name="CategoryID" Type="Int32" />
<asp:parameter Name="original_ProductID" Type="Int32" />
</UpdateParameters>


Some times back I had written an article on "4 Techniques to update
databases thru datasets when joins are involved "
published here
http://dotnetjini.jigneshdesai.com/dotnet-whidbey/ShowArticle.aspx?ID=2
will give you some more insight.

Regards
JIGNESH.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top