Basic Query: Alternatives To 'Group By' For NText Column

Apr 19, 2004

I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.

So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:

http://64.225.154.232/temp_dbDiagram.gif

I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.


SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL,
topic.TOPIC_NAME, topic.TOPIC_URL
FROM tbl_CCF_Headlines headline INNER JOIN
tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
WHERE (headline.HEADLINE_DATE IN
(SELECT TOP 3 HEADLINE_DATE
FROM tbl_CCF_HEADLINES
GROUP BY HEADLINE_DATE
ORDER BY HEADLINE_DATE DESC))
ORDER BY headline.HEADLINE_DATE DESC

View 2 Replies


ADVERTISEMENT

Rewrite Query, Alternatives To CASE WHEN

Oct 30, 2007

Hello.

I have a query which spends a lot of time calculating my CASE WHEN -statements.

My query looks like this

SELECT TOP 250

UserId,
CASE WHEN

(someCol*0.4+someOtherCol*0.3) > 99 THEN 99
ELSE

(someCol*0.4+someOtherCol*0.3)
END
FROM

(

SELECT

UserId,

CASE WHEN @myparam > 50 THEN

CASE WHEN Col5+@myincrease > 99 THEN 99 ELSE Col5+@myincrease END
ELSE

CASE WHEN Col6+@myincrease > 99 THEN 99 ELSE Col6+@myincrease ENDEND as someCol,
CASE WHEN Col8+@myincrease3 > 99 THEN 99 ELSE Col8+@myincrease3 END as SomeOtherCol
FROM

SomeTable
) t1


This is just a little bit of the full query. I cannot write the whole query since it contains alot of different views and calculations, but I have traced it down to that all these case when-statements is taking a lot of time to do. So I hope that this example will be enough for you to understand my problem.

I know about some tricks that can replace a CASE WHEN, for example using COALESCE or BETWEEN but that does not seem to work in my case.

Any suggestions?

View 3 Replies View Related

Problem With Count, Group By And Ntext

Apr 7, 2006

I have one SELECT statement that needs to return one ntext field fromone table and count something from other table, problem is that allfileds that are not in count have to be in group by and ntext can't bein group by... i hope you understend what i want to say here :), so isthere any solution to this problem or what is the best workaraund youwould use?example:TABLE projectsproject_id intproject_title nvarchar(50)project_description ntextTABLE usersuser_id intproject_id intSELECT projects.project_id, projects.project_title,projects.project_description, count(users.*) AS number_od_usersFROM projects LEFT JOIN users ON projects.project_id=users.project_idGROUP BY projects.project_id, projects.project_title,projects.project_description;this select is something that would work for me...if it wasn't for thattiny problem with ntext field

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Group By Only One Column

Jan 21, 2015

How can we write the query using groupby. I need to have group by only one column. Is it possible through subquery?

select col1, col2, col3
from testtable
group by col1.

View 4 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName'  values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC)  must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName                 Clm ABC-X

X

Page2:

GroupName                 Clm ABC-Y

Y

Page3:

GroupName                 Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

Column Size For Ntext

Aug 11, 2005

Hi,

Please help me to find out the ntext column size in a table.

Thanks in advance

Regards

Karthik

View 2 Replies View Related

Get Length Of Data In An NText Column

Feb 27, 2004

I have some text in an NText column that could be quite long.

for the web page that displays it, i want to show the first 200 characters and then have a "more..." link to bring up the full text.

i'd like to create a stored procedure that takes the left 200 characters and copies them to a ShortText column (NVarChar) for initial display and then id like to set a bit column to indicate if the length of the NText column is greater than 200.

Len and Left cant be used against NText so how can i work with the NText data ??

View 2 Replies View Related

SSIS, Problem Getting The Ntext Column Value

Mar 14, 2008



Hi,

I have an SSIS Package where I am looping through all input columns and creating an XML out of it. The Problem comes in one of the tables where I have an Ntext column. Instead of giving me the value of the column, it gives me
"Microsoft.SqlServer.Dts.Pipeline.BlobColumn"

I am trying to get the string value from this blob column. For that I am using the below code. Can you please point me to where I am going wrong. I am not very good in VB so I might be doing some casting error.

Dim blobCol As Microsoft.SqlServer.Dts.Pipeline.BlobColumn

Dim ByteVal As String


For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection


columnValue = rowType.GetProperty(column.Name)

