how to read an excel file in sql server 2000 - Database Discussion Boards

Re: cancel inner join

Pete O'Hanlon10-Jul-07 2:11

Pete O'Hanlon

10-Jul-07 2:11

Use the INNER JOIN syntax.

SELECT a.Col1, a.Col2 FROM TableA a 
INNER JOIN TableB b
ON b.FK_Field = a.PK_Field

This will return you rows where items are present in both tables only.

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Re: cancel inner join

WoutL10-Jul-07 2:15

WoutL

10-Jul-07 2:15

Use a left join:

select TableA.col1 ,TableA.col2 ,TableB.Col1<br />
From TableA<br />
inner join TableB on TableA.col3=TableB.Col9

Wout Louwers

Re: cancel inner join

Amr M. K.10-Jul-07 2:48

Amr M. K.

10-Jul-07 2:48

yes that right..
I forget that...

aj123

INSERT query for a table?(Using two Tables)

chand1010-Jul-07 0:52

chand10

10-Jul-07 0:52

Hi
I stuck at an INSERT Query using two tables.
I have two tables Table1 and Table2.In Table1 I have 2 columns,one with dupicate value and another with null values.In Table2 I have 2 columns,both the columns have unique values.

Now I have to INSERT values from Table2 to Table1 with the corresponding or maching values like

Table1
StateCode StateName
--------- ----------
AK NULL
AK NULL
AK NULL
AL NULL
AL NULL
AL NULL
CO NULL
CO NULL
CO NULL

Table2
StateCode StateName
--------- ----------
AK Alaska
AL Alabama
AR Arkansas
AZ Arizona
CA California
CO Colorado

I have to INSERT values into Table1 in the null column from Table2 w.r.t StateCode.

Help me Confused | :confused:

Suggest me an example.

I have not failed. I've just found 10,000 ways that won't work. -Thomas A. Edison

Thank u
Chandu

Re: INSERT query for a table?(Using two Tables)

andyharman10-Jul-07 1:39

andyharman

10-Jul-07 1:39

Hi Chandu

Sounds like you need to "update". Something like:

update Table1 set StateName = Table2.StateName
  from Table2
  where Table2.StateCode = Table1.StateCode

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Error "item has already been added.Key in dictionary" while programming in .Net using DB2 as backend

salimssss10-Jul-07 0:40

salimssss

10-Jul-07 0:40

i was trying to analyze this error.....
i want to know this error is due to frontend or backend.....
i just want the solution for this error in Frontend or backend....

dasdfsadfvasdcvasd

Re: Error "item has already been added.Key in dictionary" while programming in .Net using DB2 as backend

Paul Conrad15-Jul-07 5:24

Paul Conrad

15-Jul-07 5:24

Is the field a primary key?

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

how to read an excel file in sql server 2000

Amit Sk Sharma9-Jul-07 22:00

Amit Sk Sharma

9-Jul-07 22:00

I tried a query

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\TestSheet.xls',
'SELECT * FROM [Sheet1$]')

but its giving me error either sheet1 not found or syntax error near ')'

unable to find out the base of problem. Plz suggest me the perfect query that could be able to fetch data from excel sheet. I implemented it through front end (VB.NET) and its working properly but this time i need to write a stored procedure for the same.

With Thanks & Regards
Amit Sk Sharma

Re: how to read an excel file in sql server 2000

Paul Conrad13-Jul-07 11:51

Paul Conrad

13-Jul-07 11:51

Does Sheet1 exist in the file?

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

ORA-12505 [modified]

Shajeel9-Jul-07 21:52

Shajeel

9-Jul-07 21:52

i am getting the following problem when connecting to oracle using OracleClient in C# ASP.NET 1.1:

ORA-12505: TNS:listener could not resolve SID given in connect descriptor

I am using this in connection stirng with proper user and password:

Data Source=ABC;User ID={0};Password={1};Unicode=true

where ABC is the Database added in Management Console (only client is installed on the computer), i can connect to this database using Console and also by creating dsn. Can anyone figure out the problem, as my program is running on many other pcs correctly.

-- modified at 7:06 Tuesday 10th July, 2007

Regards
Shajeel

Re: ORA-12505

andyharman11-Jul-07 3:16

andyharman

11-Jul-07 3:16

Did you configure the "tnsnames.ora" file on your PC? This may help: http://www.codeproject.com/vbscript/connection_string.asp[^]

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: ORA-12505

Shajeel11-Jul-07 18:27

