[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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


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,
3 Solutions
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 http://www.asp.net/learn/
Éric MoreauSenior .Net ConsultantCommented:
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.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

JapsterexAuthor Commented:
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

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

JapsterexAuthor Commented:
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.
JapsterexAuthor Commented:
Own solution found but much learned from contributors

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now