SQL and VB.NET - Problem with UPDATE

[Jeremy Noye]

Hello all. I've had many problems solved for me by daniweb users without ever even joining. Now, though, I've run into a problem that I can't seem to solve. It feels like a very stupid problem that will probably have an easy answer, but alas I have not found it yet. Bear in mind I've only been programming with VB for a few months now and have just recently started using SQL commands to control database procedures.

The Problem:

I have a form for tracking vacation slots available for any given day. Each day in the fiscal year has it's own row, and the table has roughly 30 columns. This form only changes 6 fields based on the values in the 6 text boxes. The user selects a date with a DateTimePicker. I want the user to be able to save the new values back to the database. I've completed another form already using one technique and it worked perfectly. That form was for tracking employee information. For this form it is not working. I've also tried another approach, but that is not working either.

The first approach I tried, which worked perfectly for my employee form, goes something like this:

m_dtCalendar.Rows(m_rowPosition)("112T") = txt112T.Text.ToString()
m_dtCalendar.Rows(m_rowPosition)("112U") = txt112U.Text.ToString()
m_dtCalendar.Rows(m_rowPosition)("115T") = txt115T.Text.ToString()
m_dtCalendar.Rows(m_rowPosition)("115U") = txt115U.Text.ToString()
m_dtCalendar.Rows(m_rowPosition)("117T") = txt117T.Text.ToString()
m_dtCalendar.Rows(m_rowPosition)("117U") = txt117U.Text.ToString()

m_daDataAdapter2.Update(m_dtCalendar)

This seemed to work quite well for my other form, but in this situation it gives me an error I don't really understand. "Syntax Error (missing operator) in query expression". I searched for hours on this error and realized that I should probably create my own update code so I have better control over what is happening.

This is the subroutine I came up with:

Private Sub UpdateDatabase(ByVal v112T, ByVal v112U, ByVal v115T, ByVal v115U, ByVal v117t, ByVal v117U, ByVal vVacDay)
Dim sqlCommand As New OleDbCommand

sqlCommand.CommandText = "UPDATE Calendar SET 112T='" & v112T & "', 112U='" & v112U & "', 115T=" & v115T & "', 115U='" & v115U & "', 117T='" & v117t & "', 117U='" & v117U & "' WHERE VacDay=#" & vVacDay & "#"

End Sub

Then I call the routine, fill the values with the info from the text boxes, and try to update the datatable. I don't get the error any more, but nothing is saved. Here is my implementation of the UpdateDatabase subroutine:

Dim vv112T As New Integer
Dim vv112U As New Integer
Dim vv115T As New Integer
Dim vv115U As New Integer
Dim vv117T As New Integer
Dim vv117U As New Integer
Dim vvVacDay As New Date

vvVacDay = dtpCheckDate.Text
vv112T = txt112T.Text
vv112U = txt112U.Text
vv115T = txt115T.Text
vv115U = txt115U.Text
vv117T = txt117T.Text
vv117U = txt117U.Text

UpdateDatabase(vv112T, vv112U, vv115T, vv115U, vv117T, vv117U, vvVacDay)

m_daDataAdapter2.Update(m_dtCalendar)

Before too long I will be into double digit hours working on this problem. If anyone can help I would greatly appreciate it. If someone wants some more of the code I wouldn't mind sending it to them, but since this program will be used for work, I don't really want to post too much online. I hope I have given enough information!!

Sincerely,
Jeremy

[Jeremy Noye]

I forgot to mention that I am not getting paid for this. It is for work, but I am doing it on my own time. It is simply to make my life and those of my coworkers go much more smoothly.