VB.NET w/ SQL SELECT

wchestnut
wchestnut used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

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.

Author

Commented:
I appreciate you pointing out mistakes not related to the question, but the problem still exists even if I change it to COUNT.
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Can you explain the resulting data you are trying to retrieve from the database? Is it a count per distinct email address?
Ensure you’re charging the right price for your IT

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

Author

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?
Most Valuable Expert 2012
Top Expert 2014
Commented:
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

Author

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?
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
Awesome, thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial