Welcome to the new platform of Programmers Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use it's exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.
How to insert many fields in a table using stored procedure.
Below is a code snippet i have pasted. This will work fine if i ahve only some fields to update. If there is some were around 100 fields to update into the database how can i do it. I cannot keep in adding paramters. With dataset it is possible. But i want to call the stored procedure to insert into database. How can i pass all the parameters to the stored procedure. DO i need to use the commnad object.
can i use the simple for loop so that the burden of listing so many parameters gets reduced.
This example takes form data, passes the values to a stored procedure and inserts into a table.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQL" %>
Dim MyConnection As SQLConnection
Sub Page_Load(Sender As Object, E As EventArgs)
MyConnection = New SQLConnection("server=localhost;uid=sa;pwd=;database=MyDB")
'When the Button is clicked the data is passed and
'Inserted into the database
Sub b1_OnClick(Sender As Object, E As EventArgs)
'dim local variable to hold the SQLCommand
'This holds the value of the Stored Procedure name
Dim MyCommand = New SQLCommand("sp_ReturnIdentity", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
Dim workParam As SQLParameter = Nothing
MyCommand.Parameters.Add(New SQLParameter("@FName", SQLDataType.VarChar, 50))
MyCommand.Parameters("@FName").Value = request.form("Fname")
MyCommand.Parameters.Add(New SQLParameter("@LName", SQLDataType.VarChar, 50))
MyCommand.Parameters("@LName").Value = request.form("Lname")
MyCommand.Parameters.Add(New SQLParameter("@FavColor", SQLDataType.VarChar, 50))
MyCommand.Parameters("@FavColor").Value = request.form("Favcolor")
workParam = MyCommand.Parameters.Add(New SQLParameter("@ID",SQLDataType.Int))
workParam.Direction = ParameterDirection.Output
'Execute the command
'Place the identity value into a local variable
'To be used elsewhere
dim strID as string = MyCommand.Parameters("@ID").Value
'close connection string