executing insert statements to a database

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

Comments

  • [b][red]This message was edited by Flakes at 2004-9-10 3:31:24[/red][/b][hr]
    : 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 & ")"
    [red]here[/red]
    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?


  • : [b][red]This message was edited by Flakes at 2004-9-10 3:31:24[/red][/b][hr]
    : : 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 & ")"
    : [red]here[/red]
    : 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.
  • : [b][red]This message was edited by Flakes at 2004-9-10 3:31:24[/red][/b][hr]
    : : 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 & ")"
    : [red]here[/red]
    : 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)
    :

  • : : [b][red]This message was edited by Flakes at 2004-9-10 3:31:24[/red][/b][hr]
    : : : 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 & ")"
    : : [red]here[/red]
    : : 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
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories