Sorting Nvarchar Fields

Jun 6, 2007

Hi all,

I am having an issue which I'm guessing everyone has at one point. I just can't seem to find the answer. I might be misunderstanding something as I'm relatively new to SQL and data types etc.

Here is my issue.

I have a table with a column that has a nvarchar data type. The following is an example of field values in that column.

asystem1
zsystem19
zsystem9
bsystem23
bsystem3

I would like to sort that data alphabetically and then by number like so.

asystem1
bsystem3
bsystem23
zsystem9
zsystem19

A basic ORDER BY obviously will sort it alphabetically but will put 23 before 3. These system names are already named and can't be changed otherwise I would use "0" in front of single digit numbers.

I just can't figure out how to accomplish this. Is there a convert or cast (unfamiliar with those functions) that I can do or does it involve something more sophisticated?

I appreciate any help in advance.

Israel

View 6 Replies

SQL Server 2012 :: Sorting By Alphanumeric Table Column Of NVARCHAR Datatype

Sep 24, 2015

I am trying to sort my sql resultset by an alphanumeric column of a table which is of NVARCHAR datatype. The sample data is given below:

CREATE TABLE #Activities(activityName NVARCHAR(100))

INSERT INTO #Activities VALUES('Field phase S14-04932-01')
INSERT INTO #Activities VALUES('Phase reporting')
INSERT INTO #Activities VALUES('Phase running')
INSERT INTO #Activities VALUES('RD1')

[Code] ....

The output of the query is like this:

A1
A2
A3
A4
E1 0DAA1
E10
E2 0DAA2

[Code] .....

The output what I require is this:

A1
A2
A3
A4
E1 0DAA1
E2 0DAA2

[Code] ....

View 9 Replies View Related

Sorting Of Date Fields

Dec 27, 2004

hi,

When the autogenerate property of datagrid is set to true, how to perform sorting on date fields.

Example:

SELECT DC_NO AS [Document No],CONVERT(CHAR(10),DC_DT,103) AS [Document Date],
[Name] FROM XYZ

Here we convert the date field to the format "dd/mm/yyyy" so when sorting is done it is done in the format "dd/mm/yyyy". But instead sorting should be done in "yyyy/mm/dd" format.

View 6 Replies View Related

Sorting Mixed Type Fields...

Jul 20, 2005

I have a field in my database which stores product codes. This fieldis a varchar. Some users use characters in their codes but most justuse numbers. Whenever I go to sort the products by code the order ofthe numbers go something like: 1, 100, 101, 2, 210, 220, 2500, 3, ...I understand why this is sorted this way but is there anyway I canhave it sort the numbers in their proper numeric order (1,2,3,100,....) and just have them always appear before any codes that containcharacters? Right now I just have users put zeros in front of theirnumbers like: 0001, 0100, 0101, 0002, ...

View 4 Replies View Related

Grouping Sorting String And Numerical Fields

Feb 6, 2007

I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.

Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9

However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2

What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2

Is this something that can be done with grouping and sorting?

Thanks,
Chad

View 1 Replies View Related

JDBC - Varchar Or Nvarchar Fields?

May 10, 2006

Hi, I'm starting a new application in java using JTDS jdbc driver(http://jtds.sourceforge.net) and SQLServer 2005 Express.I have to design the database from scratch and my doubt is if I have to usevarchar or nvarchar fields to store string data.Any experience about performance issues using nvarchar instead of varchar(considering that Java internally works in unicode too)?Thanks in advance,Davide.

View 4 Replies View Related

Cannot Compare NVarChar(Max) Fields Over 4000 Chars

Oct 24, 2006

I had a post a week or so ago with this issue, I found the cause, but cannot figure out how to fix it... The problem is the comparison on the NVarChar(Max) fields when one of them exceeds 4000 chars. If I comment out the following lines of the stored proc, everything works. I tested without using COALESCE and it still does not work. COALESCE(Comments, '') = COALESCE(@o_Comments, '') ANDCOALESCE(SpecialNotes, '') = COALESCE(@o_SpecialNotes, '') ANDCOALESCE(IAppComments, '') = COALESCE(@o_IAppComments, '') ANDCOALESCE(MgmtNotes, '') = COALESCE(@o_MgmtNotes, '') AND set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[pe_updateAppraisal]
-- Add the parameters for the stored procedure here
@OrderId INT,
@FileNumber NVarChar(25),
@OrderDate DateTime,
@ClientID NVarChar(25),
@ClientFileNumber NVarChar(25),
@PropertyTypeID INT,
@EstimatedValue money,
@PurchaseValue money,
@LoanOfficer NVarChar(50),
@ReportID INT,
@ReportFee money,
@FeeBillInd bit,
@FeeCollectInd bit,
@CollectAmt money,
@Borrower NVarChar(50),
@StreetAddrA NVarChar(50),
@StreetAddrB NVarChar(50),
@City NVarChar(50),
@CountyID INT,
@StateID INT,
@Zip NVarChar(50),
@ContactName NVarChar(50),
@PhoneA NVarChar(50),
@PhoneB NVarChar(50),
@PhoneC NVarChar(50),
@ApptDate DateTime,
@ApptTime NVarChar(25),
@AppraiserID INT,
@InspectionDate DateTime,
@DateMailed DateTime,
@TrackingInfo NVarChar(50),
@ReviewedBy INT,
@PreNotesID INT,
@PostNotesID INT,
@StatusID INT,
@Comments NVarChar(MAX),
@SpecialNotes NVarChar(MAX),
@MgmtName NVarChar(50),
@MgmtContactName NVarChar(50),
@MgmtAddress NVarChar(50),
@MgmtPhone NVarChar(50),
@MgmtFax NVarChar(50),
@MgmtFee money,
@CheckNumber NVarChar(25),
@MgmtNotes NVarChar(MAX),
@SentAppraiser DateTime,
@InfoReceived DateTime,
@CheckReceived DateTime,
@CheckMailed DateTime,
@INumFamilies NVarChar(10),
@IStyle NVarChar(15),
@IUnit NVarChar(15),
@IConstruction NVarChar(15),
@IBasement NVarChar(10),
@IBFinished NVarChar(10),
@IGarage NVarChar(10),
@INumCars NVarChar(2),
@IGarageType NVarChar(10),
@IContactHas NVarChar(10),
@IAvailable NVarChar(10),
@IInformedAmt NVarChar(5),
@IRealtorContract NVarChar(10),
@IContractContact NVarChar(50),
@IPermitCO NVarChar(10),
@ICORenewal NVarChar(10),
@IRenewalInt NVarChar(15),
@IAppComments NVarChar(MAX),
@IKitchen1 NVarChar(5),
@IKitchen2 NVarChar(5),
@IKitchen3 NVarChar(5),
@IKitchen4 NVarChar(5),
@IKitchenB NVarChar(5),
@IBedroom1 NVarChar(5),
@IBedroom2 NVarChar(5),
@IBedroom3 NVarChar(5),
@IBedroom4 NVarChar(5),
@IBedroomB NVarChar(5),
@IBathroom1 NVarChar(5),
@IBathroom2 NVarChar(5),
@IBathroom3 NVarChar(5),
@IBathroom4 NVarChar(5),
@IBathroomB NVarChar(5),
@AppraiserPerc NVarChar(6),
@AppraiserFee money,

