Printing Pictures on Crystal reports,Assigning parameters from Listbox

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Dear All,

I have a Listbox lstProductID. I Want to print pictures of productid's from lstProductID list box only. how can we assign parameter from lsit box. can anyone help with sample code please?

Thanks in advance
 
3 steps

Create a public variable to hold the id value.
I create a module called 'PublicVariables' and Dimension the variables there:
VB.NET:
Public test As String
Assign the selected listbox value to the variable:
VB.NET:
test = CurrentBox.SelectedItem
Pass the variable to a report parameter:
VB.NET:
Dim rpt As New CrystalReport1
rpt.SetParameterValue("TestParameter", test)
All data types must match - example are all strings.

Let me know if you need more help.
 
There is Error in the Code

Dear DavidT_macktool,

Thanks a lot for Giving your time. There is error msg appears as attachment. 'invalid field' . It is Same for listbox.Selecteditem.Tostring also. But Strictly I want as listbox1.items.tostring. (as we have the system, where users will select items from listbox1 and move them to listbox2. items in listbox2 should be sent to report for print.Can you figure it out please.

Thanks a lot
 

Attachments

  • Untitled-1 copy.jpg
    Untitled-1 copy.jpg
    71.1 KB · Views: 203
Last edited:
test = ListBox1.SelectedItem

Your code takes all the items in the listbox an puts them in the string; You can see what you get with:
msgbox(test)

Also, there must be a parameter on the report called ModelId and it must be a string.

If you are trying to pass several values to the report you should look into a datatable. You will need to collect the selected items in some container (array, datatable, etc.) You then have to make that container available to the report so it can use the values to generate meaningful content.

I'm sorry, there is no simple line of code to pass all the selected items to a single parameter of a report.
 
error msg add values of list box to create dataset

Dear DavidT_macktool and All Forum members,

I know this is not that much simple. But I wrote the following code for the purpose. But i'm getting error msg and can't go further. Can anyone help to solve this code please. Thanks a lot for your help in this regard.

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'
Dim sql AsString = "SELECT picID,Productimage FROM Products WHERE picID = @picID"
'create a connection to the database
Dim con AsNew OleDbConnection(conString)
'create the command
Dim cmd AsNew OleDbCommand(sql, con)
cmd.CommandType = CommandType.Text
'create the parameter
Dim prmID AsNew OleDbParameter
With prmID
.ParameterName = "@picID"
.Value = ListBox1.Items
EndWith
cmd.Parameters.Add(prmID) 'add this to the command

Dim da AsNew OleDbDataAdapter(cmd)
Dim ds AsNew DataSet("Products")
Try
con.Open() 'open the connection
da.Fill(ds) 'fill the dataset

Dim rptCatalogue AsNew CrystalReport1
rptCatalogue.SetDataSource(ds)

CrystalReportViewer1.ReportSource = rptCatalogue
Catch ex As Exception
MessageBox.Show("There is problem Loading Report." & ControlChars.CrLf & ex.Message)
EndTry
EndSub
 
yousuf42 said:
Dear DavidT_macktool and All Forum members,

I know this is not that much simple. But I wrote the following code for the purpose. But i'm getting error msg and can't go further. Can anyone help to solve this code please. Thanks a lot for your help in this regard.

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'
Dim sql AsString = "SELECT picID,Productimage FROM Products WHERE picID = @picID"
'create a connection to the database
Dim con AsNew OleDbConnection(conString)
'create the command
Dim cmd AsNew OleDbCommand(sql, con)
cmd.CommandType = CommandType.Text
'create the parameter
Dim prmID AsNew OleDbParameter
With prmID
.ParameterName = "@picID"
.Value = ListBox1.Items
EndWith
cmd.Parameters.Add(prmID) 'add this to the command

Dim da AsNew OleDbDataAdapter(cmd)
Dim ds AsNew DataSet("Products")
Try
con.Open() 'open the connection
da.Fill(ds) 'fill the dataset

Dim rptCatalogue AsNew CrystalReport1
rptCatalogue.SetDataSource(ds)

CrystalReportViewer1.ReportSource = rptCatalogue
Catch ex As Exception
MessageBox.Show("There is problem Loading Report." & ControlChars.CrLf & ex.Message)
EndTry
EndSub

Or atleast is there anyway we can filter the DATASET based upon listbox.items (I Mean I wanted to filter database items based on items from the listbox) then pass this DATASET to CrystalReport1.Setdatasouce(DATASET). Can anyone help please.
 
Ok, we need to get your process straight. Here is my idea, it may or may not be the best/right way.
If the user is going to select multiple items from a listbox, you need to determine how they are going to select them. Select one at a time by double clicking or select all by using ctrl and shift.
If all at one time, you will need to loop through the selected items.
VB.NET:
        For Each item As String In Listbox1.SelectedItems
            'Your code to get data here
        Next
Next, you should look into Crystal Reports 'PUSH' method.
I think you should use the selected items loop to access all the report data on this form. Then write each record to a report datatable - can be defined with all the fields you want on the report. After you fill the Report datatable with all the report data (a temporary table) you push the table to the report.

Temporary table:
http://www.vbdotnetforums.com/showthread.php?t=4298&highlight=temporary+table

I'll keep helping till we get it working...
 
Dataset not working with Crystal Reports

Thanks a lot Mr.DavidT_macktool and all,

I think this way may slow the application as the datatable may have to hold images into memory(since images are large size in memory). I'm new to dotnet(data table and datarow also needs large lines of code). So I have simplified with the following code. Everything works upto the dataset. But the dataset is not assigning to the report(I mean when I run the report all records are displayed. Any one has solution to this please?.

Code as following:--

PrivateSub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
Dim rptCatalogue AsNew CrystalReport1
Dim frm4 AsNew Form4
Dim lstitems AsString
Dim i AsInteger
lstPartNo2.SelectedIndex = 0
lstitems = """" & lstPartNo2.SelectedItem.ToString & ""
For i = 1 To lstPartNo2.Items.Count - 1
lstPartNo2.SelectedIndex = i
lstitems = "" & lstitems & """,""" & lstPartNo2.SelectedItem.ToString & ""
Next
lstitems = lstitems & """"
Dim sql AsString = "SELECT ProductID,ProductName,ProductImage FROM Products Where ProductID in (" & lstitems & ")"
Dim con AsNew OleDbConnection(conString)

Dim cmd AsNew OleDbCommand(sql, con)
cmd.CommandType = CommandType.Text
Dim daPrint AsNew OleDbDataAdapter(cmd)
Dim dsPrint AsNew DataSet("PrintPicture")
Try
dsPrint.Clear()
con.Open()
'open the connection
daPrint.Fill(dsPrint) 'fill the dataset
rptCatalogue.SetDataSource(dsPrint)
frm4.CrystalReportViewer1.ReportSource = rptCatalogue
frm4.ShowDialog()
Catch ex As Exception
MessageBox.Show("Printing Catalogues Error." & ControlChars.CrLf & ex.Message)

EndTry

EndSub
 
VB.NET:
Dim lstitems AsString
Dim i AsInteger
lstPartNo2.SelectedIndex = 0
lstitems = """" & lstPartNo2.SelectedItem.ToString & ""
For i = 1 To lstPartNo2.Items.Count - 1
lstPartNo2.SelectedIndex = i
lstitems = "" & lstitems & """,""" & lstPartNo2.SelectedItem.ToString & ""
Next
lstitems = lstitems & """"
Dim sql AsString = "SELECT ProductID,ProductName,ProductImage FROM Products Where ProductID in (" & lstitems & ")"
Your code above, from what I can tell will load all items from the listbox except the first.
Potential problems:
For i = 1 To lstPartNo2.Items.Count - 1
Listbox indexes are zero based - first item is 0. Your code will always miss the first item.

In your loop:
lstPartNo2.SelectedIndex = i
lstitems = "" & lstitems & """,""" & lstPartNo2.SelectedItem.ToString & ""
You select each item and then add the selected item to the string - all items are added.

Not sure about this - should have all items in listbox:
Dim sql AsString = "SELECT ProductID,ProductName,ProductImage FROM Products Where ProductID in
(" & lstitems & ")"
Check your SQL select statement here:
msgbox(sql)
See excatly what string you have built.
[FONT=Verdana, Arial, Helvetica, sans-serif]Check to make sure your string is in the correct form.[/FONT]
Do a little more work on your string building for the select statement and I think you will see results.
 
msgbox(sql) gives correct result But not the Dataset

Dear DavidT_macktool and All,

Thank you verymuch for your suggestion. I tried msgbox(sql) it is giving exact result as I wanted. Even msgbox(lstitems) gives exact result with mulitpe items or single item. But I don't know why it is not going into the dataset.

any Idea please?

Thanks a lot in advance
 
Last edited:
I'm guessing here...

Try using a DataTable as the report source.
Something like this?

Dim tblTemp As DataTable
tblTemp =
DataSet1.Tables("PrintPicture")
rptCatalogue.SetDataSource(tblTemp)
 
DavidT_macktool said:
I'm guessing here...

Try using a DataTable as the report source.
Something like this?

Dim tblTemp As DataTable
tblTemp =
DataSet1.Tables("PrintPicture")
rptCatalogue.SetDataSource(tblTemp)

Thanks Mr.DavidT_macktool,

So I changed the code as here. But same thing it never goes. I'm strcuk with this for days. Is there any solution please?

Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
Dim rptCatalogue As New CrystalReport1
Dim frm4 As New Form4
Dim lstitems As String
Dim i As Integer
lstPartNo2.SelectedIndex = 0
lstitems = """" & lstPartNo2.SelectedItem.ToString & ""
For i = 1 To lstPartNo2.Items.Count - 1
lstPartNo2.SelectedIndex = i
lstitems = "" & lstitems & """,""" & lstPartNo2.SelectedItem.ToString & ""
Next
lstitems = lstitems & """"
Dim sql As String = "SELECT ProductID,ProductName,ProductImage FROM Products Where ProductID in (" & lstitems & ")"
Dim con As New OleDbConnection(conString)

Dim cmd As New OleDbCommand(sql, con)
cmd.CommandType = CommandType.Text
Dim daPrint As New OleDbDataAdapter(cmd)
Dim dsPrint As New DataSet("PrintPicture")
rptCatalogue.SetDataSource(
Nothing)
Try
dsPrint.Clear()
con.Open()
'open the connection
Dim tblTemp As DataTable
daPrint.Fill(dsPrint)
'fill the dataset
tblTemp = dsPrint.Tables("PrintPicture")
rptCatalogue.SetDataSource(tblTemp)
frm4.CrystalReportViewer1.ReportSource = rptCatalogue
frm4.ShowDialog()
Catch ex As Exception
MessageBox.Show("Printing Catalogues Error." & ControlChars.CrLf & ex.Message)
End Try
End Sub
 
Resolved

Dear Mr.DavidT_macktool and all,

Finally it is resolved with a simple 10 letter code(.Tables(0)). It was consuming hours. I 'm knocking my head to the monitor. but I did not know it is this much simple. I'm providing the code here in order to help others who have same problem.

'instead of
rptCatalogue.SetDataSource(dsPrint)

'put this one

rptCatalogue.SetDataSource(dsPrint.Tables(0))

Thanks a lot to everyone who looked into this matter to help
 
Back
Top