How do I Write a VB app to execute a stored procedure and a Crystal Report
Hi,
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,
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,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
and Exec Statement
and an example of the Crystal Report Output
including all the calculated fields and Barcode generation
Exec-SP-PartControlCard.sql
partcontrollabel.pdf
and an example of the Crystal Report Output
including all the calculated fields and Barcode generation
Exec-SP-PartControlCard.sql
partcontrollabel.pdf
ASKER
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.
But thanks you guys as you helped me get my head around it, so will share the points accordingly.
Thanks again.
ASKER
Own solution found but much learned from contributors
ASKER
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
Create-SP-PartControlCardData.sql