如何在Sql / Vb.Net中将Varchar2更改为Date [英] How Do I Change A Varchar2 To Date In Sql/Vb.Net

问题描述

我目前有一个SQL程序接受来自Visual Studio 2013中我的Web应用程序的输入表单中的数据但是我遇到了问题用户输入他们的出生日期。



基本上我需要从出生日期开始将输入从字符串转换为date_of_birth行是'DATE'日期的日期输入我的表格。



我现在将DOB设置为VB.NET中的'String'和SQL Developer中的'VARCHAR2'。 />


如果我在VB中更改类型，例如'ByVal sDob as String'to'ByVal sDob as Date'我收到以下错误

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.

如果我在SQL Developer中更改值，例如'p_date_of_birth IN VARCHAR2'到'p_date_of_birth IN DATE'来自DOB框的值输入到错误的表字段。



这是我的SQL程序...

"

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;

这是我的VB.NET代码...

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.

推荐答案

从不使用（N）VARCHAR 存储日期的数据。使用正确的数据类型！



据我所知你正试图将 addNewUser 函数空字符串作为约会。

Never use (N)VARCHAR data to store date. Use proper data type!

As per i understand you're trying to pass into addNewUser function empty string as a date.

这篇关于如何在Sql / Vb.Net中将Varchar2更改为Date的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！