3 Replies - 554 Views - Last Post: 03 October 2012 - 05:37 AM Rate Topic: -----

#1 fachagooch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 10-March 10

Counting Rows using SCALAR Function

Posted 01 October 2012 - 11:08 PM

I have a program where i read a csv file into a temp database. and need to perform some functions against the data that is listed within. One of my problems is retreiving a block of data and or Counting against this local Dataset.

Dim LocalDataSet As New DataSet("Local_Dataset")
Dim dtRecAnsTmp As DataTable = LocalDataSet.Tables.Add("Received-Answered-tmp")

Private Sub BuildDataSet() 
        ' ------------- Build Temp Dataset ----------------
        Dim colRecAnsTmp As DataColumn = dtRecAnsTmp.Columns.Add("ID", Type.GetType("System.Int32"))
        colRecAnsTmp.AllowDBNull = False
        colRecAnsTmp.Unique = True
        colRecAnsTmp.AutoIncrement = True
        colRecAnsTmp.AutoIncrementSeed = 0
        colRecAnsTmp.AutoIncrementStep = 1

        dtRecAnsTmp.Columns.Add("Name", Type.GetType("System.String"))
        dtRecAnsTmp.Columns.Add("ReceivedAll", Type.GetType("System.Int32"))
        dtRecAnsTmp.Columns.Add("AnsweredAll", Type.GetType("System.Int32"))
        dtRecAnsTmp.Columns.Add("Total", Type.GetType("System.String"))
        dtRecAnsTmp.Columns.Add("Average", Type.GetType("System.String"))
        dtRecAnsTmp.Columns.Add("Queue", Type.GetType("System.Int32"))
    End Sub



I would like to count the rows where queue name = 0 or 1, But i am not sure how to make the SQLConnection to the Local dataset that is stored in memory.
Now i know how to read data out of it, But i want to perform a scalar function on the DB, vs go read db and count each row that has a 1 or a 0 in it.

Here is the code that i think i need to perform this function, Any help would be great.

    Private Sub countRows(ByVal qNumber As Integer)

        Dim SQLResult As Object
        Dim con As New SqlConnection("server=Local;Initial Catalog=Local_Dataset")
        Dim sqlRowCount As String = "SELECT COUNT * FROM dtRecAnsTmp Where Queue = " & qNumber & ""
        con.Open()
        Dim Command As New SqlCommand
        Command.Connection = con
        Command.CommandText = sqlRowCount
        SQLResult = Command.ExecuteScalar
        rowCount(qNumber) = SQLResult
        MessageBox.Show(rowCount(qNumber))
    End Sub




Is This A Good Question/Topic? 0
  • +

Replies To: Counting Rows using SCALAR Function

#2 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 292
  • View blog
  • Posts: 1,531
  • Joined: 07-April 08

Re: Counting Rows using SCALAR Function

Posted 02 October 2012 - 05:33 AM

the correct way to use count is COUNT(<Criteria>).

Also, if your just returning the count of rows then I would do a conversion to int right away instead of trying to use an object

Dim SQLResult As Int32

.......

SQLResult = (Int32)Command.ExecuteScalar



Also your connection string looks malformed. I would suggest using connectionstrings.com for looking up proper connection strings.

for yours I would suggest the following connection string:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

This post has been edited by rgfirefly24: 02 October 2012 - 05:40 AM

Was This Post Helpful? 0
  • +
  • -

#3 fachagooch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 10-March 10

Re: Counting Rows using SCALAR Function

Posted 03 October 2012 - 02:00 AM

First Off Thanks for the response. and code correction, but i am still having problems.

Quote

Also your connection string looks malformed. I would suggest using connectionstrings.com for looking up proper connection strings.

for yours I would suggest the following connection string:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;


I knew my connection String wasn't right, I was trying to use the temp DataTable (dtRecAnsTmp) in DataSet(Local_DataSet) that gets created with the import from the csv file.

This program dosn't have access to a SQL Server for external use, am trying to keep it all consolidated to one PC.

This post has been edited by fachagooch: 03 October 2012 - 02:07 AM

Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 292
  • View blog
  • Posts: 1,531
  • Joined: 07-April 08

Re: Counting Rows using SCALAR Function

Posted 03 October 2012 - 05:37 AM

If you've created a dataset locally in your program why are you trying to connect to it using Sql commands? you can use Linq to object to retrieve what you need.



Dim result = (from i in dtRecAnsTmp.AsEnumerable()
              where i.Field<int>("Queue") = qnumber
              select i)



with this you can then do a result.Count() to get the total number, or since the linq query should return an IEnumerable anyway you can just tack on a .Count() to the end of the Linq statement.

This post has been edited by rgfirefly24: 03 October 2012 - 05:45 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1