[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

convert code to be used in stored procedure

Posted on 2013-06-10
19
Medium Priority
?
342 Views
Last Modified: 2013-06-12
I am trying to convert my vb6 code into a stored procedure in SQL.  I am very unfamiliar with doing stored procedures in SQL.  I pasted my code below in hopes that someone can show me how I can change it so that I begin processing it on the server side.

Thanks for any help you can offer

'This section selects the people from Registration that will be put into Attendance------------

        esql = " select * From tblRegistration where Fiscal = '" & Fiscal & "' And Combo11 = '" & Combo2 & "' "
        
       
        
        
                                             
      rec.CursorType = adOpenDynamic
      rec.CursorLocation = adUseClient
      rec.LockType = adLockOptimistic
      rec.Open esql, conn, , , adCmdText


Do Until rec.EOF

'-----This section will check for duplicates--------------------------------------

        Set rsClient = conn.Execute("Select ID from AttendanceGrid Where Fiscal = '" & Fiscal & "' And [ID] = '" & rec!ID & "' And [Last Name] = '" & rec!Text4 & "' And [First Name] = '" & rec!Text5 & "' And Committee = '" & rec!Combo11 & "' And Month = '" & Combo3.Text & "' And Activity = '" & cboActivity.Text & "'")
        

 
'-------This section will insert the data into AttendanceGrid-----------------------------

 
 If rsClient.BOF = True Then  'no duplicate found.. proceed to insert record
        
        conn.Execute ("insert into AttendanceGrid (ID,Nu,[Client ID],[Last Name],[First Name],Committee,BirthDate,Month,Activity,ProgType,[CSW/TitleXX],Fiscal,EntryTime,[From],[To],ActivityLength) VALUES ('" & rec("ID").Value & "','" & rec("Text22").Value & "','" & rec("Text1").Value & "','" & rec("Text4").Value & "','" & rec("Text5").Value & "','" & rec("Combo11").Value & "','" & rec("Text7").Value & "','" & Combo3.Text & "','" & cboActivity.Text & "','Core','" & rec![CSW/TitleXX] & "','" & Fiscal & "','" & ServerTime & "','" & Combo8 & "','" & Combo9 & "','" & Label15 & "')")
    

rec.MoveNext

Loop


Set rsClient = Nothing

Open in new window

0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236434
Basis template:
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
      -- Add the parameters for the stored procedure here
      <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
      <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Open in new window

0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236464
you are passing in sql and/or using followed objects/values as conditions (you need to know and/or define the appropriate datatype:

Fiscal => maybe String???
Combo1.Text => String
Combo2.Text => String
Combo3.Text => String
Combo4.Visible => Boolean
cboActivity.Text => String
Combo8.Text => String
Combo9.Text => String
Label15.Caption => String

Declare you stored procedure:
USE yourDatabaseName
GO

CREATE PROCEDURE yourStoredProcedureName
    @Fiscal varchar(max),
    @Combo1 varchar(max),
    @Combo2 varchar(max),
    @Combo3 varchar(max),
    @Combo4Visible  int,
    @cboActivity varchar(max),
    @Combo8 varchar(max),
    @Combo9 varchar(max),
    @Label15 varchar(max)
AS
BEGIN
     SET NOCOUNT ON;
---- Here put your code (T-SQL code, non VB6) and SQL-Commands
---- it isn't always required but i recommend to put ; after each command


END
GO

Open in new window


http://msdn.microsoft.com/en-us/library/ms345415.aspx
http://msdn.microsoft.com/de-de/library/bb510625(v=sql.100).aspx
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236508
some insert/select examples you can use nto your stored procedure:

http://msdn.microsoft.com/de-de/library/dd776381(v=sql.105).aspx
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:al4629740
ID: 39236839
would it look like this

USE yourDatabaseName
GO

CREATE PROCEDURE yourStoredProcedureName
    @Fiscal varchar(max),
    @Combo1 varchar(max),
    @Combo2 varchar(max),
    @Combo3 varchar(max),
    @Combo4Visible  int,
    @cboActivity varchar(max),
    @Combo8 varchar(max),
    @Combo9 varchar(max),
    @Label15 varchar(max)
AS
BEGIN
     SET NOCOUNT ON;

Select ID from AttendanceGrid Where Fiscal = '" & Fiscal & "' And [ID] = '" & rec!ID & "' And [Last Name] = '" & rec!Text4 & "' And [First Name] = '" & rec!Text5 & "' And Committee = '" & rec!Combo11 & "' And Month = '" & Combo3.Text & "' And Activity = '" & cboActivity.Text & "'

insert into AttendanceGrid (ID,Nu,[Client ID],[Last Name],[First Name],Committee,BirthDate,Month,Activity,ProgType,[CSW/TitleXX],Fiscal,EntryTime,[From],[To],ActivityLength) VALUES ('" & rec("ID").Value & "','" & rec("Text22").Value & "','" & rec("Text1").Value & "','" & rec("Text4").Value & "','" & rec("Text5").Value & "','" & rec("Combo11").Value & "','" & rec("Text7").Value & "','" & Combo3.Text & "','" & cboActivity.Text & "','Core','" & rec![CSW/TitleXX] & "','" & Fiscal & "','" & ServerTime & "','" & Combo8 & "','" & Combo9 & "','" & Label15 & "')

END
GO
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39237006
It isn't Vb6... You have to modify just a little bit your select and insert command to a insert-select command, like example:

Insert yourtargettable (column1, column2, ...)
Select column1, column2,...
From yoursourcetable 
-- here your conditions
Where column1 = @yourvariable;

Open in new window

see that variable begin with @ and that the SQL command ends with ;   , another thing... You don't need quotes anymore & "'," & <<< you can replace this with just a coma   ,

I will take a look into your code again, but I need a couple of hours... I will send you a better example (someone which can really works)...

Regards
0
 

Author Comment

by:al4629740
ID: 39237805
Thank you so much
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39237833
Are these all objects and variables you are using in VB6 to insert / update data ???

Fiscal => maybe String???
Combo1.Text => String
Combo2.Text => String
Combo3.Text => String
Combo4.Visible => Boolean
cboActivity.Text => String
Combo8.Text => String
Combo9.Text => String
Label15.Caption => String
0
 

Author Comment

by:al4629740
ID: 39238053
Also, here are the variables that go to the second executing statement some of which are redundant variables

ID
Text22
Text1
Text4
Text5
Combo11
Text7
Combo3
cboActivity
[CSW/TitleXX]
Fiscal
ServerTime ==> date
Combo8
Combo9
Label15


'This section selects the people from Registration that will be put into Attendance------------

        esql = " select * From tblRegistration where Fiscal = '" & Fiscal & "' And Combo11 = '" & Combo2 & "' "
        
       
        
        
                                             
      rec.CursorType = adOpenDynamic
      rec.CursorLocation = adUseClient
      rec.LockType = adLockOptimistic
      rec.Open esql, conn, , , adCmdText


Do Until rec.EOF

'-----This section will check for duplicates takes the previous recordset and checks for duplicates--------------------------------------

        Set rsClient = conn.Execute("Select ID from AttendanceGrid Where Fiscal = '" & Fiscal & "' And [ID] = '" & rec!ID & "' And [Last Name] = '" & rec!Text4 & "' And [First Name] = '" & rec!Text5 & "' And Committee = '" & rec!Combo11 & "' And Month = '" & Combo3.Text & "' And Activity = '" & cboActivity.Text & "'")
        

 
'-------This section will actually insert the data into AttendanceGrid-----------------------------

 
 If rsClient.BOF = True Then  'no duplicate found.. proceed to insert record
        
        conn.Execute ("insert into AttendanceGrid (ID,Nu,[Client ID],[Last Name],[First Name],Committee,BirthDate,Month,Activity,ProgType,[CSW/TitleXX],Fiscal,EntryTime,[From],[To],ActivityLength) VALUES ('" & rec("ID").Value & "','" & rec("Text22").Value & "','" & rec("Text1").Value & "','" & rec("Text4").Value & "','" & rec("Text5").Value & "','" & rec("Combo11").Value & "','" & rec("Text7").Value & "','" & Combo3.Text & "','" & cboActivity.Text & "','Core','" & rec![CSW/TitleXX] & "','" & Fiscal & "','" & ServerTime & "','" & Combo8 & "','" & Combo9 & "','" & Label15 & "')")
    


rec.MoveNext

Loop


Set rsClient = Nothing[code]

Open in new window

0
 

Author Comment

by:al4629740
ID: 39238694
Basically the first command selects all the people

Next it Loops through all the records one by one making sure there is no duplication of the current record

After it satisfies that previous command, then it executes the inserting of the new record into the AttendanceGrid

Then it starts over in the loop and goes to the next record
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 2000 total points
ID: 39239297
Hi,

Into your SQL-Server Manager you have to run this to create the procedure
USE yourDatabaseName
GO

CREATE PROCEDURE yourStoredProcedureName
    @Fiscal varchar(max),
    @Combo2 varchar(max),
    @Combo3 varchar(max),
    @cboActivity varchar(max),
    @Combo8 varchar(max),
    @Combo9 varchar(max),
    @Label15 varchar(max)
AS
BEGIN

SET NOCOUNT ON;

MERGE AttendanceGrid AS Target
USING (
    SELECT *
    FROM tblRegistration
    WHERE Fiscal = @Fiscal AND Combo11 = @Combo2
) AS Source
    ON Target.Fiscal = Source.Fiscal
   AND Target.ID = Source.ID
   AND Target.[Last Name] = Source.Text4
   AND Target.[First Name] = Source.Text5
   AND Target.Committee = Source.Combo11
   AND Target.Month = @Combo3
   AND Target.Activity = @cboActivity)
WHEN NOT MATCHED THEN
INSERT (
    ID,
    Nu,
    [Client ID],
    [Last Name],
    [First Name],
    Committee,
    BirthDate,
    Month,
    Activity,
    ProgType,
    [CSW/TitleXX],
    Fiscal,
    EntryTime,
    [From],
    [To],
    ActivityLength
) VALUES (
    Source.ID,
    Source.Text22,
    Source.Text1,
    Source.Text4,
    Source.Text5,
    Source.Combo11,
    Source.Text7,
    @Combo3,
    @cboActivity,
    'Core',
    Source.[CSW/TitleXX],
    @Fiscal,
    GetDate(),
    @Combo8,
    @Combo9,
    @Label15
);

END
GO

Open in new window

Check this for missing things...

regards
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39239318
you can run this as a sql in vba... like this:
esql = "MERGE AttendanceGrid AS Target " & _
       "USING ( " & _
       "    SELECT * " & _
       "    FROM tblRegistration " & _
       "    WHERE Fiscal = @Fiscal AND Combo11 = @Combo2 " & _
       ") AS Source " & _
       "    ON Target.Fiscal = Source.Fiscal " & _
       "   AND Target.ID = Source.ID " & _
       "   AND Target.[Last Name] = Source.Text4 " & _
       "   AND Target.[First Name] = Source.Text5 " & _
       "   AND Target.Committee = Source.Combo11 " & _
       "   AND Target.Month = @Combo3 " & _
       "   AND Target.Activity = @cboActivity) " & _
       "WHEN NOT MATCHED THEN " & _
       "INSERT ( " & _
       "    ID, " & _
       "    Nu, " & _
       "    [Client ID], " & _
       "    [Last Name], " & _
       "    [First Name], " & _
       "    Committee, " & _
       "    BirthDate, " & _
       "    Month, " & _
       "    Activity, " & _
       "    ProgType, " & _
       "    [CSW/TitleXX], " & _
       "    Fiscal, " & _
       "    EntryTime, " & _
       "    [From], " & _
       "    [To], " & _
       "    ActivityLength " & _
       ") VALUES ( " & _
       "    Source.ID, " & _
       "    Source.Text22, " & _
       "    Source.Text1, " & _
       "    Source.Text4, " & _
       "    Source.Text5, " & _
       "    Source.Combo11, " & _
       "    Source.Text7, " & _
       "    @Combo3, " & _
       "    @cboActivity, " & _
       "    'Core', " & _
       "    Source.[CSW/TitleXX], " & _
       "    @Fiscal, " & _
       "    GetDate(), " & _
       "    @Combo8, " & _
       "    @Combo9, " & _
       "    @Label15 " & _
       ");"

Open in new window

but don't forget to replace the SQL-Variables with your VB6-Variable, i mean:
- @Label15
+ '" & replace(Label15.Caption, "'", "''") & "'

if you have questions just write...
regards
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39239328
If you create some indexes for your queries it will work even faster... ;)

