Solved

Return a value it a record exists in sql

Posted on 2011-02-28
5
335 Views
Last Modified: 2012-05-11
What i am trying to do is check to see if records exist in a table before I do an insert, if it exists I do not want to insert anything but if it doe snot exist I want to insert the record.  How can i return a value to say if a record exists.

I get an error that I cannot use a return in this context, and it dowuble errors out with a update panel in the mix.
Protected Sub lnkSubmitDental_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkSubmitDental.Click
        Dim userlogon As String = Split(Current.User.Identity.Name, "\")(1)
        Dim SrpID As Integer = lblHidSrpId.Text
        Dim UnitID As String = ddlSrpEvent.SelectedValue
        Dim PersId As String = lblHidSoldierId.Text
       
        For Each row As GridViewRow In myGridDental.Rows
            Dim QuesId As Integer = myGridDental.DataKeys(row.RowIndex).Value
            Dim data As TextBox = (TryCast(row.FindControl("txtData"), TextBox))
            Dim Remarks As TextBox = TryCast(row.FindControl("txtRemarks"), TextBox)
            Dim Dtdate As TextBox = TryCast(row.FindControl("txtCal"), TextBox)
            Dim answer As RadioButtonList = TryCast(row.FindControl("rblDental"), RadioButtonList)

            sql = "If Exists (Select intPersonnelID from tblSRPEventData where intPersonnelID = '" & PersId & "' and intQuestionId = " & QuesId & ") " _
                & "Return '0' else " _
                & "Insert tblSRpEventData (intPersonnelId, intUnitSRPId, intQuestionId, bitAnswer, strData, strRemarks, strDocDate, dtLogged, strlogged) VALUES (" & PersId & ", " & UnitID & ", " & QuesId & ", " _
                    & "" & answer.SelectedValue & ", '" & data.Text & "', '" & Remarks.Text & "', '" & Dtdate.Text & "', '" & Date.Today & "', '" & userlogon & "') return True"
            
                insertUpdateDelete(sql)
        Next
End Sub

Open in new window

0
Comment
Question by:kdeutsch
5 Comments
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 100 total points
Comment Utility
Change the return to "select 0"

Then you should be able to do something along the lines of

Dim ret as Int = insertUpdateDelete(sql)

Be aware that the insert statement may return 0 as well, so you might want to do select 1 instead of 0. You'll have to play with that a little bit, but thats generally the method you should take.
0
 

Author Comment

by:kdeutsch
Comment Utility
derekkromm:
ok, heres what I did so I did not get an error I had to select 0 after the insert otherwise it gave me an error.  But the problem is that if it hits one error it bumps it out what about the rest of the rows in the grid.


For Each row As GridViewRow In myGridDental.Rows
            Dim QuesId As Integer = myGridDental.DataKeys(row.RowIndex).Value
            Dim data As TextBox = (TryCast(row.FindControl("txtData"), TextBox))
            Dim Remarks As TextBox = TryCast(row.FindControl("txtRemarks"), TextBox)
            Dim Dtdate As TextBox = TryCast(row.FindControl("txtCal"), TextBox)
            Dim answer As RadioButtonList = TryCast(row.FindControl("rblDental"), RadioButtonList)

            sql = "If Exists (Select intPersonnelID from tblSRPEventData where intPersonnelID = '" & PersId & "' and intQuestionId = " & QuesId & ") " _
                & "Select 1 " _
                & "ELSE " _
                & "Insert tblSRpEventData (intPersonnelId, intUnitSRPId, intQuestionId, bitAnswer, strData, strRemarks, strDocDate, dtLogged, strlogged) VALUES (" & PersId & ", " & UnitID & ", " & QuesId & ", " _
                    & "" & answer.SelectedValue & ", '" & data.Text & "', '" & Remarks.Text & "', '" & Dtdate.Text & "', '" & Date.Today & "', '" & userlogon & "'); Select 0"

            ret = getData(sql).Rows(0)(0)

        Next

Open in new window

0
 
LVL 9

Assisted Solution

by:joshbula
joshbula earned 150 total points
Comment Utility
Do a Try...Catch.... either here or in the actual getData() function.

Try
  ret = getData(sql).Rows(0)(0)
Catch ex As Exception
  'do something with the exeption here if you want to
End Try
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
Comment Utility
write you insert statement more like this...

insert into (columnlist)
select value as a,value as b,.....
  where not exists (select 'Y' from xxx where .....


with sql server you do not need a from clause in the select if all the selected columns are values...

with other dbms's then there is usually a dummy table supplied with a single row to allow for this type of
statement (amongst other usages) e.g. DB2  Sysibm.sysdummy1,  Oracle Dual
sql = "Insert tblSRpEventData (intPersonnelId, intUnitSRPId, intQuestionId, bitAnswer, strData, strRemarks, strDocDate, " & _
                  "dtLogged, strlogged) " & _
       " Select " & PersId & "as p, " & UnitID & " as U, " & QuesId & " as Q, " _
                    & "" & answer.SelectedValue & " as S, '" & data.Text & "' as D, '" & _
                     Remarks.Text & "' as R, '" & Dtdate.Text & "' as Dt, '" & Date.Today & _
                     "' as tod, '" & userlogon & "' as L" & _
            " Where Not exists (Select intPersonnelID from tblSRPEventData where intPersonnelID = '" & PersId & _
               "' and intQuestionId = " & QuesId & ") "

Open in new window

0
 

Author Closing Comment

by:kdeutsch
Comment Utility
thanks for help, I have a couple different ways it looks like depending on how I want to present after the data is inserted or not.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now