Insert statement Conflict in the GridView
-
Hello & smiles, Can someone kindly guide as to what is going wrong in my Sub below? I am getting the error as : System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ProfitLossData_CBValues". The conflict occurred in database "ProfitLossRegister", table "dbo.CBValues", column 'cbno'. The statement has been terminated. ------------------------------------------------------------------------------------------ Protected Sub btnsave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsave.Click Dim connectionsave = New SqlConnection(ConnectionString) Dim Commandsave As SqlCommand Dim forecastbox As String Dim commentbox As String Dim catvalue As String Dim CBVal As String Dim PLGridRow As GridViewRow Commandsave = New SqlCommand Commandsave.Connection = connectionsave Try connectionsave.open() 'iterate through all the gridrows For Each PLGridRow In PLForecastGrid.Rows forecastbox = CType(PLGridRow.FindControl("txtforecastbdgt"), TextBox).Text commentbox = CType(PLGridRow.FindControl("txtcomments"), TextBox).Text catvalue = PLGridRow.Cells(0).Text lbltest1.Text = catvalue CBVal = PLGridRow.Cells(1).Text lbltest2.Text = CBVal Commandsave.CommandText = " Insert into profitlossdata" Commandsave.CommandText += " (forecastbdgt,comments,branchno,yearno," Commandsave.CommandText += " bpno,catno,cbno)" Commandsave.CommandText += " Values ('" & forecastbox & "', " Commandsave.CommandText += "'" & commentbox & "', " Commandsave.CommandText += "'" & lblbrvalue.Text & "', " Commandsave.CommandText += "'" & lblyrvalue.Text & "', " Commandsave.CommandText += "'" & lblbpvalue.Text & "', " Commandsave.CommandText += "'" & lbltest1.Text & "', " Commandsave.CommandText += "'" & lbltest2.Text & "')" Commandsave.ExecuteNonQuery() Next lblmessage.Text = " Records Created!" Catch ex As Exception lblmessage.Text = ex.ToString Finally connectionsave.close() End Try End Sub -------------------------------------------------------------------------------------
-
Hello & smiles, Can someone kindly guide as to what is going wrong in my Sub below? I am getting the error as : System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ProfitLossData_CBValues". The conflict occurred in database "ProfitLossRegister", table "dbo.CBValues", column 'cbno'. The statement has been terminated. ------------------------------------------------------------------------------------------ Protected Sub btnsave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsave.Click Dim connectionsave = New SqlConnection(ConnectionString) Dim Commandsave As SqlCommand Dim forecastbox As String Dim commentbox As String Dim catvalue As String Dim CBVal As String Dim PLGridRow As GridViewRow Commandsave = New SqlCommand Commandsave.Connection = connectionsave Try connectionsave.open() 'iterate through all the gridrows For Each PLGridRow In PLForecastGrid.Rows forecastbox = CType(PLGridRow.FindControl("txtforecastbdgt"), TextBox).Text commentbox = CType(PLGridRow.FindControl("txtcomments"), TextBox).Text catvalue = PLGridRow.Cells(0).Text lbltest1.Text = catvalue CBVal = PLGridRow.Cells(1).Text lbltest2.Text = CBVal Commandsave.CommandText = " Insert into profitlossdata" Commandsave.CommandText += " (forecastbdgt,comments,branchno,yearno," Commandsave.CommandText += " bpno,catno,cbno)" Commandsave.CommandText += " Values ('" & forecastbox & "', " Commandsave.CommandText += "'" & commentbox & "', " Commandsave.CommandText += "'" & lblbrvalue.Text & "', " Commandsave.CommandText += "'" & lblyrvalue.Text & "', " Commandsave.CommandText += "'" & lblbpvalue.Text & "', " Commandsave.CommandText += "'" & lbltest1.Text & "', " Commandsave.CommandText += "'" & lbltest2.Text & "')" Commandsave.ExecuteNonQuery() Next lblmessage.Text = " Records Created!" Catch ex As Exception lblmessage.Text = ex.ToString Finally connectionsave.close() End Try End Sub -------------------------------------------------------------------------------------
-
Probably the problem has nothing to do with the gridview. Just check the values you are inserting as foreign key in the table, whether it exests in the master table or not.
Best Regards, Apurva Kaushal
Hi Apurva, Thankyou for your prompt reply. In my gridview the values are coming from CBValues table & CbNo is ID of that table and foreign key of my master (profitlossdata) table. I am populating my gridview with CBNo and some other template columns. Then I am trying to insert these values into my master table on a button click event as in the sub in my previous post, so these values will exist in master table only if the Insert statement works, and that is my problem, the insert stt is failing... or am I confused?...well it's 3:30pm in Adelaide :) I guess my brain is off for a nap. Can you kindly explain it a bit more in detail? Thanks a million.
Aartee. ...HE is watching Us All!
-
Hi Apurva, Thankyou for your prompt reply. In my gridview the values are coming from CBValues table & CbNo is ID of that table and foreign key of my master (profitlossdata) table. I am populating my gridview with CBNo and some other template columns. Then I am trying to insert these values into my master table on a button click event as in the sub in my previous post, so these values will exist in master table only if the Insert statement works, and that is my problem, the insert stt is failing... or am I confused?...well it's 3:30pm in Adelaide :) I guess my brain is off for a nap. Can you kindly explain it a bit more in detail? Thanks a million.
Aartee. ...HE is watching Us All!