Solved

SQL System.IndexOutOfRange Exception

Posted on 2004-10-13
27
898 Views
Last Modified: 2008-01-09
I'm getting this system.indexoutofrange exception with this line:
                If Not IsDBNull(drJobs("updatestatus_dt")) Then mUpdateStatusDt += ", " & CType(drJobs("updatestatus_dt"), String) Else mUpdateStatusDt += String.Empty

and if I take out that line of code then I get a System.Data.SqlClient.SqlException error on this line:
        oCommand.Parameters.Add(New SqlParameter("@updateStatus_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@updatestatus_dt").Value = mUpdateStatusDt

        oCommand.Parameters.Add(New SqlParameter("@NewJob_id", SqlDbType.BigInt))
        oCommand.Parameters("@NewJob_id").Direction = ParameterDirection.Output

        iRecordsUpdated = oCommand.ExecuteNonQuery()

0
Comment
Question by:computerg33k
  • 15
  • 11
27 Comments
 
LVL 2

Expert Comment

by:AmanBrar
Comment Utility
plz make it clear what type is mUpdateStatusDt, make sure u r not getting array index out of range.
and give the details of System.Data.SqlClient.SqlException error.
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
mUpdateStatusDt is a string, UpdateStatus_Dt is a smalldatetime.

The details of the System.Data.SqlClient.SqlException error are as follows:
An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll

Additional information: updatestatus_dt
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
Hi,
Can you post the Stored Procedure ?
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
       oCommand.Parameters.Add(New SqlParameter("@updateStatus_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@updatestatus_dt").Value = CheckNullString(mUpdateStatusDt)

                If Not IsDBNull(drJobs("updatestatus_dt")) Then
                    mUpdateStatusDt += ", " & CType(drJobs("updatestatus_dt"), String)
                Else
                    mUpdateStatusDt += String.Empty
                End If

                Dim sUpdateService As String
                If IsDBNull(dtInvoices.Rows(i).Item("UpdateService_dt")) Then
                    sUpdateService = " "
                Else
                    sUpdateService = "on " & CDate(dtInvoices.Rows(i).Item("updateservice_dt")).ToShortDateString() & " at " & CDate(dtInvoices.Rows(i).Item("Updatestatus_dt")).ToShortTimeString()
                End If


        '
        'SqlInsertCommand9
        '
        Me.SqlInsertCommand9.CommandText = "INSERT INTO tbl_Job(updatestatus_dt) VALUES (@updatestatus_dt); SELE" & _
        "CT updatestatus_dt AS expr37, updatestatus_dt FROM tbl_Job WHERE (Job_id = @@IDENTITY) AND (Job_id = @@IDEN" & _
        "TITY)"
        Me.SqlInsertCommand9.Connection = Me.SqlConnection1
        Me.SqlInsertCommand9.Parameters.Add(New System.Data.SqlClient.SqlParameter("@updatestatus_dt", System.Data.SqlDbType.DateTime, 4, "UpdateStatus_dt"))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "data source=EXCELSERVER;initial catalog=Excel;integrated security=SSPI;persist se" & _
        "curity info=False;workstation id=DANA;packet size=4096"
        '
        'SqlDataAdapter9
        '
        Me.SqlDataAdapter9.DeleteCommand = Me.SqlDeleteCommand9
        Me.SqlDataAdapter9.InsertCommand = Me.SqlInsertCommand9
        Me.SqlDataAdapter9.SelectCommand = Me.SqlSelectCommand9
        Me.SqlDataAdapter9.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tbl_Job", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Expr37", "Expr37"), New System.Data.Common.DataColumnMapping("updatestatus_dt", "updatestatus_dt")})
        Me.SqlDataAdapter9.UpdateCommand = Me.SqlUpdateCommand9
        '
        'SqlDeleteCommand9
        '
       Me.SqlDeleteCommand9.Parameters.Add(New System.Data.SqlClient.SqlParameter("@original_updatestatus_dt", System.Data.SqlDbType.DateTime, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "updatestatus_dt", DataRowVersion.Original, Nothing))
    Me.SqlDeleteCommand9.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Expr37", System.Data.SqlDbType.DateTime, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "updateStatus_dt", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlSelectCommand9
        '
        Me.SqlSelectCommand9.CommandText = "SELECT updatestatus_dt as expr37, updatestatus_dt FROM tbl_Job"
        Me.SqlSelectCommand9.Connection = Me.SqlConnection1
        '
        'SqlUpdateCommand9
        '
        Me.SqlUpdateCommand9.CommandText = "UPDATE tbl_Job SET updatestatus_dt = @updatestatus_dt, WHERE ((@original_updatestatus_dt OR @original_updatestatus_dt" & _
        " IS NULL AND updatestatus_dt IS NULL) (updatestatus_dt = @original_expr37 OR @original_expr37 IS NULL AND " & _
        "updatestatus_dt IS NULL) updatestatus_dt AS expr37, updatestatus_dt FROM tbl_Job WHERE (Job_id = @Job_id) AND (Job_id = @Job_id)"
        Me.SqlUpdateCommand9.Connection = Me.SqlConnection1
       ("@UpdateStatus_dt", System.Data.SqlDbType.DateTime, 4, "UpdateStatus_dt"))
        Me.SqlUpdateCommand9.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_UpdateStatus_dt", System.Data.SqlDbType.DateTime, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "UpdateStatus_dt", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand9.Parameters.Add(New System.Data.SqlClient.SqlParameter("@original_Expr37", System.Data.SqlDbType.DateTime, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "UpdateStatus_dt", System.Data.DataRowVersion.Original, Nothing))
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
Thats everything i have--i took out the stuff that works--What I don't understand is that i copied another date-to a T--and it doesn't work!?!  I dunno what i'm doing wrong--it's got me totally stumped
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
If you use an Alias for the field you must use the alias name,

 Me.SqlSelectCommand9.CommandText = "SELECT updatestatus_dt as expr37, updatestatus_dt FROM tbl_Job"

