executing insert statements to a database - Programmers Heaven

Howdy, Stranger!

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

Categories

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.