Insert data into database problem

[Guest]

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)

 

[Kevin Yu [MSFT]]

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."

 

[Guest]

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

 

[Kevin Yu [MSFT]]

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."