Working With XML Fields

Aug 3, 2012

This is my first time working with XML fields. I am trying a simple:

Select XMLField.Value('(./field)[1]','varchar(100)') as Data

But I get nothing back. I do not know how to handle the 'i="1"' stuff.

This is what the XML looks like:

<fd u="5e455ef114194473" b="1" v="31">
<field i="1">2012-07-12</field>
<field i="147">
<v>Final Inspection</v>
</field>
<field i="3">

[Code] .....

View 3 Replies


ADVERTISEMENT

Working With Price Fields

Jan 22, 2008

Hi, i was wondering what is the correct way to handle price fields in the database and then in c#.  I read somewhere to use a decimal over a money field in the database but then in c# should i cast the data returned to a double (not sure if this would work) or to a decimal.  My problem is that i thought when working in c# you should treat numbers with a decimal point as either a float or double?  Why is there a decimal type in c#?  Appreciate if someone could clear this up.
Thanks

View 3 Replies View Related

Transact SQL :: Calculated Fields Stop Working When There Are 10 Or More Rows In A Table

Nov 4, 2015

Setup:
Windows Server 2003 R2 - Enterprise - SP2 - 32 Bit
SQL Server 2014 Express - 32 Bit

Problem: I have a calculated field on a PO table that adds up item prices on an Item table to get the total PO value. This works as expected until there are at least 10 rows in the PO table. From the 10 row on the calculated field stops working and only shows 0.

I have experienced this before and it seems like calculated fields break on the 10th row of a table and onward.

My PO table
CREATE TABLE [dbo].[PO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Quote_Number] [varchar](max) NULL,
[Customer] [varchar](max) NULL,
[CustomerPO] [varchar](max) NULL,
[PO_Received_Date] [datetime] NULL,
[Total_PO_Value] [decimal](18, 2) NULL,

[Code] ....

View 10 Replies View Related

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

View 3 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How 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.

View 8 Replies View Related

Public Overridable ReadOnly Default Property Fields() As ADODB.Fields

Jan 26, 2008

sir

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

View 1 Replies View Related

Update Fields With Searched First Date Record Fields

Jul 23, 2005

Hello !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 Related

Transact SQL :: Get One Row From Multiple Based On Fields And Also Get Sum Of Decimal Fields?

Jul 2, 2015

I 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

View 3 Replies View Related

Search Multipe Fields, Compounding Fields, Like, Contains...?

Jul 20, 2005

I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M

View 1 Replies View Related

Some Things Not Working In 2005 And Working In 2000

Mar 3, 2006

hi

I had a view in which I did something like this
isnull(fld,val) as 'alias'

when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.

Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???

any clues or answers ?? it is some configuration issue ?

Thanks in advance.

View 5 Replies View Related

Update Fields With Data From Other Fields In Same Row

Jun 30, 2000

Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.

In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)

I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.

View 2 Replies View Related

Using LIKE To Find Any Of Fields In One Table In Fields Of Another

Jul 31, 2013

I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).

Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.

View 5 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try


When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

Drillthrough In Calculated Fields Enable When Drillthrough Option Is Disable In Original Fields, Is This A BUG?

Jan 21, 2008

Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.


Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.

But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link

Is this the standard and expected behavior? or its simply a BUG?

Have I done something wrong in my model? and in this case, How I can correct this?

regards.
Julio Diaz.

View 1 Replies View Related

Sql Fields.

Aug 8, 2007

What is the command to see how many lines were returned with a Select statement?

View 1 Replies View Related

No. Of Max Fields

Oct 11, 1999

what is the max number of fields allowed in a table in sql server 7.0?

thanks

View 1 Replies View Related

Fields

Oct 22, 2005

Hi!
I used MS Access database! I need to know what are the replications of MS Access fields in MS SQL Server?!
1) What is TEXT field of MS Access in MS SQL Server?!
2) What is MEMO field of MS Access in MS SQL Server?!
3) How to set AutoNumber in MS SQL Server?!
I get confused because my online web based sql admin of my host had many options like varchar...
3) Please tell me the size of the fields too as i will be storing a large amount of data!
4) In MS Access there was field size like...60...! But in MS SQL Server there was 16 written next to the TEXT field! What is it? some Bytes?!
Thanks!

