locked
Select Query in crystal report RRS feed

  • Question

  • User-1507566129 posted

    Hiiii I created crystal report and put viewer in form with textbox and button

    I want to put select query to filter record according to textbox1.text?????? I want all fields where app_no= me.textbox1.text..

    How can I make it

    Note: Adapter way doesnot work!!! any other method?

    Monday, May 17, 2010 3:05 AM

All replies

  • User1800473205 posted

    Hi,

    You can pass parameter in SqlCommand like this and get filtered data in dataset as follow:

    Dim cmd As New SqlCommand()
            Dim ds As New DataSet()
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT [LicenseCode] FROM [License] WHERE ChallengeCode = @ChallengeCode"
            cmd.Parameters.AddWithValue("@ChallengeCode", strChallengeCode)
            Dim adapts As New SqlDataAdapter(cmd)
            adapts.Fill(ds)


    Here Instead of strChallengeCode variable you can directly pass text box value. 

    Monday, May 17, 2010 6:06 AM
  • User-1507566129 posted

    I tried your code and I got all records (parameters doesn't work!


    Wednesday, May 19, 2010 2:40 AM
  • User1800473205 posted

    can you post your code for getting more idea?

    Wednesday, May 19, 2010 2:47 AM
  • User-1507566129 posted

    I think my mistake is in reading app_no from db but I don't know what is it exactly!!!


    Imports Oracle.DataAccess
    Imports System.Net.Mail
    Imports System.Net.Mail.AttachmentBase
    Imports System.Net.Mail.AttachmentCollection
    Imports System.Data.OleDb
    Imports System.IO
    Imports System.Configuration
    Imports System.Collections
    Imports System.Web
    Imports System.Web.Security
    Imports System.Web.UI
    Imports System.Web.UI.WebControls
    Imports System.Web.UI.WebControls.WebParts
    Imports System.Web.UI.HtmlControls
    Imports CrystalDecisions

    Imports CrystalDecisions.CrystalReports.Engine
    Imports CrystalDecisions.Shared
    Imports CrystalDecisions.ReportSource


    Partial Class afkar
        Inherits System.Web.UI.Page
        Dim r_select As String
        Dim MyReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument

        Dim pdfFile As String = Server.MapPath("PDF\Application.pdf")


           
        Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click



            '/\/\/\/\/\ Check the email formula /\/\/\/\/\/\

            Dim EX1 As New System.Text.RegularExpressions.Regex("^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$")

            If EX1.IsMatch(pemail.Text.Trim) = False Then
                'MsgBox("check email")
                Me.Label9.Visible = True
                Me.pemail.Focus()


            Else

                Me.Label9.Visible = False


                '/\/\/\/\/\ Create Connection and Insert Commands /\/\/\/\/\

                Dim con As New OleDbConnection
                con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\creative_minds.mdb"
                con.Open()

                Dim cmcontact As New OleDbCommand
                cmcontact.Connection = con

                cmcontact.CommandText = ("Insert into Innovative_Form(App_Name, Job_Title, Organization, Tel, Email_Add," + _
        "App_Date, Title, Proposed_Duration, Estimated_Budget,Idea_Type, Brief_Summary, Experience_Related,Participants_Names, Required_Resources, Anticipated_Benefits,Filed_Patent, Property_Disclosure, Property_Right, Funding_Source, How_to_Fund, Level_of_Fund )" + _
                "values ('" & Me.pname.Text & "','" & Me.pjob.Text & "','" & Me.porg.Text & "','" & Me.ptel.Text & "','" & Me.pemail.Text + _
        "','" + Me.Label10.Text + "','" + _
        Me.ptitle.Text & "','" & Me.pduration.Text & "','" & Me.budget.Text & "','" & Me.idea_type.SelectedValue & "','" & Me.summary.Text & "','" & Me.exp.Text & "','" & Me.par_names.Text & "','" & Me.resources.Text & "','" & Me.benefits.Text & "'," & Me.f_patent.SelectedValue & ",'" + _
               Me.prop.SelectedValue & "','" & Me.p_right.Text & "','" & Me.funding_source.Text & "','" & Me.how_to_fund.Text & "','" & Me.level_of_fund.Text & "')")


                cmcontact.ExecuteNonQuery()


                '/\/\/\/\/\/\/\ Update Statements /\/\/\/\/\/\/\/\/\

                If Me.idea_type.SelectedItem.Text = "o" Then
                    cmcontact.CommandText = " update Innovative_Form set Original_Idea = '" + Me.o_idea.Text + "'"
                    cmcontact.ExecuteNonQuery()


                ElseIf Me.idea_type.SelectedItem.Text = "q" Then
                    cmcontact.CommandText = " update Innovative_Form set Quoted_Idea =  '" + Me.q_idea.Text + "'"
                    cmcontact.ExecuteNonQuery()


                ElseIf Me.idea_type.SelectedItem.Text = "s" Then
                    cmcontact.CommandText = " update Innovative_Form set Supplementary_Idea = '" + Me.s_idea.Text + "'"
                    cmcontact.ExecuteNonQuery()


                Else
                    cmcontact.CommandText = " update Innovative_Form set Developmental_Idea =  '" + Me.d_idea.Text + "'"
                    cmcontact.ExecuteNonQuery()




                End If


                If Me.f_patent.SelectedItem.Text = "t" Then
                    cmcontact.CommandText = " update Innovative_Form set  patent_title = '" + Me.patent_title.Text & "', Patent_No = '" & Me.patent_no.Text & "',  Patent_Date_Filed = '" & Me.p_month.SelectedItem.Value + "/" + Me.p_day.SelectedItem.Value + "/" + Me.p_year.SelectedItem.Value + "'"
                    cmcontact.ExecuteNonQuery()


                End If

                '/\/\/\/\/\/\/\/\ READ App_no as ID FROM DB /\/\/\/\/\/\/\/\/\/\
                Dim idd As New TextBox

                cmcontact.CommandText = "select app_no as id from Innovative_Form "
                cmcontact.ExecuteNonQuery()

                Dim CReader6 As OleDbDataReader
                CReader6 = cmcontact.ExecuteReader
                While CReader6.Read
                    If CReader6.HasRows = False Then
                     
                        CReader6.Read()

                        MsgBox(idd.Text)

                    End If
                End While
             
                idd.Text = CReader6("id")
                '/\/\/\/\/\/\/\/\ SENDING EMAIL /\/\/\/\/\/\/\/\/\/\/\

                Dim message As New MailMessage

                message.From = New MailAddress("Innovate_Web@KISR.EDU.KW")

                'message.To.Add(New MailAddress("ashehab@safat.kisr.edu.kw"))
                message.To.Add(New MailAddress("mmahdi@safat.kisr.edu.kw"))
                'message.To.Add(New MailAddress("innovate@safat.kisr.edu.kw"))

                message.Subject = "Email from application form page in Innovate Web"
                message.BodyEncoding = Encoding.UTF8

                message.Body = "PLEASE CHECK THE ATTACHMENT FILE"


                'message.Body = "Application No. " + idd.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Preliminary Approval: " + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Committee Approval: " + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "DG's Approval: " + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Name: " + Me.pname.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Job Title: " + Me.pjob.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Organization: " + Me.porg.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Tel.: " + Me.ptel.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Email Address: " + Me.pemail.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Date (m/d/y): " + Me.Label10.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Title: " + Me.ptitle.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Proposed Duration: " + Me.pduration.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Estimated Budget: " + Me.budget.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "This Idea is: " + Me.idea_type.SelectedValue + Environment.NewLine + Environment.NewLine



                'If Me.idea_type.SelectedItem.Text = "o" Then
                '    message.Body = message.Body + "If the idea is original, mention the innovative aspects of it: " + Me.o_idea.Text + Environment.NewLine + Environment.NewLine
                'ElseIf Me.idea_type.SelectedItem.Text = "q" Then
                '    message.Body = message.Body + "If the idea is quoted, then state the original idea: " + Me.q_idea.Text + Environment.NewLine + Environment.NewLine
                'ElseIf Me.idea_type.SelectedItem.Text = "s" Then
                '    message.Body = message.Body + "If the idea is supplementary, then state the original idea: " + Me.s_idea.Text + Environment.NewLine + Environment.NewLine
                'Else
                '    message.Body = message.Body + "If the idea is developmental, then state the original idea: " + Me.d_idea.Text + Environment.NewLine + Environment.NewLine
                'End If



                'message.Body = message.Body + "Brief Summary: " + Me.summary.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Experience related to the Implementation of the Proposed Idea: " + Me.exp.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Participants Names: " + Me.par_names.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Required Resources: " + Me.resources.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Anticipated Benefits: " + Me.benefits.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Filed Patent: " + Me.f_patent.SelectedValue + Environment.NewLine + Environment.NewLine


                'If Me.f_patent.SelectedItem.Text = "t" Then
                '    message.Body = message.Body + "Patent Title: " + Me.patent_title.Text + Environment.NewLine + Environment.NewLine
                '    message.Body = message.Body + "Patent No.: " + Me.patent_no.Text + Environment.NewLine + Environment.NewLine
                '    message.Body = message.Body + "Date Filed (m/d/y): " + Me.p_month.SelectedItem.Value + "/" + Me.p_day.SelectedItem.Value + "/" + Me.p_year.SelectedItem.Value + Environment.NewLine + Environment.NewLine

                'End If


                'message.Body = message.Body + "Intellectual Property Disclosure: " + Me.prop.SelectedValue + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Intellectual Property Right: " + Me.p_right.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "Mention the Funding Source (Self/External Funding), and the Funding Amount.: " + Me.funding_source.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "State how the innovative idea has been funded to date: " + Me.how_to_fund.Text + Environment.NewLine + Environment.NewLine
                'message.Body = message.Body + "What Level of Funding that has been expended to date? " + Me.level_of_fund.Text + Environment.NewLine + Environment.NewLine




                '/\/\/\/\ CREATE CRYSTAL REPORT, EXPORT IT TO PDF AND ATTACHED IT TO THE COMMITTEE /\/\/\


                MyReport.Load(Server.MapPath("CrystalReport.rpt"))
                Dim da33 As New System.Data.DataSet
                Dim cmd As New OleDbCommand()
                cmd.Connection = con
                cmd.CommandText = "SELECT * from Innovative_Form where app_no = @app_no"
                cmd.Parameters.AddWithValue("@app_no", idd.Text)
                Dim da As New OleDbDataAdapter(cmd)

                'r_select = "SELECT * from Innovative_Form where app_no = " + idd.Text
                'Dim da As OleDbDataAdapter = New OleDbDataAdapter(r_select, System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString())


                da.Fill(da33)






                MyReport.Load(Server.MapPath("CrystalReport.rpt"))


                MyReport.SetDataSource(da33)


                Try
                    Dim CrExportOptions As ExportOptions
                    Dim CrDiskFileDestinationOptions As New  _
          DiskFileDestinationOptions()
                    Dim CrFormatTypeOptions As New PdfRtfWordFormatOptions
                    CrDiskFileDestinationOptions.DiskFileName = pdfFile
                    CrExportOptions = MyReport.ExportOptions
                    With CrExportOptions
                        .ExportDestinationType = ExportDestinationType.DiskFile
                        .ExportFormatType = ExportFormatType.PortableDocFormat
                        .DestinationOptions = CrDiskFileDestinationOptions
                        .FormatOptions = CrFormatTypeOptions
                    End With
                    MyReport.Export()
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try

                message.Attachments.Add(New Attachment(pdfFile))



                    '/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\



                    Dim client As New SmtpClient

                    client.Host = "safat.kisr.edu.kw"
                    client.Port = 25
                    client.Send(message)

                    '/\/\/\/\/\/\/\/\/\/\/\/\

                    '/\/\/\/\/\ Sending email to the user /\/\/\/\/\

                    Dim message2 As New MailMessage

                    message2.From = New MailAddress("Innovate_Web@KISR.EDU.KW")

                    message2.To.Add(New MailAddress(Me.pemail.Text))

                    message2.BodyEncoding = Encoding.UTF8

                    message2.Subject = "Thank you for your idea"

                    message2.Body = "Thank you for your participation, your idea will be considered and we will replay you as soon as possible." + Environment.NewLine + Environment.NewLine

                    message2.Body = message2.Body + "نشكر لكم مشاركتكم، سيتم الإطلاع على فكرتكم والرد في أقرب فرصة ممكنة" + Environment.NewLine + Environment.NewLine


                    message2.Body = message2.Body + "Innovate Committee"

                    Dim client2 As New SmtpClient

                    client2.Host = "safat.kisr.edu.kw"
                    client2.Port = 25
                    client2.Send(message2)





                    '/\/\/\/\/\/\/\/\/\/\/\/\

                    Server.Transfer("afkar_final.aspx")


                CReader6.Close()
                    '/\/\/\/\/\
                    con.Close()
                    '/\/\/\/\/\
            End If

        End Sub

    Wednesday, May 19, 2010 3:03 AM
  • User1800473205 posted

    Use following code 

    Dim query As String = "SELECT @@IDENTITY"
    cmcontact.CommandText = query
    cmcontact.CommandType = CommandType.Text
    Dim newid As Integer = cmd.ExecuteScalar() 
    idd.Text = newid.ToString()
    
    
    


    Instead of this:

    '/\/\/\/\/\/\/\/\ READ App_no as ID FROM DB /\/\/\/\/\/\/\/\/\/\
                Dim idd As New TextBox

                cmcontact.CommandText = "select app_no as id from Innovative_Form "
                cmcontact.ExecuteNonQuery()

                Dim CReader6 As OleDbDataReader
                CReader6 = cmcontact.ExecuteReader
                While CReader6.Read
                    If CReader6.HasRows = False Then
                     
                        CReader6.Read()

                        MsgBox(idd.Text)

                    End If
                End While
             
                idd.Text = CReader6("id")

     

    Wednesday, May 19, 2010 3:20 AM
  • User-1507566129 posted

    still got all records

    '/\/\/\/\ TEST /\/\/\/\
                Dim query As String = "SELECT app_no as id from Innovative_Form"
                cmcontact.CommandText = query

                'cmcontact.CommandType = cmcontact.CommandType.Text
                Dim newid As Integer = cmcontact.ExecuteScalar()
                idd.Text = newid.ToString()

                MsgBox(idd.Text)


                '/\/\/\/\/\/\/\/\/\/\/\



    I made test on parameters statement (put app_no directly) and still got all records check my test plz:


                MyReport.Load(Server.MapPath("CrystalReport.rpt"))
                Dim da33 As New System.Data.DataSet
                Dim cmd As New OleDbCommand()
                cmd.Connection = con
                cmd.CommandText = "SELECT * from Innovative_Form where app_no = @app_no"
                cmd.Parameters.AddWithValue("@app_no", "125")
                Dim da As New OleDbDataAdapter(cmd)

                'r_select = "SELECT * from Innovative_Form where app_no = " + idd.Text
                'Dim da As OleDbDataAdapter = New OleDbDataAdapter(r_select, System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString())


                da.Fill(da33)



    when creating dataset do I have to do something in the report itself????

    Wednesday, May 19, 2010 3:36 AM
  • User-1507566129 posted

    any help plz Iam stucking with this silly error since 2 weeeeeeeeeks!!!! YellCryCryCryCry

    Wednesday, May 19, 2010 4:04 AM
  • User1800473205 posted

    Have you tried this:

    Dim query As String = "SELECT @@IDENTITY"  as query this will return you last inserted app_no if app_no is AUTONUMBER field.

    In your code  you have taken this : Dim query As String = "SELECT app_no as id from Innovative_Form"
    this will return you all rows.

    Use this:

    Dim query As String = "SELECT @@IDENTITY"

               cmcontact.CommandText = query

                'cmcontact.CommandType = cmcontact.CommandType.Text
                Dim newid As Integer = cmcontact.ExecuteScalar()
                idd.Text = newid.ToString()

    Wednesday, May 19, 2010 4:40 AM
  • User-1507566129 posted

    STILL Yell getting all records!!!


                '/\/\/\/\ TEST /\/\/\/\
                Dim query As String = "SELECT @@IDENTITY from Innovative_Form"
                cmcontact.CommandText = query
                ''cmcontact.CommandType = cmcontact.CommandText

                Dim newid As Integer = cmcontact.ExecuteScalar()
                idd.Text = newid.ToString()



    '/\/\/\/\ CREATE CRYSTAL REPORT, EXPORT IT TO PDF AND ATTACHED IT TO THE COMMITTEE /\/\/\


                MyReport.Load(Server.MapPath("CrystalReport.rpt"))
                Dim da33 As New System.Data.DataSet
                Dim cmd As New OleDbCommand()
                cmd.Connection = con

                cmd.CommandText = "SELECT * from Innovative_Form where app_no = @app_no"
                cmd.Parameters.AddWithValue("@app_no", idd.Text)
                Dim da As New OleDbDataAdapter(cmd)
                da.Fill(da33)


    ANY HELP PLZ CryCryCryCryCryCryCryCryCryCryCryCryCryCryCryCryCry

    Wednesday, May 19, 2010 5:41 AM
  • User1800473205 posted

    Refer this link in that thr is following example given:

    http://stackoverflow.com/questions/186544/identity-after-insert-statement-always-returns-0

    Try to insert and Select identity in one transaction, It may cause prob.

     

    OleDbConnection connection =  String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Password={0};Data Source={1};Persist Security Info=True",dbinfo.Password,dbinfo.MsAccessDBFile); 
    connection.Open(); 
    OleDbTransaction transaction = null; 
    try{ 
        connection.BeginTransaction(); 
        String commandInsert = "INSERT INTO TB_SAMPLE ([NAME]) VALUES ('MR. DUKE')"; 
        OleDbCommand cmd = new OleDbCommand(commandInsert , connection, transaction); 
        cmd.ExecuteNonQuery(); 
        String commandIndentity = "SELECT @@IDENTITY"; 
        cmd = new OleDbCommandcommandIndentity, connection, transaction); 
        Console.WriteLine("New Running No = {0}", (int)cmd.ExecuteScalar()); 
        connection.Commit(); 
    }catch(Exception ex){ 
        connection.Rollback(); 
    }finally{ 
        connection.Close(); 
    }


     

    Wednesday, May 19, 2010 7:18 AM
  • User-1507566129 posted

    I'll explain again! what I want is filtering record in crystal report

    This is my code:

    '/\/\/\/\ Get App_No for last entry to use it as report parameter /\/\/\/\

                Dim idd As New TextBox
                
                Dim query As String = "SELECT @@IDENTITY from Innovative_Form"
                cmcontact.CommandText = query
                ''cmcontact.CommandType = cmcontact.CommandText

                Dim newid As Integer = cmcontact.ExecuteScalar()
                idd.Text = newid.ToString()

    Dim da33 As New System.Data.DataSet
                Dim cmd As New OleDbCommand()
                cmd.Connection = con

                cmd.CommandText = "SELECT * from Innovative_Form where AppNo = @AppNo "


                cmd.Parameters.AddWithValue("@AppNo", CInt(idd.Text))




                Dim da As New OleDbDataAdapter(cmd)
                da.Fill(da33)




                MyReport.Load(Server.MapPath("CrystalReport.rpt"))


                MyReport.SetDataSource(da33)



                '/\/\/\/\/\/\/\/\/\/\


    Wednesday, May 19, 2010 7:20 AM
  • User-1507566129 posted

    I checked the identity using msgbox it returns right value  this is not the problem, the problem with the paramter statement itself I tried

    to put direct value and still not work like the following (put @appno=125):

    Dim da33 As New System.Data.DataSet
                Dim cmd As New OleDbCommand()
                cmd.Connection = con

                cmd.CommandText = "SELECT * from Innovative_Form where AppNo = @AppNo "


                cmd.Parameters.AddWithValue("@AppNo", 125)




                Dim da As New OleDbDataAdapter(cmd)
                da.Fill(da33)



                MyReport.Load(Server.MapPath("CrystalReport.rpt"))


                MyReport.SetDataSource(da33)


    Wednesday, May 19, 2010 7:34 AM