Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Running Stored Procedures from VB6 1
- Thread starter dellyjm
- Start date
-
1
- #2
- Thread starter
- #3
Great.<br><br>Are you using an activeX Control dropped on a form or is this to be done completely in code (in a code module or class module)?<br><br>If you're using a control use the ADO control.<br><br>...now, here we go. If this is a networked app (multiple users) you'll have to decide on your database security scheme before you go any further. Reason. SQL Server will require a logon of some kind. This secuity sheme will determine your use of DSN's (or not), and how those DNS's are created. Still with me ?<br><br>You can use a DSN'less connection (the connection string) and hard code the username and password portion. But you'll have to live with this inconvience later when modifications to data source, and perhaps security scheme, occour.<br><br>You can create a DSN (the preferred method) for the app but each user will have to have access to this DSN. Placed either on the users machine or a 'File DSN' placed somewhere on the network where the users can acces it. Again we re-visit the data security issue. Still there?<br><br>DSN on users machine, typically call for user accounts on the server. You'll have to collaborate with your Database Administrator on this. This basically allways makes sense because you need to control who can do what.<br><br>Example being: you create a DSN on a users machine that allows unlimited read/write. You control what the user does with code in your app. Then..., user creates their own application using Excel, uses the DSN you created and accidently deletes the entire customer database late one friday afternoon. OOPS! ...you end up working that weekend.<br><br>At any rate. the code would look like this:<br>Using ADO<br><br>sub ExecuteSP ()<br><br>Dim Conn as new ADODB.Connection<br>Dim Comm as new ADODB.Command<br><br>' a DSN'Less (hard coded) connection. Provides unlimited <br>' access to data. Uses the default Sys Admin Account<br>' <i>Bad Idea</i> to actually use this account.<br><br>Conn.Open "Provider=SQLOLEDB;Data Source=nameOfYourServer;Initial Catalog=databasename;User Id=sa;pasword=;"<br><br> 'data source is now open. Incidently, look at the <br> 'subject 'Providers' in the ADO reference.<br><br> Comm.CommandText = "nameofstoredprocedure"<br> Comm.CommandType = adCmdStoredProcedure 'a constant <br> Comm.ActiveConnection= Conn<br> Comm.Execute ' execute the stores procedure.<br><br> Conn.Close<br> Set Comm = Nothing<br> Set Conn = Nothing<br>End Sub<br><br>This is like the bare bones stuff. Provider can be changed. The user id <u>should</u> be changed. DSN can be used instead. And the stored procedure doesn't take arguements. There is no recordset returned here either.<br>Use set rs = Comm.Execute instead for that.<br> <br><br>Hope this is helpful information. <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>
- Thread starter
- #5
Hi Dellyjm,<br><br>For parameter passing to SP method. The procedure mentioned by Amiel will be the same if you want but there are other way also to do the same thing. I don't want make you confuse at this point in time. Just go with what Amiel has said with addition to this for parameter in SP for VB6. <br> Comm.CommandText = "nameofstoredprocedure " & "'" & <Parameter> & "'"<br> Comm.CommandType = adCmdStoredProcedure 'a constant <br> Comm.ActiveConnection= Conn<br> Comm.Execute ' execute the stores procedure.<br><br>But keep it in mind that SP has also defined with input parameter through @<Parameter> <Parameter Type><br><br>
- Status
