trying to update a table after making a join select query on two tables

R

rob merritt

here is my problem distilled down I need to be able to change the
title of a book associated with an author there are 2 tables "author"
and "books"

books is keyed to author author.au_id -> books.author_key

so I select with
SelectCommand="select * from [authors],[books] where au_id =
author_key"


then try to update with
UpdateCommand="UPDATE [books] SET [book_title] = ? WHERE [author_key]
= ?"
this doesn't can I do this? I tried
UpdateCommand="UPDATE [books] SET [book_title] = ? WHERE [author_key]
= @au_id"
didn't help








<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server">
void DetailsView1_ItemUpdated(Object sender,
DetailsViewUpdatedEventArgs e)
{
GridView1.DataBind();
}
void DetailsView1_ItemInserted(Object sender,
DetailsViewInsertedEventArgs e)
{
GridView1.DataBind();
}
void DetailsView1_ItemDeleted(Object sender,
DetailsViewDeletedEventArgs e)
{
GridView1.DataBind();
}
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Editing Access Database</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:AccessDataSource ID="AccessDataSource1" Runat="server"
DataFile="data\pubs.mdb"
SelectCommand="select * from [authors],[books] where au_id
= author_key" >
</asp:AccessDataSource>
<asp:GridView ID="GridView1" Runat="server" AllowPaging="True"
AllowSorting="True" DataSourceID="AccessDataSource1"
DataKeyNames="au_id" AutoGenerateSelectButton="True">
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource2" Runat="server"
DataFile="data\pubs.mdb"
SelectCommand="select * from [authors],[books] where au_id
= author_key"
DeleteCommand="DELETE FROM [authors] WHERE [au_id] = ?"
UpdateCommand="UPDATE [books] SET [book_title] = ? WHERE
[author_key] = ?"
InsertCommand="INSERT INTO [authors] ([au_id], [au_lname],
[au_fname], [phone], [address], [city], [state], [zip], [contract],
[author_key], [book_title]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
FilterExpression="au_id='@au_id'">
<FilterParameters>
<asp:ControlParameter Name="au_id"
ControlID="GridView1"
PropertyName="SelectedValue"></asp:ControlParameter>
</FilterParameters>
</asp:AccessDataSource>
<asp:DetailsView ID="DetailsView1" Runat="server"
AutoGenerateDeleteButton="True"
AutoGenerateEditButton="True"
DataSourceID="AccessDataSource2" DataKeyNames="au_id"
AutoGenerateInsertButton="True"
onitemupdated="DetailsView1_ItemUpdated"
oniteminserted="DetailsView1_ItemInserted"
onitemdeleted="DetailsView1_ItemDeleted">
</asp:DetailsView>
</div>
</form>
</body>
</html>
 

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

Members online

Forum statistics

Threads
473,734
Messages
2,569,441
Members
44,832
Latest member
GlennSmall

Latest Threads

Top