[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Check if Record Exist

Posted on 2011-05-08
8
Medium Priority
?
339 Views
Last Modified: 2012-08-13
Hello All;
scanned google, came up with several pages of results, but nothing worth wasting a lot
Or time trying to figure out.

OK.

I need to check my SQL Database Table, and see if a Record exist.
If it does exist
("Record Exist, move on")
Else
Do the job
End if

This is my Code

Dim chFile As New System.Data.SqlClient.SqlCommand("SELECT Files.ID FROM Files INNER JOIN Entries ON Files.ID = Entries.ID WHERE Files.ID@ID", cn)
getUser.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ID", getID))
Dim rsFile As System.Data.SqlClient.SqlDataReader

Open in new window


Thanks all;
Carrzkiss
0
Comment
Question by:Wayne Barron
8 Comments
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 1000 total points
ID: 35717547
Hi, you can do something like this:
Dim myFileId = 123
Dim fileExists As Boolean

Using cn As New SqlConnection("Data Source=MyServer;Initial Catalog=MyDB;User Id=MyUser;Password=MyPassword;")
    Dim cm As New SqlCommand("SELECT Files.ID FROM Files INNER JOIN Entries ON Files.ID = Entries.ID WHERE Files.ID = @ID", cn)
    cm.Parameters.Add("@ID", SqlDbType.Int).Value = myFileId
    cn.Open()
    fileExists = Not cm.ExecuteScalar() Is Nothing
End Using

If fileExists Then
    '("Record Exist, move on")
Else
    'Do the job
End If

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35717620
Yes, Create connection and executescalar function for command "Select 1 From Table where name = ''test"

If record exists then get single value 1 else null.

Based on the value do as you need.
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 35717766
Trying to go through the code now.
This is an error that I am currently having issues with.


System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

On this line

fileExists = Not chFile.ExecuteScalar() Is Nothing
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35717788
if record exists it will return 1 or more than one
else 0
Dim myFileId = 123
Dim fileExists As Integer

Using cn As New SqlConnection("Data Source=MyServer;Initial Catalog=MyDB;User Id=MyUser;Password=MyPassword;")
    Dim cm As New SqlCommand("SELECT Count(*) FROM Files INNER JOIN Entries ON Files.ID = Entries.ID WHERE Files.ID = "&  myFileId, cn)
    cn.Open()
    fileExists =  cm.ExecuteScalar()
End Using

If fileExists >= 1 Then
    '("Record Exist, move on")
Else
    'Do the job
End If

Open in new window

0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 35717942
Still getting the same error

> fileExists =  cm.ExecuteScalar()


System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 1000 total points
ID: 35717965
are you using same connection object for other purpose also in the same page ?

When you create the second command object, you are passing in the existing connection object, which is indeed already open with a DataReader using it.  That stops anything else being able to use the connection, as DataReaders make exclusive use of them.  Change the second command to use sqlConn2.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35717974
Dim myFileId = 123
Dim fileExists As Integer

Using cn As New SqlConnection("Data Source=MyServer;Initial Catalog=MyDB;User Id=MyUser;Password=MyPassword;")
    Dim cm As New SqlCommand("SELECT Count(*) FROM Files INNER JOIN Entries ON Files.ID = Entries.ID WHERE Files.ID = "&  myFileId, cn)
    fileExists =  cm.ExecuteScalar()
End Using

If fileExists >= 1 Then
    '("Record Exist, move on")
Else
    'Do the job
End If
0
 
LVL 31

Author Closing Comment

by:Wayne Barron
ID: 35718015
Thanks Guys.
This is working and runs beautifully.

Carrzkss
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

872 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