Try this,


     If Not IsDBNull(drJobs("expr37")) Then
                    mUpdateStatusDt += ", " & CType(drJobs("expr37"), String)
                Else
                    mUpdateStatusDt += String.Empty
                End If
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
it did the same thing--system.indexoutofrange.exception   expr37
should i change this line as well?

        oCommand.Parameters.Add(New SqlParameter("@updateStatus_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@updatestatus_dt").Value = CheckNullString(mUpdateStatusDt)
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
The code is no easy to read
Where come from "oCommand" ?
What is the query to "drJobs" ?
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
       Dim oCommand As New SqlCommand(sSQL, oConnection)
            Dim drJobs As SqlDataReader

        oCommand.Connection.Open()
        oCommand.CommandType = CommandType.StoredProcedure

        oCommand.Parameters.Clear()
            drJobs = oCommand.ExecuteReader(CommandBehavior.CloseConnection)



0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
the code is probably hard to read--cause i didn't post all of it--everything inside of the update command is working properly except for the updatestatus_dt, but updatestatus_dt is working to retrieve the file (ie: when i open a job, updatestatus_dt is checked if it's not null and is not checked if it is null).
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
oCommand.CommandType = CommandType.StoredProcedure '<-- Where is the SP ?
oCommand.CommandText = "NameOfSP" ?
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
umm....    
Public Function UpdateJobDetail() As Boolean

        UpdateJobDetail = True

        Dim iRecordsUpdated As Integer

        Dim oConnection As New SqlConnection(mConnectionString)
        Dim sSQL As String = "stp_UpdateJobDetail"
        Dim oCommand As New SqlCommand(sSQL, oConnection)

        oCommand.Connection.Open()
        oCommand.CommandType = CommandType.StoredProcedure

        oCommand.Parameters.Clear()

i'm not exactly sure--where/what would i look for?
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
If you use
oCommand.CommandType = CommandType.StoredProcedure

that mean the "oCommand.CommandText" will be the name of the stored procedure


is "stp_UpdateJobDetail" you SP ? if is it , the can you post it ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Author Comment

