Drawing line at last record

Hi.
I have a table containing two groups and a detail area.My report has multiple pages.I want to draw line after the last record on every page.I think that expression will be written in record's bottom expression area.How can I choose the last record in every page.Or is there another way to do this.Could you help me please?why wont u draw a line on page footer? when u draw a line on page footer it will diplay on the bottom of each page.|||Thanks but it didn't work
I drawed horizontal line across the page footer.
But there is several blank rows between the last record and the line in page footer.And this is never looking good.If you have another idea about this please share with me.

Download image datatype to Jpegs or GIFs

Hi,

I want download photos (image datatype) from a SQL-2000 database
into windows based jpeg files (one for each record). Just wondering is
there any way of doing this without using TEXTCOPY?

Michael(mcgrathml@.gmail.com) writes:
> I want download photos (image datatype) from a SQL-2000 database
> into windows based jpeg files (one for each record). Just wondering is
> there any way of doing this without using TEXTCOPY?

There is no other program that comes with SQL 2000 for the task, but you
could write your own. Or search on Google to see if you can find anything.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||mcgrathml@.gmail.com ha scritto:
> Hi,
> I want download photos (image datatype) from a SQL-2000 database
> into windows based jpeg files (one for each record). Just wondering is
> there any way of doing this without using TEXTCOPY?
> Michael
You can write a simple .net program to read/write
images (as byte streams) on a BLOB field
of a SQL Server (or Access) database.

If you are interested I can post you the
(small and simple) code...

Bye
M.A.|||http://www.microsoft.com/technet/pr...art3/c1161.mspx

Madhivanan|||Maury,

Please email/post details of code.

Michael.

Maury wrote:

> mcgrathml@.gmail.com ha scritto:
> > Hi,
> > I want download photos (image datatype) from a SQL-2000 database
> > into windows based jpeg files (one for each record). Just wondering is
> > there any way of doing this without using TEXTCOPY?
> > Michael
> You can write a simple .net program to read/write
> images (as byte streams) on a BLOB field
> of a SQL Server (or Access) database.
> If you are interested I can post you the
> (small and simple) code...
> Bye
> M.A.sql

Double Quote in database record

In my asp.net page when I run a query against the database the datagrid get populated with only the records that starts with the first letter for example A good day. But none of the records that starts like this "A fine day" or "A nice day" displays in the datagrid. I tried to replace the Double Quote but it still returns only the records without " quotes. My query looks like this:

Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel] <> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like @.ArticleHeading) ORDER BY [Articles].[ArticleHeading]"

You need to do the replace on both the column AND the value you are comparing, otherwise they will never be equal.
eg.

Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel]<> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like REPLACE(@.ArticleHeading),'chr(34)') ORDER BY [Articles].[ArticleHeading]"|||

I tried it but still it does not produce the right results.

My code:

Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel] <> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like (REPLACE(@.ArticleHeading, 'chr(34)', ''))) ORDER BY [Articles].[ArticleHeading]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand

dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_articleHeading As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_articleHeading.ParameterName = "@.ArticleHeading"
dbParam_articleHeading.Value = Request.QueryString("Alphabet")& "%"
dbParam_articleHeading.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_articleHeading)

|||What are you trying to do with: REPLACE([Articles].[ArticleHeading], 'chr(34)', '') ?|||Some records starts with a double qoute and a need to list all for example that starts with A but that needs to include something like "A nice day"|||

Using a sql query I manage to get the right results but what do I need to do in asp.net to get the right results. My SQL code that replaces the ":

SELECT *
FROM Articles
WHERE (REPLACE(ArticleHeading, '"', '') LIKE 'A%')
ORDER BY ArticleHeading

|||

Change:

REPLACE([Articles].[ArticleHeading], 'chr(34)', '')

To:

REPLACE([Articles].[ArticleHeading], chr(34), '')

don't show blank spaces caused by hidden duplicates in detail row.