this is want i mean with merge by your other question...

some references:

http://msdn.microsoft.com/de-de/library/bb510625.aspx

http://www.purplefrogsystems.com/blog/2011/12/introduction-to-t-sql-merge-basics/
0
 

Author Comment

by:al4629740
ID: 39239560
Let me spend some time analyzing this
0
 

Author Comment

by:al4629740
ID: 39239575
If I run the code above to create the stored procedure, will it change anything in the tables?
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39239597
If I run the code above to create the stored procedure, will it change anything in the tables?
do you mean this one ???:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_28153381.html#a39239297

no, it doesn't make change in the table... you need to execute the procedure to do this...
in vb6 with
yourConnectionObject.Execute "CALL yourStoredProcedure('param1', 'param2',...)"

Open in new window


this other one:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_28153381.html#a39239318

makes changes immediately in the table... you can use this to replace you do/loop code posted.
but first you need to do the changes - in the code - that i've mentioned in this post
0
 

Author Comment

by:al4629740
ID: 39239659
I want to ask about the indexes to make the query run faster but I will ask in another question
0
 

Author Comment

by:al4629740
ID: 39239682
yourConnectionObject.Execute "CALL yourStoredProcedure('param1', 'param2',...)"
1:

When putting the parameters in the call procedure does it go in the order below

   Source.ID,
    Source.Text22,
    Source.Text1,
    Source.Text4,
    Source.Text5,
    Source.Combo11,
    Source.Text7,
    @Combo3,
    @cboActivity,
    'Core',
    Source.[CSW/TitleXX],
    @Fiscal,
    GetDate(),
    @Combo8,
    @Combo9,
    @Label15
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39240125
These are your parameters :
CREATE PROCEDURE yourStoredProcedureName
    [b]@Fiscal varchar(max),
    @Combo2 varchar(max),
    @Combo3 varchar(max),
    @cboActivity varchar(max),
    @Combo8 varchar(max),
    @Combo9 varchar(max),
    @Label15 varchar(max)[/b]
AS
BEGIN

Open in new window

in vb6 you need something like this:
yourConnectionObject.Execute "CALL yourStoredProcedure('" & replace(Fiscal, "'", "''") & "', '" & replace(Combo2.Text, "'", "''") & "', '" & replace(Combo3.Text, "'", "''") & "', '" & replace(cboActivity.Text, "'", "''") & "', '" & replace(Combo8.Text, "'", "''") & "', '" & replace(Combo9.Text, "'", "''") & "',  '" & replace(Label15.Caption, "'", "''") & "')"

Open in new window

Regards
0
 

Author Comment

by:al4629740
ID: 39241972
I get an incorrect syntax error on this line for fiscal

conn.Execute "CALL TestProcedure('" & Replace(Fiscal, "'", "''") & "', '" & Replace(Combo2.Text, "'", "''") & "', '" & Replace(Combo3.Text, "'", "''") & "', '" & Replace(cboActivity.Text, "'", "''") & "', '" & Replace(Combo8.Text, "'", "''") & "', '" & Replace(Combo9.Text, "'", "''") & "',  '" & Replace(Label15.Caption, "'", "''") & "')"

Open in new window

0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question