How to update MS Access Database (vb.net)

Question

I am created a journal program for an internship project and I am using a MS-Access Database. I am programming in VB.net. Now, I am trying to make it so that they can "Update" their journals, meaning that they click on their calendar date and it brings them to that journal if they have one for that date. If they have one for that date then it shows the title and journal text entry for that date. I want to make it so that any changes they have made to the journal (editing the textbox fields) are also changed in the database when they click the update button. Here's what i have so far

    Private Sub COE_JournalBtn_Click(sender As System.Object, e As System.EventArgs) Handles COE_JournalBtn.Click

    If DateTimePicker1.Value <> Nothing Then

        If TitleTxt.Text <> "" Then

            If JournalTextRtxt.Text <> "" Then

                myConnection.Open()

                Dim DatePicked As String = DateTimePicker1.Value
                Dim cmd As OleDbCommand
                Dim str As String

                Try

                    MyJournalTitle = TitleTxt.Text
                    MyJournalText = JournalTextRtxt.Text

                    str = "UPDATE Journals SET JournalTitle='" & MyJournalTitle & "', JournalText='" & MyJournalText & "' WHERE JournalDate=" & DatePicked

                    cmd = New OleDbCommand(str, myConnection)

                    cmd.ExecuteNonQuery()

                    myConnection.Close()

                Catch ex As Exception

                    MessageBox.Show("There was an error processing your request. Please try again." & vbCrLf & vbCrLf & _
                                    "Original Error:" & vbCrLf & vbCrLf & ex.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                    myConnection.Close()

                End Try

                myConnection.Close()

            End If

        End If

    End If

End Sub

Now my update string by itself is

"UPDATE Journals SET JournalTitle='" & MyJournalTitle & "', JournalText='" & MyJournalText & "' WHERE JournalDate=" & DatePicked

Now, what happens, is absolutely nothing. No errorboxes come up. No messageboxes appear. The program doesn't freeze. And the database remains unchanged. What am I doing wrong? Is there an error in my code or something missing? Please help me because I really want to figure this out and i've been looking everywhere for a solution in VB.net and cannot find one that applies to me being that I am using MS-Access and NOT SQL.

Thanks in advance, Richard Paulicelli

Steve · Accepted Answer · 2012-08-06 18:08:04Z

4

Use a parametrized query to avoid Sql Injection Attacks and quoting problems

str = "Journals SET JournalTitle=?, JournalText=? WHERE JournalDate=?"
cmd = New OleDbCommand(str, myConnection) 
cmd.Parameters.AddWithValue("@jounalTitle", MyJournalTitle )
cmd.Parameters.AddWithValue("@journalText", MyJournalText)
cmd.Parameters.AddWithValue("@journalDate", DatePicked)
cmd.ExecuteNonQuery()

Using parameters will free your code from that continue quoting that expose your code to an high chance of typing errors. And you don't have any problem with Sql Injection

answered Aug 6, 2012 at 18:08

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Paulicelli Over a year ago

Unfortunetly I did try that, however it came up with a long error and failed to work. I just used Hansup's method for a quick fix, however i will look into parametrized queries for future use. Thanks for your help and input

Fionnuala Over a year ago

@RichardPaulicelli I imagine this did not work for you because you have declared DatePicked as a string. If you leave it as a date, the parameters will work and you will avoid all sorts of locale problems.

HansUp · Accepted Answer · 2012-08-06 18:09:02Z

3

You may have a problem with this part of your UPDATE statement:

"' WHERE JournalDate=" & DatePicked

If the Journals field, JournalDate, is Date/Time data type, surround the date string value with Access date delimiter characters (#).

"' WHERE JournalDate=#" & DatePicked & "#"

You can also convert your date string to yyyy-mm-dd format to avoid misinterpreting the date literal based on local.

I agree with the suggestions to use a parameter query for this instead. I'm just trying to help you understand why the original attempt may have failed.

answered Aug 6, 2012 at 18:09

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

1 Comment

Richard Paulicelli Over a year ago

Thank you so much, that was where my problem was, it was the date. And i figured that's where the problem lied being that when i put a breakpoint and analyzed the code and values of everything i noticed the hash marks but i didn't even think to physically put them in the update statement. Anyways, thank you so much for your help. Btw, the date format is actually in mm-dd-yyyy. Besides this is just an internal project, not a public release. But thank you for that note for future reference.

Collectives™ on Stack Overflow

How to update MS Access Database (vb.net)

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related