I'm trying to solve on how to remove the blank row spaces in a table that is caused by hide duplicates. I have a lot of this record duplicate and it is hidden, but it display a very long blank spaces of rows before getting to the next group. Is there a way not to print the hidden row?Advance Thanks to the one giving the answers... :)
"Dzus" wrote:
> I'm trying to solve on how to remove the blank row spaces in a table that is caused by hide duplicates. I have a lot of this record duplicate and it is hidden, but it display a very long blank spaces of rows before getting to the next group. Is there a way not to print the hidden row?
>|||Hi Dzus,
For a whole row to be 'removed' in this way, I believe the 'hide duplicates' value must be true for every cell in the row.
Regards
Chris
"Dzus" wrote:
> Advance Thanks to the one giving the answers... :)
> "Dzus" wrote:
> > I'm trying to solve on how to remove the blank row spaces in a table that is caused by hide duplicates. I have a lot of this record duplicate and it is hidden, but it display a very long blank spaces of rows before getting to the next group. Is there a way not to print the hidden row?
> >
> >|||Hi Chris...
That was pretty fast response and thanks... But the problem still exists. I have already 'hide duplicates' on every cells on the same row that you've instructed, and it still show the blank rows...Hmmm...
This record that i've "hide duplicates" for every cells in the same row belongs to the detail grouping. Did i miss anything in editing the visibility properties under the details grouping?
Thanks again...,
Dzus
"Chris McGuigan" wrote:
> Hi Dzus,
> For a whole row to be 'removed' in this way, I believe the 'hide duplicates' value must be true for every cell in the row.
> Regards
> Chris
> "Dzus" wrote:
> > Advance Thanks to the one giving the answers... :)
> >
> > "Dzus" wrote:
> >
> > > I'm trying to solve on how to remove the blank row spaces in a table that is caused by hide duplicates. I have a lot of this record duplicate and it is hidden, but it display a very long blank spaces of rows before getting to the next group. Is there a way not to print the hidden row?
> > >
> > >|||You can simply delete the row from the table. Or, you can set the row
visibility to false.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dzus" <Dzus@.discussions.microsoft.com> wrote in message
news:7F901BAE-9054-4650-8574-3F7F3227479A@.microsoft.com...
> I'm trying to solve on how to remove the blank row spaces in a table that
> is caused by hide duplicates. I have a lot of this record duplicate and it
> is hidden, but it display a very long blank spaces of rows before getting
> to the next group. Is there a way not to print the hidden row?
>|||Hi Brian
Thanks for the reply.
But if i delete or set the row visibility to false, then all the 1st instance of the values i have for that particular row will not be displayed.
Actually i need the 1st instance of a value to be displayed and the duplicate ones not to be shown. I have set the properties to 'hide duplicates' for all the available fields in that particular row, and the thing is it managed to hide but it print out as a blank row before printing the next available record. Can i removed this blank row?
Thanks.
"Brian Welcker [MSFT]" wrote:
> You can simply delete the row from the table. Or, you can set the row
> visibility to false.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dzus" <Dzus@.discussions.microsoft.com> wrote in message
> news:7F901BAE-9054-4650-8574-3F7F3227479A@.microsoft.com...
> > I'm trying to solve on how to remove the blank row spaces in a table that
> > is caused by hide duplicates. I have a lot of this record duplicate and it
> > is hidden, but it display a very long blank spaces of rows before getting
> > to the next group. Is there a way not to print the hidden row?
> >
> >
>
>|||If you are setting hide duplicates on every single field, then you are
creating a group. You should create a group instead and delete the detail
row.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dzus" <Dzus@.discussions.microsoft.com> wrote in message
news:65D6F017-225C-4671-8957-F09815A34AAD@.microsoft.com...
> Hi Brian
> Thanks for the reply.
> But if i delete or set the row visibility to false, then all the 1st
> instance of the values i have for that particular row will not be
> displayed.
> Actually i need the 1st instance of a value to be displayed and the
> duplicate ones not to be shown. I have set the properties to 'hide
> duplicates' for all the available fields in that particular row, and the
> thing is it managed to hide but it print out as a blank row before
> printing the next available record. Can i removed this blank row?
> Thanks.
> "Brian Welcker [MSFT]" wrote:
>> You can simply delete the row from the table. Or, you can set the row
>> visibility to false.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dzus" <Dzus@.discussions.microsoft.com> wrote in message
>> news:7F901BAE-9054-4650-8574-3F7F3227479A@.microsoft.com...
>> > I'm trying to solve on how to remove the blank row spaces in a table
>> > that
>> > is caused by hide duplicates. I have a lot of this record duplicate and
>> > it
>> > is hidden, but it display a very long blank spaces of rows before
>> > getting
>> > to the next group. Is there a way not to print the hidden row?
>> >
>> >
>>|||Oh yes...! Thanx a lot Brian... Maybe now i'm more understand between the group and the details...
"Brian Welcker [MSFT]" wrote:
> If you are setting hide duplicates on every single field, then you are
> creating a group. You should create a group instead and delete the detail
> row.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dzus" <Dzus@.discussions.microsoft.com> wrote in message
> news:65D6F017-225C-4671-8957-F09815A34AAD@.microsoft.com...
> > Hi Brian
> >
> > Thanks for the reply.
> >
> > But if i delete or set the row visibility to false, then all the 1st
> > instance of the values i have for that particular row will not be
> > displayed.
> >
> > Actually i need the 1st instance of a value to be displayed and the
> > duplicate ones not to be shown. I have set the properties to 'hide
> > duplicates' for all the available fields in that particular row, and the
> > thing is it managed to hide but it print out as a blank row before
> > printing the next available record. Can i removed this blank row?
> >
> > Thanks.
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> >> You can simply delete the row from the table. Or, you can set the row
> >> visibility to false.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Dzus" <Dzus@.discussions.microsoft.com> wrote in message
> >> news:7F901BAE-9054-4650-8574-3F7F3227479A@.microsoft.com...
> >> > I'm trying to solve on how to remove the blank row spaces in a table
> >> > that
> >> > is caused by hide duplicates. I have a lot of this record duplicate and
> >> > it
> >> > is hidden, but it display a very long blank spaces of rows before
> >> > getting
> >> > to the next group. Is there a way not to print the hidden row?
> >> >
> >> >
> >>
> >>
> >>
>
>