@o_OrderId INT,
@o_FileNumber NVarChar(25),
@o_OrderDate DateTime,
@o_ClientID NVarChar(25),
@o_ClientFileNumber NVarChar(25),
@o_PropertyTypeID INT,
@o_EstimatedValue money,
@o_PurchaseValue money,
@o_LoanOfficer NVarChar(50),
@o_ReportID INT,
@o_ReportFee money,
@o_FeeBillInd bit,
@o_FeeCollectInd bit,
@o_CollectAmt money,
@o_Borrower NVarChar(50),
@o_StreetAddrA NVarChar(50),
@o_StreetAddrB NVarChar(50),
@o_City NVarChar(50),
@o_CountyID INT,
@o_StateID INT,
@o_Zip NVarChar(50),
@o_ContactName NVarChar(50),
@o_PhoneA NVarChar(50),
@o_PhoneB NVarChar(50),
@o_PhoneC NVarChar(50),
@o_ApptDate DateTime,
@o_ApptTime NVarChar(25),
@o_AppraiserID INT,
@o_InspectionDate DateTime,
@o_DateMailed DateTime,
@o_TrackingInfo NVarChar(50),
@o_ReviewedBy INT,
@o_PreNotesID INT,
@o_PostNotesID INT,
@o_StatusID INT,
@o_Comments NVarChar(MAX),
@o_SpecialNotes NVarChar(MAX),
@o_MgmtName NVarChar(50),
@o_MgmtContactName NVarChar(50),
@o_MgmtAddress NVarChar(50),
@o_MgmtPhone NVarChar(50),
@o_MgmtFax NVarChar(50),
@o_MgmtFee money,
@o_CheckNumber NVarChar(25),
@o_MgmtNotes NVarChar(MAX),
@o_SentAppraiser DateTime,
@o_InfoReceived DateTime,
@o_CheckReceived DateTime,
@o_CheckMailed DateTime,
@o_INumFamilies NVarChar(10),
@o_IStyle NVarChar(15),
@o_IUnit NVarChar(15),
@o_IConstruction NVarChar(15),
@o_IBasement NVarChar(10),
@o_IBFinished NVarChar(10),
@o_IGarage NVarChar(10),
@o_INumCars NVarChar(2),
@o_IGarageType NVarChar(10),
@o_IContactHas NVarChar(10),
@o_IAvailable NVarChar(10),
@o_IInformedAmt NVarChar(5),
@o_IRealtorContract NVarChar(10),
@o_IContractContact NVarChar(50),
@o_IPermitCO NVarChar(10),
@o_ICORenewal NVarChar(10),
@o_IRenewalInt NVarChar(15),
@o_IAppComments NVarChar(MAX),
@o_IKitchen1 NVarChar(5),
@o_IKitchen2 NVarChar(5),
@o_IKitchen3 NVarChar(5),
@o_IKitchen4 NVarChar(5),
@o_IKitchenB NVarChar(5),
@o_IBedroom1 NVarChar(5),
@o_IBedroom2 NVarChar(5),
@o_IBedroom3 NVarChar(5),
@o_IBedroom4 NVarChar(5),
@o_IBedroomB NVarChar(5),
@o_IBathroom1 NVarChar(5),
@o_IBathroom2 NVarChar(5),
@o_IBathroom3 NVarChar(5),
@o_IBathroom4 NVarChar(5),
@o_IBathroomB NVarChar(5),
@o_AppraiserPerc NVarChar(6),
@o_AppraiserFee money
AS
BEGIN

