How to check a SQL database table to see if a record exists

Question

I have a SQL database that creates a record for every document uploaded by the user to the server. I want to check this table before a user uploads a document to ensure they don't upload a file with name that already exists.

I know how to make the connection and make the SqlCommand to query the table for an existing record. But I don't know how to check the record count from the sqlCommand I made.

Does that make sense?

Using myConnectionCheck As New SqlConnection(myConnectionStringCheck)
                Dim myCommandCheck As New SqlCommand()
                myCommandCheck.Connection = myConnectionCheck
                myCommandCheck.CommandText = "SELECT * FROM Req_Docs WHERE Doc_Name =" & DocName
                myConnectionCheck.Open()
                myCommandCheck.ExecuteNonQuery()

            End Using

Thanks in advance,

Anthony

Answer 1

use if exists for this issue

create procedure isDocExists
@DocName varchar(255),
@isExists bit output
as
    set @isExists = 0
    if exists (SELECT Doc_Name FROM Req_Docs WHERE Doc_Name =@DocName)
    begin
           set @isExists=1
    end

to check where record is there or not

Answer 2

ExecuteNonQuery is a function, that returns an integer equal to the number of rows affected by the query.

However, it's usually used for updates.

You might consider ExecuteScalar, which returns the first column of the first row in the result set.

So if you change the query to select count(*) from..., the result of ExecuteScalar will be the number of rows, which you can then test.

Answer 3

if you want count:

SELECT COUNT(*) as count FROM Req_Docs WHERE Doc_Name = 'DocName'

Answer 4

So many things wrong here:

• Race condition between when you check and when you upload
• Multiple Documents should legitimately be allowed to have the same name. Use tags, folders, timestamps, or other means to distinguish them.
• Sql Injection vulnerability on the name parameter
• ExecuteNonQuery() on a SELECT query.

I'll give you the benefit of the doubt on the first two points that you're still gonna allow the upload, and this is just so you can ask the user how they want to relate the documents. Given that, here's how you fix the other two:

Using cn As New SqlConnection(myConnectionStringCheck), _
      cmd As New SqlCommand("SELECT COUNT(*) FROM (SELECT TOP 1 1 FROM Req_Docs WHERE Doc_Name= @DocName) t", cn)
    cmd.Parameters.Add("@DocName", SqlDbTypes.VarChar, 255).Value = DocName

    cn.Open()
    Return CInt(cmd.ExecuteScalar())
End Using

Answer 5

Chris A I used your solution. Thanks.