SqlDataSource - Modify table without primary key
-
hi guys, i have simple table without primary key that stores two foregin keys. table contains data about which doctor has which patient.
DoctorID
PatientID
1
1
1
3
1
4
2
1
2
15
what i am trying to accomplish is this:
<asp:SqlDataSource ID="sds" runat="server"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
SelectCommand="select * from DoctorPatient"
UpdateCommand="update DoctorPatient set DoctorID=@DoctorID,PatientID=@PatientID where DoctorID=@DoctorID and PatientID=@PatientID">
</asp:SqlDataSource><asp:DetailsView runat="server" ID="dv"
DataSourceID="sds" AllowPaging="true"
AutoGenerateEditButton="true">
</asp:DetailsView>but editing items wont work... can i set SqlDataSource - DetailsView to modify this table without adding primary ID column?
-
hi guys, i have simple table without primary key that stores two foregin keys. table contains data about which doctor has which patient.
DoctorID
PatientID
1
1
1
3
1
4
2
1
2
15
what i am trying to accomplish is this:
<asp:SqlDataSource ID="sds" runat="server"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
SelectCommand="select * from DoctorPatient"
UpdateCommand="update DoctorPatient set DoctorID=@DoctorID,PatientID=@PatientID where DoctorID=@DoctorID and PatientID=@PatientID">
</asp:SqlDataSource><asp:DetailsView runat="server" ID="dv"
DataSourceID="sds" AllowPaging="true"
AutoGenerateEditButton="true">
</asp:DetailsView>but editing items wont work... can i set SqlDataSource - DetailsView to modify this table without adding primary ID column?
This is an example of a Join Table and you should define a primary key on it. In this case it will be a composite key consisting of DoctorID and PatientID. Without this you have the potential for multiple entries with the same doctor and patient. Your update statement makes no sense either. Inserting actual values you can see that nothing will be updated.
UPDATE DoctorPatient
SET DoctorID = 1
PatientID = 3
WHERE DoctorID = 1 AND PatientID = 3
I know the language. I've read a book. - _Madmatt
-
hi guys, i have simple table without primary key that stores two foregin keys. table contains data about which doctor has which patient.
DoctorID
PatientID
1
1
1
3
1
4
2
1
2
15
what i am trying to accomplish is this:
<asp:SqlDataSource ID="sds" runat="server"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
SelectCommand="select * from DoctorPatient"
UpdateCommand="update DoctorPatient set DoctorID=@DoctorID,PatientID=@PatientID where DoctorID=@DoctorID and PatientID=@PatientID">
</asp:SqlDataSource><asp:DetailsView runat="server" ID="dv"
DataSourceID="sds" AllowPaging="true"
AutoGenerateEditButton="true">
</asp:DetailsView>but editing items wont work... can i set SqlDataSource - DetailsView to modify this table without adding primary ID column?
Is there really just those 2 columns in this table? What does the link represent? How is the UI making changes to this data? As has already been said you are updating the data with the same values and therefore there will be no visible change. But are you relying on knowing the values that are effectively being deleted?
Life goes very fast. Tomorrow, today is already yesterday.
-
Is there really just those 2 columns in this table? What does the link represent? How is the UI making changes to this data? As has already been said you are updating the data with the same values and therefore there will be no visible change. But are you relying on knowing the values that are effectively being deleted?
Life goes very fast. Tomorrow, today is already yesterday.
musefan wrote:
Is there really just those 2 columns in this table ?
This is quite common (its called a cross-reference or Xref table), when joining two tables together in a many-to-many fashion. For example a Author can have more than one book, but a book can have multiple authors. If you were to express this in a Database, you'd want to have a two column table as above, additionally, as in the previous post, you'd want to make both columns a [composite] PK.
-
musefan wrote:
Is there really just those 2 columns in this table ?
This is quite common (its called a cross-reference or Xref table), when joining two tables together in a many-to-many fashion. For example a Author can have more than one book, but a book can have multiple authors. If you were to express this in a Database, you'd want to have a two column table as above, additionally, as in the previous post, you'd want to make both columns a [composite] PK.
Sorry. I didn't quite mean it like that. I know the use of this kind of table and have used many times before. I was more trying to get what it was used for in this instance. Like What is the relationship? Perhaps an appointment. That's why I ask is there no other fields
Life goes very fast. Tomorrow, today is already yesterday.
-
hi guys, i have simple table without primary key that stores two foregin keys. table contains data about which doctor has which patient.
DoctorID
PatientID
1
1
1
3
1
4
2
1
2
15
what i am trying to accomplish is this:
<asp:SqlDataSource ID="sds" runat="server"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
SelectCommand="select * from DoctorPatient"
UpdateCommand="update DoctorPatient set DoctorID=@DoctorID,PatientID=@PatientID where DoctorID=@DoctorID and PatientID=@PatientID">
</asp:SqlDataSource><asp:DetailsView runat="server" ID="dv"
DataSourceID="sds" AllowPaging="true"
AutoGenerateEditButton="true">
</asp:DetailsView>but editing items wont work... can i set SqlDataSource - DetailsView to modify this table without adding primary ID column?
Why are you editing? With a table like that the relationship is binary. It exists or it doesn't. There is nothing to edit. All you should need is insert and delete. If patient 15 wants to change doctors from 2 to 1, you should just delete the 2,15 row and add a 1,15 row. Otherwise add a surrogate key. I would suggest adding a surrogate key anyway since there are probably other pieces of data you will want in the future like date. When did the patient start seeing that doctor? That would be good for reporting on things like how many new patients does the doctor get each month.
-
hi guys, i have simple table without primary key that stores two foregin keys. table contains data about which doctor has which patient.
DoctorID
PatientID
1
1
1
3
1
4
2
1
2
15
what i am trying to accomplish is this:
<asp:SqlDataSource ID="sds" runat="server"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
SelectCommand="select * from DoctorPatient"
UpdateCommand="update DoctorPatient set DoctorID=@DoctorID,PatientID=@PatientID where DoctorID=@DoctorID and PatientID=@PatientID">
</asp:SqlDataSource><asp:DetailsView runat="server" ID="dv"
DataSourceID="sds" AllowPaging="true"
AutoGenerateEditButton="true">
</asp:DetailsView>but editing items wont work... can i set SqlDataSource - DetailsView to modify this table without adding primary ID column?
As said above your statement doesn't make sense, so please consider the following :
"update DoctorPatient set DoctorID=@NewDoctorID,PatientID=@NewPatientID where DoctorID=@OldDoctorID and PatientID=@OldPatientID"Please consider other suggestions too before implementing this.
-
hi guys, i have simple table without primary key that stores two foregin keys. table contains data about which doctor has which patient.
DoctorID
PatientID
1
1
1
3
1
4
2
1
2
15
what i am trying to accomplish is this:
<asp:SqlDataSource ID="sds" runat="server"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
SelectCommand="select * from DoctorPatient"
UpdateCommand="update DoctorPatient set DoctorID=@DoctorID,PatientID=@PatientID where DoctorID=@DoctorID and PatientID=@PatientID">
</asp:SqlDataSource><asp:DetailsView runat="server" ID="dv"
DataSourceID="sds" AllowPaging="true"
AutoGenerateEditButton="true">
</asp:DetailsView>but editing items wont work... can i set SqlDataSource - DetailsView to modify this table without adding primary ID column?
hi guys, thanks for advice and info, but i found a way how to do this. so if anyone have same problem try this solution:
<asp:SqlDataSource ID="sds" runat="server"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
SelectCommand="
select
DoctorID,
PatientID,
DoctorID as DocOldID,
PatientID as PatientOldID
from
DoctorPatient"
UpdateCommand="
update DoctorPatient set
DoctorID=@DoctorID,
PatientID=@PatientID
where
DoctorID=@DocOldID and
PatientID=@PatientOldID">
</asp:SqlDataSource>
<asp:DetailsView runat="server" ID="dv"
DataKeyNames="DocOldID,PatientOldID"
DataSourceID="sds" AllowPaging="true"
AutoGenerateRows="false"
AutoGenerateEditButton="true">
<Fields>
<asp:BoundField DataField="DoctorID" />
<asp:BoundField DataField="PatientID" />
</Fields>
</asp:DetailsView>