How Do I Change A Varchar2 To Date In Sql/Vb.Net

See more:

VB

ASP.NET

SQL

Hi,

I currently have a SQL procedure that accepts data from an input form on my web application in visual studio 2013 but i am having an issue with the user entering their date of birth.

Basically i need the input from the date of birth to convert from a string to a date the date_of_birth row is a 'DATE' date type in my table.

I have the DOB at the moment set up as a 'String' in VB.NET and a 'VARCHAR2' in SQL Developer.

If i change the type in VB e.g. 'ByVal sDob as String' to 'ByVal sDob as Date' i get the following error
"

Conversion from string "" to type 'Date' is not valid.

"

If i change the value in SQL Developer e.g. 'p_date_of_birth IN VARCHAR2' to 'p_date_of_birth IN DATE' the value from the DOB box inputs into the wrong table field.

This is my SQL Procedure...

PROCEDURE add_new_user
(
 p_first_name IN VARCHAR2,
 p_family_name IN VARCHAR2,
 p_middlename IN VARCHAR2,
 p_date_of_birth IN VARCHAR2,
 p_phone_number IN VARCHAR2,
 p_ext_number IN VARCHAR2,
 p_bleep_number IN VARCHAR2,
 p_job_title IN VARCHAR2,
 p_site_name IN VARCHAR2,
 )
IS
BEGIN
  INSERT INTO USERS_DEV (FORENAME, SURNAME, MIDDLENAME, DATE_OF_BIRTH, PHONE_NO, PHONE_EXT, PAGER_NO, POSITION, SITE)
  VALUES (p_first_name, p_family_name, p_middlename, p_date_of_birth, p_phone_number, p_ext_number, p_bleep_number, p_job_title, p_site_name);
  
  COMMIT;

END add_new_user;

This is my VB.NET code...

Public Shared Function addNewUser(ByVal sFamilyName As String, ByVal sFirstName As String, ByVal sMiddlename As String, ByVal sDob As String, ByVal sPhoneNumber As String, ByVal sExt As String, ByVal sBleep As String, ByVal sSitename As String, ByVal sJobTitle As String) As String

        Dim contact As String = "Y"

        Dim conSmart As New OracleConnection
        conSmart.ConnectionString = ConfigurationManager.ConnectionStrings("myconn").ConnectionString

        
        Dim s_family_name As New OracleParameter
        s_family_name.Direction = ParameterDirection.Input
        s_family_name.OracleDbType = OracleDbType.Varchar2
        s_family_name.Value = sFamilyName

        Dim s_first_name As New OracleParameter
        s_first_name.Direction = ParameterDirection.Input
        s_first_name.OracleDbType = OracleDbType.Varchar2
        s_first_name.Value = sFirstName

        Dim s_middlename As New OracleParameter
        s_middlename.Direction = ParameterDirection.Input
        s_middlename.OracleDbType = OracleDbType.Varchar2
        s_middlename.Value = sMiddlename

        Dim s_date_of_birth As New OracleParameter
        s_date_of_birth.Direction = ParameterDirection.Input
        s_date_of_birth.OracleDbType = OracleDbType.Varchar2
        s_date_of_birth.Value = sDob

        Dim s_phone_number As New OracleParameter
        s_phone_number.Direction = ParameterDirection.Input
        s_phone_number.OracleDbType = OracleDbType.Varchar2
        s_phone_number.Value = sPhoneNumber

        Dim s_ext_number As New OracleParameter
        s_ext_number.Direction = ParameterDirection.Input
        s_ext_number.OracleDbType = OracleDbType.Varchar2
        s_ext_number.Value = sExt

        Dim s_bleep_number As New OracleParameter
        s_bleep_number.Direction = ParameterDirection.Input
        s_bleep_number.OracleDbType = OracleDbType.Varchar2
        s_bleep_number.Value = sBleep

        Dim s_site_name As New OracleParameter
        s_site_name.Direction = ParameterDirection.Input
        s_site_name.OracleDbType = OracleDbType.Varchar2
        s_site_name.Value = sSitename
        
        Dim s_job_title As New OracleParameter
        s_job_title.Direction = ParameterDirection.Input
        s_job_title.OracleDbType = OracleDbType.Varchar2
        s_job_title.Value = sJobTitle
        
        Dim oraCommand As New OracleCommand
        oraCommand.Connection = conSmart
        oraCommand.CommandType = CommandType.StoredProcedure
        oraCommand.CommandText = "MY_EMP_TABLE.ADD_NEW_USER"
       
        oraCommand.Parameters.Add(s_family_name)
        oraCommand.Parameters.Add(s_first_name)
        oraCommand.Parameters.Add(s_middlename)
        oraCommand.Parameters.Add(s_date_of_birth)
        oraCommand.Parameters.Add(s_phone_number)
        oraCommand.Parameters.Add(s_ext_number)
        oraCommand.Parameters.Add(s_bleep_number)
        oraCommand.Parameters.Add(s_site_name)
        oraCommand.Parameters.Add(s_job_title)
       
        Try
            conSmart.Open()
            oraCommand.ExecuteNonQuery()
            conSmart.Close()
        Catch ex As Exception
            contact = ex.Message
        End Try

        Return contact
    End Function

If you need anymore information please let me know thank you.