Solved

Return a value it a record exists in sql

Posted on 2011-02-28
5
341 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
ID: 35000942
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
ID: 35001104
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
ID: 35001233
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
ID: 35001551
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
ID: 35007395
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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