I am using vb.net/access/Microsoft.Jet.OLEDB.4.0/DbaseIV to do this. The syntax varies between vb.net and SQl Server so I am having trouble.
I need to select one of each version in the data (using the code field) while keeping all the columns in the final output. I am selecting distinct on a code field but I cannot figure how to return the rest of the columns.
The issue is with this line
Dim sql2 = "SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName
Here is all of my 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**
This is just an example of my output i need to select all columns using an asterisk or something....
CREATE TABLE
statement. – ypercubeᵀᴹ Dec 10 '16 at 17:21