Latest Posts

0 Comments

   '04a - EXCEL
    '-----------------------------------------------------------------------

    'Returns query against Excel file as datatable
    Function ReadExcel(ByVal sqlString As String, ByVal path As String) As System.Data.DataTable
        Dim strConn As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & path & ";" & _
        "Extended Properties=""Excel 8.0;"""
        Dim recs As New Data.DataTable()
        Dim sql As New OleDbDataAdapter(sqlString, strConn)
        sql.Fill(recs)
        Return recs
    End Function

    '04b - ACCESS
    '-----------------------------------------------------------------------

    'Returns query against Access file database
    Function ReadAccess(ByVal sqlString As String, ByVal path As String) As System.Data.DataTable
        Dim strConn As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";"
        Dim recs As New Data.DataTable()
        Dim sql As New OleDbDataAdapter(sqlString, strConn)
        sql.Fill(recs)
        Return recs
    End Function

    '04c - CSV
    '-----------------------------------------------------------------------

    'Returns datatable from CSV File
    Function ReadCSV(ByVal path As String) As System.Data.DataTable
        Dim sr As New StreamReader(path)
        Dim fullFileStr As String = sr.ReadToEnd()
        sr.Close()
        sr.Dispose()
        Dim lines As String() = fullFileStr.Split(ControlChars.Lf)
        Dim recs As New DataTable()
        Dim sArr As String() = lines(0).Split(","c)
        For Each s As String In sArr
            recs.Columns.Add(New DataColumn())
        Next
        Dim row As DataRow
        Dim finalLine As String = ""
        For Each line As String In lines
            row = recs.NewRow()
            finalLine = line.Replace(Convert.ToString(ControlChars.Cr), "")
            row.ItemArray = finalLine.Split(","c)
            recs.Rows.Add(row)
        Next
        Return recs
    End Function

0 Comments

'EXECUTE SQL Scalar or NonQuery
'Ex:
'dim count as integer = Execute("select....",True)
'OR
'Execute("update table set field=val where.... ")
Function Execute(strSQL As String, Optional ExecuteScalar As Boolean = False)
  Dim cmd As sqlCommand = conn.CreateCommand
  cmd.CommandText = strSQL
  If ExecuteScalar Then
    Return cmd.ExecuteScalar
  Else
    Return cmd.ExecuteNonQuery
  End If
End Function

'Returns Recordset as Datatable
'Ex:
'dGrid.datasource=FillRecs("select * from table")
Function FillRecs(ByVal sqlString As String) As System.Data.DataTable
  Dim recs As DataTable = New DataTable()
  Dim sql As New sqlDataAdapter(sqlString, conn)
  sql.Fill(recs)
  Return recs
End Function

'Returns Row Count
'Ex:
'if hasrecs(Datatable)
Function hasrecs(ByVal r As Data.DataTable) As Boolean
  Return (r.Rows.Count > 0)
End Function

0 Comments

Really, if your rendered code looks like this, seek help

0 Comments

0 Comments