datagrid/database cannot update.

Question

BeReady 0 Newbie Poster

15 Years Ago

hi all,
i am doing payment form using datagridview in vb.net2008

when item add to item list
it will automatic display on my datagrid.

the problem now is,
the datagrid will display out my data when i press ADD Button twice .

here is my code..
am i make a mistake ?? plss help...

the module1.connect is use to connect to my Access database

Add Button

Private Sub uiAddButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles uiAddButton.Click

        Dim StockIdsearch As String = "select Stock_ID,Selling_Price from Stock WHERE Stock_Name = '" & Me.uiItemtxt.Text & "'"
        Dim Adapter As New OleDbDataAdapter(StockIdsearch, Module1.connect)
        Dim dt As New DataTable("Stock")
        Adapter.Fill(dt)
        Dim stockid As String = dt.Rows(0).Item(0)
        Dim price As Double = dt.Rows(0).Item(1)
        Dim caltotal As Double = Val(price) * Val(Me.uiQuantitytxtbox.Text)
        Format(caltotal, "#####.##")

        Dim count_total As String = "SELECT max(Item_No) FROM Invoice WHERE Invoice_ID='" & Me.uiInvoiceNotxt.Text & "'"
        Dim countAD As New OleDbDataAdapter(count_total, Module1.connect)
        Dim countdt As New DataTable("count")
        countAD.Fill(countdt)

        Dim count As Integer
        If countdt.Rows(0).Item(0) Is DBNull.Value Then
            count = 1
            Dim insertinvoice As String = "INSERT INTO invoice(invoice_id,item_no,invoice_date,plate_no,contact_number,stock_id,Amount,price) VALUES('" & Me.uiInvoiceNotxt.Text & "'," & count & ",'" & Me.uiDatetxt.Text & "','" & Me.uiPlateNotxt.Text & "'," & Me.uicontactnotxtbox.Text & ",'" & stockid & "'," & Me.uiQuantitytxtbox.Text & ",'" & caltotal & "')"
            Dim insertinvoicecom As New OleDbCommand(insertinvoice, Module1.connect)
            insertinvoicecom.ExecuteNonQuery()

        Else
            count = countdt.Rows(0).Item(0) + 1
            Dim Iupdatecheck As String = "SELECT stock_ID,Amount,Price FROM Invoice WHERE Invoice_ID='" & Me.uiInvoiceNotxt.Text & "' AND stock_ID='" & stockid & "'"
            Dim IupdatecheckAD As New OleDbDataAdapter(Iupdatecheck, Module1.connect)
            Dim Iupdatecheckdt As New DataTable("Checking")
            IupdatecheckAD.Fill(Iupdatecheckdt)

            If Iupdatecheckdt.Rows.Count = 0 Then
                Dim insertinvoice As String = "INSERT INTO invoice(invoice_id,item_no,invoice_date,plate_no,contact_number,stock_id,Amount,price) VALUES('" & Me.uiInvoiceNotxt.Text & "'," & count & ",'" & Me.uiDatetxt.Text & "','" & Me.uiPlateNotxt.Text & "'," & Me.uicontactnotxtbox.Text & ",'" & stockid & "'," & Me.uiQuantitytxtbox.Text & ",'" & caltotal & "')"
                Dim insertinvoicecom As New OleDbCommand(insertinvoice, Module1.connect)
                insertinvoicecom.ExecuteNonQuery()

            Else
                Dim amount As Integer
                Dim newtotal As Integer
                amount = Iupdatecheckdt.Rows(0).Item(1) + Me.uiQuantitytxtbox.Text
                newtotal = amount * Val(price)
                Dim updateinvoice As String = "UPDATE invoice SET Amount=" & amount & ",price='" & newtotal & "' WHERE Invoice_ID='" & Me.uiInvoiceNotxt.Text & "' AND Stock_ID = '" & stockid & "'"
                Dim updateinvoicecom As New OleDbCommand(updateinvoice, Module1.connect)
                updateinvoicecom.ExecuteNonQuery()

            End If
        End If
        Module1.close()
        invoice()
    End Sub

