Hello Mycroft, I'm not so familiar with the stored procedure as you mentioned. Can you show me some sample,how to do this as what you told me? I need to printout the data by using crystal report. Thank you~!

Dear All, I have manage to process some sorting and calculation in a few columns of my Dataset. I want to do a printout for the data now inside the dataset using Crystal Report Viewer. Can anyone told me how should I put my Crystal Report viewer to display this Dataset? I'm using VB.NET WINFORM 2.0 with ACCESS DATABASE. This is my code now to process my data inside the dataset:

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

    Dim i As Integer
    Dim BATCH, TRNCDJ, RESONJ, REFNOJ, INVT, CUSTCODE, CUSTNAME, ADD1, ADD2, ADD3 As String
    Dim DD, MM, YY, INVNO, QTY, PONO As Integer
    Dim Day, Month, Year, D1, ADDRSS, INVNUM As String


    Dim mydatatable As New DataTable
    Dim ukCulture As CultureInfo = New CultureInfo("en-GB")
    Dim myDateTime As DateTime

    Try

        If ComboBox1.SelectedItem <> "" Then
            cn.Open()

            MyDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT \* FROM DATA WHERE ITEMJ='" & ComboBox1.Text & "' ORDER BY DD,MM,YY ASC", cn)
            MyDataAdapter.TableMappings.Add("Table", "OrderTable")
            DtSet = New System.Data.DataSet

            MyDataAdapter.Fill(DtSet)
            dt = DtSet.Tables("OrderTable")
            DataGridView1.DataSource = DtSet.Tables(0)

            cn.Close()

            If Trim(TextBox1.Text) <> "" Then
                STOCK\_BAL = Trim(TextBox1.Text)
            Else
                STOCK\_BAL = 0
            End If

            mydatatable.Columns.Add("Date", Type.GetType("System.String"))
            mydatatable.Columns.Add("CUSTNAME", Type.GetType("System.String"))
            mydatatable.Columns.Add("ADDRESS", Type.GetType("System.String"))
            mydatatable.Columns.Add("INVOICE\_NO", Type.GetType("System.String"))
            mydatatable.Columns.Add("PO\_NO", GetType(Integer))
            mydatatable.Columns.Add("BATCH\_NO", Type.GetType("System.String"))
            mydatatable.Columns.Add("Qty", GetType(Integer))
            mydatatable.Columns.Add("BALANCE", Type.GetType("System.String"))



            For i = 0 To dt.Rows.Count - 1
                'MessageBox.Show("This is " & i & " ")

                BATCH = Trim(DataGridView1.Item(3, i).Value)

                DD = Trim(DataGridView1.Item(4, i).Value)

Yes,I got the solution now. Thank you so much for your help on this issue Mr. Eric Hilel. I will solve the problem here. Thanks alot for your kind effort. Regards Drex

Yes,but the problem is I must get it work with A4 as the row header. Because I need to grab the data from the OrderingFormat.xls file I have send to you. If I start with A1 as the row header,it won't works in my project. Do you have any other ideas? Thank you. Regards Drex

I just email you my Excel file. Hope you can check it for me. Thank you~! Regards Drex

No,it was blank field for A1 and A2 field here actually. My table start with A3 actually. Do you have any other way beside this? Is that Interop can help to solve this problem? Do you have sample for using interops? Thanks~! Regards Drex

I do have a Qty column located at A3 cell here at Sheet1 actually. But I don't know why can cause this problem. My data is start at A4 column,I cannot put my Qty column to A1 because there is other data in A1 field in my Excel. Do you have any idea how should solve this problem? Thanks Regards Drex

Well..The Error appeared at this line>> MyCommand.Fill(DtSet) What might be the problem? Thanks ~!

Sorry,I'm not going to read all the Sheet1 information into my datagirdview,because I doesn't need the additional data inside the sheet1. I would like to just retrieve the table data into my DataGridView. My 1st row header column for the table in my Excel sheet are A4:G4, the details of each row of data are from A5:G5. Can I know how to do this retrieving of data into my DataGridView? Hope someone can guide me better. Thank you~! Regards Drex

