Solved

convert code to be used in stored procedure

Posted on 2013-06-10
19
330 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Accepted Solution

by:
BAKADY earned 500 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now