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

x
?
Solved

datagridview not showing columns correctly in vb.net 2005

Posted on 2007-10-08
14
Medium Priority
?
437 Views
Last Modified: 2013-11-26
hi,

I have the following code: -

    Private Sub Update_Table()



        If ds.Tables.Contains("Items") Then
            ds.Tables("Items").Clear()
        End If

        dbOpen()
        sql = "select      PurchaseOrderLineno as 'Line No', " & _
                        "Qty_Ordered as 'On Order', " & _
                        "Qty_Accepted as 'Qty Acc', " & _
                        "Product_Description, " & _
                        "OrderID, " & _
                        "Part_No, " & _
                        "dbo.Products.ProductID, " & _
                        "Notes " & _
                        "from dbo.Orders " & _
                    "INNER JOIN dbo.Products " & _
                         "on dbo.Orders.ProductID = dbo.Products.ProductID " & _
                "WHERE PurchaseOrderNo = '" & cmbOrderNumbers.Text.ToString & "' AND Closed = 'false'"

        'MsgBox(sql)

        Dim da3 As New SqlClient.SqlDataAdapter(sql, oConn)
        da3.Fill(ds, "Items") ' populate data set "da" with the data returned from sql command "ds" and set dataset name to "Rigs"
        DataGridView1.DataSource = ds.Tables("Items")
        Me.DataGridView1.Columns(0).Width = 30
        Me.DataGridView1.Columns(1).Width = 50
        Me.DataGridView1.Columns(2).Width = 50
        Me.DataGridView1.Columns(3).Width = 140
        Me.DataGridView1.Columns(5).Width = 140
        Me.DataGridView1.Columns(7).Width = 140


        Dim buttons As New DataGridViewButtonColumn()
        With buttons
            .HeaderText = ""
            .Text = "Book In"
            .UseColumnTextForButtonValue = True
            .AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            .FlatStyle = FlatStyle.Standard
            .CellTemplate.Style.BackColor = Color.Honeydew
            .DisplayIndex = DataGridView1.ColumnCount + 1
        End With
        Me.DataGridView1.Columns(4).Visible = False
        Me.DataGridView1.Columns(6).Visible = False
        'DataGridView1.Columns.Add(buttons)
    End Sub

I'm having trouble with the datagridview. It has 7 columns when it is run for the first time, the end one being a button. When I run the code again it goes down to 5 columns. what am I doing wrong please.

Many Thanks
Lee
0
Comment
Question by:ljhodgett
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 10

Expert Comment

by:digitalZo
ID: 20032845
So what exactly do you want? Do you want 5 or 7 columns at runtime?
0
 

Author Comment

by:ljhodgett
ID: 20032868
What it does is return values off the server but column 4 and 6 are sql indexes and I do not want them to be shown to the end user but I use it in my code elsewhere. Hence thats why I have tried : -

Me.DataGridView1.Columns(4).Visible = False
Me.DataGridView1.Columns(6).Visible = False

I use the code: -

    Private Sub cmbOrderNumbers_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbOrderNumbers.SelectedIndexChanged
        Update_Table()
    End Sub

to run the code above. when it runs for the first time it shows the columns correctly as which is correct: -

Line No
On Order
Qty Acc
Product Description
Part no
notes

when I click on the combobox again it only returns the columns which is incorrect: -

Line No
On Order
Qty Acc
notes

hope this makes sense, if not please let me know.

Lee
0
 
LVL 10

Expert Comment

by:digitalZo
ID: 20032921
Ahh okay, I get what is happening. It is not clearing the tables in the dataset and therefore, on the second click it's setting the visibility of the 4th and 6th column to false.

If you type the code like this:

      DataGridView1.DataSource = ds.Tables("Items")
        Me.DataGridView1.Columns(0).Width = 30
        Me.DataGridView1.Columns(1).Width = 50
        Me.DataGridView1.Columns(2).Width = 50
        Me.DataGridView1.Columns(3).Width = 140
        Me.DataGridView1.Columns(4).Visible = False
        Me.DataGridView1.Columns(5).Width = 140
        Me.DataGridView1.Columns(6).Visible = False
        Me.DataGridView1.Columns(7).Width = 140


        Dim buttons As New DataGridViewButtonColumn()
        With buttons
            .HeaderText = ""
            .Text = "Book In"
            .UseColumnTextForButtonValue = True
            .AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            .FlatStyle = FlatStyle.Standard
            .CellTemplate.Style.BackColor = Color.Honeydew
            .DisplayIndex = DataGridView1.ColumnCount + 1
        End With
         
        'DataGridView1.Columns.Add(buttons)

