asked on

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?

kaufmed

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.

wchestnut

ASKER

I appreciate you pointing out mistakes not related to the question, but the problem still exists even if I change it to COUNT.

kaufmed

Can you explain the resulting data you are trying to retrieve from the database? Is it a count per distinct email address?

wchestnut

ASKER

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?

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wchestnut

ASKER

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?

Nasir Razzaq

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.

wchestnut

ASKER

Awesome, thanks again!