How to insert many fields in a table using stored procedure. - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's 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 its 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")

End Sub

'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

MyCommand.ActiveConnection.Open()

'Execute the command
MyCommand.Execute()

'Place the identity value into a local variable
'To be used elsewhere
dim strID as string = MyCommand.Parameters("@ID").Value

'close connection string
MyCommand.ActiveConnection.Close()
End Sub




***********

Thanks
Prashant


Comments

  • justinNjustinN Posts: 5Member
    Prashant,
    what version of sql server are you using?
    SQL Server2000 is part of the .NET framework.

    You need to understand that when working with stored procedures in SQL server the parameters are part of a COLLECTION, and you will have to use the add syntax.

    eg


    Option Explicit

    Public VBInstance As VBIDE.VBE
    Public Connect As Connect

    Private Sub btnCancel_Click()
    Connect.Hide
    End Sub

    Private Sub btnInsert_Click()

    ' insert code into current cursor position

    Dim lngStartLine As Long
    Dim lngStartCol As Long
    Dim lngEndLine As Long
    Dim lngEndCol As Long

    ' determine current location in code window
    VBInstance.ActiveCodePane.CodeModule.CodePane.GetSelection lngStartLine, lngStartCol, lngEndLine, lngEndCol

    ' insert ADO code at current position, including header and footer
    VBInstance.ActiveCodePane.CodeModule.InsertLines lngStartLine, cnstCODE_HEADER & GenADOCode(Me) & cnstCODE_FOOTER

    ' hide the add-in form
    Connect.Hide

    End Sub

    Private Sub cboSproc_Click()
    txtPreview.Text = GenADOCode(Me)
    End Sub

    Private Sub chkReturnsRows_Click()
    txtPreview.Text = GenADOCode(Me)
    End Sub

    Private Sub btnGenConnStr_Click()

    ' invoke either OLE DB or ODBC connection builders

    On Error GoTo ErrHandler

    Dim cn As ADODB.Connection
    Dim objDataLink As MSDASC.DataLinks

    Set cn = New ADODB.Connection
    Set objDataLink = New MSDASC.DataLinks

    Select Case Left(lstConnType.Text, 3)
    Case "OLE"
    cn = objDataLink.PromptNew
    txtConnectionString = cn.ConnectionString
    Case "ODB"
    cn.ConnectionString = ""
    cn.Properties("Prompt") = adPromptAlways
    cn.Open
    txtConnectionString = cn.ConnectionString
    cn.Close
    End Select

    LoadSprocList

    Exit Sub
    ErrHandler:
    ' skip known error conditions; otherwise, report error
    ' (e.g. skip ODBC builder 'action cancelled' error)
    If Err.Number = 91 Or Err.Number = -2147217842 Then
    Exit Sub
    Else
    MsgBox "Error: " & Err.Description
    End If
    End Sub

    Private Sub Form_Load()

    ' set connection type to OLE DB
    lstConnType.ListIndex = 0

    ' load options into options list
    Dim lstItem As ListItem

    Set lstItem = lstADOObjects.ListItems.Add(, cnstOPT_CONNOBJECT, "cn")
    lstItem.SubItems(1) = "Connection"
    Set lstItem = lstADOObjects.ListItems.Add(, cnstOPT_CMDOBJECT, "cmd")
    lstItem.SubItems(1) = "Command"
    Set lstItem = lstADOObjects.ListItems.Add(, cnstOPT_RSOBJECT, "rs")
    lstItem.SubItems(1) = "Recordset"
    Set lstItem = lstADOObjects.ListItems.Add(, cnstOPT_PARAMSOBJECT, "params")
    lstItem.SubItems(1) = "Parameters"
    Set lstItem = lstADOObjects.ListItems.Add(, cnstOPT_PARAMOBJECT, "param")
    lstItem.SubItems(1) = "Parameter"

    Set lstItem = lstOptions.ListItems.Add(, cnstOPT_DECLAREVAR, "Declare Variables")
    lstItem.Checked = True
    Set lstItem = lstOptions.ListItems.Add(, cnstOPT_CONNECT, "Connect and Prepare Command")
    lstItem.Checked = True
    Set lstItem = lstOptions.ListItems.Add(, cnstOPT_DEFINEPARAM, "Explicitly Define Parameters")
    lstItem.Checked = True
    Set lstItem = lstOptions.ListItems.Add(, cnstOPT_REFRESHPARAM, "Refresh Parameters from DB")
    lstItem.Checked = False
    Set lstItem = lstOptions.ListItems.Add(, cnstOPT_INPARAM, "Populate Input Parameters")
    lstItem.Checked = True
    Set lstItem = lstOptions.ListItems.Add(, cnstOPT_EXECUTE, "Execute Command")
    lstItem.Checked = True
    Set lstItem = lstOptions.ListItems.Add(, cnstOPT_OUTPARAM, "Retrieve Output Parameters")
    lstItem.Checked = True
    Set lstItem = lstOptions.ListItems.Add(, cnstOPT_RETURNRS, "Return Recordset")
    lstItem.Checked = False

    SetFormState

    SetInsertCaption

    End Sub

    Private Sub Form_Paint()
    SetInsertCaption
    End Sub

    Private Sub lstADOObjects_AfterLabelEdit(Cancel As Integer, NewString As String)
    lstADOObjects.SelectedItem.Text = NewString
    txtPreview.Text = GenADOCode(Me)
    End Sub

    Private Sub lstOptions_ItemCheck(ByVal Item As MSComctlLib.ListItem)

    ' ensure no incompatible options are checked

    If Item.Key = cnstOPT_DEFINEPARAM Then
    lstOptions.ListItems(cnstOPT_REFRESHPARAM).Checked = Not Item.Checked
    ElseIf Item.Key = cnstOPT_REFRESHPARAM Then
    lstOptions.ListItems(cnstOPT_DEFINEPARAM).Checked = Not Item.Checked
    End If

    If Item.Key = cnstOPT_OUTPARAM Then
    lstOptions.ListItems(cnstOPT_RETURNRS).Checked = False
    ElseIf Item.Key = cnstOPT_RETURNRS Then
    lstOptions.ListItems(cnstOPT_OUTPARAM).Checked = False
    End If

    txtPreview.Text = GenADOCode(Me)

    End Sub

    Private Sub LoadSprocList()

    ' load list of stored procedures from database

    On Error GoTo ErrHandler

    cboSproc.Clear

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    cn.CursorLocation = adUseClient
    cn.ConnectionString = txtConnectionString.Text

    ' open connection
    cn.Open

    ' associate connection with recordset
    Set rs.ActiveConnection = cn

    ' open recordset
    rs.Open "select name from sysobjects where " & _
    "uid = user_id() and type = 'P' and " & _
    "name not like 'dt_%'"

    ' disconnect and close connection
    Set rs.ActiveConnection = Nothing
    cn.Close

    cboSproc.Clear
    Do While Not rs.EOF
    cboSproc.AddItem rs("name")
    rs.MoveNext
    Loop

    ' set to first sproc in list
    cboSproc.ListIndex = cboSproc.ListCount - 1

    rs.Close

    ExitSub:

    ' set the form state
    SetFormState
    Exit Sub

    ErrHandler:

    MsgBox "Error: " & Err.Description

    GoTo ExitSub

    End Sub

    Private Sub txtCommand_Validate(Cancel As Boolean)
    txtPreview.Text = GenADOCode(Me)
    End Sub

    Private Sub txtConnection_Validate(Cancel As Boolean)
    txtPreview.Text = GenADOCode(Me)
    End Sub

    Private Sub txtConnectionString_Validate(Cancel As Boolean)
    If Trim$(txtConnectionString.Text) = "" Then
    cboSproc.Clear
    SetFormState
    Else
    LoadSprocList
    End If
    End Sub

    Private Sub txtParameter_Validate(Cancel As Boolean)
    txtPreview.Text = GenADOCode(Me)
    End Sub

    Private Sub txtParameters_Validate(Cancel As Boolean)
    txtPreview.Text = GenADOCode(Me)
    End Sub

    Private Sub txtRecordset_Validate(Cancel As Boolean)
    txtPreview.Text = GenADOCode(Me)
    End Sub

    Private Sub SetFormState()

    ' enable/disable controls based on valid stored procedure
    frameCodeGen.Enabled = (cboSproc.ListCount > 0)
    framePreview.Enabled = (cboSproc.ListCount > 0)
    If cboSproc.ListCount = 0 Then
    txtPreview.Text = ""
    End If

    End Sub

    Private Function GetCurrentProc() As String

    ' determine name of proc to insert code into

    On Error GoTo ErrHandler

    Dim lngStartLine As Long
    Dim lngStartCol As Long
    Dim lngEndLine As Long
    Dim lngEndCol As Long
    Dim strProcName As String

    ' determine current location in code window
    VBInstance.ActiveCodePane.CodeModule.CodePane.GetSelection lngStartLine, lngStartCol, lngEndLine, lngEndCol

    On Error Resume Next

    strProcName = ""

    strProcName = VBInstance.ActiveCodePane.CodeModule.ProcOfLine(lngStartLine, vbext_pk_Proc)
    If strProcName = "" Then
    strProcName = VBInstance.ActiveCodePane.CodeModule.ProcOfLine(lngStartLine, vbext_pk_Get)
    End If
    If strProcName = "" Then
    strProcName = VBInstance.ActiveCodePane.CodeModule.ProcOfLine(lngStartLine, vbext_pk_Let)
    End If
    If strProcName = "" Then
    strProcName = VBInstance.ActiveCodePane.CodeModule.ProcOfLine(lngStartLine, vbext_pk_Set)
    End If

    GetCurrentProc = strProcName

    Exit Function

    ErrHandler:

    GetCurrentProc = ""

    End Function

    Public Sub SetInsertCaption()

    ' set caption to name of current proc

    Dim strProcName As String
    strProcName = GetCurrentProc()
    If strProcName = "" Then
    btnInsert.Enabled = False
    strProcName = "{none}"
    Else
    btnInsert.Enabled = True
    End If

    ' determine insert point
    lblInsertInto.Caption = "Insert Into Procedure:" & vbCrLf & _
    strProcName

    End Sub
    : ***************
    :
    : 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")
    :
    : End Sub
    :
    : '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
    :
    : MyCommand.ActiveConnection.Open()
    :
    : 'Execute the command
    : MyCommand.Execute()
    :
    : 'Place the identity value into a local variable
    : 'To be used elsewhere
    : dim strID as string = MyCommand.Parameters("@ID").Value
    :
    : 'close connection string
    : MyCommand.ActiveConnection.Close()
    : End Sub
    :
    :
    :
    :
    : ***********
    :
    : Thanks
    : Prashant
    :
    :




Sign In or Register to comment.