by:computerg33k
Comment Utility
   Public Function UpdateJobDetail() As Boolean

        UpdateJobDetail = True

        Dim iRecordsUpdated As Integer

        Dim oConnection As New SqlConnection(mConnectionString)
        Dim sSQL As String = "stp_UpdateJobDetail"
        Dim oCommand As New SqlCommand(sSQL, oConnection)

        oCommand.Connection.Open()
        oCommand.CommandType = CommandType.StoredProcedure

        oCommand.Parameters.Clear()

        oCommand.Parameters.Add(New SqlParameter("@Job_id", SqlDbType.BigInt))
        oCommand.Parameters("@Job_id").Value = mJobId

        oCommand.Parameters.Add(New SqlParameter("@ServiceType_id", SqlDbType.BigInt))
        oCommand.Parameters("@ServiceType_id").Value = mServiceTypeId

        oCommand.Parameters.Add(New SqlParameter("@JobGroup_id", SqlDbType.BigInt))
        oCommand.Parameters("@JobGroup_id").Value = mJobGroupId

        oCommand.Parameters.Add(New SqlParameter("@ProcessServer_vv", SqlDbType.BigInt))
        oCommand.Parameters("@ProcessServer_vv").Value = mProcessServerVV

        oCommand.Parameters.Add(New SqlParameter("@ProcessServer_pk", SqlDbType.BigInt))
        oCommand.Parameters("@ProcessServer_pk").Value = mProcessServerPK

        oCommand.Parameters.Add(New SqlParameter("@Substitute_id", SqlDbType.BigInt))
        oCommand.Parameters("@Substitute_id").Value = mSubstituteId

        oCommand.Parameters.Add(New SqlParameter("@ServiceAddress_id", SqlDbType.BigInt))
        oCommand.Parameters("@ServiceAddress_id").Value = mServiceAddressId

        oCommand.Parameters.Add(New SqlParameter("@caseStatus_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@CaseStatus_dt").Value = CheckNullString(mCaseStatusDt)

        oCommand.Parameters.Add(New SqlParameter("@Fee_amt", SqlDbType.Money))
        oCommand.Parameters("@Fee_amt").Value = mFeeAmt

        oCommand.Parameters.Add(New SqlParameter("@ProcessServer_amt", SqlDbType.Money))
        oCommand.Parameters("@ProcessServer_amt").Value = mProcessServerAmt

        oCommand.Parameters.Add(New SqlParameter("@Deadline_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@Deadline_dt").Value = CheckNullString(mDeadlineDt)

        oCommand.Parameters.Add(New SqlParameter("@Court_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@Court_dt").Value = CheckNullString(mCourtDt)

        oCommand.Parameters.Add(New SqlParameter("@SummonsFiled_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@SummonsFiled_dt").Value = CheckNullString(mSummonsFiledDate)

        oCommand.Parameters.Add(New SqlParameter("@ComplaintFiled_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@ComplaintFiled_dt").Value = CheckNullString(mComplaintFiledDate)

        oCommand.Parameters.Add(New SqlParameter("@Received_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@Received_dt").Value = CheckNullString(mReceivedDate)

        oCommand.Parameters.Add(New SqlParameter("@SpecialInstructions_desc", SqlDbType.VarChar, 255))
        oCommand.Parameters("@SpecialInstructions_desc").Value = CheckNullString(mSpecialInstructionsDesc)

        oCommand.Parameters.Add(New SqlParameter("@InHouse_Comments_desc", SqlDbType.VarChar, 255))
        oCommand.Parameters("@InHouse_Comments_desc").Value = CheckNullString(mInHouseNotes)

        oCommand.Parameters.Add(New SqlParameter("@InHouse_Comments_dt", SqlDbType.VarChar, 50))
        oCommand.Parameters("@InHouse_Comments_dt").Value = CheckNullString(mInHouseNotesDate)

        oCommand.Parameters.Add(New SqlParameter("@ClosingComments_desc", SqlDbType.VarChar, 1000))
        oCommand.Parameters("@ClosingComments_desc").Value = CheckNullString(mClosingCommentsDesc)

        oCommand.Parameters.Add(New SqlParameter("@CaseStatus_vv", SqlDbType.BigInt))
        oCommand.Parameters("@CaseStatus_vv").Value = mCaseStatusVV

        oCommand.Parameters.Add(New SqlParameter("@RequestPriority_vv", SqlDbType.BigInt))
        oCommand.Parameters("@RequestPriority_vv").Value = mRequestPriorityVV

        oCommand.Parameters.Add(New SqlParameter("@Recipient_vv", SqlDbType.BigInt))
        oCommand.Parameters("@Recipient_vv").Value = mRecipientVV

        oCommand.Parameters.Add(New SqlParameter("@Recipient_pk", SqlDbType.BigInt))
        oCommand.Parameters("@Recipient_pk").Value = mRecipientPK

        oCommand.Parameters.Add(New SqlParameter("@JobNavigation_vv", SqlDbType.BigInt))
        oCommand.Parameters("@JobNavigation_vv").Value = mJobNavigationVV

        oCommand.Parameters.Add(New SqlParameter("@ChargeLater_flg", SqlDbType.Bit))
        oCommand.Parameters("@ChargeLater_flg").Value = ConvertBoolean(mChargeLater)

        oCommand.Parameters.Add(New SqlParameter("@QuickBooks_flg", SqlDbType.Bit))
        oCommand.Parameters("@QuickBooks_flg").Value = ConvertBoolean(mQuickBooks)

        'oCommand.Parameters.Add(New SqlParameter("@updateStatus_dt", SqlDbType.VarChar, 50))
        'oCommand.Parameters("@updatestatus_dt").Value = CheckNullString(mUpdateStatusDt)

        oCommand.Parameters.Add(New SqlParameter("@NewJob_id", SqlDbType.BigInt))
        oCommand.Parameters("@NewJob_id").Direction = ParameterDirection.Output

        iRecordsUpdated = oCommand.ExecuteNonQuery()

        mNewJobId = CInt(oCommand.Parameters("@NewJob_id").Value)

        If iRecordsUpdated <> 1 Then
            UpdateJobDetail = False
            mErrorMessage = "stp_UpdateJobDetail returned " & CStr(iRecordsUpdated) & " records.  Was expecting 1."
            Exit Function
        End If

        oConnection.Dispose()
        oConnection = Nothing

        oCommand.Dispose()
        oCommand = Nothing

    End Function
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
"stp_UpdateJobDetail" Procedure just update something,
But you get the error in those lines,

If Not IsDBNull(drJobs("updatestatus_dt")) Then
                    mUpdateStatusDt += ", " & CType(drJobs("updatestatus_dt"), String)
Else
                    mUpdateStatusDt += String.Empty
End If

what SP use the "command" of "drJobs" ?


You post this.

Dim oCommand As New SqlCommand(sSQL, oConnection) '<-- sSQL SP receive Params ?
Dim drJobs As SqlDataReader

oCommand.Connection.Open()
oCommand.CommandType = CommandType.StoredProcedure

oCommand.Parameters.Clear() '<-- you clear Params before execute the SP
drJobs = oCommand.ExecuteReader(CommandBehavior.CloseConnection)
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
this...?
If objJob.UpdateJobDetail Then
                iJobID = objJob.NewJobId
                If sMode = "NEWDETAIL" Then
                    MessageBox.Show("Job detail successfully saved.  Job ID = " & objJob.NewJobId & ".", "Saved", MessageBoxButtons.OK)
                    If MessageBox.Show("Print Cover Page? ", "Cover Page", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                        miReportsCoverPagesJobDetailPrint(sender, e)
                    Else
                        If MessageBox.Show("Add another job?", "Add Job?", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                            NewDetailMode()
                        Else
                            If MessageBox.Show("Add another job group?", "Add Group?", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                                sMode = "NEWGROUP"
                                AddNewGroup()
                                NewGroupMode()
                            Else
                                CloseAndCleanUp()
                            End If
                        End If
                    End If
                Else
                    bTextChanged = False
                    MessageBox.Show("Job detail successfully saved.  Job ID = " & objJob.NewJobId & ".", "Saved", MessageBoxButtons.OK)
                    EnableAncillaryButtons()
                End If
                bJobChanged = True
            Else
            End If
        Else
        End If
    End Sub



this is what called the function--i cannot save the updatestatus_dt--that is the reason for my last posting--the last posting showed the saving and showing of updatestatus_dt
this code shows where it is called-after you hit the save button
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
I just want to know if your are selecting a field called "updatestatus_dt" in your stored procedure.
System.IndexOutOfRange Exception can be caused because isnt existe or it use an alias.
Maybe you can try with Index in place of Names

If Not IsDBNull(drJobs(0)) Then
                    mUpdateStatusDt += ", " & CType(drJobs(0), String)
Else
                    mUpdateStatusDt += String.Empty
End If

drJobs(0)  it will return then first field selected.

E.G.
SELECT
updatestatus_dt as expr37,   <- drJobs(0)
updatestatus_dt                   <- drJobs(1)
FROM tbl_Job

0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
ah--i found the SP, but if I try to change it, it gives me an error when I try to save: "The document cannot be found:not in database URL."  So how would I go about changing it?

ALTER PROCEDURE stp_UpdateJobDetail
@Job_id BIGINT,
@ServiceType_id BIGINT,
@JobGroup_id BIGINT,
@ProcessServer_vv BIGINT,
@ProcessServer_pk BIGINT,
@Substitute_id BIGINT,
@ServiceAddress_id BIGINT,
@CaseStatus_dt VARCHAR(50),
@Fee_amt SMALLMONEY,
@ProcessServer_amt SMALLMONEY,
@Deadline_dt VARCHAR(50),
@Court_dt VARCHAR(50),
@SummonsFiled_dt VARCHAR(50),
@ComplaintFiled_dt VARCHAR(50),
@Received_dt VARCHAR(10),
@SpecialInstructions_desc VARCHAR(255),
@InHouse_Comments_desc VARCHAR(255),
@InHouse_Comments_dt VARCHAR(50),
@ClosingComments_desc VARCHAR(1000),
@CaseStatus_vv BIGINT,
@RequestPriority_vv BIGINT,
@Recipient_vv BIGINT,
@Recipient_pk BIGINT,
@JobNavigation_vv BIGINT,
@ChargeLater_flg BIT,
@QuickBooks_flg BIT,
@NewJob_id BIGINT OUT
AS

DECLARE @InHousesDtToUse SMALLDATETIME

IF @InHouse_Comments_dt = ''
BEGIN
      SET @InHousesDtToUse = NULL
END
ELSE
BEGIN
      SET @InHousesDtToUse = CAST(@InHouse_Comments_dt as SMALLDATETIME)
END

DECLARE @CaseStatusDtToUse SMALLDATETIME

IF @CaseStatus_dt = ''
BEGIN
      SET @CaseStatusDtToUse = NULL
END
ELSE
BEGIN
      SET @CaseStatusDtToUse = CAST(@CaseStatus_dt as SMALLDATETIME)
END

DECLARE @DeadlineDtToUse SMALLDATETIME

IF @Deadline_dt = ''
BEGIN
      SET @DeadlineDtToUse = NULL
END
ELSE
BEGIN
      SET @DeadlineDtToUse = CAST(@Deadline_dt as SMALLDATETIME)
END

DECLARE @CourtDtToUse SMALLDATETIME

IF @Court_dt = ''
BEGIN
      SET @CourtDtToUse = NULL
END
ELSE
BEGIN
      SET @CourtDtToUse = CAST(@Court_dt as SMALLDATETIME)
END

DECLARE @SummonsFiledDtToUse SMALLDATETIME

IF @SummonsFiled_dt = ''
BEGIN
      SET @SummonsFiledDtToUse = NULL
END
ELSE
BEGIN
      SET @SummonsFiledDtToUse = CAST(@SummonsFiled_dt as SMALLDATETIME)
END
DECLARE @ComplaintFiledDtToUse SMALLDATETIME

IF @ComplaintFiled_dt = ''
BEGIN
      SET @ComplaintFiledDtToUse = NULL
END
ELSE
BEGIN
      SET @ComplaintFiledDtToUse = CAST(@ComplaintFiled_dt as SMALLDATETIME)
END

DECLARE @ReceivedDtToUse SMALLDATETIME

IF @Received_dt = ''
BEGIN
      SET @ReceivedDtToUse = NULL
END
ELSE
BEGIN
      SET @ReceivedDtToUse = CAST(@Received_dt as SMALLDATETIME)
END

UPDATE tbl_Job SET
ServiceType_id=@ServiceType_id,
JobGroup_id=@JobGroup_id,
ProcessServer_vv=@ProcessServer_vv,
ProcessServer_pk=@ProcessServer_pk,
Substitute_id=@Substitute_id,
ServiceAddress_id=@ServiceAddress_id,
CaseStatus_dt=@CaseStatusDtToUse,
Fee_amt=@Fee_amt,
ProcessServer_amt=@ProcessServer_amt,
Deadline_dt=@DeadlineDtToUse,
Court_dt=@CourtDtToUse,
SummonsFiled_dt=@SummonsFiledDtToUse,
ComplaintFiled_dt=@ComplaintFiledDtToUse,
Received_dt = @ReceivedDtToUse,
SpecialInstructions_desc=@SpecialInstructions_desc,
InHouse_Comments_desc=@InHouse_Comments_desc,
InHouse_Comments_dt=@InHousesDtToUse,
ClosingComments_desc=@ClosingComments_desc,
CaseStatus_vv=@CaseStatus_vv,
RequestPriority_vv=@RequestPriority_vv,
Recipient_vv=@Recipient_vv,
Recipient_pk=@Recipient_pk,
JobNavigation_vv=@JobNavigation_vv,
ChargeLater_flg = @ChargeLater_flg,
QuickBooks_flg = @QuickBooks_flg
WHERE
Job_id = @Job_id

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO tbl_Job
(
ServiceType_id,
JobGroup_id,
ProcessServer_vv,
ProcessServer_pk,
Substitute_id,
ServiceAddress_id,
CaseStatus_dt,
Fee_amt,
ProcessServer_amt,
Deadline_dt,
Court_dt,
SummonsFiled_dt,
ComplaintFiled_dt,
Received_dt,
SpecialInstructions_desc,
InHouse_Comments_desc,
InHouse_Comments_dt,
ClosingComments_desc,
CaseStatus_vv,
RequestPriority_vv,
Recipient_vv,
Recipient_pk,
JobNavigation_vv,
Create_dt,
ChargeLater_flg
)
VALUES
(
@ServiceType_id,
@JobGroup_id,
@ProcessServer_vv,
@ProcessServer_pk,
@Substitute_id,
@ServiceAddress_id,
@CaseStatusDtToUse,
@Fee_amt,
@ProcessServer_amt,
@DeadlineDtToUse,
@CourtDtToUse,
@SummonsFiledDtToUse,
@ComplaintFiledDtToUse,
@ReceivedDtToUse,
@SpecialInstructions_desc,
@InHouse_Comments_desc,
@InHousesDtToUse,
@ClosingComments_desc,
@CaseStatus_vv,

@RequestPriority_vv,
@Recipient_vv,
@Recipient_pk,
@JobNavigation_vv,

GETDATE(),
@ChargeLater_flg
)

SET @NewJob_id = SCOPE_IDENTITY()
END
ELSE
BEGIN
SET @NewJob_id = @Job_id
END
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
Hmmmm..

There is no SELECT stament , that mean there is no sense use a DataReader.
Explain me What suppose to return " drJobs("updatestatus_dt")  "
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
Change
ALTER PROCEDURE stp_UpdateJobDetail
for
CREATE PROCEDURE stp_UpdateJobDetail
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
updatestatus_dt is supposed to return the status_dt + 15 days.  i am new to vb.net and sql and my company threw this project on me last min.  ugh.
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
i changed it, but i still got the same error--not in database?
0
 
LVL 10

Expert Comment

by:Hans Langer
Comment Utility
You have those params in your SP (27 params)

ALTER PROCEDURE stp_UpdateJobDetail
@Job_id BIGINT,
@ServiceType_id BIGINT,
@JobGroup_id BIGINT,
@ProcessServer_vv BIGINT,
@ProcessServer_pk BIGINT,
@Substitute_id BIGINT,
@ServiceAddress_id BIGINT,
@CaseStatus_dt VARCHAR(50),
@Fee_amt SMALLMONEY,
@ProcessServer_amt SMALLMONEY,
@Deadline_dt VARCHAR(50),
@Court_dt VARCHAR(50),
@SummonsFiled_dt VARCHAR(50),
@ComplaintFiled_dt VARCHAR(50),
@Received_dt VARCHAR(10),
@SpecialInstructions_desc VARCHAR(255),
@InHouse_Comments_desc VARCHAR(255),
@InHouse_Comments_dt VARCHAR(50),
@ClosingComments_desc VARCHAR(1000),
@CaseStatus_vv BIGINT,
@RequestPriority_vv BIGINT,
@Recipient_vv BIGINT,
@Recipient_pk BIGINT,
@JobNavigation_vv BIGINT,
@ChargeLater_flg BIT,
@QuickBooks_flg BIT,
@NewJob_id BIGINT OUT
AS


you are giving 27 params and also (28 if you discomment this one)

'oCommand.Parameters.Add(New SqlParameter("@updateStatus_dt", SqlDbType.VarChar, 50))
'oCommand.Parameters("@updatestatus_dt").Value = CheckNullString(mUpdateStatusDt)

"@updatestatus_dt" param doesnt exist.

You want to return "CaseStatus_dt" + 15 days   ?
I can see  "status_dt"

0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
sorry--yes--status_dt
0
 
LVL 10

Accepted Solution

by:
Hans Langer earned 300 total points
Comment Utility
You are no receiving the CaseStatus_dt value.

To receive it you must use somthing like you do with the "@NewJob_id" param


oCommand.Parameters.Add(New SqlParameter("@CaseStatus_dt", SqlDbType.VarChar, 50))
oCommand.Parameters("@CaseStatus_dt").Direction = ParameterDirection.Output

mUpdateStatusDt = Convert.toString(oCommand.Parameters("@CaseStatus_dt").Value)




"@CaseStatus_dt" must be exist in your SP as param OUT,
@CaseStatus_dt VARCHAR(50) OUT,




And, into SP you must asign some value, like:

DECLARE @CaseStatusDtToUse SMALLDATETIME

IF @CaseStatus_dt = ''
BEGIN
     SET @CaseStatusDtToUse = NULL
END
ELSE
BEGIN
     SET @CaseStatusDtToUse = CAST(@CaseStatus_dt as SMALLDATETIME)
     SET @CaseStatus_dt = STR(DATEADD(day, 15, @CaseStatusDtToUse)
END
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
ok--i did this:
ALTER PROCEDURE stp_UpdateJobDetail
@Job_id BIGINT,
@ServiceType_id BIGINT,
@JobGroup_id BIGINT,
@ProcessServer_vv BIGINT,
@ProcessServer_pk BIGINT,
@Substitute_id BIGINT,
@ServiceAddress_id BIGINT,
@CaseStatus_dt VARCHAR(50),
@Fee_amt SMALLMONEY,
@ProcessServer_amt SMALLMONEY,
@Deadline_dt VARCHAR(50),
@Court_dt VARCHAR(50),
@SummonsFiled_dt VARCHAR(50),
@ComplaintFiled_dt VARCHAR(50),
@Received_dt VARCHAR(10),
@SpecialInstructions_desc VARCHAR(255),
@InHouse_Comments_desc VARCHAR(255),
@InHouse_Comments_dt VARCHAR(50),
@ClosingComments_desc VARCHAR(1000),
@CaseStatus_vv BIGINT,
@RequestPriority_vv BIGINT,
@Recipient_vv BIGINT,
@Recipient_pk BIGINT,
@JobNavigation_vv BIGINT,
@ChargeLater_flg BIT,
@QuickBooks_flg BIT,
@UpdateStatus_dt varchar(50),
@NewJob_id BIGINT OUT
AS

DECLARE @InHousesDtToUse SMALLDATETIME

IF @InHouse_Comments_dt = ''
BEGIN
      SET @InHousesDtToUse = NULL
END
ELSE
BEGIN
      SET @InHousesDtToUse = CAST(@InHouse_Comments_dt as SMALLDATETIME)
END

DECLARE @CaseStatusDtToUse SMALLDATETIME

IF @CaseStatus_dt = ''
BEGIN
      SET @CaseStatusDtToUse = NULL
END
ELSE
BEGIN
      SET @CaseStatusDtToUse = CAST(@CaseStatus_dt as SMALLDATETIME)
END

DECLARE @DeadlineDtToUse SMALLDATETIME

IF @Deadline_dt = ''
BEGIN
      SET @DeadlineDtToUse = NULL
END
ELSE
BEGIN
      SET @DeadlineDtToUse = CAST(@Deadline_dt as SMALLDATETIME)
END

DECLARE @CourtDtToUse SMALLDATETIME

IF @Court_dt = ''
BEGIN
      SET @CourtDtToUse = NULL
END
ELSE
BEGIN
      SET @CourtDtToUse = CAST(@Court_dt as SMALLDATETIME)
END

DECLARE @SummonsFiledDtToUse SMALLDATETIME

IF @SummonsFiled_dt = ''
BEGIN
      SET @SummonsFiledDtToUse = NULL
END
ELSE
BEGIN
      SET @SummonsFiledDtToUse = CAST(@SummonsFiled_dt as SMALLDATETIME)
END
DECLARE @ComplaintFiledDtToUse SMALLDATETIME

IF @ComplaintFiled_dt = ''
BEGIN
      SET @ComplaintFiledDtToUse = NULL
END
ELSE
BEGIN
      SET @ComplaintFiledDtToUse = CAST(@ComplaintFiled_dt as SMALLDATETIME)
END

DECLARE @ReceivedDtToUse SMALLDATETIME

IF @Received_dt = ''
BEGIN
      SET @ReceivedDtToUse = NULL
END
ELSE
BEGIN
      SET @ReceivedDtToUse = CAST(@Received_dt as SMALLDATETIME)
END

DECLARE @UpdateStatusDtToUse SMALLDATETIME

IF @UpdateStatus_dt = ''
BEGIN
      SET @UpdateStatusDtToUse = NULL
END
ELSE
BEGIN
      SET @UpdateStatusDtToUse = CAST(@UpdateStatus_dt as SMALLDATETIME)
END

UPDATE tbl_Job SET
ServiceType_id=@ServiceType_id,
JobGroup_id=@JobGroup_id,
ProcessServer_vv=@ProcessServer_vv,
ProcessServer_pk=@ProcessServer_pk,
Substitute_id=@Substitute_id,
ServiceAddress_id=@ServiceAddress_id,
CaseStatus_dt=@CaseStatusDtToUse,
Fee_amt=@Fee_amt,
ProcessServer_amt=@ProcessServer_amt,
Deadline_dt=@DeadlineDtToUse,
Court_dt=@CourtDtToUse,
SummonsFiled_dt=@SummonsFiledDtToUse,
ComplaintFiled_dt=@ComplaintFiledDtToUse,
Received_dt = @ReceivedDtToUse,
SpecialInstructions_desc=@SpecialInstructions_desc,
InHouse_Comments_desc=@InHouse_Comments_desc,
InHouse_Comments_dt=@InHousesDtToUse,
ClosingComments_desc=@ClosingComments_desc,
CaseStatus_vv=@CaseStatus_vv,
RequestPriority_vv=@RequestPriority_vv,
Recipient_vv=@Recipient_vv,
Recipient_pk=@Recipient_pk,
JobNavigation_vv=@JobNavigation_vv,
ChargeLater_flg = @ChargeLater_flg,
QuickBooks_flg = @QuickBooks_flg,
UpdateStatus_dt = @UpdateStatus_dt
WHERE
Job_id = @Job_id

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO tbl_Job
(
ServiceType_id,
JobGroup_id,
ProcessServer_vv,
ProcessServer_pk,
Substitute_id,
ServiceAddress_id,
CaseStatus_dt,
Fee_amt,
ProcessServer_amt,
Deadline_dt,
Court_dt,
SummonsFiled_dt,
ComplaintFiled_dt,
Received_dt,
SpecialInstructions_desc,
InHouse_Comments_desc,
InHouse_Comments_dt,
ClosingComments_desc,
CaseStatus_vv,
RequestPriority_vv,
Recipient_vv,
Recipient_pk,
JobNavigation_vv,
Create_dt,
ChargeLater_flg,
UpdateStatus_dt
)
VALUES
(
@ServiceType_id,
@JobGroup_id,
@ProcessServer_vv,
@ProcessServer_pk,
@Substitute_id,
@ServiceAddress_id,
@CaseStatusDtToUse,
@Fee_amt,
@ProcessServer_amt,
@DeadlineDtToUse,
@CourtDtToUse,
@SummonsFiledDtToUse,
@ComplaintFiledDtToUse,
@ReceivedDtToUse,
@SpecialInstructions_desc,
@InHouse_Comments_desc,
@InHousesDtToUse,
@ClosingComments_desc,
@CaseStatus_vv,

@RequestPriority_vv,
@Recipient_vv,
@Recipient_pk,
@JobNavigation_vv,

GETDATE(),
@ChargeLater_flg,
@UpdateStatus_dt
)

SET @NewJob_id = SCOPE_IDENTITY()
END
ELSE
BEGIN
SET @NewJob_id = @Job_id
END



and now it kinda works--however, since the users don't have the new version with this updatestatus_dt datetimepicker, it doesn't allow them to save.  How can I make it so that if they don't have a updatestatus_dt dtp it sets it to null?
0
 
LVL 4

Author Comment

by:computerg33k
Comment Utility
and by the way--thanks for being so patient with me--i'm still learning!  lol
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now