----
....does it give an error? Does it give the same output?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 10

Expert Comment

by:digitalZo
ID: 20032925
A q - why have you given this condition?

   If ds.Tables.Contains("Items") Then
            ds.Tables("Items").Clear()
        End If

Why not directly:

 ds.Tables("Items").Clear()

?
0
 

Author Comment

by:ljhodgett
ID: 20032929
hi,

Yes it does I'm afraid.

Best Regards
Lee
0
 

Author Comment

by:ljhodgett
ID: 20032937
hi,

I've had to do it like this as it comes up Object reference not set to an instance of an object if I just put in: -

ds.Tables("Items").Clear()

Best Regards
Lee
0
 

Author Comment

by:ljhodgett
ID: 20032963
Hi,

the full code that I am using is: -

Imports System.Data.SqlClient
Public Class frmBookInPurchaseOrder

    Dim sql As String
    Dim ds As DataSet
    Dim firstpass As Boolean

    Private Sub frmBookInPurchaseOrder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        firstpass = True
        ds = New DataSet
        dbOpen()
        sql = "SELECT PurchaseOrderNo  from Orders where dbo.Orders.Closed = 'false' GROUP by dbo.Orders.PurchaseOrderNo ORDER by PurchaseOrderNo"

        Dim da3 As New SqlClient.SqlDataAdapter(sql, oConn)
        da3.Fill(ds, "Orders")

        cmbOrderNumbers.DisplayMember = "PurchaseOrderNo"
        cmbOrderNumbers.DataSource = ds.Tables("Orders")

        Dim buttons As New DataGridViewButtonColumn()
        With buttons
            .HeaderText = ""
            .Text = "Book In"
            .UseColumnTextForButtonValue = True
            .AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            .FlatStyle = FlatStyle.Standard
            .CellTemplate.Style.BackColor = Color.Honeydew
            .DisplayIndex = DataGridView1.ColumnCount + 1
        End With

        DataGridView1.Columns.Add(buttons)


    End Sub

    Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
        Me.Close()
    End Sub

    Private Sub cmbOrderNumbers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Update_Table()
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim Returned_input As String
        Dim Returned_Closed As Boolean

        Returned_input = ""

        If DataGridView1.Columns(e.ColumnIndex).CellType.Equals(GetType(DataGridViewButtonCell)) Then

            If Not DataGridView1.Item("OrderID", e.RowIndex).Value.ToString = "" Then
                Returned_input = InputBox("Please enter quantity received")

                If Returned_input = "" Then
                    MsgBox("No quantity entered" & vbCrLf & "No Data has been Saved", MsgBoxStyle.Information, "Error in Field")
                    Exit Sub
                End If

                If IsNumeric(Returned_input) = False Then
                    MsgBox("Invalid entry." & vbCrLf & vbCrLf & "Entry Should be numeric", MsgBoxStyle.Information, "Error in Field")
                    Exit Sub
                End If

                Returned_Closed = False
                If MsgBox("Do you want to close the purchase order", MsgBoxStyle.YesNo, "Close Order") = MsgBoxResult.Yes Then
                    Returned_Closed = True
                End If



                dbOpen()
                Dim myCommand As New SqlCommand("Update_Orders", oConn)
                myCommand.CommandType = CommandType.StoredProcedure

                myCommand.Parameters.AddWithValue("@qty", Returned_input)
                myCommand.Parameters.AddWithValue("@closed", Returned_Closed)
                myCommand.Parameters.AddWithValue("@OrderID", DataGridView1.Item("OrderID", e.RowIndex).Value.ToString)
                myCommand.Parameters.AddWithValue("@ProductID", DataGridView1.Item("ProductID", e.RowIndex).Value.ToString)

                myCommand.ExecuteNonQuery()

                Update_Table()

            End If
        End If

    End Sub

    Private Sub Update_Table()

        If ds.Tables.Contains("Items") Then
            ds.Tables("Items").Clear()
        End If

        dbOpen()
        sql = "select      PurchaseOrderLineno as 'Line No', " & _
                        "Qty_Ordered as 'On Order', " & _
                        "Qty_Accepted as 'Qty Acc', " & _
                        "Product_Description, " & _
                        "OrderID, " & _
                        "Part_No, " & _
                        "dbo.Products.ProductID, " & _
                        "Notes " & _
                        "from dbo.Orders " & _
                    "INNER JOIN dbo.Products " & _
                         "on dbo.Orders.ProductID = dbo.Products.ProductID " & _
                "WHERE PurchaseOrderNo = '" & cmbOrderNumbers.Text.ToString & "' AND Closed = 'false'"

        'MsgBox(sql)

        Dim da3 As New SqlClient.SqlDataAdapter(sql, oConn)
        da3.Fill(ds, "Items")

        DataGridView1.DataSource = ds.Tables("Items")
        Me.DataGridView1.Columns(0).Width = 30
        Me.DataGridView1.Columns(1).Width = 50
        Me.DataGridView1.Columns(2).Width = 50
        Me.DataGridView1.Columns(3).Width = 140
        Me.DataGridView1.Columns(4).Visible = False
        Me.DataGridView1.Columns(5).Width = 140
        Me.DataGridView1.Columns(6).Visible = False
        Me.DataGridView1.Columns(7).Width = 140

    End Sub

    Private Sub cmbOrderNumbers_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbOrderNumbers.SelectedIndexChanged
        Update_Table()
    End Sub
