[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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?
0
wchestnut
Asked:
wchestnut
  • 4
  • 2
  • 2
1 Solution
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now