Have Geek Brain

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

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 the user uploads the document to make sure they are not uploading a file with a pre-existing name.

I know how to make a join and make the SqlCommand query the table for an existing record. But I dont know how to check the record count from sqlCommand that I did.

It makes 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

+2


a source to share


4 answers


use if exists

for this problem

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 the entry is or not

+2


a source


So many things are wrong:

  • Race condition between you at checkout and at boot
  • Many documents must be allowed to have the same name. Use tags, folders, timestamps, or other means to differentiate between them.
  • Sql Injection vulnerability in name parameter
  • ExecuteNonQuery () in SELECT query.


I'm going to walk you through the benefits of having doubts about the first two points that you will allow uploading anyway, and it's just so you can ask the user how they want to link the documents. Given that you fix two others here:

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
+2


a source


ExecuteNonQuery

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

However, it is 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 ExecuteScalar result will be the number of rows you can check.

+1


a source


if you want to count:

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


a source






More articles:


All Articles