View 6 Replies View Related

Trying To Sum Fields

Feb 15, 2008

Hi,

I'm very rusty on SQL, if someone could help I'd appreciate it! It's probably very easy to solve!

Ok, I have a table called results which contains results of a survey where users rated features on a scale of 1 to 7. For example I have:

results(id, feature1, feature2, feature3, feature4, feature5)

sample data

1, 5, 4, 3, 2, 6

So, I want to create a view which will summarise total points given i.e. sum of feature1 -feature5. How do I do this? Do I need a function or is there a built-in function? All I can find is info on aggregate functions... but I want it per row...

Thanks,

Lilalfyalien

View 1 Replies View Related

Key Fields

Oct 14, 2005

Edit: the question is more clear in http://www.dbforums.com/showthread.php?p=4477661&posted=1#post4477661

OK this question is not really SQL server related but since we will build the database in MS SQL Server (2000) i think this is the proper forum to ask this question.

It's actually a quite simple question i gues, but my experience with databases is very limited (buiding my first serious database actually).

when i have two tables Organization and Samples coming from this organization with the following fields:



Organization:

InstituteID (identification)
Name
Adress
...

Sample:

SampleID (sample identification)
InstituteID (references to the Institute table)
SampleDate
Location
...



In general is it better to use the sampleID and InstituteID combined as primary key in the second (sample) table (and build an index on the combined key) or to generate a new unique key for each record? And why should i choose either one of these options?

One advantage of the first option is you dont have to generate keys yourself (or use some autonumbering system), however the disadvantage is the key grows bigger and bigger when adding more tables (fraction taken from samples, analysis performed on fractions, Analysis giving results).

View 14 Replies View Related

SUM Fields In Same Row?

Sep 19, 2007

Hi,

The only SUM samples I've seen just do SUM over a column. But I need to add up fields within a row.

ie: select SUM(a, b, c) from x

How would this be done?

Thanks!

--PhB

View 4 Replies View Related

Sum Fields

Jul 23, 2005

Hi AllI need some help.I have a table with 2 fields (Nights and year) that looks like this:Nights, Year1, 20042, 200444, 200545, 2005Now I want to make an sql query that returns a sum of each year like this:field 2004,2005Data 3, 89How can I make this work?SincerelyDiedrik

View 1 Replies View Related

What Fields Are Used?

Jul 20, 2005

Hi,Is there a way in SQL Server, or Access to determine what fields arebeing used in reports or joins, etc?Scenario:Field A in Table 1 is going to be either deleted or change data types,and I'd like to know what impact that will have.Yes...No?Thanks,Bodi

View 2 Replies View Related

Where Did The Fields Go?

Dec 28, 2006

Using Visual Studio 2003 and an ODBC connection to an Oracle 7 database I created a dataset with the following query:

SELECT ITEM, REV,QTY FROM MYDB.CONTRACT
WHERE CON_NUM = 'CONTRACT_NUMBER'

All is well until I change the contract_number to a parameter using either ? or a named parameter. Report users will choose the parameter value from a drop down list generated by another query. Although the parameterized query will still run via a prompt dialog, the report field list disappears and many errors occur.

Does anyone know why this happens to the fields and how to retrieve them?

Thanks,

PH

BTW: ODBC connections seem to be the only type that will accept parameter queries to this version of Oracle.

View 1 Replies View Related

Identity Fields - Why Use Them

May 29, 2007

I am building a database for a new project. I am not a DBA or a database designer so please bear with me on this question.
My boss believes that the only time to use an identity column is when we cannot determine a unique primary key.  On tables where we can determine a unique primary key identify fields are a waste of reasources. For instance, one of the tables that I need to create is a customer table. Since all of our customers have unique customer numbers my boss believes that in this case an identity column is useless.   I don't have enough experience to determine if he is correct or not.  
Can someone please explain the pro's and con's of using identity fields to me. 
Thanks

View 6 Replies View Related

Concatenating Fields

Aug 9, 2007

Hi,there's a method to concatenate fields in a WHERE clause?I've a parameter which represents a name and surname of a person; in the table I've two fields representing one the name and the other the surname. I'd like to do a "LIKE" comparison concatenating Name and Surname field and confronting with my parameter... Is it possible?

View 3 Replies View Related

