Solved

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

Posted on 2006-07-03
7
1,466 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
Technology Partners: 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

632 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