?
Solved

Return a value it a record exists in sql

Posted on 2011-02-28
5
Medium Priority
?
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 400 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 600 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 1000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

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