-
Mar 13th, 2014, 01:31 PM
#1
Thread Starter
New Member
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.
-
Mar 13th, 2014, 02:10 PM
#2
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
-
Mar 13th, 2014, 03:36 PM
#3
Thread Starter
New Member
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?
-
Mar 13th, 2014, 03:52 PM
#4
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|