|
GoodQuestion wrote: Export xls from sql
The easiest way to export data from a database to an excel file is through an Select into querry. I know this works on an Access db, never thought of trying it on an SQL server but it should still work (using ADO).
CString str;
str.Format("SELECT * INTO [Excel 8.0;DATABASE=%s].[%s] FROM [%s]", ExcelFile, Worksheet, table);
//pConn is a ADODB Connection
pConn->Execute(str.AllocSysString(), NULL, adExecuteNoRecords);
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
-- modified at 6:31 Thursday 5th January, 2006
|
|
|
|
|
Thanks for reply my question. That means to me alot. Sadly, I cant get it working bec of DB_E_ERRORSINCOMMAND stuff. I dont understand what is DB_E_ERRORSINCOMMAND for? But I know error is related to spIConnection->Execute bec that is where error came from. can you please explain what is its cause? THANK YOU!!
_bstr_t bstrConnect( L"driver={sql server};"
L"server=COYDELL;"
L"Database=brntbl;UID=sa;PWD=;" );
_ConnectionPtr spIConnection;
spIConnection.CreateInstance( __uuidof(Connection ) );
spIConnection->ConnectionString = bstrConnect;
spIConnection->Open("DSN=BurnDB","","", NULL);
CString bstrCommand;
bstrCommand.Format("SELECT * INTO [Excel 8.0;DATABASE=C:\\exceltest.xls].[HoleDiameters$] FROM [HoleDiameters]");
HRESULT hr = spIConnection->Execute(bstrCommand.AllocSysString(),NULL,adExecuteNoRecords);
spIConnection->Close();
"HoleDiameters" is name of sheet and Table from DB
|
|
|
|
|
Hello,
I have a similar post in Asp.Net forum. My questions are related with Asp.Net 2.0 and SQL 2005 so I am not sure if this shouldn't be the best forum to post this message.
I am working on an Asp.Net 2.0 / MSSQL 2005 web site with authentication.
My web site has two type of users: "students" and "professors". They are not related!
All users need to login. Students use the role "student" and professors use the role "professor".
The information for students is different from the information of professors.
I could put all students and professors in the same table and leave some fields blank.
My option was to create the following tables:
A) "Authentication" with the fields:
[UserId], [Password], [PasswordFormat], [MobilePIN], [Email], [PasswordQuestion],
[PasswordAnswer], [IsApproved], [CreateDate], [LastLoginID] and [UserRole].
B) "Students" with the field [StudentID] and all the information fields for students.
C) "Professors" with the field [ProfessorID] and all the information fields for professors.
My questions are:
1. Am I following the right approach by using these 3 tables?
2. Should I place all info from users (students, professors and authentication) in "Authentication" table?
3. Should I keep only two tables, "Students" and "Professors" and move the login information to each record in these tables?
4. If using the 3 tables how can I know the info for a user after it logins? I could use a foreign key in Authentication table but I have 2 tables: students and professors.
Anyway this is really confusing.
Can someone, please, give me some advice of how to create the tables and set this authentication?
Thanks,
Miguel
|
|
|
|
|
Miguel,
Your approach is good, and placing all user info in one table is okay. If you were to use two tables, then you'd probably need separate login pages for students and professors. For the last question, I would keep track of the session (or a cookie, this would require cookies to be enabled on the one hand) and tie it to the user, maybe in the authentication table. I do not know much about asp.net but this is how I would do it with a database and php. Seems like you are on track and I hope this helps some.
Paul
|
|
|
|
|
If you were to go with a session id, you'd need a table holding the session id and the user associated to the session id. All you'd have to do is pass the session id from page to page.
Paul
|
|
|
|
|
hi
i have the following code
the code
CREATE PROCEDURE proc_InsertOrder
(@OrderId int output,
@CustomerName varchar(50),
@ShippingAddress varchar(50)
)
AS
INSERT INTO Orders (CustomerName, ShippingAddress)
VALUES
(@CustomerName, @ShippingAddress)
SELECT @OrderId=@@IDENTITY
when i try to execute it asks for declaration of the OrderID if i declare then it asks for declaration of CustomerName now i dont know how to deal with this situation can anyone tell what i can do.
Procedure 'proc_InsertOrder' expects parameter '@OrderId', which was not supplied.
or is there any proper code for the decalaration
thanks
sasire18
|
|
|
|
|
It depends on how you are executing the procedure. As per my knowledge the correct way of executing the procedure is :
Declare @OrderID int
exec proc_InsertOrder @OrderId output, 'Akif', 'Test'
Akif
|
|
|
|
|
write an asp page that actually creates an xml document
And in that page, after the xml document is created, the stored procedure should be called and the xml document should be passed as the input parameter
suneel
|
|
|
|
|
I want to create an xml document and want to store in sqlserver.
And i want to create xml document in asp page and want to store in database as input parameter using stored procedure.
Can any body help to me
please send to suneel@softwarenucleus.com
suneel
|
|
|
|
|
Can you tell me urgency to store whole file in sql server database.
Instead, u can save url of the file and file physically to some folder in server.
Any systematic work reflects its significance for a long time, Though I m not totally against "The Prototyping".So let's discuss in depth...
|
|
|
|
|
Iam happy about your reply. thank u.
As i have two stored procedures in my project, so i want to get all details from one stored procedure and i want to send the information as input paramenter to the second stored procedure.
suneel
|
|
|
|
|
Hello,
This is urgent.
I have a Table ARTICLE_MASTER.
fields are item_code, activation_date.
I can different item_code for each article.
each article has a activation date.
select * from ARTICLE_MASTER
inner join
ARTICLE_MASTER ARTICLE_MASTER1
on ARTICLE_MASTER.item_code= ARTICLE_MASTER1.item_code
where ARTICLE_MASTER.activation_date < = getdate() and
ARTICLE_MASTER.end_date > = getdate() and
ARTICLE_MASTER.activation_date > ARTICLE_MASTER1.activation_date
order by article_master.item_code
I can have 4 rows for a single article - say 001- for Comb.
and for a given month say December, my acticle master can have 4 rows for article 001. with activation date and end date, for a single article i can have 4 rows.
say
ITEM_CODE ACTIVATION_DATE END_DATE PRICE
001 01/12/2005 31/12/2005 15
001 07/12/2005 31/12/2005 20
001 14/12/2005 31/12/2005 25
001 21/12/2005 31/12/2005 20
SO After comparison with my current date i have to decide which price to apply.
say on 2nd dec price should be 15
on 8th dec price should be 20
on 15th dec price should be 25
on 21-22th dec price should be 20
so i need to pick the appropriate row for a given date.
The query i have written above gives only the latest one say on 16th still it gives me 21st dec's row.
Please give me a solution for it.
its urgent
Amit Malik
.NET Developer
|
|
|
|
|
It may look something like this:
SELECT
am1.*
FROM
ARTICLE_MASTER am1
WHERE
getdate() BETWEEN am1.activation_date AND am1.end_date AND
am1.activation_date = (SELECT
MIN(am2.activation_date)
FROM
ARTICLE_MASTER am2
WHERE
am2.item_code = am1.item_code AND
getdate() BETWEEN am2.activation_date AND am2.end_date)
ORDER BY
item_code
|
|
|
|
|
hi ,
i am new in mssql2000.
i have table named journal. before inserting dat into it , check that journal no. exist, if exist delete that journal and insert the new one.
is it do with trigger? then how?
with regards
vipin
vipin
|
|
|
|
|
You could do this with a trigger. However, why delete the row? Why not update the existing row with the new data.
Personally, I do this with a stored procedure.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
hello everybody
i'm Tuan, i have just stated with database(SQL server 2000).i hardly knwon anything about database,if anyone who have a small program about database , please send to hepl me to do practice :How to creat database, add in with VB.net with component connection!
Thanks bro!!!
send to me at: anhtuantelecom@yahoo.com
-- modified at 2:54 Sunday 25th December, 2005
|
|
|
|
|
I would suggest that your best course of action would be to get a book on ADO.NET and one SQL Server 2000.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
Or take a look around at Code Project. There are numerous articles that can help.
|
|
|
|
|
HOW TO DELETE A RECORDS FROM THE TABLE WHICH HAS THE FIELD SAY CSA WHICH IS NOT A NUMBER.
I.E IT SHOULD RETAIN THE RECORDS WHOSE CSA IS NUMBER???
|
|
|
|
|
use regular expressions to filter your numeric valued records
Any systematic work reflects its significance for a long time, Though I m not totally against "The Prototyping".So let's discuss in depth...
|
|
|
|
|
sorry for the delay, but i cannt sent the r.e. at this time.
well, there is no native support of regular xpressions in MS SQL server 2000
go to this link, http://www.sqlteam.com/item.asp?ItemID=13947
wish u sharp ur skills.
u need pl/sql function to filter numeric valued tuples.
if this is not a problem for always
you can apply this line of query(vb.net or vbscript)
dim sql as string
dim i as integer
dim maxlimit as integer
sql="delete from tblName where tblid not in (select tblid from tblName where csa in ("
for i=0 to maxlimit-1
sql & "'" & cstr(i) &"',"
next
sql & "'" & cstr(maxlimit) &"')"
NOTE: this code is not tested
try this out.
Wish you solve your problem
Any systematic work reflects its significance for a long time, Though I m not totally against "The Prototyping".So let's discuss in depth...
|
|
|
|
|
I have a number representing a month:
DECLARE @MONTH INT
SET @MONTH = 5
How can I use the DATENAME() function to get
the name of the month?
I must have a full date variable to use this
and I don't know how to build a date using
@MONTH as its month...
PLEASE HELP...
|
|
|
|
|
lior654#hotmail.com wrote: How can I use the DATENAME() function to get
the name of the month?
DATENAME() can accept a varchar as the date, so you can just create a random date, with the month as you want:
DECLARE @somedate varchar(20);
SET @somedate = '2005-01-01';
SELECT datename(month,@somedate); This will return "January".
However, I would recommend that user interface type things like getting a month name are best carried out in the UI layer leaving the data layer to get on with things that it is good at (like manipulating the data in the database)
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
My Table has the following structure:
ID Name Description
1 A Desc1
2 A Desc2
3 A Desc3
4 B Desc1
5 B Desc2
The expected output is:
Name D1 D2 D3
A Desc1 Desc2 Desc3
How do i get the above result by SQL Query or Function and not using CURSORS.
Pls. help me out!!!
|
|
|
|
|
i want to create query from two different SQL Server 2000 from two different computer. then how i can access this command. please help me
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