Select Case column.DataType

Case (DataType.DT_NTEXT)


blobCol = DirectCast(columnValue.GetValue(Row, Nothing), Microsoft.SqlServer.Dts.Pipeline.BlobColumn)

ByteVal = System.Text.Encoding.Unicode.GetString(blobCol.GetBlobData(0, CInt(blobCol.Length)))

newXML += ((FormatElement(column.Name) + ReplaceSpecialChars(ByteVal) + FormatElement(column.Name, True)))

Case Else


newXML += ((FormatElement(column.Name) + ReplaceSpecialChars(columnValue.GetValue(Row, Nothing).ToString()) + FormatElement(column.Name, True)))

End Select

Next

Thanks in advance.
Regards

View 8 Replies View Related

UpdateText() Not Working Correctly On Ntext Column

May 25, 2006

I'm trying to run the following SQL against my ntext column, Testtbl.Task I want to replace where the ampersand sign got HTML encoded to  "&", and I want to strip it down to only the ampersand sign and deletethe "amp" and the ";".It executes, but I still have the HTML encoding for the ampersand sign if the issue is NOT the first character in the ntext column.  If it's the first part of the ntext column, then it works.

View 5 Replies View Related

Problem Accessing A Column Of Type Ntext

Jan 16, 2007

Hi,
I am working on an application which accesses SQL database. I have a table complaint in which i have a coulmn 'history' of type "ntext". When i insert a value, the value gets stored in the db correctly, but I cannot access the complete data from that field using a simple select query. I had to export the table to a text file only then i could view the complete data of the field.
I wrote the query in query analyzer as:
"select history from complaint where pkey=142"
& the result i got is:
"
###### admin: Jan 8, 2007 3:53:57 PM GMT Type: Inactive (Key 10) Description: Activity yet to be started State: Planned "

This is only a part of the entire string that I had entered.

My requirement is that I need to access the data from the ntext field & save it in a variable & then display the entire data in my application. Can anyone tell me how should i use the select query so that i get the entire data without having to go thru the export data procedure.
Please help!!!

View 2 Replies View Related

NText Column Text Encoding/decoding

Sep 19, 2007

Hi,

I have a table column type as nText, however there are some Chinese character stored in that field and it is a messed up as it is not readable.

In my vb.net code, I did Convert to unicode by getting the byte of each character and encode it with UTF8 e.g:


Public Shared Function ConvertToUnicode(ByVal s As String) As String


' Convert To Unicode

Dim MyBytes As Byte() = Encoding.Default.GetBytes(s)

Dim GBencoding As Encoding = System.Text.UTF8Encoding.UTF8

Return GBencoding.GetString(MyBytes)

End Function


This works well but ,the problem is that it slows down the process quite alot, and I wonder are there any text encoding method I can use in SQL that can run when i do the SELECT Statement?


SELECT
Convert(MyNTEXTColumn)
....
something like that?

Thanks.
Jon

View 4 Replies View Related

#Error Displayed In Ntext Column Exported To Excel

Nov 28, 2007



Hi,

I have exported a report to excel that contains an ntext column. The exported excel file shows "#Error" for the ntext column in some of the rows. If I see the same report in reporting services I don't see any problem. I have checked that this is not any issue with the length of the text. Could you suggest me something on this?

Thanks
Soni

View 5 Replies View Related

Transact SQL :: Split A Column Which Contains Ntext Values Separated With Value

Jul 13, 2015

SQL syntax to split a column that contains ntext values that are separated with this value : ;#

I need it to create 3 new columns

i.e.: I have a column that contains values such as:

ROW1: ;#typea;#typeb;#typec;#typed;#
ROW2: ;#typeb;#typec;#
ROW3: ;#typeb;#

I need it to convert to 3 new columns and strip out the ;#:

E.g.:

Column 1
typea
typeb
typeb

Column 2
typeb
typec
blank

View 16 Replies View Related

SQL 2012 :: SSRS Average Column Group Value For Row Group

Feb 28, 2014

I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:

Year
Month
Date

And the following row groups:

Region
Product
SubType (hidden, data at the date level is summed to Product)

At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)

View 0 Replies View Related

Function That Replaces Ntext And Compares Ntext With Nvarchar

Nov 28, 2007