dont insert if record exists

/*if key values exist don't insert new record*/
SELECT

/*if exists don't insert*/
CASE
WHEN ISNULL(gradeId, -1) = -1 THEN
INSERT INTO tblScores
(gtStudentId, assignmentId, score)
VALUES (@.nStudent, @.nAssignment, 0)
END

FROM tblScores
WHERE gtStudentId = @.nStudent AND assignmentId = @.nAssignment

tblScores has two fields comprising its primary key (gtStudentId, assignmentId) and the gradeId field is a required filed in this table.

I'm getting syntax errors when I click check syntax (near keywords insert from and end).

one other note: this CASE END is nested inside a BEGIN END loop, is this the problem? Is the 'End" of the 'Case' closing the 'End' of the 'Begin'?

thanksI think the problem is that the INSERT-statement is not a result_expression (see BOL). Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?|||Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?

Its rather complicated but I'll try:

This code is inside a trigger for an enrollment table. If a student joins a class late (after assignements have been assigned) then the trigger creates new records in the scores table giving the new student a 0 for each assignment already assigned to the class. If a student gets unenrolled from the class the scores are maintained in case the student gets reenrolled. If the student gets reenrolled then I want to do a select on the scores table and bypass any scores the student may have had prior to being unenrolled.
INSERT-statement is not a result_expression
How does one do a conditional INSERT?|||IF NOT EXISTS(
SELECT TOP 1 gradeId
FROM tblScores
WHERE
gtStudentId = @.nStudent
AND assignmentId = @.nAssignment)
BEGIN

