How do I select the data from my DataTable using a SQL statement?
I am trying to select all the data in my datatable using a sql statement but I am having trouble. I then export the datatable to a dbf file. Here is what I have so far:
Code:
Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & path & “;” & “Extended Properties=dBase IV”)
dBaseConnection.Open()
Dim SQLCreateCommand As String
Dim NewString As String = dt.ToString
SQLCreateCommand = "SELECT * INTO NewTable From " & DataTable
Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(SQLCreateCommand, dBaseConnection)
dBaseCommand.ExecuteNonQuery()
dBaseConnection.Close()
Re: How do I select the data from my DataTable using a SQL statement?
What exactly is 'DataTable'? Is it a String containing the name of a database table? If so then why not name it 'tableName', which actually describes what it is? If it actually is a DataTable then of course that code isn't going to work.
Anyway, rather than say "I am having trouble" and expect us to know what that means, please actually tell us what it means. Is an exception thrown? If so, where and what is the error message? If not then what actually does happen that you don't expect?
Re: How do I select the data from my DataTable using a SQL statement?
Sorry it would be something like below, I have already populated it as well. When I run that code it gives me a syntax error because its trying to query data from the datatable but there is no OleDbConnection to the actual DataTable. So, I guess my question would be "How do I create an OleDbConnection to a DataTable?"
Baiscally I am using the SQL statement to write data to a .dbf file but I can't get the data in my DataTable to export out to the .dbf
Code:
Dim dt as new DataTable
Re: How do I select the data from my DataTable using a SQL statement?
You don't. The datatable has a few members that allows you to work with it. Take a look at the Select() method of the datatable. If that isn't enough, then LINQ would also work, and that would be more SQL like (though the syntax in Select() isn't bad).
I'd write an example, but the sample you gave doesn't make much sense. SELECT * would return EVERYTHING, but ExecuteScalar will only return the first value from the first row, so the SELECT and ExecuteScalar don't make much sense when put together. If you are all right with only getting the first value from the first field, then you would never use SELECT *. That isn't efficient, even if there is only one field in the database. SELECT * is for use when performance isn't an issue and you either want EVERYTHING, or there are a ton of fields and you want most of them. In all other cases, you shouldn't be using SELECT *.
If you are using Datatable.Select(), then you'd get back a set of rows. To get one row, it would be Datatable.Select(<some criteria>).FirstOrDefault()
That would return either one datarow or Nothing. You'd get Nothing if no rows matched the criteria. You'd then have to select the field you wanted from that row. This could all be done in a single line, but not with complete safety, so I'd use more than one row. On the other hand, if you used LINQ, you could probably get just the value from one field in one row in a safer fashion.
Re: How do I select the data from my DataTable using a SQL statement?
The problem here is not that what you're trying to do is not working. It's that you're trying to do the wrong thing. This code:
vb.net Code:
SQLCreateCommand = "SELECT * INTO NewTable From " & DataTable
Suggests that what you're trying to do is insert all the records from your DataTable into the NewTable table in your database. If that's the case then you don't have to perform any query. How do you usually insert data from a DataTable into a database table? By calling Update on a data adapter with an appropriate InsertCommand; that's how. That is what you do here. For an example, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.
Whether or not it will work as is depends on how you populated the DataTable. If you have added the rows yourself and AcceptChanges has not been called then you're good to go. If AcceptChanges has been called, either explicitly or implicitly by a Fill or Update call on a data adapter then the data is not ready to be inserted. In order that it is, the RowState of each DataRow must be Added. If you have populated the DataTable by calling Fill on a data adapter then you can simply set the AcceptChangesOnFill property of the adapter to False beforehand. Otherwise, you can loop through the Rows collection of the DataTable and call SetAdded on each row.
Re: How do I select the data from my DataTable using a SQL statement?
Having read one of your other threads, I realise now that you are trying to create the database table at the same time. To be honest, I'd be surprised if that was possible but I could be wrong.
Re: How do I select the data from my DataTable using a SQL statement?
Ok, I've narrowed my code down to just figuring out the SQL statement. Now I just need to figure out the syntax of selecting one of each version while keeping all the columns. I will open a new post, seeing how it's a completely different question, but in case you still want to help I will post it here on this thread too. Here is my updated code:
Code:
Dim ofd As New OpenFileDialog
With ofd
.Filter = "DBASE File (*.dbf)|*.dbf"
.Multiselect = False
.CheckFileExists = True
End With
If ofd.ShowDialog() = DialogResult.OK Then
Dim fi As New IO.FileInfo(ofd.FileName)
Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBase IV;Data Source='" _
& fi.DirectoryName & "'")
Dim TableName As String = fi.Name.Substring(0, fi.Name.Length - fi.Extension.Length)
Dim cmd As New OleDb.OleDbCommand(TableName, cn)
cmd.CommandType = CommandType.TableDirect
cn.Open()
Dim rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
dt.Load(rdr)
SelectField.ShowDialog()
Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & path & “;” & “Extended Properties=dBase IV”)
dBaseConnection.Open()
Dim SQLCreateCommand As String
Dim sql2 = "SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName
Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(sql2, dBaseConnection)
dBaseCommand.ExecuteNonQuery()
dBaseConnection.Close()
cn.Close()
cn.Dispose()
End If
Re: How do I select the data from my DataTable using a SQL statement?
