locked
VB.NET retrieving Values from a db RRS feed

  • Question

  • Hi There,

    I have this question, below is the piece of code that I have written on a Button Click event and what it does is it takes all the values from the text boxes and Combo boxes and inserts them into the Datagridview control, this code works great but would like to pull one of the values from a table which is "0501", "0551", "0601" and also highlighted in the code. please let me know how this can be accomplished.

    Private Sub BtnAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddRows.Click
            DtGridView.ColumnCount = 7
            DtGridView.ColumnHeadersVisible = True

            ' Set the column header style.
            Dim columnHeaderStyle As New DataGridViewCellStyle()

            columnHeaderStyle.BackColor = Color.Beige
            columnHeaderStyle.Font = New Font("Verdana", 7, FontStyle.Bold)
            columnHeaderStyle.WrapMode = DataGridViewTriState.True
            DtGridView.ColumnHeadersDefaultCellStyle = columnHeaderStyle

     
            ' Set the column header names.
            DtGridView.Columns(0).Name = "Item Number"
            DtGridView.Columns(1).Name = "Vendor"
            DtGridView.Columns(2).Name = "Size"
            DtGridView.Columns(3).Name = "Item Description"
            DtGridView.Columns(4).Name = "Unit Of Measure"
            DtGridView.Columns(5).Name = "Item Class"
            DtGridView.Columns(6).Name = "Current Cost"

            If CboSize.Text = "SHOES" Then
                DtGridView.ClearSelection()
                Dim rowNum As Integer = DtGridView.Rows.Add()
                Dim Str As String
                Dim VendID As String
                Str = cboVendor.Text
                VendID = Str.Substring(0, 5)
              
                DtGridView.Rows.Item(rowNum).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0551"
                DtGridView.Rows.Item(rowNum).Cells(1).Value = VendID
                DtGridView.Rows.Item(rowNum).Cells(2).Value = CboSize.Text
                DtGridView.Rows.Item(rowNum).Cells(3).Value = TxtItmDesc.Text
                DtGridView.Rows.Item(rowNum).Cells(4).Value = CboUOM.Text
                DtGridView.Rows.Item(rowNum).Cells(5).Value = CboItmClass.Text
                DtGridView.Rows.Item(rowNum).Cells(6).Value = TxtCurrCost.Text

                Dim rownum1 As Integer = DtGridView.Rows.Add()
                DtGridView.Rows.Item(rownum1).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0601"
                DtGridView.Rows.Item(rownum1).Cells(1).Value = VendID
                DtGridView.Rows.Item(rownum1).Cells(2).Value = CboSize.Text
                DtGridView.Rows.Item(rownum1).Cells(3).Value = TxtItmDesc.Text
                DtGridView.Rows.Item(rownum1).Cells(4).Value = CboUOM.Text
                DtGridView.Rows.Item(rownum1).Cells(5).Value = CboItmClass.Text
                DtGridView.Rows.Item(rownum1).Cells(6).Value = TxtCurrCost.Text

                Dim rownum2 As Integer = DtGridView.Rows.Add()
                DtGridView.Rows.Item(rownum2).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0651"
                DtGridView.Rows.Item(rownum2).Cells(1).Value = VendID
                DtGridView.Rows.Item(rownum2).Cells(2).Value = CboSize.Text
                DtGridView.Rows.Item(rownum2).Cells(3).Value = TxtItmDesc.Text
                DtGridView.Rows.Item(rownum2).Cells(4).Value = CboUOM.Text
                DtGridView.Rows.Item(rownum2).Cells(5).Value = CboItmClass.Text
                DtGridView.Rows.Item(rownum2).Cells(6).Value = TxtCurrCost.Text

       DtGridView.AllowUserToAddRows = False
                BtnAddRows.Enabled = False
            Else
                DtGridView.Refresh()
                MsgBox("yet To Code")
            End If
    End Sub


    Farquest

    Tuesday, June 4, 2013 11:24 PM

Answers

  • Ok Guys, I think I got it, below is the code using the DataTable as said

     Private Sub BtnAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddRows.Click
            'DtGridView.ColumnCount = 7
            DtGridView.ColumnHeadersVisible = True

            ' Set the column header style.
            Dim columnHeaderStyle As New DataGridViewCellStyle()

            columnHeaderStyle.BackColor = Color.Beige
            columnHeaderStyle.Font = New Font("Verdana", 7, FontStyle.Bold)
            columnHeaderStyle.WrapMode = DataGridViewTriState.True
            DtGridView.ColumnHeadersDefaultCellStyle = columnHeaderStyle

            Dim dt As New DataTable
            dt.Columns.Add("Item Number")
            dt.Columns.Add("Vendor")
            dt.Columns.Add("Size")
            dt.Columns.Add("Item Description")
            dt.Columns.Add("Unit Of Measure")
            dt.Columns.Add("Item Class")
            dt.Columns.Add("Current Cost")

            DtGridView.DataSource = dt
            Dim Str As String
            Dim VendID As String
            Str = cboVendor.Text
            VendID = Str.Substring(0, 6)

    dt.Rows.Add(String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) & "-" & "0551", VendID, CboSize.Text, TxtItmDesc.Text, CboUOM.Text, CboItmClass.Text, TxtCurrCost.Text)       

            DtGridView.AllowUserToAddRows = False
            BtnAddRows.Enabled = False
     End Sub

    The problem is I want to store the 0551 value in a table and pull it from there and not want to hard code and when I Choose the Size as Shoes it should add 11 rows to the Grid just the change in the size, please let me know how can this be accomplished.

    All help highly appreciated.

    Thanks,


    Farquest

    • Marked as answer by Youen Zen Friday, June 21, 2013 8:20 AM
    Thursday, June 6, 2013 6:25 PM

