Determine if Value Is Not In Database Column

Question

I have a Microsoft Access table that has only one column that contains URLs. I need to check and see if a URL DOES NOT EXIST in that table. What is the best way to do this?

score 2 · Accepted Answer · answered Nov 22 '13 at 15:29

2

A simple select with COUNT should do the trick:

SELECT COUNT(*)
FROM myTableWithUrl
WHERE URL='http://www.stackoverflow.com'

This query will always return a single row with one integer column. Zero indicates that the said URL is not present; any other number indicates that the URL is there.

The query may go faster if you define an index on the URL column.

answered Nov 22 '13 at 15:29

Sergey Kalinichenko

714,442
84
1,110
1,523

How do I do something with the answer? Like `If count = 0 Then ...` – gromit1 Nov 22 '13 at 15:41
1

@gromit1 Correct, that's what you do - you prepare the command, execute it for a scalar result by calling `myCommand.ExecuteScalar()` method, convert it to `Integer`, and check the result: `If count = 0 ...` then the URL is not present. You should send the URL as a SQL command parameter. Here is [an example of using `ExecuteScalar` in VB.](http://stackoverflow.com/a/9466724/335858) – Sergey Kalinichenko Nov 22 '13 at 15:47
How do I set `Count` to a variable and load it with the result of the SQL? – gromit1 Nov 22 '13 at 15:50
1

@gromit1 I updated the comment above to include a link to an answer that shows how to get the results of a `COUNT(*)` into a variable by calling `ExecuteScalar()`. – Sergey Kalinichenko Nov 22 '13 at 15:51

score 1 · Answer 2 · answered Nov 22 '13 at 15:29

1

I would go with

select count(*) records
from yourtable
where url = something

answered Nov 22 '13 at 15:29

Dan Bracuk

20,699
4
26
43

Stefano Bafaro · Answer 3 · 2013-11-22T16:37:42.813

1

This is your class code to do that, where columnname is the name of the column containing the Url values.

Imports System.Data.Oledb


    Dim con As New OledbConnection("Provider=microsoft.Jet.oledb.4.0DataSource=D:\mydata.mdb;")

    Dim cmd As New OledbCommand

    Public var1 As String
    Public url As String

    Public Sub New()

        con.Open()

        cmd.Connection = con
        cmd.CommandText = "SELECT COUNT(*) FROM table1 WHERE *columnname* ="+url
        Dim result = cmd.ExecuteScalar() 
        if Convert.ToInt32(result) = 0 Then //YourStuff
    End Sub

edited Nov 22 '13 at 16:37

answered Nov 22 '13 at 15:31

Stefano Bafaro

915
10
20

Shouldn't that not equal sign be an equal sign? – Dan Bracuk Nov 22 '13 at 15:53

score 1 · Answer 4 · answered Nov 22 '13 at 15:37

The best way in terms of performance would be to get the first matching record. Counting all records means that you have to go through all the matching records before returning a result. The most performant solution is to stop processing after you find the first matching record as it is unnecessary to keep count all of them when you have the answer after the first match.

That can be done with this query:

SELECT TOP 1 URL FROM TABLE
WHERE URL = 'http://www.stackoverflow.com'

If you get the URL then the URL is there. If you get an empty result set then it is not. Ideally, URL is indexed.

Determine if Value Is Not In Database Column

4 Answers4