UPDATE Orders
SET FileNumber = @FileNumber,
OrderDate = @OrderDate, ClientID = @ClientID,
ClientFileNumber = @ClientFileNumber, PropertyTypeID = @PropertyTypeID,
EstimatedValue = @EstimatedValue, PurchaseValue = @PurchaseValue,
LoanOfficer = @LoanOfficer, ReportFee = @ReportFee,
FeeBillInd = @FeeBillInd, FeeCollectInd = @FeeCollectInd,
CollectAmt = @CollectAmt, Borrower = @Borrower,
StreetAddrA = @StreetAddrA, StreetAddrB = @StreetAddrB,
City = @City, CountyID = @CountyID, StateID = @StateID, Zip = @Zip,
ContactName = @ContactName, PhoneA = @PhoneA, PhoneB = @PhoneB,
PhoneC = @PhoneC, ApptDate = @ApptDate, ReportID = @ReportID,
ApptTime = @ApptTime, AppraiserID = @AppraiserID,
InspectionDate = @InspectionDate, DateMailed = @DateMailed,
TrackingInfo = @TrackingInfo, ReviewedBy = @ReviewedBy,
StatusID = @StatusID, Comments = @Comments,
SpecialNotes = @SpecialNotes, CheckNumber = @CheckNumber,
MgmtName = @MgmtName, MgmtContactName = @MgmtContactName,
MgmtAddress = @MgmtAddress, MgmtPhone = @MgmtPhone,
MgmtFax = @MgmtFax, MgmtFee = @MgmtFee, MgmtNotes = @MgmtNotes,
CheckMailed = @CheckMailed, CheckReceived = @CheckReceived,
InfoReceived = @InfoReceived, SentAppraiser = @SentAppraiser,
PreNotesID = @PreNotesID, PostNotesID = @PostNotesID,
INumFamilies = @INumFamilies,
IStyle = @IStyle, IUnit = @IUnit, IConstruction = @IConstruction,
IBasement = @IBasement, IBFinished = @IBFinished,
IGarage = @IGarage, INumCars = @INumCars,
IGarageType = @IGarageType, IContactHas = @IContactHas,
IAvailable = @IAvailable, IInformedAmt = @IInformedAmt,
IRealtorContract = @IRealtorContract, IContractContact = @IContractContact,
IPermitCO = @IPermitCO, ICORenewal = @ICORenewal,
IRenewalInt = @IRenewalInt, IAppComments = @IAppComments,
IBedroomB = @IBedroomB, IBedroom1 = @IBedroom1, IBedroom2 = @IBedroom2,
IBedroom3 = @IBedroom3, IBedroom4 = @IBedroom4, IKitchenB = @IKitchenB,
IKitchen1 = @IKitchen1, IKitchen2 = @IKitchen2, IKitchen3 = @IKitchen3,
IKitchen4 = @IKitchen4, IBathroomB = @IBathroomB, IBathroom1 = @IBathroom1,
IBathroom2 = @IBathroom2, IBathroom3 = @IBathroom4, IBathroom4 = @IBathroom4,
AppraiserPerc = @AppraiserPerc, AppraiserFee = @AppraiserFee
WHERE OrderID = @o_OrderId AND
COALESCE(FileNumber, '') = COALESCE(@o_FileNumber, '') AND
COALESCE(OrderDate, 01/01/1900) = COALESCE(@o_OrderDate, 01/01/1900) AND
COALESCE(ClientID, 0) = COALESCE(@o_ClientID, 0) AND
COALESCE(ClientFileNumber, '') = COALESCE(@o_ClientFileNumber, '') AND
COALESCE(PropertyTypeID, 0) = COALESCE(@o_PropertyTypeID, 0) AND
COALESCE(EstimatedValue, 0) = COALESCE(@o_EstimatedValue, 0) AND
COALESCE(PurchaseValue, 0) = COALESCE(@o_PurchaseValue, 0) AND
COALESCE(LoanOfficer, '') = COALESCE(@o_LoanOfficer, '') AND
COALESCE(ReportID, 0) = COALESCE(@o_ReportID, 0) AND
COALESCE(ReportFee, 0) = COALESCE(@o_ReportFee, 0) AND
COALESCE(FeeBillInd, 0) = COALESCE(@o_FeeBillInd, 0) AND
COALESCE(FeeCollectInd, 0) = COALESCE(@o_FeeCollectInd, 0) AND
COALESCE(CollectAmt, 0) = COALESCE(@o_CollectAmt, 0) AND
COALESCE(Borrower, '') = COALESCE(@o_Borrower, '') AND
COALESCE(StreetAddrA, '') = COALESCE(@o_StreetAddrA, '') AND
COALESCE(StreetAddrB, '') = COALESCE(@o_StreetAddrB, '') AND
COALESCE(City, '') = COALESCE(@o_City, '') AND
COALESCE(CountyID, 0) = COALESCE(@o_CountyID, 0) AND
COALESCE(StateID, 0) = COALESCE(@o_StateID, 0) AND
COALESCE(Zip, '') = COALESCE(@o_Zip, '') AND
COALESCE(ContactName, '') = COALESCE(@o_ContactName, '') AND
COALESCE(PhoneA, '') = COALESCE(@o_PhoneA, '') AND
COALESCE(PhoneB, '') = COALESCE(@o_PhoneB, '') AND
COALESCE(PhoneC, '') = COALESCE(@o_PhoneC, '') AND
COALESCE(ApptDate, 01/01/1900) = COALESCE(@o_ApptDate, 01/01/1900) AND
COALESCE(ApptTime, '') = COALESCE(@o_ApptTime, '') AND
COALESCE(AppraiserID, 0) = COALESCE(@o_AppraiserID, 0) AND
COALESCE(InspectionDate, 01/01/1900) = COALESCE(@o_InspectionDate, 01/01/1900) AND
COALESCE(DateMailed, 01/01/1900) = COALESCE(@o_DateMailed, 01/01/1900) AND
COALESCE(TrackingInfo, '') = COALESCE(@o_TrackingInfo, '') AND
COALESCE(ReviewedBy, 0) = COALESCE(@o_ReviewedBy, 0) AND
COALESCE(PreNotesID , 0) = COALESCE(@o_PreNotesID, 0) AND
COALESCE(PostNotesID, 0) = COALESCE(@o_PostNotesID, 0) AND
COALESCE(StatusID, 0) = COALESCE(@o_StatusID, 0) AND
/*COALESCE(Comments, '') = COALESCE(@o_Comments, '') AND
COALESCE(SpecialNotes, '') = COALESCE(@o_SpecialNotes, '') AND*/
COALESCE(CheckNumber, '') = COALESCE(@o_CheckNumber, '') AND
COALESCE(MgmtName, '') = COALESCE(@o_MgmtName, '') AND
COALESCE(MgmtContactName, '') = COALESCE(@o_MgmtContactName, '') AND
COALESCE(MgmtAddress, '') = COALESCE(@o_MgmtAddress, '') AND
COALESCE(MgmtPhone, '') = COALESCE(@o_MgmtPhone, '') AND
COALESCE(MgmtFax, '') = COALESCE(@o_MgmtFax, '') AND
COALESCE(MgmtFee, '') = COALESCE(@o_MgmtFee, '') AND
/*COALESCE(MgmtNotes, '') = COALESCE(@o_MgmtNotes, '') AND*/
COALESCE(SentAppraiser, 01/01/1900) = COALESCE(@o_SentAppraiser, 01/01/1900) AND
COALESCE(InfoReceived, 01/01/1900) = COALESCE(@o_InfoReceived, 01/01/1900) AND
COALESCE(CheckReceived, 01/01/1900) = COALESCE(@o_CheckReceived, 01/01/1900) AND
COALESCE(CheckMailed, 01/01/1900) = COALESCE(@o_CheckMailed, 01/01/1900) AND
COALESCE(INumFamilies, '') = COALESCE(@o_INumFamilies, '') AND
COALESCE(IStyle, '') = COALESCE(@o_IStyle, '') AND
COALESCE(IUnit, '') = COALESCE(@o_IUnit, '') AND
COALESCE(IConstruction, '') = COALESCE(@o_IConstruction, '') AND
COALESCE(IBasement, '') = COALESCE(@o_IBasement, '') AND
COALESCE(IBFinished, '') = COALESCE(@o_IBFinished, '') AND
COALESCE(IGarage, '') = COALESCE(@o_IGarage, '') AND
COALESCE(INumCars, '') = COALESCE(@o_INumCars, '') AND
COALESCE(IGarageType, '') = COALESCE(@o_IGarageType, '') AND
COALESCE(IContactHas, '') = COALESCE(@o_IContactHas, '') AND
COALESCE(IAvailable, '') = COALESCE(@o_IAvailable, '') AND
COALESCE(IInformedAmt, '') = COALESCE(@o_IInformedAmt, '') AND
COALESCE(IRealtorContract, '') = COALESCE(@o_IRealtorContract, '') AND
COALESCE(IContractContact, '') = COALESCE(@o_IContractContact, '') AND
COALESCE(IPermitCO, '') = COALESCE(@o_IPermitCO, '') AND
COALESCE(ICORenewal, '') = COALESCE(@o_ICORenewal, '') AND
COALESCE(IRenewalInt, '') = COALESCE(@o_IRenewalInt, '') AND
/*COALESCE(IAppComments, '') = COALESCE(@o_IAppComments, '') AND*/
COALESCE(IKitchen1, '') = COALESCE(@o_IKitchen1, '') AND
COALESCE(IKitchen2, '') = COALESCE(@o_IKitchen2, '') AND
COALESCE(IKitchen3, '') = COALESCE(@o_IKitchen3, '') AND
COALESCE(IKitchen4, '') = COALESCE(@o_IKitchen4, '') AND
COALESCE(IKitchenB, '') = COALESCE(@o_IKitchenB, '') AND
COALESCE(IBedroom1, '') = COALESCE(@o_IBedroom1, '') AND
COALESCE(IBedroom2, '') = COALESCE(@o_IBedroom2, '') AND
COALESCE(IBedroom3, '') = COALESCE(@o_IBedroom3, '') AND
COALESCE(IBedroom4, '') = COALESCE(@o_IBedroom4, '') AND
COALESCE(IBedroomB, '') = COALESCE(@o_IBedroomB, '') AND
COALESCE(IBathroom1, '') = COALESCE(@o_IBathroom1, '') AND
COALESCE(IBathroom2, '') = COALESCE(@o_IBathroom2, '') AND
COALESCE(IBathroom3, '') = COALESCE(@o_IBathroom3, '') AND
COALESCE(IBathroom4, '') = COALESCE(@o_IBathroom4, '') AND
COALESCE(IBathroomB, '') = COALESCE(@o_IBathroomB, '') AND
COALESCE(AppraiserPerc, 0) = COALESCE(@o_AppraiserPerc, 0) AND
COALESCE(AppraiserFee, 0) = COALESCE(@o_AppraiserFee, 0)
END

