VB.NET w/ SQL SELECT

I have the following code that goes out to our SQL FileTable and searches for e-mail addresses within the documents:

Dim connectionString As String = "Data Source=AV-W12-ROMS-1;Initial Catalog=RESUMES;Integrated Security=True"
Dim sql As String = ""
Dim connection As New SqlConnection(connectionString)
Dim dataadapter As New SqlDataAdapter(sql, connection)
Dim ds As New DataSet()

Later on, there's a Loop reading lines in a TextBox:

   sql = "SELECT IsNull(SUM(1),0) as 'count' FROM [RESUMES].[dbo].[ftbl_resume_files] WHERE contains(file_stream, '" + email_address + "')"
   connection.Open()
   ds.Clear()
   dataadapter.Fill(ds)
   connection.Close()
   email_count = ds.Tables(0).Rows(0).Item(0)
   Main.DataGridViewCandidates.Rows.Add(candidate_name, email_address + " (" + email_count.ToString + ")", activity_date, date_available, title, skills_list, sql)

End of Loop

Open in new window

The problem is the email_count value from the dataset is always 1 whether the e-mail address is found or not.  But if I copy the exact sql string variable value and paste it into a Query in SQL Server Management Studio, some of the results are 0.

What am I missing?
wchestnutAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
You specified SUM(1). Why are you using SUM to count anyway? Try using Count instead.

e.g.

sql = "SELECT COUNT(1) as 'count' FROM [RESUMES].[dbo].[ftbl_resume_files] WHERE contains(file_stream, '" + email_address + "')"

Open in new window


Also, using string concatenation to build SQL queries is bad business. Read up on SQL injection to see why.
0
wchestnutAuthor Commented:
I appreciate you pointing out mistakes not related to the question, but the problem still exists even if I change it to COUNT.
0
käµfm³d 👽Commented:
Can you explain the resulting data you are trying to retrieve from the database? Is it a count per distinct email address?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

wchestnutAuthor Commented:
Yes, the result should always be a single row and column consisting of a count of how many e-mail addresses were found.  This query is executed in a loop generated from scanning a TextBox line by line with some containing e-mail addresses then thrown into a DataGridView.

It was my understanding that the result would be located in ds.Tables(0).Rows(0).Item(0) but that value is always 1.  Make sense?
0
CodeCruiserCommented:
Try following code



Dim connectionString As String = "Data Source=AV-W12-ROMS-1;Initial Catalog=RESUMES;Integrated Security=True"
Dim sql As String = ""
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SQLCommand
Later on, there's a Loop reading lines in a TextBox:

   sql = "SELECT Count(*) as [Count] FROM [RESUMES].[dbo].[ftbl_resume_files] WHERE contains(file_stream, '" + email_address + "')"
   connection.Open()
   cmd.Connection = connection
   cmd.CommandText = sql
   email_count = cmd.ExecuteScalar()
   Main.DataGridViewCandidates.Rows.Add(candidate_name, email_address + " (" + email_count.ToString + ")", activity_date, date_available, title, skills_list, sql)
   connection.Close()

End of Loop

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wchestnutAuthor Commented:
THAT WORKED!  Thank you!

Being a VB.Net newbie, I'm assuming this approach is better for single row/column returns than what I was using which is better for populating DataGridViews with multiple columns/rows?
0
CodeCruiserCommented:
Yes. ExecuteScalar is used when you want to get a single value (of any type) from the database. TableAdapters are used when you want to populate datatables for using with controls like grids etc.
0
wchestnutAuthor Commented:
Awesome, thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.