Please help want to use the stored procedure to insert record to table

Please help, first time trying to use the stored procedure.

Can you please modify my code below which is in asp.net to insert the record's in table(tbl_labels) using my stored procedure, i don't have problem inserting records, but i want to use my stored procedure to insert the record. Please help I never used a stored procedure.
**********

CREATE PROCEDURE sp_insert_label
(
@engl nvarchar,
@espl nvarchar,
@frlbl nvarchar,
@gerlbl nvarchar
)
AS
INSERT INTO tbl_labels
(
eng_lbl,
esp_lbl,
fr_lbl,
ger_lbl
)
VALUES
(
@engl,
@espl,
@frlbl,
@gerlbl
)
GO

********** the following is my asp.net code***

Sub Addlabel_Click(Sender As Object, E As EventArgs)
Page.Validate()
If Not Page.IsValid
Return
End If
Dim DS As DataSet
Dim MyCommand As SqlCommand
Dim InsertCmd As String = "insert into tbl_labels(eng_lbl, esp_lbl, " & _
"fr_lbl, ger_lbl) values " & _
"(@engl, @espn, @fren, @german)"
MyCommand = New SqlCommand(InsertCmd, MyConnection)


MyCommand.Parameters.Add( "@engl", eng_lbl.Value )
MyCommand.Parameters.Add( "@espn", esp_lbl.Value )
MyCommand.Parameters.Add( "@fren", fr_lbl.Value )
MyCommand.Parameters.Add( "@german", ger_lbl.Value )
MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "Label Added Successfully to Dictionary.<br>" '& InsertCmd.ToString()
ger_lbl.Value = ""
fr_lbl.Value = ""
esp_lbl.Value = ""
eng_lbl.Value = ""

Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the " & _
"same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
"the fields are correctly filled out"
End If
Message.Style("color") = "red"
End Try
MyCommand.Connection.Close()
BindGrid()
End Sub



Note by moderator SomeNewKid:
Reddymade, you did the right thing in showing code between <code> and </code> tags.
However, the following (with spaces) is wrong: < code > and < /code >.
0
reddymade
7/13/2004 4:09:30 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

1 Replies
684 Views

Similar Articles

[PageSpeed] 27
Get it on Google Play
Get it on Apple App Store

(1) DO NOT name your store proc with "sp_". sp + system proc and sql server looks under master db firstwhen you try to call it. so its a performance hit.

(2) specify the lengs of the variables - varchar(100). etc
(3) heres some sample code to call a stored proc.

Dim myCommand As SqlCommand
Dim myParam As SqlParameter
dim resvalue as integer
myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "spTest"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@userid",SqlDbType.int))
myCommand.Parameters("@userid").Value = Trim(userid)
myCommand.Parameters.Add(New SqlParameter("@name",SqlDbType.varchar,100))
myCommand.Parameters("@name").Value = Trim(name)
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.int
mycommand.Parameters.Add(myParam)
Try
If objCon.State = 0 Then objCon.Open()
mycommand.ExecuteNonQuery()
resvalue=convert.toint16((mycommand.Parameters("@result").Value))
Catch exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End Try

hth
***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
7/13/2004 7:18:21 PM
Reply: