CODEWITHSUNDEEP
sqlvb.nett-sqlstring.format
user1216456
user1216456

Reputation:

Error upon insertion ...Index (zero based) must be greater than or equal to zero and less than the size of the argument list

I get a "Index (zero based) must be greater than or equal to zero and less than the size of the argument list." error in the following Insert command (see the insert sql command).. I already corrected the parameter list and I am referring to the correct table....

 Dim dtResultParentGUID As DataTable

                'Get the GUID for the parent ObsSetCode
                Dim sbSql As New System.Text.StringBuilder()
                sbSql.Append("DECLARE @parentObsSetGUID uniqueidentifier ")
                sbSql.Append("SELECT GUID ")
                sbSql.Append("FROM {0} ")
                sbSql.Append("WHERE {1} = '{2}';")

                dtResultParentGUID = AicDatabase.ExecuteSqlReturnTable(String.Format(sbSql.ToString(), aicObsSet, pTablePrimaryKeyName, parentObsSetCode))


                Dim dtResultChildGUID As DataTable

                'Get the GUID for the child ObsSetCode
                Dim sbSql1 As New System.Text.StringBuilder()
                sbSql1.Append("DECLARE @childObsSetGUID uniqueidentifier ")
                sbSql1.Append("SELECT  GUID ")
                sbSql1.Append("FROM {0} ")
                sbSql1.Append("WHERE {1} = '{2}';")


                dtResultChildGUID = AicDatabase.ExecuteSqlReturnTable(String.Format(sbSql.ToString(), aicObsSet, pTablePrimaryKeyName, strInsert))

                strSql.Append("DECLARE @cur_date_time datetime; ")
                strSql.Append("SELECT @cur_date_time = getdate(); ")
                strSql.Append("WAITFOR DELAY '00:00:00.100'; ")

                strSql.Append("INSERT INTO aic_obs_set_obs_set_obs_item_xref_chg (ModifiedBy, ContentGUID, ParentObsSetGUID, ParentObsSetCode,")
                strSql.Append("ChildObsSetGUID, ChildObsSetCode, ChildObsItemGUID, ChildObsItemCode, RationaleText, RationaleFreeText")
                strSql.Append(String.Format("SELECT '{0}', '{1}', '{2}', '{3}','{4}', '{5}','{6}', '{7}','{8}', '{9}',"))
                strSql.Append("'ExcelSheet', 'GUID', dtResultParentGUID(0), parentObsSetCode, dtResultChildGUID(0), strInsert,")
                strSql.Append("'','', rationaleText, rationaleType")

                latLog.Operation = sbSql.ToString
                latLog.WriteLog()

                latLog.Operation = sbSql1.ToString
                latLog.WriteLog()

Upvotes: 0

Views: 1646

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

You're doing a String.Format but not providing any values for it to substitute in here:

String.Format("SELECT '{0}', '{1}', '{2}', '{3}','{4}', '{5}','{6}', '{7}','{8}', '{9}',"))

(It's the last-bar-two Append call, towards the bottom of your code)

What were you expecting to go into those placeholders?

Based on comments then, it should be something like:

strSql.Append(String.Format("SELECT '{0}', '{1}', '{2}', '{3}','{4}', '{5}','{6}', '{7}','{8}', '{9}'," _
      "ExcelSheet", "GUID", dtResultParentGUID(0), parentObsSetCode, dtResultChildGUID(0), strInsert, _
      "","", rationaleText, rationaleType)

Upvotes: 0

Related Questions