Updating database using textbox in Vb.Net

[yvrej17]

Hi, I need a little help here. I'm Using vs 2005 with ms access 2003 database and finding myself how to get rid of this error "Syntax Error on UPDATE Statement" can you take a look at my codes below and tell me what's wrong? Thank you.

Public Class Transactions

    Dim cnnOLEDB As New OleDbConnection
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand

    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\LibraryDatabase.mdb"

    Private Sub Transactions_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.CenterToScreen()
        cnnOLEDB.ConnectionString = strConnectionString
        cnnOLEDB.Open()
End Sub

   Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click

        i = "SELECT Lib_Book1,Lib_Book2,Lib_Book3 FROM members WHERE ID=" & txtmem_id.Text & " ;"
        j = "SELECT ID FROM books WHERE ID=" & txtbook_id.Text & " ;"

        If txtmem_id.Text <> "" And txtbook_id.Text <> "" And txtborrow_from.Text <> "" And txtborrow_until.Text <> "" Then
            cmdUpdate.CommandText = "UPDATE books SET Borrowed_By, Bk_Status, Bk_From, Bk_Due = '" & txtmem_id.Text & "', '" & "Borrowed" & "', '" & txtborrow_from.Text & "', '" & txtborrow_until.Text & "' WHERE ID = " & txtbook_id.Text & ";"


            cmdUpdate.CommandType = CommandType.Text
            cmdUpdate.Connection = cnnOLEDB
            cmdUpdate.ExecuteNonQuery()
            MsgBox("Transaction Recorded.")
            txtmem_id.Text = String.Empty
            txtbook_id.Text = String.Empty
            txtborrow_from.Text = String.Empty
            txtborrow_until.Text = String.Empty
        Else
            MsgBox("Error to be set later!")

        End If
        cmdUpdate.Dispose()

    End Sub

Please help :(

[artemix22]

split it.

UPDATE table_name SET column1=value, column2=value2,... WHERE some_column = some_value

[yvrej17]

I already did it bro but it display an error "Data type mismatch in criteria expression" I'm just new in vb.net below is the modified code base on your reply.

"UPDATE books SET Borrowed_By = '" & "h" & "', Bk_Status = '" & "Borrowed" & "', Bk_From = '" & txtborrow_from.Text & "', Bk_Due = '" & txtborrow_until.Text & "' WHERE ID = " & txtbook_id.Text & ";"

[artemix22]

"UPDATE books SET Borrowed_By = 'h', Bk_Status = 'Borrowed', Bk_From = '" & txtborrow_from.Text & "', Bk_Due = '" & txtborrow_until.Text & "' WHERE ID = '" & txtbook_id.Text & "';"

[yvrej17]

Same error occured bro... I'm thinking if the problem is in the code or is it in my database >.< noob me.. anyway bro thanx a lot. hmmm tommorrrow morning is our defence for this system and yet I can't fix the error :(

[artemix22]

i'm just curious how you declare i and j?

[yvrej17]

It works! :) the error was gone the problem is in my database where I set the borrower column to number instead of text that's why it displays an error datatype mismatch.. thanx a lot for your big help bro.. Someday I'll pay you back :) thanx again.

[artemix22]

:D no need to pay, it's just for fun, here is what i do with same result :

    Public Class Transactions

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click

    Using cnnOLEDB As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\LibraryDatabase.mdb")
       Try
       cnnOLEDB.Open()
       Dim cmdOLEDB As New OleDbCommand("UPDATE books SET Borrowed_By = 'h', Bk_Status = 'Borrowed', Bk_From = '" & txtborrow_from.Text & "', Bk_Due = '" & txtborrow_until.Text & "' WHERE ID = '" & txtbook_id.Text & "';")
       cmdOLEDB.ExecuteNonQuery()
       MsgBox("Transaction Recorded.")
       cmdOLEDB.Dispose()
       cnnOLEDB.Close()

       txtmem_id.Text = String.Empty
       txtbook_id.Text = String.Empty
       txtborrow_from.Text = String.Empty
       txtborrow_until.Text = String.Empty

    End Using
    Catch Ex As Exception
       msgbox(Ex.Message)
    End Try
    End Sub

    End Class

[yvrej17]

Catch Ex As Exception? for what is that bro? is that code better than before?

[yvrej17]

Bro can I ask one more thing? About this line
UPDATE books SET Borrowed_By = 'h',

I declared it as

h = "SELECT Lib_Name FROM members WHERE ID=" & txtmem_id.Text & " ;"

where txtmem_id.Text is the value for a textbox named textmem_id

The problem is it's not reflecting in my database. What could be the problem? Sorry for my noob questions. I'm still in the process of learning vb.net. thanx in advance.

[artemix22]

try catch block is to handle any error for your app, if you got some error, it will throw the reason of error, so you will easy to find the problem and fix it and it will avoid from application crash.

[yvrej17]

Hello? Still here? :)

[artemix22]

yarp

[yvrej17]

My question is posted before your reply :) can you still help me if you don't mind... :) Thankz

