[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

vb.net 2005 display sql results in datagrid

Posted on 2007-10-02
6
Medium Priority
?
334 Views
Last Modified: 2013-11-26
I want to display the results from a sql statement like this in a dategrid:

select this colume as 1st, sum((column2)+(column3)) as column2 from my table

My table has three columns, my datagrid needs just two because I want to display column2 and column3 totals as one column.  
0
Comment
Question by:bergertime
  • 3
  • 3
6 Comments
 
LVL 14

Expert Comment

by:ajitha75
ID: 19999684


        Dim conn As New System.Data.SqlClient.SqlConnection("Server=abc;Database=Northwind;user=x;pwd=x")
        Dim sqlAdapt As New System.Data.SqlClient.SqlDataAdapter("select col1, SUM (col1 + col2 ) as col2 from table1", conn)
        Dim ds As New DataSet()

        conn.Open()
        sqlAdapt.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)
0
 
LVL 2

Author Comment

by:bergertime
ID: 19999895
That's pretty much what I'm doing, it works until I add the SUM, then it just leaves it empty.  
0
 
LVL 14

Expert Comment

by:ajitha75
ID: 20000056
I missed the group by clause.. the actual query was
select col1, SUM (col1 + col2 ) as col2 from table1 group by col1
can you paste your code here?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 2

Author Comment

by:bergertime
ID: 20000160
Dim cn As iDB2Connection = New iDB2Connection("User ID=XXXX;Password=XXX;Data Source=XX.XX.XX;Connection Timeout = 0")
        Dim cmd As iDB2Command
        Dim excelsqlstring As String

excelsqlstring = "SELECT IALRTAFC# AS AFC, IALRTORG AS Origin, IALRTDEST AS DEST, IALRTAWTLB AS Weight, IALRTDWTLB AS DimWeight , IALRTARTOT AS Billed, sUM(IALRTVPTOT-ialrtartot)  AS Cost" _
                & " FROM air400.IALERT WHERE ialrtcusbl = " & txtCustomerNumber.Text & " And ialrtstat <> ('A') and (substring(ialrtrevdt, 4, 2) || '0' || substring(ialrtrevdt, 1, 1) || substring(ialrtrevdt, 2, 2) >= " & lblStartDate.Text & ") AND (substring(ialrtrevdt, 4, 2) || '0' ||substring(ialrtrevdt, 1, 1) || substring(ialrtrevdt, 2, 2) <= " & lblEndDate.Text & ")"

Dim da As New iDB2DataAdapter(excelsqlstring, cn)
        Dim ds As New DataSet()

        'Open connection
        cn.Open()
        cmd = New iDB2Command(excelsqlstring, cn)
         da.Fill(ds)
         Grid1.DataSource = ds.Tables(0)
0
 
LVL 14

Accepted Solution

by:
ajitha75 earned 2000 total points
ID: 20000406
I think you are missing group by clause at the end of query
0
 
LVL 2

Author Comment

by:bergertime
ID: 20007134
Thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Screencast - Getting to Know the Pipeline
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

830 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