Shajeel

11-Jul-07 18:27

Problem is that my application is running on dozens of other boxes, so it is not the problem in which i want to change my code. This is some sort of configuration problem which i am not able to resolve.

Regards
Shajeel

Connection problem pls help

micydon9-Jul-07 21:39

micydon

9-Jul-07 21:39

Hi

I am using asp.net 2003(vb code behind). I hve given the connection in web.config like this

<add key="test" value="Provider=SQLOLEDB.1;Password=mypwd;Persist Security Info=True;User ID=myuid;Initial Catalog=Test;Data Source=myserver">
when i changed the data source to another server this is working properly in a lan network
i hve uploaded my project to a site for eg. if i give like www.mysite.com/login.aspx then if i enter the user id and password wht i want is i hve to access the local systems sql database( i want to get data from the database test whose connection i give above). But this is showing error as object reference not set. What should i do now to
Any body pls help me to resolve this problem.do i give any other thing in the connection string

Thanks in advance
with regards
anitha

Re: Connection problem pls help

Paul Conrad13-Jul-07 11:53

Paul Conrad

13-Jul-07 11:53

micydon wrote:
showing error as object reference not set

Set the object reference using New might be a start...

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Help needed for an user defined Function

Exelioindia9-Jul-07 21:14

Exelioindia

9-Jul-07 21:14

Hi all,

I am new to SQL user defined function, I wrote an function on my own, I creates this function to get the count of total records available in an speciifed country, it works well, but where is no record in a specified country, it returs NULL and row count as 1. The function which i written is as below

CREATE FUNCTION [dbo].[cnt_country] (@country varchar(12))
RETURNS varchar(5) AS
BEGIN
declare @row_count varchar(5)
select @row_count=[country_code] from [dbo].[countrywise_users]
where [country_code]=@country
return @row_count
END

Actually i need to get the total row's available based on a country. If there is no records for an country , it should return null. Plz any one help me...

Thanks in advance

Know is Drop, Unknown is Ocean

Re: Help needed for an user defined Function

WoutL9-Jul-07 22:53

WoutL

9-Jul-07 22:53

Exelioindia wrote:
If there is no records for an country , it should return null.

Did you mean: it should return 0?

In that case you can use:
return IsNull(@row_count, 0)

Wout Louwers

Re: Help needed for an user defined Function

Exelioindia9-Jul-07 23:02

Exelioindia

9-Jul-07 23:02

Hi
thanks a lot. I need this only. Thank you very much

Know is Drop, Unknown is Ocean

Re: Help needed for an user defined Function

gauthee9-Jul-07 23:26

gauthee

9-Jul-07 23:26

you can use the count function too!

select @row_count = count([country_code]) from.....

Gautham

Right Outer Join Probs...

Member 38798819-Jul-07 20:19

Member 3879881

9-Jul-07 20:19

SELECT DISTINCT dbo.magdates.mydate, EM.empId AS EId, EM.empName AS EName,
TC.carddate, TC.shift, PM.location, TC.reportingtime AS FromTime,
TC.leavingtime AS ToTime, MHD.AppWHours AS Reg, MHD.AppOtHours AS Ot,
MHD.AppSHours AS SReg, MHD.AppSOtHours AS SOt, SD.Whrs
FROM dbo.tblTimeCardmain TC
INNER JOIN dbo.tblEmployeeMaster EM ON EM.empId = TC.empid
INNER JOIN dbo.tblsalarydetails SD ON SD.EmpID = TC.empid
INNER JOIN dbo.tblProjectMaster PM ON PM.projectID = TC.projectid
INNER JOIN dbo.tblMonthHourDetails MHD ON MHD.EmpId = TC.empid
RIGHT OUTER JOIN dbo.magdates ON TC.carddate = dbo.magdates.mydateWHERE (EM.empId = 213) AND (DATEPART(mm, TC.carddate) = 6) AND
(DATEPART(yyyy, TC.carddate) = 2007) ORDER BY TC.carddate

Hi this is my query...,
See in that magdates table contain dates between 6/1/2007 to 6/30/2007...,
But the tbltimecardmain contain dates between 6/1/2007 to 6/28/2007 only...,
So i used right outer join and selected all the dates from magdates table...,
But its giving only what are the dates contain tbltimecardmain those dates only...,
But i want show the dates 6/29/2007 and 6/30/2007 also...,
How to display magdates all the dates...,
hlp me plz,
Advance Thanks,

