Solved

Return a value it a record exists in sql

Posted on 2011-02-28
5
342 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 33
Visual studio 2015 1 21
Index and Stats Management-Specific tables 8 22
Groupbox Control ? 2 17
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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…

856 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