Solved

SQL System.IndexOutOfRange Exception

Posted on 2004-10-13
27
916 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
ID: 12305669
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
ID: 12308328
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
ID: 12313972
Hi,
Can you post the Stored Procedure ?
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 4

Author Comment

by:computerg33k
ID: 12319905
       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
ID: 12319914
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
ID: 12320037
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
ID: 12320076
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
ID: 12320174
The code is no easy to read
Where come from "oCommand" ?
What is the query to "drJobs" ?
0
 
LVL 4

Author Comment

by:computerg33k
ID: 12320490
       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
ID: 12320502
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
ID: 12320583
oCommand.CommandType = CommandType.StoredProcedure '<-- Where is the SP ?
oCommand.CommandText = "NameOfSP" ?
0
 
LVL 4

Author Comment

by:computerg33k
ID: 12320687
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
ID: 12320734
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
 
LVL 4

Author Comment

by:computerg33k
ID: 12320758
   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
ID: 12320907
"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
ID: 12321019
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
ID: 12322179
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
ID: 12322438
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
ID: 12322476
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
ID: 12322484
Change
ALTER PROCEDURE stp_UpdateJobDetail
for
CREATE PROCEDURE stp_UpdateJobDetail
0
 
LVL 4

Author Comment

by:computerg33k
ID: 12322495
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
ID: 12322517
i changed it, but i still got the same error--not in database?
0
 
LVL 10

Expert Comment

by:Hans Langer
ID: 12322618
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
ID: 12322631
sorry--yes--status_dt
0
 
LVL 10

Accepted Solution

by:
Hans Langer earned 300 total points
ID: 12322723
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
ID: 12322837
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
ID: 12322840
and by the way--thanks for being so patient with me--i'm still learning!  lol
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

16 Experts available now in Live!

Get 1:1 Help Now