Member Avatar for gabanxx

can u spot the error in this code..

Private Sub fncExcelExport(ByVal strSql As String)

        'fncExcelExport("SELECT * FROM tbl_rop WHERE Name_User ='" & (TextBox1.Text) & "', Date_of_birth= '" & (TextBox2.Text) & "' , Hobbies='" & (TextBox3.Text) & "' , Phone_Number ='" & (TextBox4.Text) & "' where Name_User = '" & (TextBox1.Text) & "'")



        Dim dsExcelExport As New DataSet
        Dim daExcelExport As New SqlDataAdapter
        Dim Excel As New Excel.Application
        Dim intColumn, intRow, intColumnValue As Integer
        Dim strExcelFile As String
        Dim strFileName As String
        Dim conCurrent As New SqlConnection

        conCurrent = New SqlConnection("Server=(local);user id=sa;password=;Initial Catalog=ROP1")
        'conCurrent = New SqlConnection("server=;(local) catalog=ROP1;User ID=sa;Password=")
        daExcelExport = New SqlDataAdapter(strSql, conCurrent)
        varCommand = New SqlCommand(strSql, varConnection)
        daExcelExport = New SqlDataAdapter(varCommand)
        daExcelExport.Fill(dsExcelExport)

        Dim strAppPath = System.Reflection.Assembly.GetExecutingAssembly.Location.Substring(0, System.Reflection.Assembly.GetExecutingAssembly.Location.LastIndexOf("\") + 1)

        With Excel
            .SheetsInNewWorkbook = 1
            .Workbooks.Add()
            .Worksheets(1).Select()
            'For displaying the column name in the the excel file.            
            For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
                .Cells(1, intColumn + 1).Value = dsExcelExport.Tables(0).Columns(intColumn).ColumnName.ToString
            Next
            'For displaying the column value row-by-row in the the excel file.            
            For intRow = 0 To dsExcelExport.Tables(0).Rows.Count - 1
                For intColumnValue = 0 To dsExcelExport.Tables(0).Columns.Count - 1
                    .Cells(intRow + 1, intColumnValue + 1).Value = dsExcelExport.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
                Next

            Next
            strFileName = InputBox("Please enter the file name.", " ")
            strExcelFile = strAppPath & "tbl_rop"
            .ActiveWorkbook().SaveAs(strExcelFile)
            .ActiveWorkbook.Close()
            .AddChart()


        End With
        MessageBox.Show("File exported sucessfully.", "Exporting done", MessageBoxButtons.OK, MessageBoxIcon.Information)
NormalExit:
        Excel.Quit()
        Excel = Nothing
        GC.Collect()
        Exit Sub
    End Sub
  • 2 Contributors
  • 2 Replies
  • 216 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by gabanxx
Member Avatar for binoj_daniel

On which line are you getting error? Can you paste the error?

Member Avatar for gabanxx

this the line which the error occur...
daExcelExport.Fill(dsExcelExport)

i think the error come from the connection bridge that i created earlier at form_load....

fncExcelExport("SELECT Name_User ='" & (TextBox1.Text) & "', Date_of_birth= '" & (TextBox2.Text) & "' , Hobbies='" & (TextBox3.Text) & "' , Phone_Number ='" & (TextBox4.Text) & "' where Name_User = '" & (TextBox1.Text) & "'")

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.