Wrox Programmer Forums
|
BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5
This is the forum to discuss the Wrox book Beginning Visual Basic 2005 Databases by Thearon Willis; ISBN: 9780764588945
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 9th, 2007, 01:01 PM
Registered User
 
Join Date: Mar 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Adding Date parameter

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?
 
Old March 9th, 2007, 01:18 PM
Registered User
 
Join Date: Jan 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try enclosing the data in "'", BDate = '01/23/2005'.

 
Old March 9th, 2007, 02:56 PM
Registered User
 
Join Date: Mar 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 9th, 2007, 03:50 PM
Registered User
 
Join Date: Jan 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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









Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Parameter in a Union Query bright_mulenga Access VBA 8 January 25th, 2008 08:13 AM
Passing a Date Parameter ego_mouse BOOK: Professional Crystal Reports for VS.NET 1 January 27th, 2005 08:12 AM
Date parameter problem SethTalbott SQL Server ASP 3 September 22nd, 2004 12:24 PM
Passing Date Parameter jmss66 VB How-To 4 April 28th, 2004 03:02 PM
Adding second parameter to open page nbryson ADO.NET 0 September 18th, 2003 06:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.