End Class

It seems to be something to do with adding the button called "Book In". When I remove this the code works fine but I need the button at the end of the columns.

Best Regards
Lee
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20033078
Put the code for adding button call inside ispostback condtion as that it excuted only at first time

if ( me.ispostback = false)

firstpass = True
        ds = New DataSet
        dbOpen()
        sql = "SELECT PurchaseOrderNo  from Orders where dbo.Orders.Closed = 'false' GROUP by dbo.Orders.PurchaseOrderNo ORDER by PurchaseOrderNo"

        Dim da3 As New SqlClient.SqlDataAdapter(sql, oConn)
        da3.Fill(ds, "Orders")

        cmbOrderNumbers.DisplayMember = "PurchaseOrderNo"
        cmbOrderNumbers.DataSource = ds.Tables("Orders")

        Dim buttons As New DataGridViewButtonColumn()
        With buttons
            .HeaderText = ""
            .Text = "Book In"
            .UseColumnTextForButtonValue = True
            .AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            .FlatStyle = FlatStyle.Standard
            .CellTemplate.Style.BackColor = Color.Honeydew
            .DisplayIndex = DataGridView1.ColumnCount + 1
        End With

        DataGridView1.Columns.Add(buttons)

end if
0
 
LVL 10

Accepted Solution

by:
digitalZo earned 2000 total points
ID: 20033101
Instead of:

        If ds.Tables.Contains("Items") Then
            ds.Tables("Items").Clear()
        End If

Try this:

 ds.Clear()

Or if this throws the object reference exception, then try this:

  GridView1.DataSource = ""
        GridView1.DataBind()

<<It seems to be something to do with adding the button called "Book In". When I remove this the code works fine but I need the button at the end of the columns.>>

You can add the button using the Template Field without coding it programmatically.

Go to Desing View. Click on the GridView --> Properties. There's an option 'COLUMNS'. Click on it and add the button to your grid as and where you want it. You can modify it too.    
0
 
LVL 10

Expert Comment

by:digitalZo
ID: 20033105
pratima, i thought of is not postback too, but the update code isn't on page_load. will the postback still work?
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20033133
when we go for any event on the page it first excutes the page_load .
So If we check the postback condtion , then at the time of postback it will notexcute the same things agian
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20040480
Lee,

1) DataGridView is WinForms, not ASP.NET, so there isn't any post-backs.

2) Are you defining columns through the designer?  

3) Did you set AutoGenerateColumns = False in code somewhere?

Bob
0
 
LVL 10

Expert Comment

by:digitalZo
ID: 20054575
<<1) DataGridView is WinForms, not ASP.NET, so there isn't any post-backs.>>

You're right! ><

<<
3) Did you set AutoGenerateColumns = False in code somewhere?>>

Is there a behavior option 'AutoGenerateColumns = False' in DataGridView?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20058659
Yes, the AutoGenerateColumns for the DataGridView, oddly enough, is not a browsable property, so you can only set it in code.

Bob
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
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