Solved

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

Posted on 2006-07-03
7
1,465 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get sourcecode path 14 61
Send SMS from vb.net desktop app 30 51
Suppress if value zero or NULL in crystal report 2 43
Click print button programmatically on print showdialog 1 24
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 …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 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