Insert data into database problem

[c676228]

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.invalidc astexception

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(By Val 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.Param eters.Add(pDFla g)

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


Dim pDSex As SqlParameter = New SqlParameter("@ sex", SqlDbType.VarCh ar, 5)
pDSex.Value = DSex
myCommand.Param eters.Add(pDSex )
--
Betty

[Kevin Yu [MSFT]]

RE: Insert data into database problem

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

[c676228]

RE: Insert data into database problem

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,@ln ame,@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(By Val 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(C onfigurationSet tings.AppSettin gs("ConnectionS tring"))
Dim myCommand As SqlCommand = New SqlCommand("Add _dependent",
myConnection)
myCommand.Comma ndType = CommandType.Sto redProcedure

Dim pDFName As SqlParameter = New SqlParameter("@ FName",
SqlDbType.VarCh ar, 50)
pDFName.Value = DFName
myCommand.Param eters.Add(pDFNa me)

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

Dim pDLName As SqlParameter = New SqlParameter("@ lname",
SqlDbType.VarCh ar, 50)
pDLName.Value = DLName
myCommand.Param eters.Add(pDLNa me)

Dim pSsn As SqlParameter = New SqlParameter("@ ssn",
SqlDbType.VarCh ar, 11)
pSsn.Value = Ssn
myCommand.Param eters.Add(pSsn)

Dim pDSex As SqlParameter = New SqlParameter("@ sex",
SqlDbType.VarCh ar, 5)
pDSex.Value = DSex
myCommand.Param eters.Add(pDSex )

Dim pDSsn As SqlParameter = New SqlParameter("@ dpssn",
SqlDbType.VarCh ar, 11)
pDSsn.Value = DSsn
myCommand.Param eters.Add(DSsn)

myConnection.Op en()
myCommand.Execu teNonQuery()
myConnection.Cl ose()

End Function
--
Betty


"Kevin Yu [MSFT]" wrote:
[color=blue]
> 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."
>
>[/color]

[Kevin Yu [MSFT]]

RE: Insert data into database problem

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