Solved

table.Columns.Add(New DataColumn("Conversion", GetType(Integer), "Captured \ Applications * 100"))  Check for 0's

Posted on 2006-07-03
7
1,454 Views
Last Modified: 2008-03-04
 table.Columns.Add(New DataColumn("Conversion", GetType(Integer), "Captured \ Applications * 100"))

hello, im trying to get a perventage column into my dt, how can i check if "Applications" = 0
or on Error divide by 0 enter a  0

0 is Sql default, no NULLS


TIA
0
Comment
Question by:5thcav
  • 4
  • 3
7 Comments
 
LVL 6

Expert Comment

by:carmodyk
ID: 17034278
How about doing the calculations before placing the value into the table column

Dim intValue = iif(Applications = 0, 0,Captured\Applications * 100)
 table.Columns.Add(New DataColumn("Conversion", GetType(Integer), intValue.Tostring))
0
 
LVL 6

Expert Comment

by:carmodyk
ID: 17034280
Sorry, correction

Dim intValue as integer = iif(Applications = 0, 0,Captured\Applications * 100)
 table.Columns.Add(New DataColumn("Conversion", GetType(Integer), intValue.Tostring))
0
 
LVL 7

Author Comment

by:5thcav
ID: 17034450
the way im doing it is Captured and Applications are only seen in that one line seeing those values
are currently in the table...I havent done much work in this area..

i was thinking of keeping the workload of our SQL server down my not having the script return  my data with the percentage
and have the local systems do the math,,,,, i may just use a datareader to fill an array then use that array to fill the dataset with the percentage column,,



        Dim ds As New DataSet
        Dim bindingSource1 As New BindingSource()
        Me.DataGridView1.DataSource = bindingSource1
        oSqlCmdlocal = New SqlClient.SqlCommand(strSQL)
        oDAlocal = New SqlClient.SqlDataAdapter(oSqlCmdlocal)
        oConnLocal = New SqlClient.SqlConnection(strConn)
        oSqlCmdlocal.Connection = oConnLocal
        oConnLocal.Open()
        Dim table As New DataTable("MER")
        oDAlocal.Fill(table)          
        table.Columns.Add(New DataColumn("Conversion", GetType(Integer), "Captured \ Applications * 100"))
        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        bindingSource1.DataSource = table
        Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 6

Accepted Solution

by:
carmodyk earned 500 total points
ID: 17034502
how Many Columns are you grabbing from the database and do you know the names of the columns? Are you concerned there might be a timeout happening if the sql connection is open to long?  Here's maybe what I would do.

        Dim cnSql As SqlConnection
        Dim cmSql As SqlCommand
        Dim drSql As SqlDataReader
        Dim strSql As String
        Dim intValue as integer

        Dim myDataTable As DataTable
        Dim myDataColumn As DataColumn
        Dim myDataRow As DataRow

        Try
            myDataTable = New DataTable("MER")

            myDataColumn = New DataColumn
            myDataColumn.DataType = GetType(System.String)
            myDataColumn.ColumnName = "ID"
            myDataTable.Columns.Add(myDataColumn)

            myDataColumn = New DataColumn
            myDataColumn.DataType = GetType(System.String)
            myDataColumn.ColumnName = "Captured"
            myDataTable.Columns.Add(myDataColumn)

            myDataColumn = New DataColumn
            myDataColumn.DataType = GetType(System.Double)
            myDataColumn.ColumnName = "Pecentage"
            myDataTable.Columns.Add(myDataColumn)

           
               strSql = "SELECT ID, Captured, Applications FROM SomeTable"
           
           cnSql = New SqlConnection(strConn)
            cnSql.Open()

            cmSql = New SqlCommand(strSql, cnSql)
            drSql = cmSql.ExecuteReader()

            Do While drSql.Read()
                myDataRow = myDataTable.NewRow()
                myDataRow(0) = drSql.Item("ID").ToString()
                myDataRow(1) = drSql.Item("Captured").ToString()
                if drSQL.Item("Applications").ToString = "" or drSQL.Item("Applications").ToString = "0" then
                   myDataRow(2) = 0.0
                Else
                    myDataRow(2) = drSQL.Item("Captured").ToInteger() \ (drSQL.Item("Applications").ToInteger * 100)
                End If
                myDataTable.Rows.Add(myDataRow)
            Loop

            drSql.Close()
            cnSql.Close()
            cmSql.Dispose()


if you get a timeout error with having the SQL connection open too long, then grab all you can and then calculate using solely the datatable.






0
 
LVL 7

Author Comment

by:5thcav
ID: 17034542
ya,, I was leaning that way,,,,, i'll have 250 users pulling this data for a winapp....  I know SQL is good but , how good?

Any way I’m sure it will be a much better performance hit using the reader then having SQL doing Cast/if /then/Sum to produce the percentage.


Thanks

0
 
LVL 6

Expert Comment

by:carmodyk
ID: 17034554
SQL is good, very good.  It's not exactly Oracle, but for what you're doing SQL will at times probably yawn a little, unless all 250 users hit it at the same time.  I've used SQL to handle Corporate applications, with an administrative office and 30 retail stores sending info back and forth from the admin office.  Honestly don't be afraid to let SQL do the calculations.  Most of the time, that computer where SQL resides is mostly sitting idle, so giving it some work, is OK to do at times.  
0
 
LVL 7

Author Comment

by:5thcav
ID: 17034612
Its the other 100 tasks im worried about!,, grin,, it does allot of DTS imports from db4 and excel with constant reporting MAPI,,,
and its on an old Compaq dual xeon 1meg 550's with only 512 ram... im afraid a new P4 3ghz 2meg L2 would put it to shame and make the Compaq think its an 8088....

in any case i'll probably just use the reader for this,,,,,




0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now