CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: problem with insert data to sql database table

  1. #1
    Join Date
    Dec 2007
    Posts
    42

    problem with insert data to sql database table

    hi,please help.currently facing some problem to when using th sql database.previously this program use access database.but now change to use sql database facing the incorrect syntax error near the keyword .and message runtime error -2147217900.

    this is the current code:
    Code:
    Set list_item = Me.ListView1.ListItems
        For abc = 1 To list_item.Count
            If list_item(abc).Checked = True Then
                With list_item(abc)
                     'create the insert statement
                    strSQL = "Insert into Overtime "
                    strSQL = strSQL & "Select * from [Temp] where EmployeeName" & genSQLtext(.SubItems(1), Text)
                    strSQL = strSQL & " and EmployeeNo" & genSQLtext(.SubItems(2), Text)
                    strSQL = strSQL & " and Designation" & genSQLtext(.SubItems(3), Text)
                    strSQL = strSQL & " and Department" & genSQLtext(.SubItems(4), Text)
                    strSQL = strSQL & " and Shift" & genSQLtext(.SubItems(5), Text)
                    strSQL = strSQL & " and [OvertimeDate]= '" & Format(.SubItems(6), "dd-mmm-yyyy") & "'"
                    strSQL = strSQL & " and [TimeFrom]='" & Format(.SubItems(7), "HH:mm:ss") & "' "
                    strSQL = strSQL & " and [TimeTo]= '" & Format(.SubItems(8), "HH:mm:ss") & "'"
                    strSQL = strSQL & " and [Breaktime]='" & Format(.SubItems(9), "HH:mm:ss") & "'"
                    strSQL = strSQL & " and OtRate" & genSQLtext(.SubItems(10), Text)
                    strSQL = strSQL & " and CategoryDay" & genSQLtext(.SubItems(11), Text)
                    strSQL = strSQL & " and WorkDescript" & genSQLtext(.SubItems(12), Text)
                    strSQL = strSQL & " and WorkDetail" & genSQLtext(.SubItems(13), Text)
                    strSQL = strSQL & " and othours" & genSQLtext(.SubItems(14), Text)
                    strSQL = strSQL & " and not exists (select * from Overtime where [EmployeeNo] " & genSQLtext(.SubItems(2), Text) & " and [OvertimeDate]=' " & Format(.SubItems(6), "dd-mmm-yyyy") & "' and [TimeFrom]='" & Format(.SubItems(7), "HH:ss:mm") & "'  and [TimeTo]='" & Format(.SubItems(8), "HH:mm:ss") & " ')"
                    
                    'create the delete statement
                    strSQLDel = "DELETE * FROM temp"
                    strSQLDel = strSQLDel & " where EmployeeName" & genSQLtext(.SubItems(1), Text)
                    strSQLDel = strSQLDel & " and EmployeeNo" & genSQLtext(.SubItems(2), Text)
                    strSQLDel = strSQLDel & " and Designation" & genSQLtext(.SubItems(3), Text)
                    strSQLDel = strSQLDel & " and Department" & genSQLtext(.SubItems(4), Text)
                    strSQLDel = strSQLDel & " and Shift" & genSQLtext(.SubItems(5), Text)
                    strSQLDel = strSQLDel & " and [OvertimeDate]= '" & Format(.SubItems(6), "dd-mmm-yyyy") & "'"
                    strSQLDel = strSQLDel & " and [TimeFrom]='" & Format(.SubItems(7), "HH:mm:ss") & "' "
                    strSQLDel = strSQLDel & " and [TimeTo]= '" & Format(.SubItems(8), "HH:mm:ss") & "'"
                    strSQLDel = strSQLDel & " and [Breaktime]='" & Format(.SubItems(9), "HH:mm:ss") & "'"
                    strSQLDel = strSQLDel & " and OtRate" & genSQLtext(.SubItems(10), Text)
                    strSQLDel = strSQLDel & " and CategoryDay" & genSQLtext(.SubItems(11), Text)
                    strSQLDel = strSQLDel & " and WorkDescript" & genSQLtext(.SubItems(12), Text)
                    strSQLDel = strSQLDel & " and WorkDetail" & genSQLtext(.SubItems(13), Text)
                    strSQLDel = strSQLDel & " and othours" & genSQLtext(.SubItems(14), Text)
            End With
                'Execute update statement
                Debug.Print strSQL
                conn.Execute strSQL, lngRecords, adCmdText
                      var = lngRecords + var
                 If lngRecords = 0 Then
         MsgBox "record already exists" & vbCrLf & vbCrLf & "EmployeeName" & genSQLtext(list_item(abc).SubItems(1), Text) & vbCrLf & " EmployeeNo" & genSQLtext(list_item(abc).SubItems(2), Text) & vbCrLf & " OvertimeDate" & genSQLtext(list_item(abc).SubItems(6), Text) & vbCrLf & " TimeFrom" & genSQLtext(list_item(abc).SubItems(7), Text)
                    'Debug.Print strsql
             End If
                 'Execute Delete statement
                conDataConnection.Execute strSQLDel, , adCmdText
            End If
        Next
    this is debug print result:
    Insert into Overtime Select * from [Temp] where EmployeeName= 'Amar Bahadur Basnet' and EmployeeNo= 'N045' and Designation= 'Operator' and Department= 'CPR - DD' and Shift= 'A' and [OvertimeDate]= '05-Nov-2009' and [TimeFrom]='23:00:00' and [TimeTo]= '07:00:00' and [Breaktime]='00:00:00' and OtRate= '1.0' and CategoryDay= 'OFFDAY Night' and WorkDescript= 'Buyoff' and WorkDetail and othours= '8' and not exists (select * from Overtime where [EmployeeNo] = 'N045' and [OvertimeDate]=' 05-Nov-2009' and [TimeFrom]='23:00:01' and [TimeTo]='07:00:00 ')

    please help.thanks

  2. #2
    Join Date
    Oct 2009
    Posts
    1

    Re: problem with insert data to sql database table

    hi monchichi2, i though the problem are come from the genSQLtext procedure.
    just see what is the return string from this procedure, especially if the value of the 'SubItems' is "".

    This error code will raise, when the SQL statement are wrong, and I saw in your debug print result... there is no value for WorkDetail in sql statement.

    '... and WorkDetail and ...' it should be '... and WorkDetail='' and ...'

    I hope it will help

  3. #3
    Join Date
    Dec 2007
    Posts
    42

    Re: problem with insert data to sql database table

    tolesb,
    hi,thanks your reply.ya i need to use the condition to delete employee record when the data was insert into the sql server database table.i was change the sql insert statement ,but then i still facing the problem after the data insert into the table and delete the selected employee from the Temp table.There have a syntax error incorrect syntax near '('.

    this is the current code:
    Code:
    Set rs = New ADODB.Recordset
    For Each lstsel In ListView1.ListItems
    If lstsel.Checked = True Then
    strSQL = "Insert Into [Overtime] ([EmployeeName],[EmployeeNo],[Designation], [Department],[Shift],[OvertimeDate],[TimeFrom],[TimeTo],[Breaktime], [OtRate],[CategoryDay],[WorkDescript],[WorkDetail],[othours])select top 1 " & _
               "'" & lstsel.SubItems(1) & "', " & _
               "'" & lstsel.SubItems(2) & "', " & _
               "'" & lstsel.SubItems(3) & "', " & _
               "'" & lstsel.SubItems(4) & "', " & _
               "'" & lstsel.SubItems(5) & "', " & _
               "'" & Format(lstsel.SubItems(6), "dd-mmm-yyyy") & "'," & _
               "'" & Format(lstsel.SubItems(7), "HH:mm:ss") & "'," & _
               "'" & Format(lstsel.SubItems(8), "HH:mm:ss") & "'," & _
               "'" & Format(lstsel.SubItems(9), "HH:mm:ss") & "'," & _
               "'" & lstsel.SubItems(10) & "'," & _
               "'" & lstsel.SubItems(11) & "'," & _
               "'" & lstsel.SubItems(12) & "'," & _
               "'" & lstsel.SubItems(13) & "'," & _
               "'" & lstsel.SubItems(14) & "'"
               
                 
           'del statement
               strSQLDel = "Delete from [Temp] where([EmployeeName],[EmployeeNo],[Designation], [Department],[Shift],[OvertimeDate],[TimeFrom],[TimeTo],[Breaktime], [OtRate],[CategoryDay],[WorkDescript],[WorkDetail],[othours])" & _
               "'" & lstsel.SubItems(1) & "', " & _
               "'" & lstsel.SubItems(2) & "', " & _
               "'" & lstsel.SubItems(3) & "', " & _
               "'" & lstsel.SubItems(4) & "', " & _
               "'" & lstsel.SubItems(5) & "', " & _
               "'" & Format(lstsel.SubItems(6), "dd-mmm-yyyy") & "'," & _
               "'" & Format(lstsel.SubItems(7), "HH:mm:ss") & "'," & _
               "'" & Format(lstsel.SubItems(8), "HH:mm:ss") & "'," & _
               "'" & Format(lstsel.SubItems(9), "HH:mm:ss") & "'," & _
               "'" & lstsel.SubItems(10) & "'," & _
               "'" & lstsel.SubItems(11) & "'," & _
               "'" & lstsel.SubItems(12) & "'," & _
               "'" & lstsel.SubItems(13) & "'," & _
               "'" & lstsel.SubItems(14) & "'"
    
    
          Debug.Print strSQL
      conn.Execute strSQL, lngRecords
          'conDataConnection.Execute strSQL, lngRecords
       
      Debug.Print strSQLDel
    conn.Execute strSQLDel, lngRecords
    this is the debug print result:
    Delete from [Temp] where([EmployeeName],[EmployeeNo],[Designation], [Department],[Shift],[OvertimeDate],[TimeFrom],[TimeTo],[Breaktime], [OtRate],[CategoryDay],[WorkDescript],[WorkDetail],[othours])'AJAY PANJIYAR', 'N112', 'Operator', 'CPR', 'A', '05-Nov-2009','23:00:00','07:00:00','00:00:00','1.0','OFFDAY Night','Buyoff','','8'
    please help.thanks!
    Last edited by monchichi2; October 25th, 2009 at 10:35 PM.

  4. #4
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,883

    Re: problem with insert data to sql database table

    Tolesb hit the nail on the head - you were missing a test

    ERROR HIGHLIGHTED BELOW IN RED

    Code:
    Insert into Overtime Select * from [Temp] where EmployeeName= 'Amar Bahadur Basnet' and EmployeeNo= 'N045' and Designation= 'Operator' and Department= 'CPR - DD' and Shift= 'A' and [OvertimeDate]= '05-Nov-2009' and [TimeFrom]='23:00:00' and [TimeTo]= '07:00:00' and [Breaktime]='00:00:00' and OtRate= '1.0' and CategoryDay= 'OFFDAY Night' and WorkDescript= 'Buyoff' and WorkDetail and othours= '8' and not exists (select * from Overtime where [EmployeeNo] = 'N045' and [OvertimeDate]=' 05-Nov-2009' and [TimeFrom]='23:00:01' and [TimeTo]='07:00:00 ')
    You now show us

    Code:
    Delete from [Temp] where([EmployeeName],[EmployeeNo],[Designation], [Department],[Shift],[OvertimeDate],[TimeFrom],[TimeTo],[Breaktime], [OtRate],[CategoryDay],[WorkDescript],[WorkDetail],[othours])'AJAY PANJIYAR', 'N112', 'Operator', 'CPR', 'A', '05-Nov-2009','23:00:00','07:00:00','00:00:00','1.0','OFFDAY Night','Buyoff','','8'
    Which has no tests

    You need to show us what the Query looks like after you FIXED the WORKDETAIL ERROR


    The error being reported now is because you have no tests in the SQL Query

    Code:
    where ([EmployeeName],[ etc etc
    Ie, You did not replace [EmployeeName] with EmployeeName= 'Amar Bahadur Basnet'
    Last edited by George1111; October 26th, 2009 at 09:49 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)