Help with simple query

B

Brett

The first query will run but the second will not:

Dim constring As String
Dim id As String = "10-03"
File.Delete(FilePath & CSVFileName)
constring = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
FilePath & DataBaseFileName)
Dim objadapter As New OleDbDataAdapter

With objadapter
.SelectCommand = New OleDbCommand
.SelectCommand.CommandText = ("SELECT * INTO
[Text;DATABASE=C:\myFiles\].[test.csv] FROM [Interest Group List];")
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.Connection = New OleDbConnection(constring)
.SelectCommand.Connection.Open()
.SelectCommand.ExecuteNonQuery()
.SelectCommand.Connection.Close()
End With


Using this line throws the following error on the ExecuteNonQuery() line:
..SelectCommand.CommandText = ("SELECT [list number] INTO
[Text;DATABASE=C:\myFiles\\].[test.csv] FROM [Interest Group List];")

Error:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll

Why do I get a column name into the SELECT? The column name does have a
space and I can't get around that.

Thanks,
Brett
 
M

Michael C#

Are you sure that the extra space is the issue? You appear to have an extra
\ in the second query also. I would put a Try...Catch blosk around this
code and look at the Exception.Message to get a more detailed error message
description.
 
B

Brett

No, that was a mistake in my posting. If you try this with the default
Northwind database, it will throw an error:

Dim AccessConn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT
unitprice, quantity INTO [Text;DATABASE=C:\test].[Table.csv] FROM [order
details]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

Use:
SELECT *
and everything will be fine.

Brett

Michael C# said:
Are you sure that the extra space is the issue? You appear to have an
extra \ in the second query also. I would put a Try...Catch blosk around
this code and look at the Exception.Message to get a more detailed error
message description.

Brett said:
The first query will run but the second will not:

Dim constring As String
Dim id As String = "10-03"
File.Delete(FilePath & CSVFileName)
constring = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
FilePath & DataBaseFileName)
Dim objadapter As New OleDbDataAdapter

With objadapter
.SelectCommand = New OleDbCommand
.SelectCommand.CommandText = ("SELECT * INTO
[Text;DATABASE=C:\myFiles\].[test.csv] FROM [Interest Group List];")
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.Connection = New OleDbConnection(constring)
.SelectCommand.Connection.Open()
.SelectCommand.ExecuteNonQuery()
.SelectCommand.Connection.Close()
End With


Using this line throws the following error on the ExecuteNonQuery() line:
.SelectCommand.CommandText = ("SELECT [list number] INTO
[Text;DATABASE=C:\myFiles\\].[test.csv] FROM [Interest Group List];")

Error:
An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll

Why do I get a column name into the SELECT? The column name does have a
space and I can't get around that.

Thanks,
Brett
Click to expand...
Click to expand...
 
M

Michael C#

Sorry, I'm not familiar with using OleDb to access or generate CSV files.
Sounds like OleDb doesn't recognize individual column names in a CSV file,
but I'm not sure. Sorry bout that.

Brett said:
No, that was a mistake in my posting. If you try this with the default
Northwind database, it will throw an error:

Dim AccessConn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT
unitprice, quantity INTO [Text;DATABASE=C:\test].[Table.csv] FROM [order
details]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

Use:
SELECT *
and everything will be fine.

Brett

Michael C# said:
Are you sure that the extra space is the issue? You appear to have an
extra \ in the second query also. I would put a Try...Catch blosk around
this code and look at the Exception.Message to get a more detailed error
message description.

Brett said:
The first query will run but the second will not:

Dim constring As String
Dim id As String = "10-03"
File.Delete(FilePath & CSVFileName)
constring = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
FilePath & DataBaseFileName)
Dim objadapter As New OleDbDataAdapter

With objadapter
.SelectCommand = New OleDbCommand
.SelectCommand.CommandText = ("SELECT * INTO
[Text;DATABASE=C:\myFiles\].[test.csv] FROM [Interest Group List];")
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.Connection = New OleDbConnection(constring)
.SelectCommand.Connection.Open()
.SelectCommand.ExecuteNonQuery()
.SelectCommand.Connection.Close()
End With


Using this line throws the following error on the ExecuteNonQuery()
line:
.SelectCommand.CommandText = ("SELECT [list number] INTO
[Text;DATABASE=C:\myFiles\\].[test.csv] FROM [Interest Group List];")

Error:
An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll

Why do I get a column name into the SELECT? The column name does have a
space and I can't get around that.

Thanks,
Brett
Click to expand...
Click to expand...
Click to expand...
 
Top