I get an error call "Child list for field Customers cannot be created." after query execution specially after changing my sql queries to parameters instead of sql injection queries , here is the code
//////UPDATES\\\\\\\
Sorry for not providing full info however here are the needed updates: the error appear only if i used the customer query , which is the use type an sql statement to a text box called SQLText.Text and it points to the below line
DataGridViewReports.DataMember = "Customers"
while debugging here is the value of sqlQry = "UPDATE Customers SET ShortCode = '0000' WHERE (ServiceName='Block ALL ADs' AND SupplierName='Block Ads - All Vendors' AND SupplierFeedbackDate='.... / .... / .....' AND Supplier_Feedback='.....');"
to be precise : there is no errors at all in case i used the queries from check boxes
What I have tried:
Private Sub btn_custom_query_Click(sender As Object, e As EventArgs) Handles btn_custom_query.Click
Try
If cnnOLEDB.State = ConnectionState.Open Then
cnnOLEDB.Close()
End If
cnnOLEDB.ConnectionString = strConnectionString
cnnOLEDB.Open()
Dim sqlQry As String = SQLText.Text
'Only add "WHERE" if at least one of the boxes is checked
sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked Or DateAdded.Checked Or SupplierFeedbackDate.Checked, " WHERE ", "")
'Only add the parameter for Supplier if Supplier box is checked
If SupplierName.Checked Then
If cbo_Supplier.Text = vbNullString Then
sqlQry &= "SupplierName IS NOT NULL"
Else
sqlQry &= "SupplierName = @cbo_Supplier"
End If
End If
If Supplier_Feedback.Checked Then
If cbo_Feedback.Text = vbNullString Then
'Only add "AND" if Supplier box was checked (i.e. we already have a filter)
sqlQry &= IIf(SupplierName.Checked, " AND ", "")
sqlQry &= "Supplier_Feedback IS NOT NULL"
Else
'Only add "AND" if Supplier box was checked (i.e. we already have a filter)
sqlQry &= IIf(SupplierName.Checked, " AND ", "")
sqlQry &= "Supplier_Feedback = @cbo_Feedback"
End If
End If
If Reason.Checked Then
If cbo_Reason.Text = vbNullString Then
sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked, " AND ", "")
sqlQry &= "Reason IS NOT NULL"
Else
'Only add " AND " if we have a previous filter in place already
'I.e. one or both of Supplier and/or Action is checked
sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked, " AND ", "")
sqlQry &= "Reason = @cbo_Reason"
End If
End If
If ServiceName.Checked Then
If TXTServiceName.Text = vbNullString Then
'I.e. one or both of Supplier and/or Action is checked
sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked, " AND ", "")
sqlQry &= "ServiceName IS NOT NULL"
Else
'Only add " AND " if we have a previous filter in place already
'I.e. one or both of Supplier and/or Action is checked
sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked, " AND ", "")
sqlQry &= "ServiceName LIKE %@TXTServiceName%"
End If
End If
If DateAdded.Checked Then
'Only add " AND " if we have a previous filter in place already
'I.e. one or both of Supplier and/or Action is checked
sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked, " AND ", "")
sqlQry &= "DateAdded Between @cbo_DateSent And @cbo_DateSentTo"
End If
If SupplierFeedbackDate.Checked Then
'Only add " AND " if we have a previous filter in place already
'I.e. one or both of Supplier and/or Action is checked
sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked Or DateAdded.Checked, " AND ", "")
sqlQry &= "SupplierFeedbackDate Between @cbo_FeedbackDate And @cbo_FeedbackDateTo"
End If
SQLText.Text = sqlQry
Dim ds As DataSet = New DataSet
Dim da As New SqlDataAdapter(sqlQRY, cnnOLEDB)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
da.SelectCommand.Parameters.AddWithValue("@cbo_Supplier", cbo_Supplier.Text)
da.SelectCommand.Parameters.AddWithValue("@cbo_Feedback", cbo_Feedback.Text)
da.SelectCommand.Parameters.AddWithValue("@cbo_Reason", cbo_Reason.Text)
da.SelectCommand.Parameters.AddWithValue("@TXTServiceName", TXTServiceName.Text)
da.SelectCommand.Parameters.AddWithValue("@cbo_DateSent", cbo_DateSent.Value)
da.SelectCommand.Parameters.AddWithValue("@cbo_DateSentTo", cbo_DateSentTo.Value)
da.SelectCommand.Parameters.AddWithValue("@cbo_FeedbackDate", cbo_FeedbackDate.Value)
da.SelectCommand.Parameters.AddWithValue("@cbo_FeedbackDateTo", cbo_FeedbackDateTo.Value)
da.Fill(ds, "Customers")
DataGridViewReports.DataSource = ds
DataGridViewReports.DataMember = "Customers"
lbl_RowCount.Text = DataGridViewReports.RowCount
Catch ex As SqlException
MessageBox.Show(ex.Message)
End Try
cnnOLEDB.Close()
End Sub