| 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,@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("C onnectionString"))
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:
[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]
| 
