how Insert record into two tables
I have two tables, that is tblProduct and tblPacking.
i write a stored procedure two insert data in two tables which is:
CREATE PROCEDURE sp_add
@ProId varchar(50),
@Pro_Id varchar(50),
@Composition varchar(100),
@Our_Brand varchar(100),
@Generic varchar(100),
@Cat_id int,
@Pack_id int,
@Strength varchar(50),
@Pack_Type varchar(50),
@Qty int,
@Price float(8)
AS
Begin
Insert into tblProduct (Pro_Id, Composition, Our_Brand, Generic, Cat_id) Values (@ProId, @Composition, @Our_Brand, @Generic, @Cat_id);
Insert into tblPacking (Pack_id, Pro_id, Strength, Pack_Type, Qty, Price) Values (@Pack_id, @Pro_Id, @Strength, @Pack_Type, @Qty, @Price)
End
GO
and my code is:
Dim objConn As New SqlConnection("server=(local); uid=sa; pwd=; database=cheapbrandpharmacy")
If Page.IsValid Then
Dim objcmd As New SqlCommand("sp_Add", objConn)
objcmd.CommandType = CommandType.StoredProcedure
Dim objParam As SqlParameter
Dim int As Integer = 0
objParam = objcmd.Parameters.Add("@Pro_id", OleDbType.VarChar)
objParam.Direction = ParameterDirection.Input
objParam.Value = "CBP-" & txtPro_id.Text
objParam = objcmd.Parameters.Add("@Our_Brand", OleDbType.VarChar)
objParam.Direction = ParameterDirection.Input
objParam.Value = txtBrand.Text
objParam = objcmd.Parameters.Add("@Composition", OleDbType.VarChar)
objParam.Direction = ParameterDirection.Input
objParam.Value = txtComposition.Text
objParam = objcmd.Parameters.Add("@Generic", OleDbType.VarChar)
objParam.Direction = ParameterDirection.Input
objParam.Value = txtGeneric.Text
objParam = objcmd.Parameters.Add("@Cat_id", OleDbType.VarChar)
objParam.Direction = ParameterDirection.Input
objParam.Value = txtCat_id.Text
objParam = objcmd.Parameters.Add("@Pack_id", OleDbType.Integer)
objParam.Direction = ParameterDirection.Input
objParam.Value = txtPack_id.Text
objParam = objcmd.Parameters.Add("@Strength", OleDbType.Integer)
objParam.Direction = ParameterDirection.Input
objParam.Value = txtStrength.Text
objParam = objcmd.Parameters.Add("@Pack_Type", OleDbType.VarChar)
objParam.Direction = ParameterDirection.Input
objParam.Value = txtPack_Type.Text
objParam = objcmd.Parameters.Add("@Quantity", OleDbType.Integer)
objParam.Direction = ParameterDirection.Input
objParam.Value = txtQuantity.Text
objParam = objcmd.Parameters.Add("@Price", OleDbType.VarChar)
objParam.Direction = ParameterDirection.Input
objParam.Value = txtPrice.Text
Try
objcmd.Connection.Open()
int = objcmd.ExecuteNonQuery()
objcmd.Connection.Close()
Catch ex As Exception
lblMessage.Text = ex.Message
End Try
If int <> 0 Then
lblMessage.Text = int & "Record successfully added."
Else
lblMessage.Text = "No Record is added"
End If
End If
End Sub
but the record is not inserted in both of tables.
Plz tell me what is problem in my code, I have great stress.
|
