June 10, 2005 at 7:29 am
I need a little help with my SP in SQL server 2003. I am working with vb.net 2003 for this application.
I have two databases, one (DB1) has information that is requested. The other (DB2) has a table that I want to copy data into.
How can I do the following ...
1) Use a button click event to call the sp and pass it a parameter from the querystring (x)
I am guessing something like the following
PrivateSub btnPublish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPublish.Click
Dim myconnection As SqlConnection = New SqlConnection("Server = myserver; database=Db2; user id=USERID; password ='PASSWORD'")
2) Using a stored procedure, select data from DB1 where @parameter = x
thus far I have only the following and I need more but am not sure how to do it.
CREATE PROCEDURE DBO.COPY_DATA (
@RequestID bigint,
@Ann_PostDate datetime,
@AnnUserID varchar(50),
@Ann_Title varchar(200),
@Ann_Body varchar(2000),
@AnnExpireDate datetime,
@AnnDocLink varchar(200)
)
AS begin
insert into DB2.ANNOUNCEMENTS (
Ann_PostDate,
AnnUserID,Ann_Title,
Ann_Body,
AnnExpireDate,
AnnDocLink,
RequestID
)
Values (
@RequestID,
@Ann_PostDate,
@AnnUserID,
@Ann_Title,
@Ann_Body,
@AnnExpireDate,
@AnnDocLink
)
End
Go
The parameters can all be set to a static value easily but, how do I set it up to be dynamic as it will change with every request.
The button click is the approval, the data in DB1 is requested however it does not get written to DB2 until it is approved.
TIA,
-lance
June 13, 2005 at 8:00 am
This was removed by the editor as SPAM
June 13, 2005 at 1:41 pm
If both databases are on the same server, just write a stored procedure in DB2 that selects from DB1, and inserts into DB2, and call that proc from your application.
Don't return the data to the client, it's unnecessary unless the client needs to actually see the information.
June 13, 2005 at 2:31 pm
for this app they actually will see the data but not becuase the SP displays it.
I am sitting in the 2311A course today and we have gone across a little bit of stuff for a SP. I think my syntax may be a litle off for what I am actually trying to do.
But, I thought the best route would be to use DB1 to write to DB2 not the other way around? then I can call it and pass the @requestID via the button click using sqlhelper.
I think I am on the right track now... but, I am still a rookie with it.
June 13, 2005 at 2:36 pm
Deciding which one does the writing is a matter of preference. I'd lean toward the proc existing in DB2 simply because I'd rather all data manipulating procs reside in the database they manipulate. Otherwise, you may end up having the same insert/update/delete in multiple locations, and have to maintain it in multiple locations if the table ever changes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply