0

Hi,
I am using visual basic express 2008 . and i am adding few rows to the database using some stored procedure. it is working fine. after adding the row i am displaying in a grid using dataset . fine.but when i close the program and reopen the program i can't see any rows in the database..

I don't know the problem.this is my code to create a procedure.
Code:

ALTER PROCEDURE AddNewStaff
	@SName varchar(50),
	@DOJ date,
	@Qualification varchar(50),
	@Add1 varchar(50),
	@Add2 varchar(50),
	@Add3 varchar(50),
	@pc varchar(50),
	@Tel1 int=0,
	@status varchar(50)="P"	
AS
	insert into OfficeStaff([SName],[DOJ],[Qualification],[Add1],[Add2],[Add3],[pc],Tel1,[status])
	values(@SName,@doj,@Qualification,@Add1,@Add2,@Add3,@pc,@Tel1,@status)
	
	RETURN SCOPE_IDENTITY()

this is my code to insert the row using a stored procedure

Public Function insertNewStaff(ByVal insertQ As String, ByVal sname As String, ByVal qual As String, ByVal add1 As String, ByVal add2 As String, ByVal add3 As String, ByVal pc As String, ByVal tel1 As String, Optional ByVal doj As String = "") As String
        Dim msg As String = ""
        Dim mycon As New SqlConnection
        mycon.ConnectionString = constr

        Try
            mycon.Open()
            Dim myCom As SqlCommand = New SqlCommand(insertQ, mycon)
            myCom.CommandType = CommandType.StoredProcedure

            Dim retValParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)

            retValParam.Direction = ParameterDirection.ReturnValue

            myCom.Parameters.Add("@sname", SqlDbType.VarChar).Value = sname

            Dim parameter As New SqlParameter()
            If doj = "" Then
                myCom.Parameters.Add("@doj", SqlDbType.Date).Value = DBNull.Value

            Else
                parameter = New SqlParameter("@DOJ", SqlDbType.Date)
                parameter.Value() = Convert.ToDateTime(doj)
                myCom.Parameters.Add(parameter)
            End If

            myCom.Parameters.Add("@qualification", SqlDbType.VarChar).Value = qual
            myCom.Parameters.Add("@add1", SqlDbType.VarChar).Value = add1
            myCom.Parameters.Add("@add2", SqlDbType.VarChar).Value = add2
            myCom.Parameters.Add("@add3", SqlDbType.VarChar).Value = add3
            myCom.Parameters.Add("@pc", SqlDbType.VarChar).Value = pc
            myCom.Parameters.Add("@tel1", SqlDbType.VarChar).Value = tel1

            myCom.Parameters.Add(retValParam)
            Dim dr As SqlDataReader
            dr = myCom.ExecuteReader
            Dim retVal As Integer = Convert.ToInt32(retValParam.Value)
            If retVal > 0 Then
                msg = "OK"
            Else
                msg = "Error"
            End If
            mycon.Close()
            msg = "Success"
        Catch ex As Exception
            msg = ex.Message
            mycon.Close()
        End Try
        Return msg
    End Function
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by lolafuertes
0

This may happen because the INSERT sentence in the procedure fails for any reason and you do not trap the error at store procedure level, raising an error if the insert fails.

Can you also put the table definition, indexes, triggers, defaults, permissions, etc and also the values passed to the stoed procedure inorder to try to verify where the problem is?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.