Insert Error

[ggootee]

Hi, I have a SQL DB with two tables Borrower and Loan. The primary key in the Borrower Table is SSN which is a foreign key in the Loan table. The Primary key in the Loan table is LoanID. I have a relationship set up with referential inetgraty on these tables in SQL. In VB I get an error "Insert statement conflict with Column Foreignkey.." I want to beable to delete a SSN and have all associated records deleted. But I want to be able to insert a loan. It works fine in SQL but obviously I have a problem with my insert. I have a sql dataadapter and I generated the select, insert, update, and delete command. Is there some good info out there on how to handle this or any examples.

Thanks Greg

 

[wayko]

Public Sub DgContributor_Update(ByVal Sender As Object, ByVal E As System.Web.UI.WebControls.DataGridCommandEventArgs)
Dim uprefix As String = CType(E.Item.Cells(1).FindControl("ddlPrefix"), DropDownList).SelectedItem.Value
Dim ulast As String = CType(E.Item.Cells(2).FindControl("txtlast"), TextBox).Text
Dim ufirst As String = CType(E.Item.Cells(3).FindControl("txtfirst"), TextBox).Text
Dim usuffix As String = CType(E.Item.Cells(4).FindControl("ddlSuffix"), DropDownList).SelectedItem.Value
Dim uAuthorid As String = CType(E.Item.Cells(5).FindControl("lbid"), Label).Text
Dim nIdx As Int32 = E.Item.DataSetIndex
Dim oCmd As SqlClient.SqlCommand
If DsContributor1.Tables(0).Rows(nIdx).RowState = DataRowState.Added Then
oCmd.CommandText = "select max(AuthorId) from dbo.Contributor"
oCmd.Connection.Open()
Try
oCmd.ExecuteNonQuery()
Catch err As SqlClient.SqlException
txtmsg.Text = err.Message
Return
End Try
Dim nCountedValue As Int32 = oCmd.ExecuteScalar()

With oCmd
oCmd.CommandText = "INSERT INTO dbo.Contributor(AuthorId,prefix,AuthorLast,AuthorFirst,IsPhotographer, Suffix)" _
& " VALUES(&@nCountedValue + 1,@uprefix,@ulast, @ufirst, @ddlistvalue, @usuffix) "

.Parameters.Add("@prefix", uprefix)
.Parameters.Add("@AuthorLast", ulast)
.Parameters.Add("@AuthorFirst", ufirst)
.Parameters.Add("@IsPhotographer", ddlistvalue)
.Parameters.Add("@Suffix", usuffix)
.Connection = DaContributor.SelectCommand.Connection
End With

Else
oCmd = DaContributor.UpdateCommand
DaContributor.UpdateCommand.Parameters("@AuthorId").Value = uAuthorid
DaContributor.UpdateCommand.Parameters("@prefix").Value = uprefix
DaContributor.UpdateCommand.Parameters("@AuthorLast").Value = ulast
DaContributor.UpdateCommand.Parameters("@AuthorFirst").Value = ufirst
DaContributor.UpdateCommand.Parameters("@Suffix").Value = usuffix
End If

If oCmd.Connection.State = ConnectionState.Closed Then
oCmd.Connection.Open()
End If

Try
oCmd.ExecuteNonQuery()
Catch err As SqlClient.SqlException
txtmsg.Text = err.Message
Return
End Try
oCmd.Connection.Close()

reload data so we refect what was actually saved
LoadDataSet()
DgContributor.EditItemIndex = -1
FinishPage()

End Sub

 

[ggootee]

Thanks Worked