Unable to import Data into crystal report

Question

0.00/5 (No votes)

See more:

Hello All,

I am a newbie to VB.net and i am not sure if I am making mistake somewhere.

I am trying to search for a orderid and pull the same into a crystal report. For this i am trying to create a temp Dataset for my report.
Main dataset name = testdatadataset

I tried writing a function but it is still not pulling up the data with the search option.

There is something wrong that I am doing but unable to find out where.

My Code is below.

VB

Private Sub Generate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Generate.Click
        Dim Gentxt As Integer = TxtOrderID.Text
        Dim SQLGEN As New SqlCommand
        Dim cryRpt As New GenRep
        Dim RetPath As String = Path.GetDirectoryName(Application.ExecutablePath)
        Dim sqlLink = New SqlConnection()
        sqlLink.ConnectionString = ConStr 'I have this in my config file
        Try
            Dim ds As testdataDataSet = GetData()
            cryRpt.Load("F:\Projects\2008 Projects\Test Application\Test Application\Genrep.rpt")
            cryRpt.SetDataSource(ds)
            ReportViewer.ReportSource = cryRpt
            ReportViewer.Refresh()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub



Private Function GetData() As DataSet
        Dim Query As String = "SELECT [ORDERID],[USERNAME],[ORDERDATE],[PRODUCT],[SUPPLYDATE],[SUPPLYQUAN],[TOTSUPPLY],[AMTPAID],[TAKENBY] FROM TESTDATA "
        If TxtOrderID.Text <> "" Then          
            Query += "WHERE ORDERID = " & TxtOrderID.Text & ""
        End If
        Dim Sqlcon As New SqlConnection
        Sqlcon.ConnectionString = ConStr
        Dim sqlda As New SqlDataAdapter(Query, Sqlcon)
        Dim DS As New testdataDataSet
        sqlda.Fill(DS)' if i check in visual studio 2008 dataset analyser, i get all  columns blank.
        Return DS
    End Function

I am using visual studio 2008 professional and SQL server 2008 express R2

Please help me.

Posted 7-Aug-11 11:09am

Vamshi Krishna Naidu

Add a Solution

Comments

thatraja 7-Aug-11 23:45pm

So there is problem in db side? check db if you have any data in that table. Also check the connection status.

Vamshi Krishna Naidu 8-Aug-11 5:34am

The connection is opening without any problem. Also there is a table by name testdata with orderid from 1-7 with full data. I tried passing the dataset without the function and then it works like charm. Crystal report shows the complete information listed in the DB Table. But no luck when i am using a search function.

Please help.

2 solutions

Solution 1

Is orderID a number or a string? If it's a string you may have to format it properly before comparing it to the database. For example, if in the database everything is in upper case, then make sure to use TxtOrderID.Text.ToUpper instead. If the orderID is a string but holds number values you may have to pad left with zeros or in some cases pad left or right with spaces. You should consider using parms to pull this data instead of allowing direct text written by the user to be put into an SQL statement. It can lead to SQL Injection[^]. Here[^] is a good CP Article about SQL Injection and how to prevent it. Look for where it talks about Parameterized Queries for help with parameters.

Posted 8-Aug-11 9:18am

Kschuler

Comments

Vamshi Krishna Naidu 8-Aug-11 15:27pm

Thank you for your reply.

The order ID is not a string but it is just a text box where i had a comment saying enter only integer. Also the same order id is a autogen value and a primary key. So even if i format the value, a val to val search should atleast get the data or if it is not working, it should give me a "convert to integer from string" error. but this is not happening. I also found something in one article here that when you pull the data from a dataset, to show it, we need to have a temp table created. But i am not sure how to do that.

Is that creating the problem? If yes, how should i handle the code?

Kschuler 8-Aug-11 15:38pm

You are sure the database column that holds the order ID is also a number? Sometimes when you would expect an conversion error because of numbers/strings you don't get one because it tries to convert it for you to be nice, so it's worth a double check. About temp table, I think it's talking about a DataTable object which you probably already have and just don't know it. A DataSet is a container that contains one or many DataTable objects. If you set a breakpoint in your program after the dataset is filled, run the program until it his the breakpoint, highlight your DataSet variable, then right click and select QuickWatch. You should be able to see that your DataSet has Tables, and if you click on the magnify glass icon you can even see what is inside those tables. The only other suggestion I can come up with is to try it with parms. Since it works when you leave the order id box blank it HAS to be something finiky with that order id.

Vamshi Krishna Naidu 8-Aug-11 17:15pm

I will surely give it a try. Thanks for your prompt response.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vamshi Krishna Naidu · Accepted Answer · 2011-08-08T14:26:00

I made a small mistake which costed me a lot of time.

1) When you pull the data from dataset, we create a temp table to save that (Which i did not do)

2) We need to pass the datatable (Temp Table in this case) to show the information.(Which i did not do)

After taking care of these two things, My App started showing the correct data.

I am posting the code modified also for reference.

VB

Private Sub Generate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Generate.Click
        Dim Gentxt As Integer = TxtOrderID.Text
        Dim SQLGEN As New SqlCommand
        Dim cryRpt As New GenRep
        Dim RetPath As String = Path.GetDirectoryName(Application.ExecutablePath)
        Dim sqlLink = New SqlConnection()
        sqlLink.ConnectionString = ConStr 'I have this in my config file
        Try
            Dim ds As datatable = GetData()
            cryRpt.Load("F:\Projects\2008 Projects\Test Application\Test Application\Genrep.rpt")
            cryRpt.SetDataSource(dt)
            ReportViewer.ReportSource = cryRpt
            ReportViewer.Refresh()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
 

 
Private Function GetData() As DataSet
        Dim Query As String = "SELECT [ORDERID],[USERNAME],[ORDERDATE],[PRODUCT],[SUPPLYDATE],[SUPPLYQUAN],[TOTSUPPLY],[AMTPAID],[TAKENBY] FROM TESTDATA "
        If TxtOrderID.Text <> "" Then          
            Query += "WHERE ORDERID = " & TxtOrderID.Text & ""
        End If
        Dim Sqlcon As New SqlConnection
        Sqlcon.ConnectionString = ConStr
        Dim sqlda As New SqlDataAdapter(Query, Sqlcon)
        Dim DS As New testdataDataSet
        sqlda.Fill(DS)
        Dim dt As DataTable = DS.Tables("Temp Table")
        Return dt
    End Function

And this worked...

Thanks for your reply guys...