sqlDataAdapter doest not refresh the query

akosidandan

Hello Experts, Good day/Good Evening Experts I am here again to ask some few questions. :) I would like to ask if why does my sqldataAdapter do not refresh the query it fill my datatable. I am making a borrow transaction for borrowing a book where in a student or ... borrow. The quantity of book will decrease depending on the quantity of book borrowed.My problem is that my sqlDataAdapter to load the table of my bookRecords does not refresh what it need to fill.Confuse :confused:why does it refresh my sql query. Below here is my code:

'save the borrow transaction
Public Sub SaveBorrowTransaction(ByVal txtBorrowerID As TextBox, ByVal txtLastName As TextBox, _
ByVal txtFirstName As TextBox, ByVal txtMI As TextBox, _
ByVal txtContactNo As TextBox, ByVal cboPosition As ComboBox, _
ByVal txtYear As TextBox, ByVal txtSection As TextBox, _
ByVal txtLibrarianLastName As TextBox, ByVal txtLibarianFirstName As TextBox, _
ByVal txtLibrarianMI As TextBox, ByVal cboNoOfDays As ComboBox, _
ByVal txtTransactionID As TextBox, _
ByVal lvItems As ListView, ByVal con As SqlConnection)
Try
Dim dt As New DataTable
Dim ds As New DataSet
ds.Tables.Add(dt)
Dim da As New SqlDataAdapter

        con.Open()
        da = New SqlDataAdapter("Select \* from BorrowTransactionTable", con)
        da.Fill(dt)

        Dim dt2 As New DataTable
        Dim ds2 As New DataSet
        ds2.Tables.Add(dt2)
        Dim da2 As New SqlDataAdapter

        da2 = New SqlDataAdapter("Select \* from transactionitemstable", con)
        da2.Fill(dt2)


        Dim dt3 As New DataTable
        Dim ds3 As New DataSet
        ds3.Tables.Add(dt3)
        Dim da3 As New SqlDataAdapter

        Dim j As Integer = 0


        While j <> lvItems.Items.Count

            'for borrow transaction////////////////////////////////
            Dim newRow As DataRow = dt.NewRow
            With newRow
                .Item("txtTransactionID") = txtTransactionID.Text
                .Item("txtBorrowerID") = txtBorrowerID.Text
                .Item("txtLibrarianLastName") = txtLibrarianLastName.Text
                .Item(

Not Active

Which of the three DataAdapters are you asking about? What are you expecting to happen? It doesn't even appear you are using the first one. You are creating DataSets then never using them. I would say this could also be handled better with a stored procedure. And lastly, you're using VB X| ;P

I know the language. I've read a book. - _Madmatt

akosidandan

Hello, Sorry bout not giving good info for my question, the problem came from my 3rd dataAdapter at this part:

'update changes to quantity of book catalogue records when borrowing///////////////////
da3 = New SqlDataAdapter("Select * from BookCatalogueTable where txtAccessionNo='" lvItems.Items(j).SubItems(0).Text & "'", con)
da3.Fill(dt3)
dt3.Rows(0).BeginEdit()
dt3.Rows(0).Item("intQuantity") = Val(dt3.Rows(0).Item("intQuantity")) - 1
dt3.Rows(0).EndEdit()
Dim cb3 As New SqlCommandBuilder(da3)
da3.Update(dt3)

My da3 suppose to load again the new query while its on a loop block so that I can Subtract those quantity value,but my da3 doesn't refresh the query that will load new records. For example my da3 select query at first is "Select * from BookCatalogueTable where txtAccessionNo='1'". After I update the table the new da3 select query for example become "Select * from BookCatalogueTable where txtAccessionNo='2'".My da3 does not execute the next query instead it still using the first query. :) If my question is not yet clear I can still clarify to the best I can. I'm really confuse why my da3 doesn't load the new query. All comments and suggestion are soo much appreciated. Thanks, Dan

Not Active

As I said, this is better handled with other methods, i.e. not using a DataAdapter for everything. You creating far too much overhead and I'm sure your code doesn't perform well. Use a stored proc to handle this, or use SqlCommands directly.

I know the language. I've read a book. - _Madmatt