 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / July 2007Create Storedprocedure with VB6? Is this possible?
Can a Storedprocedure be created using code in VB6? If so how? Is there some sample code? TIA Sam Sure. I have examples in all of my Hitchhiker Guide books. Basically, you can create and execute an SQL query that includes the CREATE PROCEDURE call. I don't think the VB6 IDE itself could create a stored procedure, but there are lots of other tools that will. SQL Server Enterprise Manager can and so can SQL Server Management Studio (there's even a free Express version). hth  Signature____________________________________ William (Bill) Vaughn Author, Mentor, Consultant, Dad, Grandpa Microsoft MVP INETA Speaker www.betav.com www.betav.com/blog/billva Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- > Can a Storedprocedure be created using code in VB6? > [quoted text clipped - 5 lines] > > Sam > Sure. I have examples in all of my Hitchhiker Guide books. > Basically, you can create and execute an SQL query that includes the CREATE [quoted text clipped - 4 lines] > > hth <snipped> And then there is your favorite engine William - Jet - which allows a developer to create and store "ADO Stored Queries" using ADO from a VB6 app. But I would be slow to recommend it and would hate to defend it. <bg> -ralph I don't want to use a third party tool. Is there any way I can create a storedprocedure from a VB6 Application. Or can itbe done another way, I do not want to use .Net TIA Sam > Sure. I have examples in all of my Hitchhiker Guide books. > Basically, you can create and execute an SQL query that includes the [quoted text clipped - 15 lines] >> >> Sam >I don't want to use a third party tool. > Is there any way I can create a storedprocedure > from a VB6 Application. Or can itbe done another way, > I do not want to use .Net To paraphrase the answer you've already been given... 1. Create and open a Connection object, using a login context that's permitted to create stored procedures; 2. Call the connection's Execute method, to execute a CREATE PROCEDURE statement; This, of course, assumes you have already composed the CREATE PROCEDURE statement, and that it is valid, and that a procedure with the same name doesn't already exist. In actuality, it assumes dozens of other things too tedious to go into here, so if, having been given accurate advice, you are still unable to do this, try posting some code so we can see what you've tried, along with whatever errors it may have thrown. -Mark > TIA > [quoted text clipped - 19 lines] >>> >>> Sam Mark, William Vaughn did not answer my question Quote "Sure. I have examples in all of my Hitchhiker Guide books. Basically, you can create and execute an SQL query that includes the CREATE PROCEDURE call. don't think the VB6 IDE itself could create a stored procedure, but there are lots of other tools that will. SQL Server Enterprise Manager can and so can SQL Server Management Studio (there's even a free Express version)." This does NOT answer my simple question. I want to know if VB6 can do it. Ralph answer was a remark to William Please answer my question as I am here for help not to be put down by peoples replys. To explain my question again. Can VB6 create a stored procedure on the fly. In the same way as I can create a table with VB Alter a Table With VB Not a SQL manager Tool Sample Code Stored procedure I want to create in MSSQL --Start Here ' CREATE PROCEDURE [dbo].[ReturnAmt] /*-- Add the parameters for the stored procedure here */ @RetBal int OUTPUT AS BEGIN /* Procedure body */ SELECT @RetBal = Sum(dbo.Tick.MyVal) From dbo.Tick End RETURN @RetBal --Ends Here Sample VB6 Code Dim MyConn As ADODB.Connection Set MyConn = New ADODB.Connection On Error GoTo ErrorHandler ' Enable error-handling routine. MyConn.Open SqlConn strSql = "CREATE PROCEDURE [dbo].[ReturnAmt] " & _ "'/*-- Add the parameters for the stored procedure here */ " & _ "@RetBal int OUTPUT" & _ "AS " & _ "BEGIN " & _ "/* Procedure body */" & _ "SELECT" & _ "@RetBal = Sum(dbo.Tick.MyVal) " & _ "From dbo.Tick " & _ "End " & _ "RETURN @RetBal;" MyConn.Execute strSql MyConn.Close Set MyConn = Nothing This Does Not work Any Help Appreciated Sam >>I don't want to use a third party tool. >> Is there any way I can create a storedprocedure [quoted text clipped - 40 lines] >>>> >>>> Sam > Mark, > [quoted text clipped - 55 lines] > "/* Procedure body */" & _ > "SELECT" & _ This looks like a syntax error (no space after SELECT.) To solve problems like this it is almost always useful to output (using Debug.Print or something similar) exactly what you're sending to the db engine (via ADO), for a reality check. If you don't see any problems with it, paste it into Query Analyzer -- if it doesn't work in QA, chances it will work with ADO are quite poor. Another useful technique for dealing with problems is to examine the Connection.Errors collection, but at the very least your error handler should make it easy for you to see what's in Err.Description. Error output may not always be entirely forthcoming, but it is always a reasonable place to start... Which would be exactly why I suggested you include any error information, "does not work" is much too vague, and in a larger sense, it isn't even accurate. "Does not work as you expected," is a given, but in this case, you passed a program statement that did not conform to syntax rules; ADO threw an error, which is exactly what it's supposed to do. Rule #1 of database programming is that exceptions will occur. Dealing with them constructively should be integral to your design. -Mark > "@RetBal = Sum(dbo.Tick.MyVal) " & _ > "From dbo.Tick " & _ [quoted text clipped - 55 lines] >>>>> >>>>> Sam The Following code actually works VB6 and ADO no .NET strSql = "CREATE PROCEDURE [dbo].[ReturnAmtTest1] " & vbLf & _ "/*-- Add the parameters for the stored procedure here */ " & vbLf & _ "@RetBal int OUTPUT " & vbLf & _ "AS " & vbLf & _ "BEGIN " & vbLf & _ "/* Procedure body */" & vbLf & _ "SELECT " & vbLf & _ "@RetBal = Sum(dbo.TableName.FieldName) " & vbLf & _ "From dbo.TableName" & vbLf & _ "End " & vbLf & _ "RETURN @RetBal;" Problem was I had customised my error message from a previous version and just cut the source in and modified the SQL string the exception message that was appearing was a end user message anyway sorted. Hope the sample code is of some use to someone Thanks for your help Sam >> Mark, >> [quoted text clipped - 139 lines] >>>>>> >>>>>> Sam Sam question was Cross posted in VB.General.Discusion "Mark Y" Wrote +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ You can code XPs (Extended Stored Procedures) using VB6 (COM). Details are in SQL Server Books On Line. Examples can be found in SQL 2000 BOL, but there's only "how to keep it running" information in SQL 2K5. Coding XPs is deprecated in SQL 2K5 and are disabled by default (enable in Surface Area Configuration). Under SQL 2K5, CLR SPs are the preferred way, but that doesn't have VB6 support. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ this is a great and straight forward reply which addresed the question asked by me. VB6 cannot do what I want. I will know search for another solution. Any Ideas? Script java??? Many Thanks Sam > Sam question was Cross posted in VB.General.Discusion > [quoted text clipped - 9 lines] > but that doesn't have VB6 support. > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ This refers to *extended* stored procedures, not stored procedures -- two entirely different constructs. This 'answer' doesn't even pertain to your question. -Mark > this is a great and straight forward reply which addresed the question > asked by me. [quoted text clipped - 6 lines] > > Sam This seems to be the story of my life lately. Can't seem to get anything right - sorry to all. Thanks to all who helped, ultimatly we got it working Regards Sam >> Sam question was Cross posted in VB.General.Discusion >> [quoted text clipped - 29 lines] >> >> Sam |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.