View 13 Replies View Related

Nvarchar Fields With Time Data - Calculate Difference In Hours

Sep 19, 2014

I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. I tried substring to trim the OTIM, I am unable to make it work.

View 3 Replies View Related

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.

So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.

Any suggestions would help.

Thanks

View 1 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.

View 6 Replies View Related

The Parameterized Query '(@contactdate Nvarchar(4000),@dnbnumber Nvarchar(4000),@prospect' Expects The Parameter '@futureopportunity', Which Was Not Supplied.

Jan 10, 2008

HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub

View 6 Replies View Related

Compare Nvarchar(10) With Nvarchar(1000)

Sep 4, 2007

I had this question for quite a long time.

It seems the latter one don't take any extra storage space than the previous one.

As long as the real string length is less than 10.

Is that mean the latter one not cost anything?

I once heard the different is when they are in memory. But not sure of it.

Can anyone explain it and provide some official reference on it?

Thank.

View 6 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

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

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

SORTING

Apr 10, 2006

Ok this is what I have
$query = $query = "SELECT date, file name FROM comment WHERE file name ='', AND CorS='C', AND Approved='Y', ORDER BY file name DESC";

But what I want is to return all results based on the file name from the most recent to the oldest. Can anyone help????

View 7 Replies View Related

Sorting

