|
Subject:
|
Adding Date parameter
|
|
Posted By:
|
joeri
|
Post Date:
|
3/9/2007 12:01:38 PM
|
Hi,
I've created a project simular to the TimeTracker application. I have a class WDACarpool which inherits from the WDABase class.
It has a method getUsersBySchedule which returns a dataset from a pl/sql procedure. The pl/sql procedure works when testing it in SQLPlus. Yet in VB.NET, I can't get it to work.
Two of the IN parameters in this procedure are of type 'DATE'. How are these supposed to be added to the command? I can't use either myBase.AddParameter() method since Date doesn't have a size and since it's an input parameter.
Now, i've changed the parametertype's in both pl/sql procedure and vb method to VARCHAR2/String. I convert them to DATE within the pl/sql procedure, but it still doesn't give me any records, while it should give me one record.
The pl/sql procedure still works under SQLPlus.
Where should I begin to solve this problem?
|
|
Reply By:
|
digitdaddy
|
Reply Date:
|
3/9/2007 12:18:55 PM
|
Try enclosing the data in "'", BDate = '01/23/2005'.
|
|
Reply By:
|
joeri
|
Reply Date:
|
3/9/2007 1:56:08 PM
|
I can't do that. the data comes from an Item in a dataset received from another procedure. this data is correct. So i have something like this:
Public Function getStudentsBySchedule( _
ByVal areaCode As Integer, _
ByVal blockStart As String, ByVal blockEnd As String, _
ByVal weekday As String) As DataSet
Try
getStudentsBySchedule = New DataSet
MyBase.SQL = "carpool_package.getStudentsBySchedule"
MyBase.InitializeCommand()
MyBase.AddParameter("p_areaCode", _
OracleClient.OracleType.Number, 4, areaCode)
MyBase.AddParameter("p_blockStart", _
OracleClient.OracleType.VarChar, 20, blockStart)
MyBase.AddParameter("p_blockEnd", _
OracleClient.OracleType.VarChar, 20, blockStart)
MyBase.AddParameter("res_cur", _
OracleClient.OracleType.Cursor, ParameterDirection.Output)
MyBase.AddParameter("p_weekday", _
OracleClient.OracleType.VarChar, 15, weekday)
MyBase.FillDataSet(getStudentsBySchedule, "students")
Catch ex As Exception
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Function
|
|
Reply By:
|
digitdaddy
|
Reply Date:
|
3/9/2007 2:50:20 PM
|
I use the following functions to prepare strings and dates from db records for use in SELECT statements.
Public Function PrepareStr(ByVal strValue As String) As String
' This function accepts a string and creates a string that can
' be used in a SQL statement by adding single quotes around
' it and handling empty values.
If strValue.Trim() = "" Then
Return "NULL"
Else
Dim CorrectString As String = strValue.Replace("'", "''")
Return "'" & CorrectString.Trim() & "'"
End If
End Function
Public Function PrepareDate(ByVal wkdate As Date) As String
' This function accepts a DATE and creates a string that can
' be used in a SQL statement by adding single quotes around
' it and handling empty values.
If wkdate = Date.MinValue Then
Return "NULL"
Else
Return "'" & wkdate.ToShortDateString & "'"
End If
End Function
|
