Solved

VB.NET w/ SQL SELECT

Posted on 2014-02-26
8
488 Views
Last Modified: 2014-02-27
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
Comment
Question by:wchestnut
  • 4
  • 2
  • 2
8 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39890584
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
 

Author Comment

by:wchestnut
ID: 39892398
I appreciate you pointing out mistakes not related to the question, but the problem still exists even if I change it to COUNT.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39892515
Can you explain the resulting data you are trying to retrieve from the database? Is it a count per distinct email address?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:wchestnut
ID: 39892698
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39892927
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
 

Author Closing Comment

by:wchestnut
ID: 39893008
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39893052
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
 

Author Comment

by:wchestnut
ID: 39893065
Awesome, thanks again!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question