All replies

  • You can hightlight an entire contents of a cell generally done via CellFormatting event. For partial highlights of cells you will need to a custom control/cell like RichTextBoxCell.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, June 5, 2013 3:04 AM
  • Try to stop with your beginners style programming. 

    Simply split the Graphical User Interface from the Data.

    The only two which then set the Graphical User Interface is the User (and that is then put in the Data) and the Data. 

    No filling of controls from other controls.


    Success
    Cor

    • Proposed as answer by Andrew Morton Wednesday, June 5, 2013 7:13 PM
    Wednesday, June 5, 2013 7:06 AM
  • I think that's not my answer, I am looking for something which will help me move forward with what I want to do, All your comments will be helpful once I am done with this basic thing, I appreciate it.

    Farquest

    Wednesday, June 5, 2013 6:53 PM
  • I think that's not my answer, I am looking for something which will help me move forward with what I want to do

    It really is the answer you're looking for. If you put the data in, say, a DataTable, then it will be much easier to show it in a DataGridView. It will be easier to keep the data strongly-typed (which reduces programming errors). And it will make it easier to move forward with adding to and maintaining the program.

    Try to keep data and the presentation of the data separate in your program, because they really are separate things.

    P.S. the string concatenation operator in VB.NET is "&", not "+".

    --
    Andrew

    Wednesday, June 5, 2013 7:13 PM
  • The lines like this:

    DtGridView.Rows.Item(rowNum).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0551"

    could do with being expanded to several statements. It will aid readability and it won't make it any slower.

    HTH,

    Andrew

    Wednesday, June 5, 2013 7:18 PM
  • Andrew,

    I appreciate it and I am new to this and trying my level best can you let me know if you have any example to bind the datagrid to a datatable and then insert those rows to the db.

    Thanks,


    Farquest

    Wednesday, June 5, 2013 7:27 PM
  • Farquest

    It is so easy that there are few samples without anything else. 

    But here an old one on our website.

    http://www.vb-tips.com/DataGridViewSample.aspx

     This one direct from a database.

    http://www.vb-tips.com/Access4withCombobox%20and%20DataGridView%20.ASPX


    Success
    Cor


    Wednesday, June 5, 2013 9:07 PM
  • I will certainly try it out and let you know, thanks for the info

    Farquest

    Thursday, June 6, 2013 12:18 AM
  • I did take a look at both the examples but did not understand how it fits my requirement here.

    I have a few text boxes and a couple of Combo boxes, The user fills in the data and based on a combo box class when the user clicks on Add Rows To Grid button then it will create the rows with sizes in the dataviewgrid, once this is done, I take this info and send them to the DB.

    Please see below for the screen shot of the form


    Farquest


    • Edited by Farquest Thursday, June 6, 2013 11:00 PM
    Thursday, June 6, 2013 5:56 PM
  • Ok Guys, I think I got it, below is the code using the DataTable as said

     Private Sub BtnAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddRows.Click
            'DtGridView.ColumnCount = 7
            DtGridView.ColumnHeadersVisible = True

            ' Set the column header style.
            Dim columnHeaderStyle As New DataGridViewCellStyle()

            columnHeaderStyle.BackColor = Color.Beige
            columnHeaderStyle.Font = New Font("Verdana", 7, FontStyle.Bold)
            columnHeaderStyle.WrapMode = DataGridViewTriState.True
            DtGridView.ColumnHeadersDefaultCellStyle = columnHeaderStyle

            Dim dt As New DataTable
            dt.Columns.Add("Item Number")
            dt.Columns.Add("Vendor")
            dt.Columns.Add("Size")
            dt.Columns.Add("Item Description")
            dt.Columns.Add("Unit Of Measure")
            dt.Columns.Add("Item Class")
            dt.Columns.Add("Current Cost")

            DtGridView.DataSource = dt
            Dim Str As String
            Dim VendID As String
            Str = cboVendor.Text
            VendID = Str.Substring(0, 6)

    dt.Rows.Add(String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) & "-" & "0551", VendID, CboSize.Text, TxtItmDesc.Text, CboUOM.Text, CboItmClass.Text, TxtCurrCost.Text)       

            DtGridView.AllowUserToAddRows = False
            BtnAddRows.Enabled = False
     End Sub

    The problem is I want to store the 0551 value in a table and pull it from there and not want to hard code and when I Choose the Size as Shoes it should add 11 rows to the Grid just the change in the size, please let me know how can this be accomplished.

    All help highly appreciated.

    Thanks,


    Farquest

    • Marked as answer by Youen Zen Friday, June 21, 2013 8:20 AM
    Thursday, June 6, 2013 6:25 PM
  • Thanks Guys for all your support, I solved my problem and works great.

    Thanks Again.


    Farquest

    Thursday, June 6, 2013 11:29 PM