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.

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

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

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