Referencing Certain Excel Fields
Jul 24, 2002Anyone know a way to pull specific fields from an excel spreadsheet.
I'm using SQL7 DTS to pull data into a SQL table. I can pull the entire
sheet but I just want certain fields.
thanks, archie
ADVERTISEMENT
Referencing Fields Returned By SqlDataReader
Apr 3, 2008In C#, when I'm using an SqlDataReader to run a simple select query and use the values returned I have a question about referencing the field values.
There seem to be a large number of "Get" type methods (i.e reader.GetValue(int i), etc.) All of them (that I have found so far, require the use of the index of the field (GetValue(0) is the first field and so on).
Is there a way to use the actual name of the field? (i.e. GetValue("myField"))? This helps code readability but the big reason is that changes to the underlying field list can completely hose up the application.
I know there are such functions in VB, but I just can't seem to find the equivelants in C#.
Mind Boggling / How Can You Query Self Referencing Tables? (self Referencing Foreign Keys)
Jun 15, 2006For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID . . . . . . . . . . int
Name . . . . . . . . . . . nvarchar(50)
ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?
Certain Numeric Fields Not Read From The Excel File When Using A Excel File Source.
Jul 20, 2006I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
Thanks,
Manisha
Hide Fields + Export To Excel
Jan 30, 2008Hi,
I hide some fields in the Report based on the user's query selection.
However, when the user exports to excel all the report fields appear.
How do I show only the relevant fields in the excel export?
Thanks.
Blank Fields In Excel File
Dec 7, 2006I am trying to validate and import a Excel file into the database table using script component. The file contains some blank columns in the sheet. How can I handle the blank spaces while validating the file in the Script Component?The code is as follows:Dim excelcmd As OleDbCommand = New OleDbCommand("SELECT Item,TaxCode,ItemDescription FROM [Input$]WHERE LEN(Item)>=0 AND LEN(ItemDescription)>=0 AND LEN(TaxCode)>=0", excelConn) Dim excelreader As OleDbDataReader = excelcmd.ExecuteReader() Dim row As Integer = 0 While excelreader.Read() NameValsBuffer.AddRow() NameValsBuffer.ItemCode = CStr(IIf(excelreader.GetString(0).Length = 0, "#", excelreader.GetString(0))) NameValsBuffer.TaxCode = CStr(IIf(excelreader.GetString(1).Length = 0, "#", excelreader.GetString(1))) NameValsBuffer.ItemDescription = CStr(IIf(excelreader.GetString(2).Length = 0, "#", excelreader.GetString(2))) NameValsBuffer.CompanyId = Me.Variables.CompanyId NameValsBuffer.UserId = Me.Variables.UserId End While
View 5 Replies View RelatedExport Hidden Fields To Excel
May 14, 2008
I have a report that has 10 columns. While viewing, users should see only 8 columns and 2 will be hidden.
But is there any way we can export those 2 hidden columns to excel?
Any help is greatly appreciated.
Thanks,
Excel Destination Errors For Fields &&> 255 Chars
Apr 13, 2007Hi:
Am trying to write sql2005 data to Excel. I have problems when data length exceeds 255 chars. I used a sample destination file with cells containing >255 chars where required, so that the Excel Destination external column was recognized as DT_Text.
My oledb source columns(external and output) are both varchar (1000).
Works fine , but fails when values>255 chars.
Error 0xC0202009 on ProcessInput.
I use SQL2005 enterprise SP2.
I tried changing the Oledb source output to Textstream, but that does not work at all.
How do I export values >255 chars to excel?
TIA
Kar
Importing Data From Excel To Update Existing Fields
May 12, 2004I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.
Analysis :: Hidden Cube Measure Groups Are Shown In Excel Pivot (Show Fields Related To) Drop Down
Jun 18, 2015We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined
Feb 20, 2008How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
Public Overridable ReadOnly Default Property Fields() As ADODB.Fields
Jan 26, 2008sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
Update Fields With Searched First Date Record Fields
Jul 23, 2005Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedTransact SQL :: Get One Row From Multiple Based On Fields And Also Get Sum Of Decimal Fields?
Jul 2, 2015I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
Referencing SQLDataSource In A Sub
Nov 16, 2006I have a SqlDataSource - The select statement
selects the DB row with an ID that equals a querystring value.... So I
know I am only selecting one row..Now I want to create a little
Sub that grabs any field(s) of my choice and then I can assign the
value of it to a textbox on my page or a variable if I need to... Any
idea how I actually reference the fields via the SQLDataSource in a
sub? I see you can reference parameters using..SqlDataSource1.SelectParameters()I was hoping I could use something similar to get the actual DB fields like the below...SqlDataSource1.Select("MyDataBaseField").Value = TextBox.TextCan anyone help please??Thanks
Referencing Sql In IF THEN ELSE Statement
Mar 13, 2007The Background:
From page 1 a search is created and this value is converted to a querystring and given the name "id=" and passed to page 2. Page 2 then does a lookup in the sql 2000 db useing this querystring.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ListingDBConnectionString %>"
SelectCommand="SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE ([ID] = @ID)">
<SelectParameters>
<asp:QueryStringParameter Name="ID" QueryStringField="id" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
The Problem:
What I then need to do is an IF THEN ELSE statement.
IF [Package in SQL1] = 4 Then
Package 4
IF [Package in SQL1] = 3 Then
Package 3
Only how do I reference the sql value in the if statement.
I have tried
<%IF Eval("Package") = 4 Then %> and with Bind - No good
Image Referencing
May 4, 2008Hi all, How do I return pictures along with my search results? For example, I have a shoe database and for the results I would like one column to hold a picture of the shoe (the other columns hold Brand, Name, Price, etc) The way I am looking at implementing this is by referencing the image and retrieving it from the server. I'm a bit lost on how to do this though. Any help? Thanks in advance,Nick
View 4 Replies View RelatedSelf-referencing Table
Sep 1, 2007Hi,
I'm using MS SQL 2005 Express.
CREATE TABLE Folder (
iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
folderName varchar(50) NOT NULL,
parentFolderID int NULL
FOREIGN KEY REFERENCES Folder (iD)
)
GO
if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted.
The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'
Anyone got any advice?
Referencing To A Function..
Jul 20, 2005I was created a function named aa.It is possible to reference to it without using the dbo. prnounce ?eg: select aa(), not select dbo.aa()thanks,Viktor
View 1 Replies View RelatedReferencing Subreport Value
Nov 16, 2007Hello,
I need to sum up a sub-report value, but I don't know the syntax to reference the subreport.
How do I reference a subreports value to sum it up?
Thanks,
Trigger Referencing
Nov 28, 2007
I'm setting up a trigger that will fire off an email explaining that this entry has been updated, however I need a reference to the updated row so I only send that bit of information off.
Heres my code thus far:
ALTER TRIGGER [dbo].[completeCreation2]
ON [Database_Test].[dbo].[Projects]
AFTER UPDATE
AS
-- SEND EMAIL
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administration',
@recipients = 'email@email.com',
@body = 'A new project has been created view details of the project below:',
@subject = 'Project Created',
@body_format = 'TEXT',
@importance = 'High',
@query = 'Select * FROM updated';
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
END
I thought the select From updated would work but it doesn't I need a reference for that line, select * FROM _____.
Thanks
Referencing Issues
Apr 17, 2008
I am trying to run a mobile device with SQL's mobile 3.5.
When I run my application I am given an error that sqlceme35.dll plus several other references cannot be found
When I try referencing them It does not allow me too.
Is there any steps I should take to make referencing allowed?
Referencing Result Set
Nov 20, 2007Hi
This is probably a noob question. In reference to my previous post about OLE DB and variables, I need to copy the whole result set to a table. i tried
Code Block
exec('insert into '+@tableName+' ('+@ColumnNames+') '+@curTableResult)
where @tableName is the table name, @ColumnNames are the column names and @curTableResult is the Result set produced by the Execute SQL statement before. Obviously didnt work.
How do I copy the Result set to a table?
Thanks
CoyoteM
Referencing Variables By Name
Nov 8, 2007Hi there everyone,
A quick question. How do I reference variables (system and user) in SSIS by name instead of by just using a "?". The reason why I'm asking is because I'm trying to log what happens in a package to SQL, and if I cannot reference a variable by name it tries to insert variable values into incorrect fields?
An example of the code I'm trying to use can be found below:
Code Block
UPDATE dbo.History_Control_Table
SET
End_Date = GetDate()
, Result = 'Success'
, Status = NULL
, No_Of_Records_Processed = ? --This is where I want to tell it which variable to use
, Package_Name = ?
, No_Of_Records_Rejected = ?
--etc etc etc
WHERE
Package_Name = ?
AND Task_Name = 'Deals_Fact_Stg'
AND Status = 'Running'
Thanks in advance.
Chow.
Foreign Key NOT Referencing PK
Sep 18, 2006This book 'MS SQL Server 2000 Bible' says the following:
'The foreign key can reference primary keys, unique constraints, or unique indexes of any table'
I've never heard of this before, I thought that the FK always established referential integrity by referencing the PK in the parent table. Does anyone have a further explanation of this?
thx,
Kat
Referencing Textboxes
Feb 7, 2008Is there a way to reference textboxes in an SSRS table like one would for cells in Excel? For example, something like =textbox1/textbox2? I am trying to replace a spreadsheet by turning it into an automated report and need to do some horizontal formulas and when I put in the grouped total, it always defaults to averaging percetages from the rows above, which isnt a true summary in my case.
View 11 Replies View RelatedSelect Self Referencing Table
May 25, 2005I have a table that holds a ParentID and the RecordID. There is a column called IsEnabled which is a bit field indicating if a folder can be displayed or not. 0 = NO, 1 = YESThe table is for a directory structure which is virtual and displays folders on a web page.Root----- 1---------- 1-1---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4---------- 1- 3---------- 1- 4I need a query that will not select any children that are under a Parent that is disabled. So if ' 1- 2 ' is disabled then:---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4SHOULD NOT SHOW.Can anyone give me a query that will overcome this problem i have.-J
View 4 Replies View RelatedReferencing A Table By Variable
Jul 18, 2000I would like a stored proc to be fed the table name as a paramater.
I tried below
create procedure testit as
set nocount on
declare @tblnm varchar(50)
select * from @tblnm
but it get Server: Msg 170, Level 15, State 1, Procedure testit, Line 5
Line 5: Incorrect syntax near '@tblnm'.
Any idea
Referencing Data From A Query
Sep 30, 2006I'm quite new to using t-sql, so hopefully the answer to this should be fairly simple... I have the following basic stored procedure:
Quote: CREATE PROCEDURE dbo.SP_Check_Login (
@arg_UserEmail VARCHAR(255),
@arg_UserPassword VARCHAR(255))
AS
BEGIN
SELECT a.userArchived,a.userPasswordDate
FROM app_users a
WHERE a.userEmail = @arg_UserEmail
AND a.userPassword = @arg_UserPassword
END;
GO
What I want to do is some conditional statements on the query results.
i.e:
IF (userArchived = 1)
RETURN "Archived"
ELSE IF (userPasswordDate < dateadd(month,-3,getdate())
RETURN "UpdatePassword"
ELSE
RETURN "OK"
So firstly how to I reference the data returned by the query, and secondly am I on the right track with the conditional code?
Thanks, Mike
Referencing Other Databases In SQL Statements
Oct 17, 2005Hello,
I have an access database and an SQL database and using data transformation services, i want to update the access database using the SQL data.
Can anyone tell me the syntax for referencing the access database?
Is it something like: [TABLENAME].dbo.FIELDNAME ?
Just to clarify, i have
Microsoft Access Database
Table 1 (UnitHistory)
SQL Database
Table 1 (UnitHistory)
How do i reference these seperately? I want to update the microsoft access database based on the SQL database data.
Eventually i'm trying to update an access database using the data held on my SQL server. Is DTS the best way for me to acomplish this or should i use another method?
Thanks guys
Referencing DataSet From Other View
May 8, 2008Hi there
Let say i have single dataset with different view (tables/list/matrix etc) with filter on it. Is it possible referencing from one view (table/list/matrix) to another? Basically how to access the other field for instance SUM from different view and used for the other view instead.
Thank you
UniqueIdentifier Self Referencing Foreign Key
May 16, 2008I have a UniqueIdentifier as a self referencing foreign key. The pk gets set by default using the newid(). I need the foreign key to default to that same value. how can i do that? @@identity doesnt work, $rowguid doesnt work, column name doesnt work. Any ideas?
View 9 Replies View Related