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.
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!!