I am running this query to an sql server 2000 database from my aspcode:"select * from MyTable whereMySqlServerRemoveStressFunction(MyNtextColumn) = '" &MyAdoRemoveStressFunction(MyString) & "'"The problem is that the replace function doesn't work with the ntextdatatype (so as to replace the stresses with an empty string). I hadto implement the MySqlServerRemoveStressFunction, i.e. a function thattakes a column name as a parameter and returns the text contained inthis column having replaced some letters of the text (the letters withstress). Unfortunately, I could not do that because user-definedfunctions cannot return a value of ntext.So I have the following idea:"select * from MyTable whereCheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &"')"How can I implement the CheckIfTheyAreEqualIngoringTheStessesfunction? (I don't know how to combine these functions to do what Iwant: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)

View 2 Replies View Related

Way To Retrieve Actual Size Of Ntext Column In Native Code?

Aug 3, 2007

Howdy folks! (I feel im creating too many topics...but all my questions are unrelated :-/)

Dev environment: SSCE, Native c++ OLE code, Win CE 5

I have a routine that calculates the size of a set of columns in order to create a byte buffer for retrieving data from an IRowset and inserting it into a custom class. This method works great for all data types except ntext. When I look at the column information from an ntext field, the DBCOLUMNINFO::ulColumnSize member holds 536,870,911 (the max length of an ntext type). Obviously I dont want to allocate 1GB of memory for each ntext column. Is there anyway (natively) to determine the actual number of characters in a field?

View 12 Replies View Related

How Do I Handle REPLACE A Particuler Matched String Within An NTEXT Column In SQL Server?

Mar 27, 2008

Hi!
I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error

I tried following

UPDATE CaseTypeDefs SET definition = replace(LTRIM(RTRIM(definition)), '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]>', '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]>')


But this is producing following error

Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.

For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.

Need help, how to do it?.


Thanks In Advance

Devloper
Anil Kumar Dwivedi

View 4 Replies View Related

Query To Get Duplicate Ntext

Mar 2, 2007

I have records in a table as :

Col1 Col2 Col3 Col4

1115090122084511
1119610122084511

How do I query to get records where count(Col4) >1 and Col3 =12
Please note that Col4 is ntext datatype.

TIA,
Andy

View 2 Replies View Related

Query Performence About NText

Sep 5, 2006

Is there any difference between a nText field value='' and value=NULL ?

View 4 Replies View Related

Transact SQL :: Get Multiple Rows Based On Comma-separated Ntext List In On Column?

Jun 2, 2015

I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122).  I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.

This obviously works:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')

But this does not:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)

View 5 Replies View Related

SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?

Jan 10, 2014

I'd like to ask how you would get the OUTPUT below from the TABLE below:

TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B

OUTPUT:

category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6

The code would go something like:

Select category, count(*), .... as id's
from TABLE
group by category

I just need to find that .... part.

View 3 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

A Basic Sql Query Problem

Jul 14, 2007

 Suppose we get two following tables:
    Table A:     [USER_ID] [varchar] (11)   NOT NULL  ,    [COURSE_ID] [varchar] (11)   NOT NULL  ,
    Table B:
    [COURSE_ID] [varchar] (11)   NOT NULL  ,    [COURSE_NAME] [varchar] (50)  NOT NULL  ,    [COURSE_NO] [varchar] (15)    NULL  ,    [BEGIN_DATE] [datetime]    NULL  ,    [END_DATE] [datetime]    NULL  ,    [CREATER] [varchar] (11)    NOT NULL  ,
and during the execution of my program, I can get the current use's id (USER_ID), say U0001.
How can I retrieve the result set containing [COURSE_NAME], [COURSE_ID], but the current user's id (U0001) have Not been assigned in Table A.
Thanks in advance.
Ricky.

View 3 Replies View Related

Basic Query Help - Revisited

Jul 14, 2001

I'm not getting it. The query below "e.g." is exactly as I think it should be except for the <Join Clause> as there is only the match on site name. I tried joing on the site name, but only got the sites in the log. I want all the site names even if they don't have a log item for a given date range.

I'd post my real query if it would help, it's just that I'm chopping up some strings and converting some time stamps to dates as varchar for readability for the final report, which is an MSExcel pivot table.

My original post lies below.

TIA

jEfFp...

Reply...
Date: Basic Query Help (reply)
RickD (Rick.Davis@Schroders.com)
7/11/01 6:41:26 AM

Make log an outer join to site.

e.g

