|
Colin Angus Mackay wrote:
You didn't get my little joke
lol...no I did not...
now I do...
As far as I know (AFAIK) this question has nothing to do with databases, SQL or ADO.NET
I honestly believed it was one of the feature within .NET (hence - the reference to microsoft) that I did not know about! Boy, do I feel dumb.
|
|
|
|
|
|
How do I rename a table in SQL Server 2000? I mean by script, I can do it easily enough in the Enterprise Manager.
Cheers,
Andy
|
|
|
|
|
|
hello, everybody ...
recently, I've been writing a data-access application in C# which works with MS Access database...
in my DB, i've got some fields of OLE Object data type...but i haven't got a clue how to retireve, insert and modify them programmatically...especially MS Word documents, Text files, MS Excel Worksheets.......
Does any body have any idea how to extract ole objec( .doc, .xls, .txt, .rtf and so on ) from MS Access database and vice versa... how to insert those types of files into Access......
I will really appreciate if you can give me a hand...
Alex Tian
|
|
|
|
|
Helll All
Can anyone guide me about how will ADO connection string for connecting to a named instance Sql server over internet, I am wondering do I need to mention instance name too or not, and if I don't mention named instance what will happen if there are more than one instance of Sql Server running on the same machine (in that case all will have same ip address).
Please guide me and reply ASAP.
I am stuck up...
|
|
|
|
|
I wrote this code to create an excel file using data retrieved from either SQL Server
or Microsoft Access. Here is the practice code that I wrote. When you execute the application it will create an excel file with the data from SQL server or Access database which is inserted into the excel worksheet. However, when you examine the cells in Excel you will notice that all the cells that contain text have an apostrophe (' single quote) character at the begining of cell. The cells that have integers or float numbers do not have any ' character at the beining of the cell. I like to know if anyone knows how to fix this issue. What changes do I have to make in order Please take a look at my code and let me know as to how I can fix it. Why does apostrophe get inserted into cells that have text.
/*
*
*/
// csc /target:exe /optimize+ /nologo WriteToExcel.cs
using System;
using System.Data;
using System.Data.OleDb;
namespace writetoexcel
{
public class WriteToExcel
{
private static String connectMDB;
private static OleDbConnection conMDB;
private static String connectxls;
private static OleDbConnection conxls;
[STAThread]
public static void Main (String[] args)
{
try
{
connectMDB = "Provider=Microsoft.JET.OLEDB.4.0;data source=jewelrycastle_db.mdb";
conMDB = new OleDbConnection(connectMDB);
conMDB.Open();
Console.WriteLine("Made the connection to the my access mdb database");
connectxls = "Provider=Microsoft.Jet.OLEDB.4.0;data source=froogle.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"";
conxls = new OleDbConnection(connectxls);
conxls.Open();
Console.WriteLine("Made the connection to the froogle.xls file");
String query = "SELECT b.JewelryType, b.Category, b.Metal, b.itemNumber, b.Title, b.Description, b.ourPrice, b.vendorNumber "
+ "FROM Bracelets AS b WHERE b.VIEW = TRUE UNION " +
"SELECT e.JewelryType, e.Category, e.Metal, e.itemNumber, e.Title, e.Description, e.ourPrice, e.vendorNumber "
+ "FROM Earrings AS e WHERE e.VIEW = TRUE UNION " +
"SELECT n.JewelryType, n.Category, n.Metal, n.itemNumber, n.Title, n.Description, n.ourPrice, n.vendorNumber "
+ "FROM Necklaces AS n WHERE n.VIEW = TRUE UNION " +
"SELECT p.JewelryType, p.Category, p.Metal, p.itemNumber, p.Title, p.Description, p.ourPrice, p.vendorNumber "
+ "FROM Pendants AS p WHERE p.VIEW = TRUE UNION " +
"SELECT r.JewelryType, r.Category, r.Metal, r.itemNumber, r.Title, r.Description, r.ourPrice, r.vendorNumber "
+ "FROM Rings AS r WHERE r.VIEW = TRUE";
OleDbDataAdapter adapter = new OleDbDataAdapter(); // Note 8
adapter.SelectCommand = new OleDbCommand(query, conMDB); // Note 9
DataSet ds = new DataSet("myaccess");
adapter.Fill(ds); // Note 10
DataTable inventoryTable = ds.Tables[0];
OleDbCommand cmd = conxls.CreateCommand();
// cmd.CommandText = "DROP TABLE [Sheet1$]";
// cmd.ExecuteNonQuery();
// Console.WriteLine("Drop Sheet1");
// cmd.CommandText = "DROP TABLE [Sheet2$]";
// cmd.ExecuteNonQuery();
// Console.WriteLine("Drop Sheet2");
// cmd.CommandText = "DROP TABLE [Sheet3$]";
// cmd.ExecuteNonQuery();
// Console.WriteLine("Drop Sheet3");
cmd.CommandText = "CREATE TABLE froogle (JewelryType char(255), Category char(255), Metal char(255), itemNumber char(255), Title char(255), Description char(255), ourPrice float, vendorNumber char(255))";
cmd.ExecuteNonQuery();
Console.WriteLine("create table done");
// cmd.CommandText = "INSERT INTO froogle (JewelryType, Category, Metal, itemNumber, Title, Description, ourPrice, vendorNumber) VALUES " +
// "('JewelryType', 'Category', 'Metal', 'itemNumber', 'Title', 'Description', 'ourPrice', 'vendorNumber')";
// cmd.ExecuteNonQuery();
Console.WriteLine("Insert row one");
// int numrows = 0;
foreach(DataRow row in inventoryTable.Rows)
{
// Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7]);
cmd.CommandText = "INSERT INTO froogle "
+ "(JewelryType, Category, Metal, itemNumber, Title, Description, ourPrice, vendorNumber) "
+ "VALUES ("
+ "'" + row[0].ToString() + "', "
+ "'" + row[1].ToString() + "', "
+ "'" + row[2].ToString() + "', "
+ "'" + row[3].ToString() + "', "
+ "'" + row[4].ToString() + "', "
+ "'" + row[5].ToString() + "', "
+ "'" + row[6] + "', "
+ "'" + row[7].ToString() + "')";
cmd.ExecuteNonQuery();
// numrows++;
} // end of foreach
Console.WriteLine("the number of rows returned is {0}", inventoryTable.Rows.Count);
} // end of try
catch(Exception e)
{
Console.WriteLine(e);
}
finally
{
conMDB.Close();
Console.WriteLine("the connection to the my access database is now closed");
conxls.Close();
Console.WriteLine("the connection to the excel file is closed is now closed");
}
} // end of Main()
} // end of class
} // end of namespace
|
|
|
|
|
how can get case sensitive controll in ado.net access database?
i've tryed in this way but no case sensitive controll is made:
string query = "SELECT * FROM myTable WHERE Name = '" + textBoxName.Text + "' AND StrComp(Password, '" + textBoxPassword.Text + "', 0) = 0";
|
|
|
|
|
As you can see below I am calling various user definded functions. When I removed two of my functions and replaced them with inline sql I gained 6 seconds for my query execution.
<br />
Select * from tableName<br />
dbo.fn_GetParameterSamples(tbl_project.ID," ","0") as ParameterList, <br />
dbo.fn_GetTemplateSamples(tbl_project.ID," ","0") as TemplateList, <br />
(select count(ID) from tbl_samples where tbl_Project.ID = id) as sampleCount,<br />
(SELECT COUNT(id) from tbl_samples where tbl_samples.ProjectID=tbl_Project.ID and isHold != 0 ) as SampleHold,<br />
What I would like to do is replace my other user defined funtions with inline sql(please look below at the sql). I seem to be having some trouble doing this. I would like to add the following line but I keep getting an error(Error 141: A Select statement that assigns a value to a variable must not be combined with data-retrieval operations) is there a way around this error? I believe removing the user defined funtions could improve the performance of my query.
<br />
(Select @ReturnParam = ISNULL(@ReturnParam + ', ','') + tbl_template.type from tbl_samples,tbl_template <br />
where tbl_Samples.projectID = tbl_project.ID and tbl_Samples.ID = tbl_template.sampleID) as TemplateLIst<br />
<br />
|
|
|
|
|
Hi
how i can find out that a column is FK?
how i can find out that a column is PM?
FK:Foreign Key
PM:Primary Key
|
|
|
|
|
HI,
I have a question about using a temp table in stored procedure. If for exmaple two individuals are using my applicaton and both press a button that calls the same stored procedure at roughly the same time will SQL Server know how to handle the creation of a temp table and perhaps wait unitl the first called sp is done executing. Or do I have to deal with some type of concurency check when using temp tables.
Thanks, I appreciate any advice that can be given to me.
|
|
|
|
|
Books Online has a full discussion of the under CREATE TABLE. It has a section on Temporary Tables that explains about the # table name prefix.
#MyTempTable: Local temp table visible only within the current SQL session. This is the one you want to use if you have multple users creating temporary tables with the same name.
##MyTempTable: Global temp table that all can see.
|
|
|
|
|
Thanks Michael, Thats exactly the information I was looking for. What is books online? I went to www.bookOnline.com... I must be going to the wrong place anyhow thanks for the info.
|
|
|
|
|
Books Online is the Transact-SQL help that is found in the SQL Query Analyzer that comes with SQL Server.
You will not have it if you are using MSDE only.
|
|
|
|
|
DOWNLOAD Books Online HERE[^]
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Hi all,
I'm hoping that somebody can help me with the performance of my a slow stored procedure that I have. This is a part of my scenario:
I have many Projects each project can have many samples and each sample can have many paramters. W
When I run my stored procedure to return all projects I have a funtion that I call on each row that gets all the samples(cursor) for a project. Then for each sample I get all the paramters(cursor).
Project -> samples -> paramters
for each sample I run through a cursor and concatnate a string of paramters that I find in a function I call.
With in the paramters function I run through the cursor and concatnate a string of paramters in the format of paramtername, paramtername, paramtername.
Now I have reason to belive this query is slow becuase of all the cursors we use. One of my collegues belives that the cursors are not the problem, infact he belives they are not slow at all that is all hype that cursors are poor in perforance is this right? Does anybody have any suggestions?
|
|
|
|
|
Cursors are slow. For example, last year I rewrote a stored procedure that used cursors to do the same thing without. I went from taking 20 minutes down to sub-one minute. A little bit more tweaking and the final result was about 7 seconds. So, while there may be other factors slowing down a stored procedure, eliminating cursors will almost certainly speed up things.
Remember that database systems are designed to work on sets. If you can give it a set of information to process then it works more efficiently than if you give it one row at a time.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Right now I am trying to figure out how I can eliminate the different cursors I have. I can not seem to think of the logic required to get say ten rows from a select statement and then concatnate a string based on a value from each row. Could this be done with a temp table? would a temp table be faster then a cursor? Is there perhaps a way to do this with a select statement to concatenate the string together kind of like when you use the sum method?
|
|
|
|
|
Here is some sample code to concat a strings without using a cursor. You will still have to loop through to append the results but it will eliminate one of your cursors.
DECLARE @ParamStr VARCHAR(1000)
SELECT @ParamStr = ISNULL(@ParamStr + ', ','') + ParameterName
FROM Parameters
PRINT @ParamStr
|
|
|
|
|
Thanks Michael this just might be what I'm looking for. I'll let you know how it turns out. My original query returns 318 records and takes about 1 min to run. I'm hopeing to knock it down to a few seconds.
|
|
|
|
|
Thanks for everyones help, I got my query down to 3 seconds from 1 min. I used a combination of a temp table and removed some user defined funtions that were absolutly useless and slowing my SP down. I normally hate working with SP's but I enjoyed learning about how to optomize a SP.
|
|
|
|
|
Hi, I am creating a dataset, by adding up to 10000 rows at a time. At present, I add each of these rows using a stored procedure. Is there anyway I can add the entire dataset instead of doing this row by row?
Thanks
|
|
|
|
|
Hi all,
How to make "ERD diagram" in SQL Server 2000.
plz discuss in detail
Thanx in advance
Sajjad
|
|
|
|
|
|
Hi,
Is there any way to define stored procedute to return nText(memo).
I need this to convert my memo field to my own data.
Any help?
Be simple and Be sample.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