INSERT INTO tblScores (
gtStudentId,
assignmentId,
score)

SELECT
@.nStudent,
@.nAssignment,
0
END|||I forget which site...but we just did this...Gotta find the code I wrote...

Why would you want to double every access to the database?

Just handle the dup key error...in a calling sproc or the application...

EDIT: It was originally about Contraints...same thing

USE Northwind
GO

CREATE TABLE myTable99(Col1 char(1), CHECK(Col1 IN ('Y','N')))
GO

CREATE PROC mySproc99 @.x char(1) AS INSERT INTO myTable99 SELECT @.x RETURN
GO

CREATE PROC mySproc00
AS
DECLARE @.rc int
EXEC @.rc = mySproc99 'Y'
SELECT @.rc, @.@.ERROR
EXEC @.rc = mySproc99 'B'
SELECT @.rc, @.@.ERROR
GO

EXEC mySproc00
GO

DROP PROC mySproc00
DROP PROC mySproc99
DROP TABLE myTable99
GO|||I did not understand very clearly what you are trying to do... is it inserting into your table only those records that _do not already exist_ in that table?

If so, ty this:

Insert into tblScores (gtStudentId, assignmentId, score)

VALUES (@.nStudent, @.nAssignment, 0)

Where (not exists (Select * from tblScores
Where WHERE gtStudentId = @.nStudent
AND assignmentId = @.nAssignment))|||My Point, just attempt the insert...and trap the Error if it fails

Don't make 2 data access attempts to the database

Doing a CONTAINS search on multiple tables and columns combined.

