Insert data into database problem

Discussion in 'ASP .Net' started by =?Utf-8?B?YzY3NjIyOA==?=, Feb 6, 2006.

  1. Hi all,
    I think I have data type problem when I tried to insert data into sql server
    200.
    The erroe message is like this:
    system.invalidcastexception

    data type DFlage char(1) in sql 2000,
    DMI is depdent middile initial, char(1) in database
    Sex is char(1) in database, but in web page, I have "choose", "F", "M" there
    values.
    Here is how I deal with in asp.net code which I don't know where I am wrong.
    Public Function AddDependent(ByVal DFName As String, ByVal DMI As string,
    ByVal DLName As String, _
    ByVal Ssn As String, ByVal DSex As String, ByVal DSsn As String)
    Dim pDFlag As SqlParameter = New SqlParameter("@DFlag", SqlDbType.Char, 1)
    If DFlag Then
    pDFlag.Value = "Y"
    Else
    pDFlag.Value = "N"
    End If
    myCommand.Parameters.Add(pDFlag)

    Dim pDMI As SqlParameter = New SqlParameter("@MI", SqlDbType.Char, 1)
    pDMI.Value = DMI
    myCommand.Parameters.Add(pDMI)


    Dim pDSex As SqlParameter = New SqlParameter("@sex", SqlDbType.VarChar, 5)
    pDSex.Value = DSex
    myCommand.Parameters.Add(pDSex)
    --
    Betty
     
    =?Utf-8?B?YzY3NjIyOA==?=, Feb 6, 2006
    #1
    1. Advertising

  2. Hi Betty,

    Could you please show us the stored procedure so that we can make it more
    clear? Also, please set a breakpoint in this code to see if the value of
    DMI and DSex has been passed correctly.

    Kevin Yu
    =======
    "This posting is provided "AS IS" with no warranties, and confers no
    rights."
     
    Kevin Yu [MSFT], Feb 7, 2006
    #2
    1. Advertising

  3. Hi Kevin,
    Here is the store procedure. And also I don't know what to do in asp.net
    code if I don't need to collect sex column value. How to decalre optional
    parameter in .net, so when user doesn't enter some fields which are not
    required, my function will not fail.
    I did set the breakpoint
    DMI and DSEx have value, "J" and "F", but both in string format, I don't
    know when String data type and length is 1 to be implicitly converted to char
    1, any problem? in code, what should I do to avoid the data conversion
    problem, any reference I can look at it?
    Thank you.

    CREATE PROCEDURE add_dependent

    @FName varchar(50),
    @MI char(1),
    @lname varchar(50),
    @ssn varchar(11),
    @sex char(1)=NULL,
    @dpssn varchar(11)

    AS

    -- Execute the INSERT statement.
    INSERT INTO Dependents
    ( fname, MI, lname,ssn, sex, dependentssn) values
    (@fname,@mi,@lname,@ssn,@sex, @dpssn)

    -- Test the error value.
    IF @@ERROR <> 0
    BEGIN
    -- Return 99 to the calling program to indicate failure.
    PRINT 'An error occurred inserting the new dependent information'
    RETURN(99)
    END
    ELSE
    BEGIN
    -- Return 0 to the calling program to indicate success.
    PRINT 'The new dependent information has been loaded'
    RETURN(0)
    END


    GO
    /********/
    The following is corresponding code in data access layer:
    Public Function AddDependent(ByVal DFName As String, ByVal DMI As Char,
    ByVal DLName As String, _
    ByVal Ssn As String, ByVal DSex As String, ByVal DSsn As String)
    Dim myConnection As SqlConnection = New
    SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    Dim myCommand As SqlCommand = New SqlCommand("Add_dependent",
    myConnection)
    myCommand.CommandType = CommandType.StoredProcedure

    Dim pDFName As SqlParameter = New SqlParameter("@FName",
    SqlDbType.VarChar, 50)
    pDFName.Value = DFName
    myCommand.Parameters.Add(pDFName)

    Dim pDMI As SqlParameter = New SqlParameter("@MI",
    SqlDbType.Char, 1)
    pDMI.Value = DMI
    myCommand.Parameters.Add(pDMI)

    Dim pDLName As SqlParameter = New SqlParameter("@lname",
    SqlDbType.VarChar, 50)
    pDLName.Value = DLName
    myCommand.Parameters.Add(pDLName)

    Dim pSsn As SqlParameter = New SqlParameter("@ssn",
    SqlDbType.VarChar, 11)
    pSsn.Value = Ssn
    myCommand.Parameters.Add(pSsn)

    Dim pDSex As SqlParameter = New SqlParameter("@sex",
    SqlDbType.VarChar, 5)
    pDSex.Value = DSex
    myCommand.Parameters.Add(pDSex)

    Dim pDSsn As SqlParameter = New SqlParameter("@dpssn",
    SqlDbType.VarChar, 11)
    pDSsn.Value = DSsn
    myCommand.Parameters.Add(DSsn)

    myConnection.Open()
    myCommand.ExecuteNonQuery()
    myConnection.Close()

    End Function
    --
    Betty


    "Kevin Yu [MSFT]" wrote:

    > Hi Betty,
    >
    > Could you please show us the stored procedure so that we can make it more
    > clear? Also, please set a breakpoint in this code to see if the value of
    > DMI and DSex has been passed correctly.
    >
    > Kevin Yu
    > =======
    > "This posting is provided "AS IS" with no warranties, and confers no
    > rights."
    >
    >
     
    =?Utf-8?B?YzY3NjIyOA==?=, Feb 7, 2006
    #3
  4. Hi Betty,

    The string will be implicitly truncated and converted to char. So it's not
    hte problem I think. To set a default value for the parameter, write it in
    the parameter list of SP directly like:

    CREATE PROCEDURE add_dependent

    @FName varchar(50) = 'aaa',
    @MI char(1),
    @lname varchar(50),
    @ssn varchar(11),
    @sex char(1)=NULL,
    @dpssn varchar(11)

    AS

    ......

    Kevin Yu
    =======
    "This posting is provided "AS IS" with no warranties, and confers no
    rights."
     
    Kevin Yu [MSFT], Feb 8, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Larry B via DotNetMonster.com

    Insert data into Access database

    Larry B via DotNetMonster.com, Aug 10, 2005, in forum: ASP .Net
    Replies:
    7
    Views:
    24,536
    Larry B via DotNetMonster.com
    Aug 10, 2005
  2. Murali
    Replies:
    4
    Views:
    12,383
    Vidhi_shrivastava
    Aug 30, 2008
  3. Harry Zoroc
    Replies:
    1
    Views:
    980
    Gregory Vaughan
    Jul 12, 2004
  4. Rod
    Replies:
    0
    Views:
    450
  5. randi2160
    Replies:
    0
    Views:
    493
    randi2160
    Jun 12, 2009
Loading...

Share This Page