Im trying to return a datatable when using OleDbCommand
-
I’m trying to write a function that returns a datatable. Within the function I’m calling a stored procedure in access. The code looks like this: Public Shared Function LoadSearchResults(ByVal whereClause As String) As DataTable Dim SQL As String = " EXECUTE qrySearchResult" Dim dt As DataTable = New DataTable() Dim da As OleDbDataAdapter = New OleDbDataAdapter() dbConnection.Open() 'Dim Cmd As New OleDbCommand(SQL, dbConnection) Dim Cmd As New OleDbCommand() Cmd.CommandType = CommandType.StoredProcedure Cmd.CommandText = "EXECUTE qrySearchResult" Cmd.Parameters.Add(Cmd.CreateParameter()) Cmd.Parameters(0).Value = whereClause '''''dr = Cmd.ExecuteReader() '''''''''''''''''''' da.SelectCommand = Cmd da.Fill(dt) dbConnection.Close() dbConnection.Close() Return dt End Function How can I accomplish this? Thanks, Martin
-
I’m trying to write a function that returns a datatable. Within the function I’m calling a stored procedure in access. The code looks like this: Public Shared Function LoadSearchResults(ByVal whereClause As String) As DataTable Dim SQL As String = " EXECUTE qrySearchResult" Dim dt As DataTable = New DataTable() Dim da As OleDbDataAdapter = New OleDbDataAdapter() dbConnection.Open() 'Dim Cmd As New OleDbCommand(SQL, dbConnection) Dim Cmd As New OleDbCommand() Cmd.CommandType = CommandType.StoredProcedure Cmd.CommandText = "EXECUTE qrySearchResult" Cmd.Parameters.Add(Cmd.CreateParameter()) Cmd.Parameters(0).Value = whereClause '''''dr = Cmd.ExecuteReader() '''''''''''''''''''' da.SelectCommand = Cmd da.Fill(dt) dbConnection.Close() dbConnection.Close() Return dt End Function How can I accomplish this? Thanks, Martin
Whatr's the problem ? Why are you closing your connection twice ? Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
-
Whatr's the problem ? Why are you closing your connection twice ? Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
The problem here is that I don't know how to get a datatable from: dr = Cmd.ExecuteReader(). Please ignore the 2nd dbConnection.Close().
-
The problem here is that I don't know how to get a datatable from: dr = Cmd.ExecuteReader(). Please ignore the 2nd dbConnection.Close().
You can try this code to use your datareader to build your datatable Dim table As New DataTable() Dim row As DataRow Dim rows As DataRowCollection ' read table schema from datareader rows = DataReader.GetSchemaTable.Rows 'If you already know your field names you can use this table.Clear() table.Columns.Clear() ' Define the columns. table.Columns.Add("Last Name", GetType(String)) table.Columns.Add("First Name", GetType(String)) table.Columns.Add("Phone Number", GetType(String)) table.Columns.Add("Email Address", GetType(String)) 'Other wise do this ' use schema to create the columns for the new datatable For Each row In rows Dim col As New DataColumn() col.ColumnName = row("ColumnName").ToString col.Unique = System.Convert.ToBoolean(row("IsUnique")) col.AllowDBNull = System.Convert.ToBoolean(row("AllowDBNull")) col.ReadOnly = System.Convert.ToBoolean(row("IsReadOnly")) col.DataType = Type.GetType(row("DataType").ToString) table.Columns.Add(col) Next While DataReader.Read row = table.NewRow Dim col As DataColumn For Each col In table.Columns row(col) = DataReader(col.ColumnName) Next table.Rows.Add(row) End While Return table Please let me know if this helped you.
-
The problem here is that I don't know how to get a datatable from: dr = Cmd.ExecuteReader(). Please ignore the 2nd dbConnection.Close().
You might also want to download this project and look at it. It has a class that might do what you are looking for. http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=352&lngWId=10\\
-
The problem here is that I don't know how to get a datatable from: dr = Cmd.ExecuteReader(). Please ignore the 2nd dbConnection.Close().
You might also want to download this project and look at it. It has a class that might do what you are looking for. http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=352&lngWId=10\\
-
You might also want to download this project and look at it. It has a class that might do what you are looking for. http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=352&lngWId=10\\
Thanks for all your help. I ended up doing this: Public Shared Function LoadSearchResults(ByVal whereClause As String) As DataTable Dim table As DataTable = New DataTable() table.Columns.Add(New DataColumn("SSN")) table.Columns.Add(New DataColumn("REFERRED")) table.Columns.Add(New DataColumn("COMMENTS")) table.Columns.Add(New DataColumn("ADDITIONAL1")) table.Columns.Add(New DataColumn("ADDITIONAL2")) table.Columns.Add(New DataColumn("ADDITIONAL3")) Dim dr As OleDbDataReader Dim da As OleDbDataAdapter = New OleDbDataAdapter() Dim Cmd As New OleDbCommand() Try Cmd.Connection = dbConnection Cmd.CommandType = CommandType.StoredProcedure Cmd.CommandText = "qrySearchResult" Cmd.Parameters.Add(Cmd.CreateParameter()) Cmd.Parameters(0).Value = whereClause dbConnection.Open() dr = Cmd.ExecuteReader() Dim i As Int16 While dr.Read Dim ar As ArrayList = New ArrayList() For i = 0 To dr.FieldCount - 1 ar.Add(dr(i)) Next Dim row As DataRow = table.NewRow() row.ItemArray = ar.ToArray table.Rows.Add(row) End While dbConnection.Close() Return table End Function and It works fine except that MS Access ignores the where clause and returns all records. Do y:)ou know what I should do so that the query returs according to the where clause. below it the access query. SELECT SSN, REFERRED, COMMENTS, Additional1, Additional2, Additional3 FROM MAIN WHERE @WhereCluase;