Results 1 to 4 of 4

Thread: Move VB.Net code to a stored procedure in SQL Server and call procedure from vb.net

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2006
    Posts
    8

    Move VB.Net code to a stored procedure in SQL Server and call procedure from vb.net

    Hi All,

    I have this code in vb.net and what I want to do is move the code into a stored procedure in SQL Server. Basically, all the code does is loop through records in one table and insert the records from that table into another table. Pretty simple... Not sure how to do that in SQL Server and also call the stored procedure from vb.net. I would have to feed the date in from vb.net into the stored procedure.

    Any help would be appreciated!

    Code from vb.net below:

    da = New SqlClient.SqlDataAdapter("SELECT * FROM vwPeople Where MemberStatus <> 'Former Member' OR MemberStatus <> 'Inactive Member' Order By LastName Asc", SqlConn)
    da.Fill(ds, "GETPERSONS1")

    Dim i As Integer = 0

    While i <= ds.Tables("GETPERSONS1").Rows.Count - 1
    Dim name As String
    name = u.FTM(ds.Tables("GETPERSONS1").Rows(i).Item("LastName")) + " " + u.FTM(ds.Tables("GETPERSONS1").Rows(i).Item("FirstName"))
    u.ExecuteSQL(SqlConn, "INSERT INTO tblContributionsN (PeopleID, ContributionDate, Name, Amount, Code, GivingType, EnvelopeNumber, Household, CheckNumber, ContribType, Service, Description) values ('" & Val(ds.Tables("GETPERSONS1").Rows(i).Item("RecordId")) & "', '" & Format(Me.dtWorshipDate.Value, "MM/dd/yyyy") & "', '" & name & "', '0', '1-General Fund', '', '" & Val(ds.Tables("GETPERSONS1").Rows(i).Item("EnvelopeNumber")) & "','" & u.FTM(ds.Tables("GETPERSONS1").Rows(i).Item("Household")) & "', '', 'Regular', '', '')")

    i = i + 1
    End While

    At the end of the SP, I would want to return something to vb.net telling me it was done.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,835

    Re: Move VB.Net code to a stored procedure in SQL Server and call procedure from vb.n

    It should all be in SQL in the first place:

    Code:
    INSERT INTO tblContributionsN (PeopleID, ContributionDate, Name, Amount, Code, GivingType, EnvelopeNumber, Household, CheckNumber, ContribType, Service, Description) 
    select RecordId, getdate(), LastName + ', ' + FirstName, 0, '1-General fund', EvelopeNumber, Household, '', 'Regular', '', ''
    FROM vwPeople Where MemberStatus <> 'Former Member' OR MemberStatus <> 'Inactive Member' Order By LastName Asc
    You can put that in a stored proc and run it... or simply set it right in the code and execute it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2006
    Posts
    8

    Re: Move VB.Net code to a stored procedure in SQL Server and call procedure from vb.n

    Thanks tg. Do you know the code to pass the date into the stored procedure from vb.net?

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,835

    Re: Move VB.Net code to a stored procedure in SQL Server and call procedure from vb.n

    That's what the getdate() was for... it pulls the current date from the system... since that's not hte case here... replace the getdate() with "@WDate"...


    Code:
    create procedure usp_MoveData(@Wdate datetime)
    as
    begin
      INSERT INTO tblContributionsN (PeopleID, ContributionDate, Name, Amount, Code, GivingType, EnvelopeNumber, Household, CheckNumber, ContribType, Service, Description) 
      select RecordId, @WDate, LastName + ', ' + FirstName, 0, '1-General fund', EvelopeNumber, Household, '', 'Regular', '', ''
      FROM vwPeople Where MemberStatus <> 'Former Member' OR MemberStatus <> 'Inactive Member' Order By LastName Asc
    end
    then from VB:
    Code:
    Using myCon as new SQLConnection("put your connection string here")
      Using myCmd as new SQLCommand("usp_MoveData", myCon)
        myCmd.Parameters.AddWithValue("@WDate", dtWorshipDate.Value)
        myCon.Open
        try
          myCmd.ExecuteNonQuery 'it doesn't return anything so we execute it as a non-query
          mycon.Close
        Catch ex as SystemException 
          MesssageBox.Show(ex.ToSting) 
        end try
      end Using 'command
    end Using 'Connection
    I'm shooting from the hip, so I don't know if it'll work as is... but it should get you into the neighborhood at least.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width