|
Hello,
I am getting an error when trying to save my XLS to .DBF. The error is: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'. I am getting the error at this line of code: var cnn = new OleDbConnection(cnnStr);
var cnnStr = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\file.xls;ReadOnly=1;ColNameHeader=False";
var cnn = new OleDbConnection(cnnStr);
var dt = new DataTable();
try
{
cnn.Open();
string sqlConnectionString = "Data Source=Zach-PC;Initial Catalog=master;Integrated Security=True";
var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
string sql = String.Format("select * from [{0}]", sqlConnectionString);
var da = new OleDbDataAdapter(sql, cnn);
da.Fill(dt);
}
catch (Exception e)
{
throw e;
}
finally
{
cnn.Close();
}
Any pointers?
Regards,
Glen
|
|
|
|
|
That looks like an ODBC connection string. Try one of the OLEDB Excel connection strings from:
http://www.connectionstrings.com/excel/[^]
For example:
var cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\file.xls;Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks solved that error, I am getting a different error now tho error: 'DataSource=excelFilePath;InitialCatalog=master;IntegratedSecurity' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
|
|
|
|
|
DataSource is wrong, it should be Data Source : two words.
Veni, vidi, abiit domum
|
|
|
|
|
Okay thanks, same error occurs though with Data Source as two words.
|
|
|
|
|
The rest of the information in that statement does not look like the example that was suggested to you.
Veni, vidi, abiit domum
|
|
|
|
|
Connection strings are provider-specific. The sting you have there uses an Excel file as the "data store" but you are passing SQL-server parameters with InitialCatalog and IntegratedSecurity. Those aren't parameters the "Excel data provider" understands.
|
|
|
|
|
You've opened an OLEDB connection to an Excel spreadsheet, and you're now trying to execute the query:
select * from [Data Source=Zach-PC;Initial Catalog=master;Integrated Security=True]
That's not a valid query. A valid query would look something like:
select * from [SheetName$Range]
Perhaps if you explain what you're trying to achieve, we might be able to help.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am trying to achieve an application that is able to read in an Excel Spreadsheet (.XLS) and be able to convert/save out to a Database file (.DBF).
Regards,
|
|
|
|
|
Start by reading the sheet:
static DataTable LoadExcelSheet(string fileName, int worksheetNumber, bool headers)
{
if (string.IsNullOrEmpty(fileName)) throw new ArgumentNullException("fileName");
if (!File.Exists(fileName)) throw new FileNotFoundException(null, fileName);
var cnnStr = string.Format(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR={1};IMEX=1\"",
fileName,
headers ? "Yes" : "No");
using (var cnn = new OleDbConnection(cnnStr))
{
cnn.Open();
var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var sheetRow = schemaTable.AsEnumerable()
.Where(row => !((string)row["TABLE_NAME"]).EndsWith("$"))
.Skip(worksheetNumber).FirstOrDefault();
if (sheetRow == null) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
string sql = string.Format("SELECT * FROM [{0}$]", sheetRow["TABLE_NAME"]);
var da = new OleDbDataAdapter(sql, cnn);
var dt = new DataTable();
da.Fill(dt);
return dt;
}
}
That will give you a DataTable containing the data from the Excel sheet. You'll then need to use a SqlConnection and a SqlDataAdapter to update your SQL database.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Okay Thanks will try that!
|
|
|
|
|
I am working on a small application that will read sql xml type to C# WPF. Since my database information is changing I need to display the root node name for each sql data type created. I can query the database and display the hyperlinks but unable to open them and get to the root node name. Any suggestions?
|
|
|
|
|
Member 10306797 wrote: Any suggestions? ..you could post the code that you're using to query, display and open them here, and we could have a look.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
void fill_listbox2()
{
SqlConnection connection = new SqlConnection(@"Data Source=****");
try
{
connection.Open();
string Query = "SELECT * FROM myTable"
SqlCommand command = new SqlCommand(Query, connection);
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
string name = dr.GetString(1);
listview2.Items.Add(name);
}
connection.Close();
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message);
}
}
|
|
|
|
|
I am working on a project that will accept the input 'H', 'U', or 'E' and the input of two textboxes. The end result is calculated to show "Bill".
I have everything working except I can't seem to be able to set a default number for the textboxes based on the 'H','U', or 'E' input.
A few guidelines are: 'H' can have a maximum of 5 txtinstallations. The default number is 1.
'E' has a default of 10 txtinstallations included. For each installation above 10, an additional price is added.
Both 'H' and 'E' have a default of additional programs of 0. Number must not be negative.
How do I go about writing this code?
|
|
|
|
|
You have already posted this question at C# Programming... Help![^], and received some suggestions. If you have further input then please respond in the origianal question rather than posting the same thing in multiple forums.
Veni, vidi, abiit domum
|
|
|
|
|
If you want numbers, you want a NumericUpDown, not a TextBox.
|
|
|
|
|
Have you tried it with regular expressions
|
|
|
|
|
Hello, this question is related to the System.ComponentModel namespace.
I started to work on a project that allows creating forms at design-time. I have a DesignSurface and all features are already implemented except for the Undo/Redo and Copy/Paste feature.
By reading on the net, I got that I could use the UndoEngine class for Undo/Redo functionality if I create an additional service, a serialization service. It`s still blurry.
I know even little about the requirements for the Copy/Paste features. I can invoke the command, but nothing happens.
I think the commands will work if I add another unknown service,
but this is an assumption.
If someone could shade light on how this works.
Also, can anyone tell me what the heck is CodeDom Serialization?
Thanks
|
|
|
|
|
How is this?[^]
That may help for the first part.
Also...
CodeDom Serialization is basically taking the designer and creating the backend code for it (like what is in the *.designer.cs files).
Keep Clam And Proofread
--
√(-1) 23 ∑ π...
And it was delicious.
|
|
|
|
|
Hi All!
I have programmed two years in VB.Net and I'm thinking of moving on to C#. Main reason being that some applications at work are in C# and the online documentation I feel is better. I'm selflearned.
My question is if you have tips regarding the problems below, documentation or tutorials that helped you once upon a time.
Now to the problems:
1. I'm having difficulties to grasp how my new projects structure internally would be, best case. Often I start programming and finding out as I go. I feel I'm having planning issues. I work alot with database access applications.
2. When I'm programming I feel that I have a problem with finding good object structure. Per say; I have a class that handles errormessages but I have trouble seeing the solution for the structure of it and how other classes should us this class.
3. I work alot with databases, SQLite, Access etc. I've been reading into Dapper-solutions for easy CRUD work. Somehow I feel I should start of more basic.
4. I lack some basic OOP understanding. Even if I've read alot I feel I cannot implement it. For example interfaces, inheritence and project patterns. Examples I've read is relatade to real-time objects as Cats, Cars etc. But when I have for example "Register an abscense / holiday" etc. How should that object look and fall in line with other objects, no clue...
Any tips on either number is greatly apprieciated!
H
|
|
|
|
|
Bankaida wrote: have programmed two years in VB.Net and I'm thinking of moving on to C#.
IMHO there is not much difference between the two. For basic syntax and usage try Microsoft Virtual academy .[^]
I'm not sure if this is what your looking for.
There are many great articles here on Code Project. Just search for relevant articles. Also in Q&A You can ask specific questions. Some of the guru's will help. (usually)
Hope this helps.
David
|
|
|
|
|
|
You are correct sir.
David
|
|
|
|
|