select s.name, l.ondate from
site s
RIGHT OUTER JOIN log l ON <Join Clause> where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1

You didn't seem to have any logical join between the tables, but i'm sure you have, just put this in the <Join Clause>.

Oh, and read BoL, it helps no end.....

------------
Original Post...
Jeff Proctor at 7/10/01 11:18:31 PM

my 7 day query....

select s.name, l.ondate from
site s, log l where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1

This returns those sites that exist in the current log, however what I want is a result set that has a row for each name regardless if they are in the log.

I have 2 tables...

table1 sites
name
smithj
anandt
burtot
proctj
etc....

table2 log
id ondate
02anandt 07/01/01
02anandt 07/03/01
02anandt 07/03/01
01smithj 07/03/01
03burtot 07/05/01
01smithj 07/05/01
03burtot 07/05/01
etc.....

View 1 Replies View Related

Basic COUNT Query

Aug 7, 2013

I am trouble getting the count of applications. In the below query MerApp.ApplicationID represents the applications. I'm trying to get a results of a count of applications for each sales team member.Before adding the COUNT() function. My query results show 3811 because it shows the sales team member numerous times because he has many applications with different IDs. For example sales team member A shows up in 75 rows because he has 75 different applications assigned to him.

SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus

I want sales team member A to show up once with the count of applications to be 75 because that's how many are assigned to him.

This didn't work for me:
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, COUNT (MerApp.ApplicationID) AS [Applications], stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus

It didn't count the number of applications for each person.I have about 14 different sales team members. So I'm trying to have a query that produces 14 rows not 3811 rows

View 5 Replies View Related

What Looks Like A Basic SQL Query Still Not Resolved

Nov 23, 2005

ARRRRRRRGGGGGHHHHH!!Please can you help, I'm going round the bend with this.I have a simple and small table called STOCKCATS, which I need to query toget back a dataset in a particular order, but although it looks simple Ican't get it to work. My table schema plus sample data to see the problemis as follows:DROP TABLE IF EXISTS `STOCKCATS`;CREATE TABLE `STOCKCATS` (`CATID` varchar(30) NOT NULL default '',`LEVEL` varchar(30) default NULL,PRIMARY KEY (`CATID`),KEY `indxCATEGORYID` (`CATID`));INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('A001',''),('A002','A001'),('A003','A001'),('A004','A001'),('A005','A001'),('PCHW01',''),('MHW01',''),('FD01',''),('ELEC01',''),('MHW02','MHW01');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('MHW03','MHW01'),('MHW04','MHW01'),('MHW05','MHW01'),('PCHW02','PCHW01'),('PCHW03','PCHW01'),('PCHW04','PCHW01'),('PCHW05','PCHW01'),('PCSW01',''),('MSW01',''),('C001',''),('C002','C001'),('C003','C001'),('MV',''),('SUZ','MV'),('ALF','MV'),('PLASMA','ELEC01'),('T01','ELEC01'),('HEATING',''),('RAD','HEATING'),('P01',''),('B01','P01'),('BB','HEATING'),('FS','HEATING'),('WM','HEATING'),('AEROSOL',''),('SOLVENTS','AEROSOL'),('DGC','');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('DGXWINDOWS','DGC'),('DGXEXTRA','DGC'),('DGXCON','DGC');As you can see from the table structure, this table consists of 2 fieldvalues. The 1st is the category code and the 2nd is the level is at. If acatid has a level of nothing, eg '', then it means that it is a root levelcategory. If a catid has a another cat's catid in it's level, eg B01 hasP01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.All I want to do is query this table and bring back the data so thatalphabetically it goes root level cat A1, then all the sub-cats for thisroot level, then root level A2, then all sub-cats for this root level and soon. An example using the above would be as follows:^ ^ A to G of root level cats plus their sub-cats....HEATING << root levelBB << sub-cat of heatingFS << sub-cat of heatingWM << sub-cat of heating/ / I to Z of root level cats plus their sub-cats....A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),CATID and I thought this had done it, but I was looking at the ('A001',''),('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')entries as these naturally fell into place. If you use this order commandon the above you will see that ('P01','') and it's associated ('B01','P01')sub-cat just don't come together.Does any body have any ideas?ThanksLaphan

View 6 Replies View Related

Basic Sql Query Needed

Jul 20, 2005

