Improve company productivity with a Business Account.Sign Up

x
?
Solved

Return a value it a record exists in sql

Posted on 2011-02-28
5
Medium Priority
?
353 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 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
There is a wide range of advantages associated with the use of ASP.NET. This is why this programming framework is used to create excellent enterprise-class websites, technologies, and web applications.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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