Mar 27, 2008

hi,

i need a query based on sorting.
i have date column and time as varchar in another column and other order details

datecol timcol ordername type
12/31/2007 1:00 pm order1 1
12/31/2007 5:00 pm order2 1
12/22/2007 6:00 pm order3 1

so i need to get query o/p as

12/22/2007 6:00pm
12/31/2007 1:00 pm
12/31/2007 5:00pm

based on date and time. pls help!

View 6 Replies View Related

Sorting Twice?!

Jun 27, 2006

here's a good one for you...I want to return the last 20 records I have modified. I have adatemodified field - excellent.So I run a query to select the top 20 when ordered by datemodifieddesc.But now I have these results I want them sorted by companyname.Is this possible?Yes I could use my GUI to do the second sort, but can it be done justin a query?ThanksTim

View 3 Replies View Related

Sorting

Oct 11, 2006

I have a text field in the form of numbers separated by periods like this: 1,1.2,1.2.1

When I sort, it works fine unless I get out to 10 digits.

Desired sort: 1,1.1,1.1.1,1.1.2,.....,1.1.9,1.1.10

Actual sort: 1,1.1,1.1.1,1.1.10,1.1.2,.....,1.1.9

The 10 is showing up smaller than the 2 because of the straight text comparison, any ideas on how to get around this?