Ok, I'm a beginner so forgive me for my ignorance. Could someone helpme with this?tbl_x has two fields xid and xlistxlist being a list of numbers.....1, 5, 6, 8i want to create a new table from tbl_x that converts each number inthat xlist into a row in tbl_newso for exampletbl_x has 3 rowsxid xlist1 1, 4, 52 2, 3, 73 2, 1, 7i need a query or sql script that will convert that table with a listinto the following....tbl_newid xid xlid1 1 12 1 43 1 54 2 25 2 36 2 77 3 28 3 19 3 7

View 2 Replies View Related

Basic Insert And Query

Nov 20, 2007

Hi I am having two very basic problems which are very frustrating.
I have read http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx and changed my DB to "Copy if newer" however when I use the connection string "|DataDirectory|MyDB.sdf" MyDB remains unchanged. (If I put the full path to MyDB in the connection string I will see the changes.) I have tried copying the DB file in the bindebug directory and opening the copy separately, and the changes still do not appear.

This leads me to my second issue. While trying to verify if the insert worked, I am trying to query the DB for the row I just inserted. I have found very little documentation describing how to do this; am I missing something/someplace obvious? Here is my code:

string strCommand = "SELECT * FROM Log WHERE Severity = @Sev AND Message = @Msg";
SqlCeResultSet resultSet = null;
SqlCeCommand sqlCommand = new SqlCeCommand(strCommand, pConn);

sqlCommand.Parameters.Add(SevParam);
sqlCommand.Parameters.Add(MsgParam);

try
{
pConn.Open();
resultSet = sqlCommand.ExecuteResultSet(ResultSetOptions.None);
}
finally
{
pConn.Close();
}
return !(resultSet == null);


resultSet always returns non-null, so I am clearly doing something wrong. Do I need to "read through" the resultSet to make sure there is something there? And if so, how? Should I be using a DataReader instead? (and if so how?) Am I going in the wrong direction? Just to be clear, I want to insert a row into my DB and then check that that row has been inserted by querying the DB.

Thanks!
=Spencer Whitman

View 1 Replies View Related

Insert Query - Basic Question

Apr 28, 2006

Ok, I have a table that contains a number of columns, one of these columns contains a 'unitref' e.g.AC02/001D.

I import a new set of records, approx 7,000 per week in a DTS package from CSV Flat File into the table.

What I need to achieve at either the point of import of new data weekly, or once the new data is sitting in its final resting home, is a copy of the first two 2 Chars of the UnitRef, in the example above, this would make it 'AC' and then place that in a column named 'site_ref'.

Having posted the question on this forum relating to grabbing the first two chars of a value and placing them in a temporary table by utilising the Left(field,2) command in SQL (Kindly answered by CryptoKnight), I was wondering how I can do this possibly by using the inesrt into type command. I have many columns that get imported this is only a tiny step of many things that ideally would need to happen on an import,

Regards

View 1 Replies View Related

Transact SQL :: Select Row With Max (column Value) Group By Another Column

May 19, 2015

i dont't know how to select row with max column value group by another column. I have T-SQL

CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (

[code]....

View 3 Replies View Related

Implicit Conversion From Data Type Ntext To Varchar Is Not Allowed. Use The CONVERT Function To Run This Query.

Oct 9, 2007

Hello Guys,Have been getting this error(
Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query.
) when running on the live environment but it was fine when run locally. If anyone has similar problem please let me know the fix you have done.
Thank you.

View 2 Replies View Related

Alternatives To SQL *Plus

Jul 23, 2005

Hi,I have an SQL assignment to do and at my school we use SQL *Plus therehowever I don't have Oracle at home, where I would like to do the work ,so Iwas wondering whats the easiest way to get an SQL environment up so I cancode in that then just paste it into SQL *Plus later.I don't really want to install Oracle on my home pc and I was wondering ifthere are other IDE's for SQl that would fit my need for this.I discovered an instant SQL *Plus client that sounded really promising butwhen i unpacked it, it was just a load of dll's so I think it wasn't what Ithought it was.So does anyone know of anything that might be able to help me out here?Any advice much appreciated!Thanks--Ant

View 2 Replies View Related

Converting Basic EffDt Query To SQL-92 Standard

Jan 28, 2005

How would I convert this query to ANSI 92 standard?

select p.InternalID,
n.LastName
from tblPatient p,
tblName n
where p.PatientID = n.EntID and
n.EffDt = (
select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate()
)

View 1 Replies View Related







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