Wednesday, March 07, 2012 10:31 AM
In an older thread i was looking to read data into a temporary datatable, Now I think I have gotten this working, but i am not sure how to connect the temporary data set (if needed) to access the data from this.
Again here is my code
Imports System.Data Imports System.Data.SqlClient Public Class TestOperatorChecklistForm Private Property ShiftTasksTable As SqlDataReader Private Property ShiftTasksDataTable As SqlDataReader Dim daShiftTaskData As SqlDataAdapter Dim dsShiftTask As New DataSet Dim cmdShiftTaskData As New SqlCommand Dim qryShiftTaskData As String Dim LocalDataSet As New DataSet("Local_Dataset") Dim dtShiftTasksData As DataTable = LocalDataSet.Tables.Add("ShiftTasksData") Private Sub LoadDataFromDate(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim cmd As New SqlCommand Dim con As New SqlConnection Dim sqlShift As String Dim TaskDate As Date con = New SqlConnection(VariableModule.ConnString) '---- Handles the loading of data from "ShiftTasksData" ---- dtShiftTasksData.Clear() sqlShift = "SELECT * FROM ShiftTasksData" Try con.Open() Dim adapter As New SqlDataAdapter(sqlShift, con) adapter.FillSchema(dtShiftTasksData, SchemaType.Source) dtShiftTasksData.Columns.Item(0).AutoIncrement = False dtShiftTasksData.Columns.Item(0).Unique = True cmd.Connection = con cmd.CommandText = VariableModule.ShiftTasksDataTable ShiftTasksDataTable = cmd.ExecuteReader While ShiftTasksDataTable.Read() TaskDate = ShiftTasksDataTable.GetDateTime(1) If TaskDate = VariableModule.DateField Then 'dtShiftDate '[ID], [Date], [ShiftTaskIDNumber], [DataField] Dim newRow As DataRow newRow = dtShiftTasksData.NewRow() newRow("ID") = ShiftTasksDataTable.GetInt32(0) newRow("Date") = ShiftTasksDataTable.GetDateTime(1) newRow("ShiftTaskIDNumber") = ShiftTasksDataTable.GetInt32(2) newRow("DataField") = ShiftTasksDataTable.GetString(3) dtShiftTasksData.Rows.Add(newRow) 'adapter.Update(dtShiftTasksData, "ShiftTasksData") End If End While ShiftTasksDataTable.Close() 'I know i want to put it here to test that the data that i am reading is there. Catch ex As Exception MessageBox.Show("Error while connecting to SQL Server." & ex.Message) Finally con.Close() End Try End Sub End ClassAny help would be great!
- Changed Type fachagooch Wednesday, March 07, 2012 11:00 AM Wrong type
Wednesday, March 07, 2012 10:37 AM
I'm always curious, why have you set expressly this question to discussion.
It looks more to a question. If it is a question than set it back to question, all was it alone that the chance on answers become then higher.
Non answered question are kept in sight. Discussion mostly slowly die.
Therefore make it again a question in top of that first message.
Wednesday, March 07, 2012 11:13 AM
In what way do you want to "connect the temporary data set".
In your earlier thread you said you simply wanted a readonly datatable so you don't actually need a dataset and in fact your code doesn't really make use of one. Simply declare the temporary table as a New Datatable and get rid of the dataset declarations.
If you simply want to view the content of the dataset then add a DataGridView and set its DataSource to be the DataTable.
If you want something different then define what you want.
Thursday, March 08, 2012 5:08 AM
Here is the problem and a quick description of the program.
Where i work we had a paper check list that we have to perform that is approximatly 75 tasks per shift * 3, Approximatly 125 system checks per shift & fax system checks every hour *6 fax systems. I have created a program that dynamicly loads with each shift (first, Second, Third) each of these shifts has there own tasks except system & Faxes. So on the main load it loads the tasks and data that might be associated with that date/shift data.
Now each line in the data table for task takes 4 fields of data [ID], [Date], [ShiftTaskIDNumber], [DataField]. We have been running in test for 4 months and the load time on the page is starting to lag when looking at older data.
In an attempt to speed it up i am trying is to pull the data out of the sql DB when the date is selected and move it into a temporary DB so that i don't have to go to the sql server, search through ALL the data Again every time that you want to populate one of these fields. In Theory it should cut load time dramaticly since i am only going to the SQL Server once when the Date Field is populated vs every line it loads it goes to the sql server and looks to see if there is somthing in that field.
Now i can tell you that i am a newb to this, but can gennerally figure out what i am trying to do, wether it correct or not. and i know that you need a connection statment to connect to the sql DB example of the one that i am using during this revision is out of a my variable module.
Public Property ConnString As String = "Data Source=PCNAME\SQLEXPRESS;Initial Catalog=OperationsChecklist;Integrated Security=True"
All i am looking for is how do you connect the temporary table that was just created. Or is there a read statement that would be best suited for this?
Again any help is appriciated.
Thursday, March 08, 2012 12:01 PM
Well the "bare bones" of what you are trying to do is in the following:
Imports System.Data.SqlClient Public Class Form1 Dim DGV As New DataGridView Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load DGV.Dock = DockStyle.Fill Controls.Add(DGV) Dim DT As New DataTable Dim SelectString As String = "SELECT * FROM ShiftTasksData where Date=@Date" Dim DateToSelect As Date = #1/1/2012# Dim ConnectionString As String = "Data Source=PCNAME\SQLEXPRESS;Initial Catalog=OperationsChecklist;Integrated Security=True" Try Using Con As New SqlConnection(ConnectionString) Dim Command As New SqlCommand(SelectString, Con) Command.Parameters.AddWithValue("Date", DateToSelect) Using DA As New SqlDataAdapter() DA.SelectCommand = Command DA.Fill(DT) End Using End Using Catch ex As Exception MsgBox(ex.Message) End Try DGV.DataSource = DT End Sub End Class
Tuesday, March 13, 2012 6:50 AM
Thanks for the answer, although it is a much simpler way to gather the data that i am looking for, but It still dosn't answer my question of How does one read the data from the temporary data set once you have the data loaded into it.
While reading the data from a SQL Dataset i am using
'Do your meaningful code here
End WhileI want to do somthing the same with the data that i have stored in the LocalDataSet dataset. This is where i need the help at.
- Edited by fachagooch Tuesday, March 13, 2012 6:51 AM
Tuesday, March 13, 2012 7:08 AM
Be aware that every dataset is temporally.
DataSets are disconnected collections for a database. They reflect the same scheme as the database.
Somehow you try to do a very simple task very difficult. What is your reason for that.
If you want to create two the same datasets you can use this (and this is a real copy not a reference)
Imports System.Data Imports System.Data.SqlClient Public Class TestOperatorChecklistForm Dim LocalDataSet As New DataSet("Local_Dataset") Dim dtShiftTasksData As DataTable = LocalDataSet.Tables.Add("ShiftTasksData") Private Sub LoadDataFromDate(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim con As New SqlConnection(VariableModule.ConnString) dtShiftTasksData.Clear() Dim sqlShift = "SELECT * FROM ShiftTasksData" Using daShiftTaskData As New SqlDataAdapter(sqlShift, con) Try Dim adapter As New SqlDataAdapter(sqlShift, con) adapter.Fill(dtShiftTasksData) Catch ex As Exception MessageBox.Show("Error while connecting to SQL Server." & ex.Message) End Try End Using LocalDataSet.Tables.Add(dtShiftTasksData.DefaultView.ToTable) End Sub End Class
And try to avoid that kind of VB5 style coding. It is only misleading and has not any purpose in current programming.
Tuesday, March 13, 2012 7:22 AM
the reason for the temp dataset is to lessen the read time from the sql server... if i have a datatable with 500 rows of data vs the full datatable that contains 25000 rows of data this should will take a shorter time when i go have to call this data later in the process.
I used somthing similar to what you posted and it works in extracting the data that i am needing, now my question how do i utilize this data it was all simple when i was reading it one row at a time, doing my task with the data and then moving to the next data read. Now that i haveit stored in a data set i need to start pulling the data out of it and build my pages.
Thanks in advance
- Edited by fachagooch Tuesday, March 13, 2012 8:02 AM
Tuesday, March 13, 2012 1:55 PM
Well referring back to my previous post you have a datatable "DT". There are various ways to access the content of the table depending upon your exact requirement. My post showed the simplest method of just displaying it in a DataGridView.
You could also access it via its rows and columns, e.g.
More commonly you would only wish to access certain rows in which case you could do a Select on the table which will return an array of datarows
Dim Rows As DataRow() = DT.Select("ColumnName = value")
For Each Row As DataRow In Rows
- Marked As Answer by fachagooch Thursday, March 15, 2012 4:42 AM