Ron Ronmonstererer Ankrah Ron Ronmonstererer Ankrah - 4 months ago 10
Vb.net Question

Delete if record exists and Insert if record doesn't exist

In my for loop, I'm writing an Insert and delete query which does the following:

In the case if the checkbox is not check then check if the record exist & if it exist then delete. If it doesn’t exist then don’t do anything.

If checkbox is check then also check if record exist & if it exist then don’t do anything but if it doesn’t exist then insert it.

I need a check on whether my queries, especially my subqueries would execute properly or would return an error because I'm writing it without wanting to change my database right now.

For Each oDataGridItem In valgrid2.Items
chk = oDataGridItem.FindControl("CheckBox1")
rn = oDataGridItem.FindControl("lblrptName")
rid = oDataGridItem.FindControl("lblReportID")
If chk.Checked = True Then
rpti = CType(oDataGridItem.FindControl("lblReportID"), Label).Text

'valsql2 = "INSERT INTO CompanyReportListTable(CompanyID, ReportID) Values (valCompanyId, rpti) WHERE NOT EXISTS(SELECT * FROM CompanyReportListTable WHERE ReportID <> rpti and companyid <> valCompanyId and " & chk.Checked & "= 1)"
'if it exists then don’t do anything
'valsql2 = valsq2 & ";IF ReportID = rpti and companyid = valCompanyId BEGIN WHERE CompanyID = CompanyID AND ReportID = ReportID END"
'To do nothing, i'd equal the parameter's value to the column name

count += 1
rptn = CType(oDataGridItem.FindControl("lblrptName"), Label).Text
oExArgs.Add(rptn)
Response.Write(rpti + " - " + rptn + "<br/>")
Else
'valsql2 = valsq2 & ";DELETE FROM CompanyReportListTable WHERE EXISTS(Select * FROM CompanyReportListTable WHERE ReportID = rpti and companyid = valCompanyId and" & chk.Checked & " = 0)"
'if it doesn't exist then don’t do anything
'valsql2 = valsq2 & ";IF ReportID <> rpti and companyid <> valCompanyId BEGIN WHERE CompanyID = CompanyID AND ReportID = ReportID END"

End If

Answer
valsql2 = "INSERT INTO CompanyReportListTable(CompanyID, ReportID) Values (valCompanyId, rpti) WHERE NOT EXISTS(SELECT * FROM CompanyReportListTable WHERE ReportID <> rpti and companyid <> valCompanyId and " & chk.Checked & "= 1)"

In this code " & chk.Checked & "= 1)"will give error . True =1 which is not support in sql .

Comments