Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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

  • FlakesFlakes Posts: 642Member
    [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?


  • GenjuroGenjuro Posts: 913Member
    : [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.
  • Dana RoyDana Roy Posts: 5Member
    : [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)
    :

  • FlakesFlakes Posts: 642Member
    : : [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.