• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

error with basic script - SQLServer 2005

Hi,
I've this "basic script" code:

Sub Create_PM_WR_Resources (Str_WR_ID As String, Date_Assigned As String, _
                              PMP_ID As String, PMPS_ID As String, Table As String)

      Dim rs_pmps_res      As New Recordset
      Dim Fields            As String
      Dim PMP_Table            As String
      Dim WR_Table            As String
      Dim Insert_Fields      As String
      Dim Values_Str            As String
      Dim Str_Part_id      As String
      Dim Part_qty            As Integer
      Dim Status            As String
      Dim sSqlStmt            As String

      Select Case Table
      Case "tr"
            Fields = "tr_id, hours_req"
            PMP_Table = "pmpstr"
            WR_Table = "wrtr"
            Insert_Fields = "tr_id, hours_est"
      Case "pt"
            Fields = "part_id, qty_required"
            PMP_Table = "pmpspt"
            WR_Table = "wrpt"
            Insert_Fields = "part_id, qty_estimated, status"
      Case "tt"
            Fields = "tool_type, hours_req"
            PMP_Table = "pmpstt"
            WR_Table = "wrtt"
            Insert_Fields = "tool_type, hours_est"
      End Select

      rs_pmps_res.Open "SELECT " & Fields & " FROM " & PMP_Table & _
                        " WHERE pmp_id = " & sup_MakeLiteralOrNull(PMP_ID) & _
                        " AND pmps_id = '" & PMPS_ID & "'"

      Do While Not rs_pmps_res.EOF

'            ---- Explicitly set the thousands of a second values to "000" by using
'            ---- ProjDb.ISOToNativeTime( "CURRENT TIME" ) for time_assigned field
'            ---- in the "insert" statement. This resolved KB 3009644
            If Table = "pt" Then
                  Str_Part_id = "'" & rs_pmps_res.Fields( 0 ).Value & "'"
                  Part_qty = rs_pmps_res.Fields( 1 ).NumValue
                  Status = Reserve_Part( Str_Part_id, Part_qty )
                  Values_Str = "(" & Str_WR_ID & "," & _
                      ProjDb.ISOToNativeDate( Date_Assigned ) & "," & _
                      ProjDb.ISOToNativeTime( "CURRENT TIME" ) & "," & _
                      Str_Part_id & "," & CStr( Part_qty ) & ",'" & Status & "')"
            Else
                  Values_Str = "(" & Str_WR_ID & "," & _
                                  ProjDb.ISOToNativeDate( Date_Assigned ) & "," & _
                                  ProjDb.ISOToNativeTime( "CURRENT TIME" ) & ",'" & _
                                  rs_pmps_res.Fields( 0 ).Value & "'," & _
                                  CStr( rs_pmps_res.Fields( 1 ).NumValue ) & ")"
            End If
            
            Msgbox "sSqlStmt: " & sSqlStmt
            
            sSqlStmt = "INSERT INTO " & WR_Table & _
                         " (wr_id, date_assigned, time_assigned," & Insert_Fields & ")" & _
                         " VALUES " & Values_Str

            ProjDb.ExecuteSQL sSqlStmt
            
            Msgbox "sSqlStmt: " & sSqlStmt

            rs_pmps_res.MoveNext
      Loop
      rs_pmps_res.Close

End Sub

I put MsgBox to return sql statement.
I attached the screenshots with sql statement steps and the error.

How can I fix this error?

Thanks in advance!
Raf
error.zip
0
ralph_rea
Asked:
ralph_rea
  • 4
  • 3
1 Solution
 
openshacCommented:
It looks like your INSERT statement has the wrong number of columns, or you have a primary keep which auto increments. (If so use IDENTITY_INSERT ON)
0
 
RiteshShahCommented:
is wr_id IDENTITY field?
0
 
RiteshShahCommented:
as per your screen shot there is no mismatch in the filed specified in INSERT and value given so there is only one probability that you have IDENTITY column in your table. remove the field from INSERT's field and value list and you will be ok.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
openshacCommented:
If so then try:

SET IDENTITY_INSERT WR_Table ON

Open in new window

0
 
RiteshShahCommented:
openshac,

you may be right if WR_table has identity but I would not recommend that command as it will lead to create data integrity problem. rather than doing so, remove that field from INSERT list is good idea, isn't it? otherwise there is no  reason to keep IDENTITY field.
0
 
openshacCommented:
Fair point, but I was making the assumption that the user is happy that they have a good DB  design and correctly knows what the value of the identity column.

This might not be the case, but I couldn't tell.
0
 
ralph_reaAuthor Commented:
MUST I CHANGE:

      sSqlStmt = "INSERT INTO " & WR_Table & _
                         " (wr_id, date_assigned, time_assigned," & Insert_Fields & ")" & _
                         " VALUES " & Values_Str

WITH:

      sSqlStmt = "INSERT INTO " & WR_Table & _
                         " (date_assigned, time_assigned," & Insert_Fields & ")" & _
                         " VALUES " & Values_Str

??
0
 
RiteshShahCommented:
yes, remove wr_id from INSERT and don't forget to remove its value from Values_Str variable also.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now