[yvrej17]

Additional question bro..
Scenario: As the code I use above if I input the row ID I can get it's datas/infos to update the other table as stated in the code above but what if I enter an ID which is not existing in the rows of table how will I code an if else for that to display an errror that ther's no record found?

[artemix22]

i think it can't do like that, it's about book's rental, so you have to provides a table first in your application to handle member list, based on that table, you can pass its member_id to the next table (table rental) and choose what book are they borrow. you can't pass with query like that. that's my opinion. i will give you some UI example, hope give you some idea.

[yvrej17]

I have 3 tables in ms access 1 is for book records 2nd is for members and 3rd is for fees and fine. I just want to capture some data from the borrower like his name which is in the members table and update the books table with his name as borrower. Is it possible? and what if i input a wrong id which is not existing how will i display that the record was not found?

[yvrej17]

Here's the full source code bro.. http://www.sendspace.com/file/lfcu72

[artemix22]

ok. i have see it, and here is what i suggest :

first, if i were you, i will create 2 form with table on it to hold member list and book list.

in book transaction, i will add two "button add" like picture1 below :

http://i1184.photobucket.com/albums/z328/waluhkuning/for%20share/toyou1.jpg

when someone come to rent a book, you just simply click "button add" member to give you name of that member and his/her id from database, like picture2 below :

http://i1184.photobucket.com/albums/z328/waluhkuning/for%20share/toyou2.jpg

simply double click the name of member, then :

http://i1184.photobucket.com/albums/z328/waluhkuning/for%20share/toyou3.jpg

same thing with book, you simply click "button add" book to give you book tittle and its id, like picture3 below :

http://i1184.photobucket.com/albums/z328/waluhkuning/for%20share/toyou4.jpg

http://i1184.photobucket.com/albums/z328/waluhkuning/for%20share/toyou5.jpg

based on that, you can issue it without this anymore : h = "SELECT Lib_Name FROM members WHERE ID=" & txtmem_id.Text & " ;"
and you will not found error "no record" again, because that both table came from database.

[yvrej17]

OMG! That idea is genius! But I'm not a pro to code like what you did in the UI. It's so amazing when I did it but sadly I can't :( I'm just a student starting to learn vb.net and more on searching codes for editing and applying to my app.

[yvrej17]

Bro can I take a peak of that source in your UI screenshot? I want it that way but I can't code it like what you did.

[artemix22]

then start() it :D, first add two form to your application, first form to hold member list and second form to hold book list, on button click event for member >> Form1.Show(), and for button click event for book >> Form2.show(), on LoadEvent Form1 bind your whole member to it, same thing for Form2 bind your whole book list to it, it's not so difficult as you imagine if you try. Trust Me!

wait for a hour.. maybe i will post it to you, i will fix something first.

[yvrej17]

You mean the 2 forms will contain 1 datagridview each? Then I ahave to click a row on it to display it's ID on textbox?

[artemix22]

yarp, that's the point what i told. simply double click the row, it will close automatically then send the id you've been click to the textbox.

[yvrej17]

Wow.. I'll wait for it :) thanx a lot in advance.

[artemix22]

what is the rule for fee and fine? can you expalin more?

[yvrej17]

Every member will pay 400 pesos for registration for 1 year.
About the Fine when a member borrow a book and exceeds it's due date a fine of 10pesos per day will be collected. every time a member borrow a book he must return it in 3 days.

In the Fee/Fine button it display a record of fine = textbox, fee = another textbox and total = another textbox also. if a new member register automaticaly a 400pesos will be added at the fee textbox and also the total will be updated. if a member fail to return a book for example he must return on dec. 25 but he returned it on dec. 27 then 20pesos will automaticaly added to fine textbox and total will also be updated.

[yvrej17]

I'm preserving the registration form for an automatic addition for fees in the transaction form.. I'm just finishing the other forms before I continue to work on fine/fees section. Your help is very much appreciated. I don't know how to pay you for that..

[artemix22]

i was write 80% code now, i need something to be clear first to complete it.

1. Registration = 400 Pesos for 1 year.
2. 10 Pesos will collected for one day if due date exceeded.

let's forget about program/application and we go to the bussiness calculation :D, my question is :

1. is registration fee (400 Pesos) reduced everytime member borrow one book?
2. if yes, how much it will reduced for one book?
3. if no, how much fee for one book? is it free? because they already pay 400 Pesos? and 400 Pesos only reduced when due date exceeded (10 Pesos one day); or, if not reduced at all, is each book has a price to pay everytime member borrow it?

i'm just curious, is your profit just came from registration and fine? :D

[yvrej17]

Registration = 400 Pesos for 1 year.
10 Pesos will collected for one day if due date exceeded.

Every member has 3 cards. If they borrow a book they have to use their cards. 1 card = 1 book. If a member already borrowed 3 books then it means he already uses 3 cards so he can't borrow anymore unless he return the book. Borrowing of books is free as long us it was returned on or before due date (3days).