Regards,
Magi

Passing an array to stored procedures

M. J. Jaya Chitra9-Jul-07 18:40

M. J. Jaya Chitra

9-Jul-07 18:40

Hello,

Can you please tell me how to pass an array or list to the SQL StoredProcedure.

Best Regards,
M. J. Jaya Chitra

Re: Passing an array to stored procedures

Sathesh Sakthivel9-Jul-07 19:16

Sathesh Sakthivel

9-Jul-07 19:16

Get it[^]

Regards,

Satips. Rose | [Rose]

Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus

Query Insert Date?

Vodstok9-Jul-07 16:14

Vodstok

9-Jul-07 16:14

Okay, I don't think this is humanly possible, but I have been asked to run a query on a table based on the insert time of the records. The problem? There is no "Date Created" Column. Is there a way to retrieve the creation time of a row if it is not specifically saved?

______________________
Mr Griffin, eleventy billion is not a number... WTF | :WTF:

Re: Query Insert Date?

Frank Kerrigan16-Jul-07 2:32

Frank Kerrigan

16-Jul-07 2:32

Does it have an ID column and do some gues work.

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Query probs.............,

Member 38798819-Jul-07 3:36

Member 3879881

9-Jul-07 3:36

Hi the follwing procedure i am getting a month all dates...,
In that i am using my own query for the select statement place...,
I used left outer join to show all the dates of @tbl_date...,
But it's displaying only What are the dates i am having in tblMonthhourdetails table those dates only...,
But i want to display all the dates of that month and if the @tbl_date table dates and tbltimecardmain and tblmonthhourdetails dates are matchable those records also want to display...,
Else if there's is no matachable date in that timecardmain table and month hour details table those date place it want to show null...,
I pasted my query on here,
I tried with all joins but i didnt get answer...,
Plz hlp me how to do that...,

Stored procedure:

DECLARE @startDate datetime
declare @nextdate datetime
DECLARE @endDate datetime
declare @tbl_date table (mydate datetime)
set nocount on
SET @startDate = CAST( CAST( 2007 AS varchar(4) ) + '0101' AS datetime )
SET @startDate = DATEADD( m, 6 - 1, @startDate )
set @enddate=dateadd(m,1,@startdate)
while @startdate < @enddate
begin
--SET @nextdate = DATEADD( d, 0, @startdate )
insert into @tbl_date values (@startdate)
set @startdate=dateadd(d,1,@startdate)
end

SELECT EM.empId as EId, EM.empName as EName,ttd.mydate,
TC.shift,PM.location,TC.reportingtime as FromTime, TC.leavingtime as ToTime,
MHD.AppWHours as Reg,MHD.AppOtHours as Ot,MHD.AppSHours as SReg,
MHD.AppSOtHours as SOt,SD.Whrs
FROM dbo.tblEmployeeMaster EM INNER JOIN dbo.tblTimeCardmain TC ON EM.empId = TC.empid
left outer join @tbl_date ttd on ttd.mydate=TC.carddate
left outer JOIN dbo.tblMonthHourDetails MHD ON (ttd.mydate = MHD.SDate
and TC.empid = MHD.EmpId)
--order by EM.empid,ttd.mydate
INNER JOIN dbo.tblProjectMaster PM ON TC.projectid = PM.projectID
INNER JOIN dbo.tblsalarydetails SD ON MHD.EmpId = SD.EmpID
where EM.empid=213 and datepart(mm,Ttd.mydate)=6 and
datepart(yyyy,ttd.mydate)=2007
order by ttd.mydate

Regards,
Magi

Re: Outer join problem

andyharman9-Jul-07 6:49

andyharman

9-Jul-07 6:49

Hi Magi

I think what you want is:

FROM @tbl_date ttd
LEFT OUTER JOIN dbo.tblTimeCardmain TC
  ON TC.carddate = ttd.mydate
LEFT OUTER JOIN dbo.tblEmployeeMaster EM
  ON EM.empId = TC.empid
LEFT OUTER JOIN dbo.tblsalarydetails SD
  ON SD.EmpID = TC.empid
LEFT OUTER JOIN dbo.tblProjectMaster PM
  ON PM.projectID = TC.projectid
LEFT OUTER JOIN dbo.tblMonthHourDetails MHD
  ON MHD.EmpId = TC.empid
  AND MHD.SDate = TC.carddate

Your @tbl_date table is the lead-table in the query. The rest of the tables should be outer-joined as they contain the optional data.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.