I want to do a CONTAINS search on several columns combined.
This example searches each column separately, so that a record is not
included that has one of the search terms in the Desired Position and the
other two search terms in a section body.
SET @.SearchPhrase = 'Engineer AND SQL AND VB'
SELECT DISTINCT
dbo.tblResumes.ResumeID
, dbo.tblResumes.DesiredPosition
FROM
dbo.tblResumes LEFT OUTER JOIN
dbo.tblResumeSections ON dbo.tblResumes.ResumeID =
dbo.tblResumeSections.ResumeID
WHERE (CONTAINS(dbo.tblResumes.DesiredPosition, @.SearchPhrase)
OR CONTAINS(dbo.tblResumeSections.SectionTitle, @.SearchPhrase)
OR CONTAINS(dbo.tblResumeSections.Body, @.SearchPhrase))
How could I return a resume that has all three terms, but not in any one
column?
Also, I don't understand how the search works when their are several
tblResumeSections records for one tblResumes record. Does it search each
section record?
I am grateful for any suggestions.
As I've played with this in light of other recent similar posts, the only
approach I can see at this point is to create the query in code and send it
to the database server. This allows me to dynamically build the query to
allow for a variable number of search terms.
This must be a very common search and newsgroup queston: how to do an 'AND'
search invoving multiple tables and columns where all of a variable number
of search terms must be found in a row, but not any one column..
I can't find a way to do this with FULL TEXT searches in a stored procedure
so far. I get an error if I try to concatenate columns in a CONTAINS search
as below.
Again, any suggestions are very welcome.
The following query is constrcted in code on the web page and does a LIKE
search on a concatenation of the relevant columns. It works but falls short
in that a resume that has the search terms in different sections (child
table) is not returned. But it seems the best I can do for now.
DECLARE @.SearchPhrase VarChar(50)
, @.Role VarChar(15)
SET @.SearchPhrase = 'SQL AND VB AND Engineer'
SET @.Role = 'Job Seeker'
SELECT DISTINCT
dbo.tblResumes.ResumeID
, dbo.tblResumes.DesiredPosition
, dbo.tblPostionTypes.Abbr AS PType
, dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName AS
JobSeekerName
, dbo.tblResumes.DateRevised AS Posted
, dbo.tblContacts.City + ', ' + dbo.tblContacts.State AS CityState
FROM
dbo.tblResumes INNER JOIN
dbo.tblContacts ON dbo.tblResumes.ContactID = dbo.tblContacts.ContactID
INNER JOIN
dbo.tblPostionTypes ON dbo.tblResumes.PositionTypeID =
dbo.tblPostionTypes.PositionTypeID LEFT OUTER JOIN
dbo.tblResumeSections ON dbo.tblResumes.ResumeID =
dbo.tblResumeSections.ResumeID LEFT OUTER JOIN
dbo.tblExperiences ON dbo.tblResumeSections.ResSectionID =
dbo.tblExperiences.ResSectionID LEFT OUTER JOIN
dbo.tblQualifications ON dbo.tblResumes.ResumeID =
dbo.tblQualifications.ResumeID
WHERE dbo.tblResumes.DateStart < GETDATE()
AND dbo.tblResumes.DateStop > GETDATE()
AND dbo.tblResumes.Deleted Is Null
AND dbo.tblResumes.Active = 1
AND dbo.tblResumes.Approved = 1
AND dbo.tblContacts.Active = 1
AND dbo.tblContacts.Approved = 1
AND dbo.tblContacts.Role = @.Role
-- Keyword tests
AND dbo.tblResumes.DesiredPosition + ' ' +
dbo.tblResumeSections.SectionTitle + ' ' + dbo.tblResumeSections.Body LIKE
'%SQL%'
AND dbo.tblResumes.DesiredPosition + ' ' +
dbo.tblResumeSections.SectionTitle + ' ' + dbo.tblResumeSections.Body LIKE
'%VB%'
AND dbo.tblResumes.DesiredPosition + ' ' +
dbo.tblResumeSections.SectionTitle + ' ' + dbo.tblResumeSections.Body LIKE
'%Engineer%'
"GM" <gmdevREMOVE@.starband.net> wrote in message
news:OiI%23kzSREHA.3140@.tk2msftngp13.phx.gbl...
> I want to do a CONTAINS search on several columns combined.
> This example searches each column separately, so that a record is not
> included that has one of the search terms in the Desired Position and the
> other two search terms in a section body.
> SET @.SearchPhrase = 'Engineer AND SQL AND VB'
> SELECT DISTINCT
> dbo.tblResumes.ResumeID
> , dbo.tblResumes.DesiredPosition
> FROM
> dbo.tblResumes LEFT OUTER JOIN
> dbo.tblResumeSections ON dbo.tblResumes.ResumeID =
> dbo.tblResumeSections.ResumeID
> WHERE (CONTAINS(dbo.tblResumes.DesiredPosition, @.SearchPhrase)
> OR CONTAINS(dbo.tblResumeSections.SectionTitle, @.SearchPhrase)
> OR CONTAINS(dbo.tblResumeSections.Body, @.SearchPhrase))
> How could I return a resume that has all three terms, but not in any one
> column?
>
> Also, I don't understand how the search works when their are several
> tblResumeSections records for one tblResumes record. Does it search each
> section record?
> I am grateful for any suggestions.
>

Does the UPDATE trigger fire when a record is updated or only when it is deleted?

I've gotten conflicting info about this in the past so I thought I'd try to get clarification.

When a record is deleted, I'm sure it fires the delete trigger. Does it also fire the update trigger?

ThanksThe update trigger doesnot fire on a delete.

however:

when an update occurs and the update trigger is fired, there are 2 virtual recordset that you can inspect to see what changed

inserted and deleted

the deleted recordset contains the old values
the inserted recordset contains the new values|||dpends on when you set the trigger to fire..could be update and/or delete and/or insert..

CREATE TRIGGER <trigger_name>
ON <table_name>
FOR DELETE, INSERT, UPDATE
AS ...

hth|||Thanks mbanavige. That's what I needed to know.