Well,I have try with this code as you suggest below: <code> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyConn As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Try MyConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\TestPharmaExcel.xls'; Extended Properties=Excel 8.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter("select Qty from [Sheet1$] ", MyConn) MyCommand.TableMappings.Add("Table", "TestTable") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConn.Close() Catch ex As Exception MessageBox.Show(ex.Message) End Try And it promped me an error: No value given for one or more required parameter. What should I do to this? Anyone can give me some idea? Thanks for reading. Regards Drex

I have tried it. It doesn't appeared anythings inside my Datagirdview with that. Shows that it return nothing,I'm using Excel 2003 format for my file. Do you have any idea to identify that the data table should retrive date from A4 to G4 as table header and A5 to G5 onwards are the data I need? Thanks for your help. Regards Drex

Hello all,I need some help here on reading specified column and row of data from the Excel Sheet. I have manage to read the full sheet into my datagridview now,but what I need is to read specified column and row but not all.I'll try to explain it like below: Its a table in my excel that the table start at location in the PO Detail spreadsheet of A4 until G4. And thats the Header of the table,the Header name of each column are Product Code, Description , Internal Product Code, Selling Price , Order Quantity, Amount and other. But currently my code now read all the data into my datagridview which is not convenient for me to do further process with some unuseful details inside the PO Detail excel sheet. Can anyone teach me how could you retrieve only the column and row I need and put into the datagridview? This is the code I have currently which read everything into my datagridview: Imports System.Data Imports System.Data.OleDb Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyConn As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Try MyConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\OrderingFormat.xls'; Extended Properties=Excel 8.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [PO Detail$] ", MyConn) MyCommand.TableMappings.Add("Table", "TestTable") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConn.Close() Catch ex As Exception End Try End Sub Thank you for reading and hope someone can give me some guidance on this issue. Regards Drex

Thank you so much Mr. S.M.Kanerkar. Your solution works well. Regards Drex

Hello all, I would like to make the Tab movement can also pressing the "Enter" Key. I have google and found the code which possible to used. But I found its not running fine with me. When I press "Enter" key,it runs fine for the tab movement. But if I put something into my textbox,it only allowed me to put 1 character and it will move next. Anyone can help me solve this? Because the Textbox 2 can able to put in any kind of numbers. But after add in the code that allowed "Enter" key move the Tabindex,it will move to the next tab index after I lick 1 character. Please help me to check is that I use this code wrongly?

Private Sub TextBox2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox2.KeyPress

    If Keys.Enter Then
        SendKeys.Send("{TAB}")
        Exit Sub
    End If

    Select Case e.KeyChar
        Case "0" To "9"
        Case Microsoft.VisualBasic.ChrW(8) 'backspace
            'Case Microsoft.VisualBasic.ChrW(46) 'dot (.)
            '    If InStr(TextBox2.Text, ".") > 0 Then
            '        e.Handled = True
            '    End If

        Case Else
            e.Handled = True
    End Select

End Sub

Thanks you all so much for reading. Anyone can give me some suggestion how should I do to make the "Enter" key press work as tab? Regards Drex

I'm still wondering how to use that Crystal Report? Anyone can let me know how to show a dataset table result using a Crystal Report after clicking a button? And how can I pass 3 string parameter to the Crystal report? Thanks for reading. Drex

Hello all,I'm new to using Crystal Reports and VB.NET Programming here. I have try to Google for how it works. Currently I'm able to show my Blank Reports by Clicking a Button2. I have to ask anyone can tell me how to display my data using the Crystal Report? The following are the code I'm currently working with:

'When the following button Click,the Report will showout.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim objRpt As New FrmCrystalRpt
objRpt.strRptName = New CachedCrystalReport1

    objRpt.Show()

End Sub

'The below is my ReportViewer WinForm
Public Class FrmCrystalRpt
Public strRptName As Object
Public strRptFilter As String

Private Sub FrmCrystalRpt\_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If strRptFilter <> "" Then
        crptVW.SelectionFormula = strRptFilter
    End If
    crptVW.ReportSource = strRptName
    crptVW.Show()