Concatenating Fields

Aug 9, 2007

Hi,I've a table with two fields representing one Name and the other teh Surname of a persona. I've a to create a Stored Procedure with one input parameter that is a string containing Name and Surname (I don't know in waht order...)What I'd like to do is to concatenate teh fields Name and Surname and confronting with "LIKE" in the "WHERE" clause... something like this:Select Name, Surname FROM XXX where (Name + ' ' + Surname LIKE @parameter OR Surname + ' '+ Name LIKE @parameter).The problem is that I don't know neither if it is possible neither the correct syntax... 

View 1 Replies View Related

Concatenating Fields In SQL

Apr 11, 2008

I am using SQL server 2000 and trying to do a concatenation of firstname,middle-initial and lastname.
SELECT first_name+('mid_ini'= casewhen middle_init is null then ''when middle_init ='' then ''else middle_initend)+nsl_last_nameFROM tbl_names
How is it possible to use the CASE condition along with concatenating the fields. Is this possible?
I would like my result to be "Bob J Sam".
and if there is no middle initial it should be blank or when its null it should be blank.
I know you can separate and get the results with the CASE statement,but is this feasible when you want to concatenate?
Thank you

View 9 Replies View Related

Selecting Fields On The Fly

Sep 20, 2005

Hi

I have a content management system that I am building. My CMS has a
pages table which contains 60 fields, some are varchars, some are ntext
some are dates etc. They have field names which match their purpose
like varchar1, ntext3 etc. These fields are used up depending on which
template is applied to the page.

The templatefields table holds holds the information about each field
in a template. It's control type (rich text control, date control etc)
and the field in the pages table that the data will be stored in
(varchar1,ntext3 etc)

What I want to be able to do is loop through the templatefeilds table
for a particular template and then using the field name for the pages
table, go to the pages table and get the value to populate the control.

So to clarify, I would like to produce a stored procedure which outputs
the page field name (varchar1) from the templatefields table and
varchar1's value in the pages table. Maybe in a temporary table.

Is this possible?

Any guidance would be appreciated..

Thanks in advance

View 1 Replies View Related

SP To Add Fields To Table

Feb 14, 2006

I want to let a user a fields and select the data type and size from a short list. For example: nvarchar(?), Money, Int, or Bit. These are most likely the only choices for now. There will be a web page that lets them enter a field name and select a data type.
Can someone suggest how the sproc might look? Here is what I have so far.
CREATE PROCEDURE [dbo].AddCustomField ASALTER TABLE MyDataSQL.dbo.tblCustomFields ADD @FieldName @FieldDataType NULLGO
Thank you,

View 1 Replies View Related

Truncated Fields

Apr 11, 2001

Hi!

In the results of our queries, the datas in the fields are truncated to 255 chars.

Is there a parameter to change to get the entire field? Their format is varchar 8000 and the fields are longer than 255 characters in the db.

Thanks for your help.

View 2 Replies View Related

Merging Fields

Sep 4, 2001

Help!!, Does anyone know of a way that I can merge three seperate fields into one field in my sql statement.
EG.
SELECT catnumber, catnumber2,catnumber3
FROM Categories
WHERE Customer = 'xxxx' and itemnumber = 1 or 2 or 4

I want the catnumber, catnumber2, catnumber3 all in one field so I can sort by it.
If anybody could help me it would be greatly appreciated.
Thanks
Mike

View 1 Replies View Related

Use Only Certain Fields Of The Output Of A SP

Feb 22, 2000

Can I use only one output field of the result of a stored procedure and use this in a say IN(...) clause?

This is the output of a 'sp_helpuser avor' command:

Group_name Group_id Users_in_group Userid
------------------------- -------- ------------------------- ------
Avor 16400 dbggElephant 6
Avor 16400 Elephant 7

Can I use this like: 'SELECT * FROM TABLE WHERE FIELD IN (SELECT Users_in_group FROM sp_helpuser avor'

Thomas

View 1 Replies View Related

Indexed Fields

Jun 22, 2000

I have a table with three fields that are marked as indexed fields with unique keys. I want
to remove one of the fields. When I do this and hit the rebuild button -- I get an error, that
there is a duplicate field, so it will not just let me remove the one field and leave the other
two?? How can I get around this?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved