Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-03
7
Medium Priority
?
1,467 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 2000 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

Industry Leaders: 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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

721 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