End Sub

End Class

Currently my report showing nothing,I'm wondering how could I make this report show my selection of data from the MySQL5.0 database? I'm using MySQL ODBC3.51 Driver to connect my Database=logistic_sysdb. I wish the Crystal Report can show my selection of data below:

Dim SQL4 As String
selectmonth = ComboBox2.SelectedItem

    SQL4 = "SELECT \* FROM lorry\_commission WHERE Lorry\_No='" & ComboBox1.SelectedItem & "' AND MONTH(Date)='" & selectmonth & "';"
   
    Try
        ObjMyConn.Open()
        ObjAdapter = New MySqlDataAdapter(SQL, ObjMyConn)
        ObjAdapter.Fill(ds, "Salary44")
    Catch ex As Exception
        MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly)
    Finally
        ObjMyConn.Close()
    End Try

I guess some parameter is needed to be passed into the Crystal Report as well. How to do that? Anyone can give me a sample is better so I can learn easier to solve this. Thanks for reading. Drex

Thanks Mr. Dave,I have solve the problem. I make a mistaken on the DataGridView1.Column1 header name. So I change it to the original name and it works now. Thanks alot to Mr. Dave and everyone reading here. Regards :) Drex

Hello, I'm not sure what you want is something like this:

Dim Newform1 as new Form1
Newform1.show() ' To Load a form

Newform1.Close() 'To Unload

'Or

Newform1.Hide() 'To Unload

I just hope it helps you. :)

modified on Monday, February 23, 2009 1:17 AM

Hi all,I'm having problem here with my code. I would like to Update the Database with my DataGridView1 when Click the Button1. But from the code below,I found that an Error have occured. Error from Visual Studio 2008= System.Data: Missing the DataColumn 'DO_No'in the DataTable 'Main_table' for the SourceColumn 'DO_No'. The following are the code I load the data from database to my DataGridView1:

Private Sub MaintainDO_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ObjMyConn = New MySqlConnection(MyConnString)

    SQL = "SELECT \* FROM main\_table"
    Try

        ObjMyConn.Open()
        ObjAdapter = New MySqlDataAdapter(SQL, ObjMyConn)
        ObjComBuilder = New MySqlCommandBuilder(ObjAdapter)
        ds = New DataSet
        ObjAdapter.Fill(ds, "Main\_table")
        dt = ds.Tables("Main\_table")
        dt.Columns(0).ColumnName = "Date"
        dt.Columns(1).ColumnName = "DO No."
        dt.Columns(2).ColumnName = "Lorry No."
        dt.Columns(3).ColumnName = "Load From"
        dt.Columns(4).ColumnName = "To Site"
        dt.Columns(5).ColumnName = "Company Name"
        dt.Columns(6).ColumnName = "Item Description"

        DataGridView1.DataSource = dt.DataSet.Tables("Main\_table")
        DataGridView1.Refresh()
    Catch ex As Exception
        MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly)
    Finally
        ObjMyConn.Close()
    End Try
End Sub

The code I put inside my Button1:

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

    SQL = "SELECT \* FROM main\_table"
    Try
        ObjAdapter.Update(ds.Tables("Main\_table")) '<< Here is how I used to update my database.

        ds.Tables("Main\_table").Clear()
        DataGridView1.DataSource = Nothing
        DataGridView1.Refresh()

        ObjMyConn.Open()
        ObjAdapter = New MySqlDataAdapter(SQL, ObjMyConn)
        ObjComBuilder = New MySqlCommandBuilder(ObjAdapter)
        ObjAdapter.Fill(ds, "Main\_table")
        dt = ds.Tables("Main\_table")

        dt.Columns(0).ColumnName = "Date"
        dt.Columns(1).ColumnName = "DO No."
        dt.Columns(2).ColumnName = "Lorry No."
        dt.Columns(3).ColumnName = "Load From"
        dt.Columns(4).ColumnName = "To Site"

Thanks Jon_Boy and Mr. Dave for the help. I have solve the problem here by changing what you recommend to me. It work well now. :)