View 6 Replies View Related

Gridview Sorting

Aug 17, 2006

I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc. So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this. For only ASC sorting, I've got
ORDER BY CASE WHEN @sortBy='' THEN DateCreated END, CASE WHEN @sortBy='DateCreated' THEN DateCreated END
and so on. However, columns can also be sorted with DESC. I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC. How can I do this?

View 2 Replies View Related

Problem With Sorting

Jun 25, 2007

I am trying to set up custom paging and sorting with my gridview. All is well but the sorting. The problem is with the stored procedure. If I pass in the value @sortExpression as, for example "discussions_Posts.post_time", i does not sort it at all. But if I replace the @sortExpression with discussions_Posts.post_time in the actual stored procedure, it gets sorted. how do I sort this query with a input parameter with values like "discussions_Topics.topic_title" or something? ALTER PROCEDURE discussions_GetTopicsSubSet@startRowIndex as int,@maximumRows as int,@sortExpression as nvarchar(50),@board_id as intASDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,topic_time DATETIME,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)--DECLARE @TopicsFrom Datetime--SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END-- populate the table CAST(getdate() as int)INSERT INTO @TopicsSELECT ROW_NUMBER() OVER (ORDER BY @sortExpression), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type,discussions_Topics.topic_time, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username, user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM discussions_Topics INNER JOIN discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE (discussions_Topics.board_id = @board_id ANDdiscussions_Topics.topic_type NOT LIKE '1' )SELECT * from @TopicsWHERE RowNumber BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

View 1 Replies View Related

Need To Do Some Tricky SQL Sorting

Dec 13, 2007

This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it.
I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).

View 2 Replies View Related

Sorting By Date And Then Sorting By Name (within Date)

Jan 7, 2008

Hi,
I hope I can explain what I am trying to achieve (in MS SQL Server):
The results set that shows Names and Dates (plus others, but not relevant here), that needs to be sorted by Date and then by name.
I have tried:
ORDER BY date, surname, forename (gives dates in right order but not names)
and
ORDER BY surname, forename, date (gives names in right order but not dates)

Thanks for any advice!

View 2 Replies View Related

Sorting And Performance

Feb 26, 2004

I noticed when looking at the execution path that the SQL sort (order by) was taking up 16% of the total for the query. However the execution time for the sort was like .007 ms in SQL so I doubt it was really that much overhead.

Is it more effecient to sort a dataset within my asp.net program?

View 5 Replies View Related

Sorting Question

Aug 2, 2004

Hi,

i have a simple table with NAME and a SORTORDER. Now i want to sort my table on the NAME desc and then insert a number into the SORTORDER. The number has to correspond with the number the NAME has in the list.

EXAMPLE

Initial situation:
BAKKER 0
AKKER 0
CARNIE 0

Now my question is how can i update the table with a sp in wich te outcome is:

AKKER 1
BAKKER 2
CARNIE 3

Can someone please help me?

Greetings,

Mark

View 3 Replies View Related

Sorting Question

Aug 11, 2004

i have a question that is there any methods to order by the column which's datatype is ntext with t-sql?

View 1 Replies View Related

Lexigraphical Sorting

Apr 3, 2006

Anybody know how to accomplish lexigraphical sorting? Any how-tos, docs, tuts or if you just want to paste code

THanks!

View 2 Replies View Related