How do I Write a VB app to execute a stored procedure and a Crystal Report

Posted on 2010-01-04
Last Modified: 2013-12-26

I'm very new to VB and Visual Studio but I know what I want to acheive and have already written the underlying SQL Stored Procedure and the Crystal Report to produce the output reports required by my company. Just need to make a user friendly program to run it.

What I'm trying to do is this :-

Make a Visual Studio Solution that reads a SQL table to give a list of part numbers to the app which the user chooses one, they then enter No. of cards to be printed. Both values are then used as parameters to execute a SQL Stored procedure (already written and tested) which populates a SQL table from which the Crystal Report Reads the records to produce the desired output from the printer.

So in order
1) Choose part number in Visual Studio app (from SQL data lookup in a listbox or combobox)
2) enter number of labels into Visual Studio app (text box ?)
3) Stored procedure uses the chosen values to make data records in a table on the same server- so pass the values from items 1 & 2 to the Stored Procedure
4) Crystal Report Envoked from Visual Studio App to print the records created in the table from item 3
5) App closed no data saved as Stored Procedure clears data table on next execution

Both SP, datatables and Crystal ODBC links to the same SQL Database

Any help appreciated in acheiving my goal,
Question by:Japsterex
    LVL 100

    Assisted Solution

    LVL 2

    Accepted Solution

    Open Visual Studio and create a new ASP.NET Web Application

    1)  Open Default.aspx and drag and drop a Drop Down List on the page and click the ">" smart tag.  Select "Choose Data source" and select and enter your relevant information for a SQL Data Source.  Right click the control, select properties and give it a friendly ID that you can remember, eg.  ddlPartNumber

    2)  Drag and drop a textbox on the page and right click and select properties.  Give it a friendly ID, eg. txtNumberOfLabels

    3,4)  Drag and drop a gridview onto the web page (first view the data, you can do the report later).  Select the smart tag and choose datasource.  Select SQL Datasource and select your stored procedures.  Wire up the variables to the drop down list and the textbox IDs.

    5).  Press F5, click yes to enable debugging and test your app.

    If you want more detailed video tutorials, head over to
    LVL 69

    Assisted Solution

    by:Éric Moreau
    My article (linked by mlmcc) is showing you how to retreive all the data from the application and pass that dataset to the report instead of having CR connect directly to the datasource.

    Author Comment

    Thanks for the responses,
    I think I might mention that all my Stored Procedure does is populate a table based on a select statement from a join on 3 other tables based on the selection parameter "Part Number" then populates two columns (1 calculated from the current week no.) the other from a sequential number taken from the "Number of Labels" paramater. This allows me to create a record for each label with a unique datecode & serial number.
    For this reason the Stored Procedure doesn't return any data.
    All I want to do then is invoke the Crystal Report (which can be an exe file as I'm using old Crystal 8.5 with executable reports distrubution) or a .rpt file which can open in Crystal Viewer. Either ways the only interaction I want with the user is choose part no., choose qty and press print.
    Crystal will then print the records populated by the Stored Procedure and once the process is complete the data is no longer required as we do not want the posibility of duplicated labels printed with the same serial number.
    I might not be doing things the normal way but I've got the Stored Procedure and the Crystal Report already written and working so basically all I want to do is Execute a Stored Procedure with the data chosen from a pulldown list and a integer as the parameters then kick of Crystal to do the printing.
    Sorry but my VB is beginner level but Crystal and SQL are pretty ok :-)
    Tried Solution No. 2 but because my Stored Procedure doesn't return anything I got an error message.
    Please see attached Stored Procedure & ExecuteStatement


    Author Comment

    and Exec Statement
    and an example of the Crystal Report Output
    including all the calculated fields and Barcode generation


    Author Comment

    Right guys, I've altered my Stored Procedure to return the full data set to my Crystal Report and wham it all kinda works without the need for VB or anything else as the Crystal Report prompts for the SP parameters.
    But thanks you guys as you helped me get my head around it, so will share the points accordingly.
    Thanks again.

    Author Closing Comment

    Own solution found but much learned from contributors

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Suggested Solutions

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    737 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

    21 Experts available now in Live!

    Get 1:1 Help Now