SQL Server Performance

how can i use a stored procedure from vb6

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by Druss, Feb 14, 2005.

  1. Druss New Member

    i want to add a record to a table via storedprocedure.

    i did a stroedprocedure in sqlserver. it runs well.
    how can i use the stored procedures in vb6.0?

    stored procedure;

    CREATE PROCEDURE dbo.addNewRow
    (
    @S_CID int,
    @S_Desc nvarchar(255)
    )
    AS
    INSERT INTO tblDummy
    (
    S_CID,
    S_Desc,
    S_Date
    )
    VALUES
    (
    @S_CID,
    @S_Desc,
    GETDATE()
    )
    GO

    (moved from General DBA section to here due to topic relevancy)
    Druss, Feb 14, 2005
    #2

  2. satya Moderator

    satya, Feb 15, 2005
    #2

  3. ranjitjain New Member

    U can call the SP in vb using Command object and then passing parameters which u want to insert in table.
    U can directly execute sp using connection object in VB.

    When SP dont return any rows its good to execute it with connection object
    else with command object
    ranjitjain, Feb 15, 2005
    #2

  4. dineshasanka Moderator

    Private Sub RunRestoreSP(buffer As String)
    Dim aCmdI As New adodb.Command
    Dim RecArr(5) ' parameters
    aCmdI.ActiveConnection = connConfig ' Connection object
    aCmdI.CommandTimeout = 10
    aCmdI.CommandType = adCmdStoredProc
    aCmdI.CommandText = "addNewRow" ' SP Name
    RecArr(0) = Trim(buffer)
    RecArr(1) = txtSource.Text & "" & Trim(buffer) & ".bak"
    RecArr(2) = Trim(buffer) & "_Data"
    RecArr(3) = strSqlPath & Trim(buffer) & "_Data.mdf"
    RecArr(4) = Trim(buffer) & "_Log"
    RecArr(3) = strSqlPath & Trim(buffer) & "_Log.ldf"
    aCmdI.Execute , RecArr, adCmdStoredProc
    End Sub
    dineshasanka, Feb 16, 2005
    #2

  5. ranjitjain New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by dineshasanka</i><br /><br />Private Sub RunRestoreSP(buffer As String)<br />Dim aCmdI As New adodb.Command<br /> Dim RecArr(5) ' parameters<br /> aCmdI.ActiveConnection = connConfig ' Connection object<br /> aCmdI.CommandTimeout = 10<br /> aCmdI.CommandType = adCmdStoredProc <br /> aCmdI.CommandText = "addNewRow" ' SP Name<br /> RecArr(0) = Trim(buffer)<br /> RecArr(1) = txtSource.Text & "" & Trim(buffer) & ".bak"<br /> RecArr(2) = Trim(buffer) & "_Data"<br /> RecArr(3) = strSqlPath & Trim(buffer) & "_Data.mdf"<br /> RecArr(4) = Trim(buffer) & "_Log"<br /> RecArr(3) = strSqlPath & Trim(buffer) & "_Log.ldf"<br /> aCmdI.Execute , RecArr, adCmdStoredProc<br />End Sub<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Hi dinesh, u have given good eg. but ive seen optimising VB code for speed can be achieved more faster when using trim$() instead of Trim. try it out[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
    ranjitjain, Feb 16, 2005
    #2

  6. dineshasanka Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ranjitjain</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by dineshasanka</i><br /><br />Private Sub RunRestoreSP(buffer As String)<br />Dim aCmdI As New adodb.Command<br /> Dim RecArr(5) ' parameters<br /> aCmdI.ActiveConnection = connConfig ' Connection object<br /> aCmdI.CommandTimeout = 10<br /> aCmdI.CommandType = adCmdStoredProc <br /> aCmdI.CommandText = "addNewRow" ' SP Name<br /> RecArr(0) = Trim(buffer)<br /> RecArr(1) = txtSource.Text & "" & Trim(buffer) & ".bak"<br /> RecArr(2) = Trim(buffer) & "_Data"<br /> RecArr(3) = strSqlPath & Trim(buffer) & "_Data.mdf"<br /> RecArr(4) = Trim(buffer) & "_Log"<br /> RecArr(3) = strSqlPath & Trim(buffer) & "_Log.ldf"<br /> aCmdI.Execute , RecArr, adCmdStoredProc<br />End Sub<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Hi dinesh, u have given good eg. but ive seen optimising VB code for speed can be achieved more faster when using trim$() instead of Trim. try it out[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thankx for the infomation. I am hardly using VB anyway [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
    dineshasanka, Feb 16, 2005
    #2

Share This Page