how to compare datareader value in if

[compu66]

Hi all,

I am getting an error patientid as not declared

Dim strstatus As String = drAccessionNumbers.Item(1).ToString()
If (DirectCast(drAccessionNumbers("status"), String) <> "Provisional") AndAlso (DirectCast(drAccessionNumbers("status"), String) <> "Completed") Then

sql = "UPDATE ExamRecord SET Status='Cancelled' WHERE AccessionNumber = " & drAccessionNumbers.Item(0)
sql2 = "UPDATE AdditionalExamRecord SET Ordered ='0',CancelledOn='" & String.Format("{0:d}", Today) & "', CancelledBy='" & sUserID & "' WHERE AccessionNumber = " & drAccessionNumbers.Item(0)

End If

here if I am using the below code I am not getting the error
Dim strstatus As String = drAccessionNumbers.Item(1).ToString()
If (DirectCast(drAccessionNumbers("status"), String) <> "Provisional") Then

sql = "UPDATE ExamRecord SET Status='Cancelled' WHERE AccessionNumber = " & drAccessionNumbers.Item(0)
sql2 = "UPDATE AdditionalExamRecord SET Ordered ='0',CancelledOn='" & String.Format("{0:d}", Today) & "', CancelledBy='" & sUserID & "' WHERE AccessionNumber = " & drAccessionNumbers.Item(0)

End If





Entire code

Public Function UpdateAllStatusesSQL(ByVal statusAbbr As String, _ ByVal patientId As String, _
ByVal scheduleDate As String, Optional ByVal sUserID As String = "") As Integer

'--- Variables

Try

cmd.Connection = New SqlConnection(ConnString)
cmd.Connection.Open()

'--- First get all accession numbers associated with this patient id

sql = "SELECT AccessionNumber,status FROM ExamRecord WHERE PatientID = @PatientID "

sql += "AND ScheduleDate = @ScheduleDate AND Status <> 'Cancelled'"

cmd.CommandText = sql

da.SelectCommand = cmd

da.SelectCommand.Parameters.Add("@PatientID", SqlDbType.NChar).Value = patientIdda.SelectCommand.Parameters.Add("@ScheduleDate", SqlDbType.DateTime).Value = scheduleDate
drAccessionNumbers = da.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection)

Dim dhSql As New SQLDataHandler

While drAccessionNumbers.Read()cmd2.Connection = New SqlConnection(ConnString)
cmd2.Connection.Open()



Select Case statusAbbr.ToUpper()
Case "C"

Dim strstatus As String = drAccessionNumbers.Item(1).ToString()
If (DirectCast(drAccessionNumbers("status"), String) <> "Provisional") AndAlso (DirectCast(drAccessionNumbers("status"), String) <> "Completed") Then

sql = "UPDATE ExamRecord SET Status='Cancelled' WHERE AccessionNumber = " & drAccessionNumbers.Item(0)
sql2 = "UPDATE AdditionalExamRecord SET Ordered ='0',CancelledOn='" & String.Format("{0:d}", Today) & "', CancelledBy='" & sUserID & "' WHERE AccessionNumber = " & drAccessionNumbers.Item(0)

End If
End Select

Thanks for any help in advance..

 

[jbenson001]

First , what line of code is causing the error?
Second, you should be using stored procedures with parameters for any database access. They way you are writing code is leaving your open to SqlInjection attacks.

 

[ca8msm]

I don't understand your question, but this line looks fairly confusing:

da.SelectCommand.Parameters.Add("@PatientID", SqlDbType.NChar).Value = patientIdda.SelectCommand.Parameters.Add("@ScheduleDate", SqlDbType.DateTime).Value = scheduleDate

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[compu66]

Hi,

If (DirectCast(drAccessionNumbers("status"), String) <> "Provisional") AndAlso (DirectCast(drAccessionNumbers("status"), String) <> "Completed") Then

sql = "UPDATE ExamRecord SET Status='Cancelled' WHERE AccessionNumber = " & drAccessionNumbers.Item(0)
sql2 = "UPDATE AdditionalExamRecord SET Ordered ='0',CancelledOn='" & String.Format("{0:d}", Today) & "', CancelledBy='" & sUserID & "' WHERE AccessionNumber = " & drAccessionNumbers.Item(0)

End If
End Select

 

[compu66]

There is problem in the if condition. The error is coming up from that part. The error is "@patientid should be declared"

I hope I am clear.

thanks for your time and quick responses.