Invoice()

Public Sub invoice()
        Try
            Dim invoicedetail As String = "SELECT invoice.Item_No, invoice.Amount,invoice.Price,stock.Stock_Name FROM stock LEFT JOIN invoice ON stock.stock_ID = invoice.stock_ID WHERE Invoice_ID ='" & Me.uiInvoiceNotxt.Text & "'"
            Dim invoicedetailDA As New OleDbDataAdapter(invoicedetail, Module1.connect)
            Dim itemlist As New DataTable("invoice")
            invoicedetailDA.Fill(itemlist)
            uiitemlistdatagrid.DataSource = itemlist
            uiStockDetailDataGrid.Refresh()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
    End Sub

vb.net

2 Contributors
6 Replies
122 Views
1 Day Discussion Span
Latest Post 15 Years Ago Latest Post by BeReady

c0deFr3aK 1 Junior Poster in Training

15 Years Ago

hi all,

Private anyVariable As String
Private Sub uiAddButton_Click(ByVal sender As System.Object, 
                Dim updateinvoicecom As New OleDbCommand(updateinvoice, Module1.connect)
                updateinvoicecom.ExecuteNonQuery()

            End If
        End If
        Module1.close()
        anyVariable = Me.uiInvoiceNotxt.Text
        invoice()
    End Sub

Public Sub invoice()
        Try
            Dim invoicedetail As String = "SELECT invoice.Item_No, invoice.Amount,invoice.Price,stock.Stock_Name FROM stock LEFT JOIN invoice ON stock.stock_ID = invoice.stock_ID WHERE Invoice_ID ='" & anyVariable & "'"
            Dim invoicedetailDA As New OleDbDataAdapter(invoicedetail, Module1.connect)
            Dim itemlist As New DataTable("invoice")
            invoicedetailDA.Fill(itemlist)
            uiitemlistdatagrid.DataSource = itemlist
            uiStockDetailDataGrid.Refresh()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
    End Sub

I added "Private Sub anyVariable As String" and assign a value from Me.uiInvoiceNotxt.Text before calling invoice sub.
Please take note of the changes in your invoice sub SQL statement in WHERE clause.

Hope it will solved your problem.

c0deFr3aK 1 Junior Poster in Training

15 Years Ago

Ok here's the trick you called the invoice sub outside the If statement so basically invoice sub will be called first before you actually write the data in your database.

Place it inside your last Enf If statement as shown below.

Private Sub uiAddButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles uiAddButton.Click

       
        If countdt.Rows(0).Item(0) Is DBNull.Value Then
            count = 1
            Dim insertinvoice As String = "INSERT INTO invoice(invoice_id,item_no,invoice_date,plate_no,contact_number,stock_id,Amount,price) VALUES('" & Me.uiInvoiceNotxt.Text & "'," & count & ",'" & Me.uiDatetxt.Text & "','" & Me.uiPlateNotxt.Text & "'," & Me.uicontactnotxtbox.Text & ",'" & stockid & "'," & Me.uiQuantitytxtbox.Text & ",'" & caltotal & "')"
            Dim insertinvoicecom As New OleDbCommand(insertinvoice, Module1.connect)
            insertinvoicecom.ExecuteNonQuery()

        Else
            count = countdt.Rows(0).Item(0) + 1
            Dim Iupdatecheck As String = "SELECT stock_ID,Amount,Price FROM Invoice WHERE Invoice_ID='" & Me.uiInvoiceNotxt.Text & "' AND stock_ID='" & stockid & "'"
            Dim IupdatecheckAD As New OleDbDataAdapter(Iupdatecheck, Module1.connect)
            Dim Iupdatecheckdt As New DataTable("Checking")
            IupdatecheckAD.Fill(Iupdatecheckdt)

            If Iupdatecheckdt.Rows.Count = 0 Then
                Dim insertinvoice As String = "INSERT INTO invoice(invoice_id,item_no,invoice_date,plate_no,contact_number,stock_id,Amount,price) VALUES('" & Me.uiInvoiceNotxt.Text & "'," & count & ",'" & Me.uiDatetxt.Text & "','" & Me.uiPlateNotxt.Text & "'," & Me.uicontactnotxtbox.Text & ",'" & stockid & "'," & Me.uiQuantitytxtbox.Text & ",'" & caltotal & "')"
                Dim insertinvoicecom As New OleDbCommand(insertinvoice, Module1.connect)
                insertinvoicecom.ExecuteNonQuery()

            Else
                Dim amount As Integer
                Dim newtotal As Integer
                amount = Iupdatecheckdt.Rows(0).Item(1) + Me.uiQuantitytxtbox.Text
                newtotal = amount * Val(price)
                Dim updateinvoice As String = "UPDATE invoice SET Amount=" & amount & ",price='" & newtotal & "' WHERE Invoice_ID='" & Me.uiInvoiceNotxt.Text & "' AND Stock_ID = '" & stockid & "'"
                Dim updateinvoicecom As New OleDbCommand(updateinvoice, Module1.connect)
                updateinvoicecom.ExecuteNonQuery()

            End If
 Module1.close()
       anyVariable = Me.uiInvoiceNotxt.Text
        invoice()
        End If
       
    End Sub

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

BeReady 0 Newbie Poster · Answer 1 · 2009-04-22T12:05:23+00:00

c0deFr3aK
i tried ur code , but it still need to press the add button twice to display out the data in datagrid ><

but i found out something that is when i put a msgbox at the addbutton sub . it work..
but pop-up a msgbox everytime a user add item is like very annoy.

is there any way can i do except putting a msgbox ?
or something like refresh the main menu ?

im new to vb.net ><"

thx for ur help...

BeReady 0 Newbie Poster · Answer 2 · 2009-04-22T15:46:45+00:00

i change the code as u said but the problem still there ...
according to my code, the data should be inserted to database before the data retrieve out rite ?
i thinking izzit possible the database havent insert on 1st time click ???

Private Sub uiAddButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles uiAddButton.Click

        Try
            Dim StockIdsearch As String = "select Stock_ID,Selling_Price from Stock WHERE Stock_Name = '" & Me.uiItemtxt.Text & "'"
            Dim Adapter As New OleDbDataAdapter(StockIdsearch, Module1.connect)
            Dim dt As New DataTable("Stock")
            Adapter.Fill(dt)
            Dim stockid As String = dt.Rows(0).Item(0)
            'MsgBox(stockid)
            Dim price As Double = dt.Rows(0).Item(1)
            'MsgBox(price)
            Dim caltotal As Double = Val(price) * Val(Me.uiQuantitytxtbox.Text)
            Format(caltotal, "#####.##")
            'MsgBox(caltotal)

            Dim count_total As String = "SELECT max(Item_No) FROM Invoice WHERE Invoice_ID='" & Me.uiInvoiceNotxt.Text & "'"
            Dim countAD As New OleDbDataAdapter(count_total, Module1.connect)
            Dim countdt As New DataTable("count")
            countAD.Fill(countdt)

            Dim count As Integer
            If countdt.Rows(0).Item(0) Is DBNull.Value Then
                count = 1
                Dim insertinvoice As String = "INSERT INTO invoice(invoice_id,item_no,invoice_date,plate_no,contact_number,stock_id,Amount,price) VALUES('" & Me.uiInvoiceNotxt.Text & "'," & count & ",'" & Me.uiDatetxt.Text & "','" & Me.uiPlateNotxt.Text & "'," & Me.uicontactnotxtbox.Text & ",'" & stockid & "'," & Me.uiQuantitytxtbox.Text & ",'" & caltotal & "')"
                Dim insertinvoicecom As New OleDbCommand(insertinvoice, Module1.connect)
                insertinvoicecom.ExecuteNonQuery()
                'MsgBox("count 1 insert successfull")
                'invoiceno = Me.uiInvoiceNotxt.Text
                'invoice()
            Else
                count = countdt.Rows(0).Item(0) + 1
                Dim Iupdatecheck As String = "SELECT stock_ID,Amount,Price FROM Invoice WHERE Invoice_ID='" & Me.uiInvoiceNotxt.Text & "' AND stock_ID='" & stockid & "'"
                Dim IupdatecheckAD As New OleDbDataAdapter(Iupdatecheck, Module1.connect)
                Dim Iupdatecheckdt As New DataTable("Checking")
                IupdatecheckAD.Fill(Iupdatecheckdt)


                If Iupdatecheckdt.Rows.Count = 0 Then
                    Dim insertinvoice As String = "INSERT INTO invoice(invoice_id,item_no,invoice_date,plate_no,contact_number,stock_id,Amount,price) VALUES('" & Me.uiInvoiceNotxt.Text & "'," & count & ",'" & Me.uiDatetxt.Text & "','" & Me.uiPlateNotxt.Text & "'," & Me.uicontactnotxtbox.Text & ",'" & stockid & "'," & Me.uiQuantitytxtbox.Text & ",'" & caltotal & "')"
                    Dim insertinvoicecom As New OleDbCommand(insertinvoice, Module1.connect)
                    insertinvoicecom.ExecuteNonQuery()
                    'MsgBox("count > 1 insert successfully")
                Else
                    Dim amount As Integer
                    Dim newtotal As Integer
                    amount = Iupdatecheckdt.Rows(0).Item(1) + Me.uiQuantitytxtbox.Text
                    newtotal = amount * Val(price)
                    Dim updateinvoice As String = "UPDATE invoice SET Amount=" & amount & ",price='" & newtotal & "' WHERE Invoice_ID='" & Me.uiInvoiceNotxt.Text & "' AND Stock_ID = '" & stockid & "'"
                    Dim updateinvoicecom As New OleDbCommand(updateinvoice, Module1.connect)
                    updateinvoicecom.ExecuteNonQuery()
                    'MsgBox("count > 1 insert successfully")
                End If
                Module1.close()
                invoiceno = Me.uiInvoiceNotxt.Text
                invoice()
            End If

        Catch ex As OleDbException
            MsgBox(ex.ToString)
        End Try
        
    End Sub

c0deFr3aK 1 Junior Poster in Training · Answer 3 · 2009-04-23T06:07:30+00:00

Else
Dim amount As Integer
Dim newtotal As Integer
amount = Iupdatecheckdt.Rows(0).Item(1) + Me.uiQuantitytxtbox.Text
newtotal = amount * Val(price)
Dim updateinvoice As String = "UPDATE invoice SET Amount=" & amount & ",price='" & newtotal & "' WHERE Invoice_ID='" & Me.uiInvoiceNotxt.Text & "' AND Stock_ID = '" & stockid & "'"

ohh, by the way if your second "If Statement" will execute nothings happen then move up the calling of your invoice sub, so place it inside like below.

Dim updateinvoicecom As New OleDbCommand(updateinvoice, Module1.connect)
                    updateinvoicecom.ExecuteNonQuery()
                    'MsgBox("count > 1 insert successfully")


                Module1.close()
                invoiceno = Me.uiInvoiceNotxt.Text
                invoice()
                End If  'Second If statement
            End If    'First If statement

        Catch ex As OleDbException
            MsgBox(ex.ToString)
        End Try
        
    End Sub

BeReady 0 Newbie Poster · Answer 4 · 2009-04-23T10:46:49+00:00

><"
still cannot !
but nevermind , i plan to re-do it .
maybe mistake on somewhere.
anyway thx u for helping me along ^^