| Author |
 Topic  |
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
 Posted - 08/09/2006 : 11:55:46
|
You need to use Inner Join to join four tables. Read about Joins in sql server help file
Also make sure that the file is closed when executing the query
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
shybi
Starting Member
41 Posts |
Posted - 08/10/2006 : 08:51:39
|
| how can i close the windows form after executing it? is it (windows form name).CLOSE(); ? |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 08/10/2006 : 12:13:41
|
I meant, when you run that query make sure your EXCEL file is closed
Madhivanan
Failing to plan is Planning to fail |
|
|
|
shybi
Starting Member
41 Posts |
Posted - 08/11/2006 : 05:31:03
|
| not the excel sheet, i am asking how to close the windows application window after executing the program.. it still remains opened even after the execution. |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 08/11/2006 : 09:33:16
|
I am getting the following error when trying to export the SQL table data to excel. Please help.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
I used the following query:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT * FROM [Sheet1$]') select TOP 100 FirstName, LastName from Employee
what am i doing wrong?
Also, how can i create the xls on the fly rather than creating the xls manually and then specifying the column names etc? Is there a way to do that?
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 08/11/2006 : 11:41:42
|
shybi,Which front end application are you using?
ssrikrish, as error specifies, check the name of the sheet whether it is sheet1 or sheet 1
Madhivanan
Failing to plan is Planning to fail |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 08/11/2006 : 21:21:49
|
| It is Sheet1. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 08/14/2006 : 12:53:56
|
Check it again. If the name is correct you wont get that error
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Rusty0918
Starting Member
9 Posts |
Posted - 08/14/2006 : 13:06:19
|
| Is there a way I can store query results (I'm only doing it with one field) into an array, and then loop through and run queries that way? |
|
|
|
BCI_Guy
Starting Member
2 Posts |
Posted - 08/14/2006 : 16:05:17
|
| Thank you to everyone posting on this thread! It has been so helpful to me so far. I am having one big issue, though. I was trying to setup my sproc that exported to Excel (works fine) to be called from a trigger that passed the sproc an OrderNumber. Through my reading, I've learned that you can't dump to Excel when you're calling the sproc from a trigger. I guess it's because you can't do a distributed trasaction through Jet. Is there anyway to do this?? The other idea I've read is to use the trigger to fire an alert that starts a job to call the sproc, but I don't know how to pass the OrderNumber through all that mess! Any help would be great! |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 08/15/2006 : 09:24:40
|
Do you want to automate exporting data to EXCEL?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Rusty0918
Starting Member
9 Posts |
Posted - 08/16/2006 : 15:12:49
|
| Essentially, that's what I want to do, Madhivanan. Automate the export. |
|
|
|
BCI_Guy
Starting Member
2 Posts |
Posted - 08/16/2006 : 16:26:38
|
yup! I want SQL Server to export several records to Excel when a record is updated/inserted that has a particular value. I think I have been able to do it, but it's very guly. The trigger looks for the value, inserts the ordernumber into a new table, then raises an error. The error is set to fire a job. The job pulls the ordernumber where MAX(ID) of the new table. Then the job calls my sproc passing the ordernumber. The sproc pulls all the details for that ordernumber into a temp table and then copies the temp table to Excel, which maps to a different spreadsheet that formats it into the form I need.
It's ugly, and it's not perfect, but it works... except for sometimes, but I have no idea when/where/why it sometimes won't fire everything correctly. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 08/17/2006 : 03:07:54
|
If you want to automate the process, use stored procedure having the code and schedule it as a job to run periodically
Madhivanan
Failing to plan is Planning to fail |
|
|
|
shybi
Starting Member
41 Posts |
Posted - 08/17/2006 : 05:23:14
|
hi madhi,
i want to insert textbox value into the sql database like :
string sql = "insert into dummy (UserID,FirstName,LastName,Dept,Location,Company,Groups) values ('" + TextBox1 + "','" + TextBox2 + "','" + TextBox3 + "','" + TextBox4 + "','" + TextBox5 + "','" + TextBox6 + "','" + TextBox7 + "')";
here TextBox1,2,..,7 is the ID of the seven textboxes where i write values which should be passed to the code behind on click of submit button.but i am not able to pass the values
what should i do? |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 08/17/2006 : 08:09:12
|
what does that have to do with export to excel?
start a new thread rather than continuing this one.
request("TextBoxName") gives you the value of the posted textbox.
Go with the flow & have fun! Else fight the flow 
blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
Rusty0918
Starting Member
9 Posts |
Posted - 08/17/2006 : 10:48:06
|
How do I do a stored procedure? Do I do it via VBScript with a FOR loop? Essentially what I want to do is to first off get data from a specific field from one query and store it into an array, and then use the array to loop through and run the queries.
Essentially my Algortithm is this:
RUN QUERY WITH SPECIFIC SCHOOL FIELDS AND STORE THEM INTO ARRAY
FOR X = 1 to ARRAYLENGTH
SELECT * FROM DATABASE TABLES WHERE SCHOOL = ARRAYVALUE
WRITE TO EXCEL FILES
NEXT X |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 08/17/2006 : 11:26:53
|
Rusty0918,
If two tables are used, cant you simply join them and export the desired result to EXCEL?
Madhivanan
Failing to plan is Planning to fail |
Edited by - madhivanan on 08/17/2006 11:30:30 |
|
|
|
Rusty0918
Starting Member
9 Posts |
Posted - 08/19/2006 : 13:48:20
|
| No I don't want to join them. These are supposed to be seperate. Essentially I have a database with four tables and want to divide them up into multiple excel files with their own copies of those specific four tables, except that it exports the results from a certian query. And I want to do this multiple times, in a loop essentially, take the data from one of the column (a distict query) and store them into an array, then loop through that array, using the data field in the array to determine the query, the excel file name, and the data to be exported. |
|
|
|
Topic |
|
Export to Excel
