I've used this code, but it doesn't seem to updating my database. Does anyone have any suggestions??


Dim bAgencyStaff As Integer
Dim ssql As String

ssql = "insert into casual_staff_used (date_booked, casual_staff_code, agency_staff, hours_worked, per_hour) values (" & dtBookingDate & "," & cboStaffName.ItemData(cboStaffName.ListIndex) & "," & bAgencyStaff & "," & txtHours.Text & "," & txtRate.Text & ")"

If optAgency = True Then
bAgencyStaff = 0
Else
bAgencyStaff = 1
End If

adoCN.Open
adoCN.BeginTrans
adoCN.Execute ssql
adoCN.CommitTrans
adoCN.Close

Unload Me
End Sub

This message was edited by Flakes at 2004-9-10 3:31:24

---

: I've used this code, but it doesn't seem to updating my database. Does anyone have any suggestions??
:
:
: Dim bAgencyStaff As Integer
: Dim ssql As String
:
: ssql = "insert into casual_staff_used (date_booked, casual_staff_code, agency_staff, hours_worked, per_hour) values (" & dtBookingDate & "," & cboStaffName.ItemData(cboStaffName.ListIndex) & "," & bAgencyStaff & "," & txtHours.Text & "," & txtRate.Text & ")"
here
If optAgency = True Then
: bAgencyStaff = 0
: Else
: bAgencyStaff = 1
: End If
:
: adoCN.Open
: adoCN.BeginTrans
: adoCN.Execute ssql
: adoCN.CommitTrans
: adoCN.Close
:
: Unload Me
: End Sub
:


What do you see when you put a
debug.print ssql
or a
msgbox ssql

where i have marked in red?

: This message was edited by Flakes at 2004-9-10 3:31:24

---

: : I've used this code, but it doesn't seem to updating my database. Does anyone have any suggestions??
: :
: :
: : Dim bAgencyStaff As Integer
: : Dim ssql As String
: :
: : ssql = "insert into casual_staff_used (date_booked, casual_staff_code, agency_staff, hours_worked, per_hour) values (" & dtBookingDate & "," & cboStaffName.ItemData(cboStaffName.ListIndex) & "," & bAgencyStaff & "," & txtHours.Text & "," & txtRate.Text & ")"
: here
: If optAgency = True Then
: : bAgencyStaff = 0
: : Else
: : bAgencyStaff = 1
: : End If
: :
: : adoCN.Open
: : adoCN.BeginTrans
: : adoCN.Execute ssql
: : adoCN.CommitTrans
: : adoCN.Close
: :
: : Unload Me
: : End Sub
: :
:
:
: What do you see when you put a
: debug.print ssql
: or a
: msgbox ssql
:
: where i have marked in red?

Also, check whether such an INSERT query works - perhaps the database returns a SQL error, which you don't get because it isn't a VB error but still causes the query to fail.

: This message was edited by Flakes at 2004-9-10 3:31:24

---

: : I've used this code, but it doesn't seem to updating my database. Does anyone have any suggestions??
: :
: :
: : Dim bAgencyStaff As Integer
: : Dim ssql As String
: :
: : ssql = "insert into casual_staff_used (date_booked, casual_staff_code, agency_staff, hours_worked, per_hour) values (" & dtBookingDate & "," & cboStaffName.ItemData(cboStaffName.ListIndex) & "," & bAgencyStaff & "," & txtHours.Text & "," & txtRate.Text & ")"
: here
: If optAgency = True Then
: : bAgencyStaff = 0
: : Else
: : bAgencyStaff = 1
: : End If
: :
: : adoCN.Open
: : adoCN.BeginTrans
: : adoCN.Execute ssql
: : adoCN.CommitTrans
: : adoCN.Close
: :
: : Unload Me
: : End Sub
: :
:
:
: What do you see when you put a
: debug.print ssql
: or a
: msgbox ssql
:
: where i have marked in red?
:
: it prints out the query correctly but it says something about date not being able to be added as a value for date_booked column


insert into casual_staff_used (date_booked, casual_staff_code, agency_staff, hours_worked, per_hour) values (10/09/2004,13,0,7,7)
:

: : This message was edited by Flakes at 2004-9-10 3:31:24

---

: : : I've used this code, but it doesn't seem to updating my database. Does anyone have any suggestions??
: : :
: : :
: : : Dim bAgencyStaff As Integer
: : : Dim ssql As String
: : :
: : : ssql = "insert into casual_staff_used (date_booked, casual_staff_code, agency_staff, hours_worked, per_hour) values (" & dtBookingDate & "," & cboStaffName.ItemData(cboStaffName.ListIndex) & "," & bAgencyStaff & "," & txtHours.Text & "," & txtRate.Text & ")"
: : here
: : If optAgency = True Then
: : : bAgencyStaff = 0
: : : Else
: : : bAgencyStaff = 1
: : : End If
: : :
: : : adoCN.Open
: : : adoCN.BeginTrans
: : : adoCN.Execute ssql
: : : adoCN.CommitTrans
: : : adoCN.Close
: : :
: : : Unload Me
: : : End Sub
: : :
: :
: :
: : What do you see when you put a
: : debug.print ssql
: : or a
: : msgbox ssql
: :
: : where i have marked in red?
: :
: : it prints out the query correctly but it says something about date not being able to be added as a value for date_booked column
:
:
: insert into casual_staff_used (date_booked, casual_staff_code, agency_staff, hours_worked, per_hour) values (10/09/2004,13,0,7,7)
: :
:
:

try this
ssql = "insert into casual_staff_used (date_booked, casual_staff_code, agency_staff, hours_worked, per_hour) values ('" & dtBookingDate & "'," & cboStaffName.ItemData(cboStaffName.ListIndex) & "," & bAgencyStaff & "," & txtHours.Text & "," & txtRate.Text & ")"


I just added the single quotes around the date value