Hi!
I am evaluating how far i can go in implementing repoting features of SRS in
my program.
I want to be able to write a Windows form (in C#) that has a grid/list
containing all the reports that I have made. The list may have been
previously enterd into the database along with other information needed to
run the particular report.
I want to be able to select in the grid/list(e.g. using a check box) which
reports to run, and what format to render each individual report, as well as
other parameters.
Is the Report Manager that extensible? Is it possible to create this sort of
replacement for Report Manager from C#?
I am using VS2005 and SRS 2005.
Thanks in advance!It is absolutely possible... I have written a report for a customer which
searches the reporting services met data database and returns a list of
reports which match the search criteria the customer wants.. It creates a
report which displays the report name, directory and description... Each
report name is a hot link to run the report... This was done in a report!~
Using a programming language you can do anything you wish... There is a
ReportViewer control ( Search for ReportViewer in Books on line.) that you
can use..
There are sample programs in the SQL directory /90 subdirectory which will
help get you started...
Reporting Services is also just a DotNet Web service, with an entire API
which can you call from your program as well.. It is the same API that MS
uses in the Report Manager..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"PacMan" wrote:
> Hi!
> I am evaluating how far i can go in implementing repoting features of SRS in
> my program.
> I want to be able to write a Windows form (in C#) that has a grid/list
> containing all the reports that I have made. The list may have been
> previously enterd into the database along with other information needed to
> run the particular report.
> I want to be able to select in the grid/list(e.g. using a check box) which
> reports to run, and what format to render each individual report, as well as
> other parameters.
> Is the Report Manager that extensible? Is it possible to create this sort of
> replacement for Report Manager from C#?
> I am using VS2005 and SRS 2005.
> Thanks in advance!
Showing posts with label form. Show all posts
Showing posts with label form. Show all posts
Monday, March 26, 2012
Replacement for my LIKE Clause
Dear all,
I need some help from all my Transact SQL Guru friends out there..
Here is the scenario in its most simplified form.. ..
I have two tables.. A(Lookup table) and B(Transaction Table)
TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...
TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)
I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...
Therefore, this is what I had wrote initially..
update B
set B.EmployeeLocationID =A.EmployeeLocationID
>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null
This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...
For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...
Therefore this is the expected match for my examples based on
requirement..
"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..
Looking forward to hearing from someone ASAP.. Please help..
Best regards,
VM...Interesting. Maybe this will give you and angle to try.
UPDATE B
SET EmployeeLocationID = (SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL
Roy Harvey
Beacon Falls, CT
On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:
>Dear all,
>I need some help from all my Transact SQL Guru friends out there..
>Here is the scenario in its most simplified form.. ..
>I have two tables.. A(Lookup table) and B(Transaction Table)
>TableA Fields
> EmployeeLocationID
> EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>TableB Fields
> EmployeeID
> EmployeeName......
> EmployeeLocationID (will have null initially when rows are populated
>first time)
> EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>Therefore, this is what I had wrote initially..
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID
>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
>'%'
>where B.EmployeeLocationID is null
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>Therefore this is the expected match for my examples based on
>requirement..
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>Looking forward to hearing from someone ASAP.. Please help..
>Best regards,
>VM...|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>> A(Lookup table) and B(Transaction Table) <<
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN '
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>> I hope you get where I am leading this to, from my examples.. <<
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.|||Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> >> A(Lookup table) and B(Transaction Table) <<
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN '
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
> >> I hope you get where I am leading this to, from my examples.. <<
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.
I need some help from all my Transact SQL Guru friends out there..
Here is the scenario in its most simplified form.. ..
I have two tables.. A(Lookup table) and B(Transaction Table)
TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...
TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)
I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...
Therefore, this is what I had wrote initially..
update B
set B.EmployeeLocationID =A.EmployeeLocationID
>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null
This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...
For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...
Therefore this is the expected match for my examples based on
requirement..
"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..
Looking forward to hearing from someone ASAP.. Please help..
Best regards,
VM...Interesting. Maybe this will give you and angle to try.
UPDATE B
SET EmployeeLocationID = (SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL
Roy Harvey
Beacon Falls, CT
On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:
>Dear all,
>I need some help from all my Transact SQL Guru friends out there..
>Here is the scenario in its most simplified form.. ..
>I have two tables.. A(Lookup table) and B(Transaction Table)
>TableA Fields
> EmployeeLocationID
> EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>TableB Fields
> EmployeeID
> EmployeeName......
> EmployeeLocationID (will have null initially when rows are populated
>first time)
> EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>Therefore, this is what I had wrote initially..
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID
>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
>'%'
>where B.EmployeeLocationID is null
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>Therefore this is the expected match for my examples based on
>requirement..
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>Looking forward to hearing from someone ASAP.. Please help..
>Best regards,
>VM...|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>> A(Lookup table) and B(Transaction Table) <<
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN '
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>> I hope you get where I am leading this to, from my examples.. <<
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.|||Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> >> A(Lookup table) and B(Transaction Table) <<
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN '
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
> >> I hope you get where I am leading this to, from my examples.. <<
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.
Replacement for my LIKE Clause
Dear all,
'%'
where B.EmployeeLocationID is null
>'%'
>where B.EmployeeLocationID is null
>
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>
>Therefore this is the expected match for my examples based on
>requirement..
>
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>
>Looking forward to hearing from someone ASAP.. Please help..
>
>Best regards,
>
>VM...|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
Why did you avoid clear names?
No. Clear specs would have been nice, along with real DDL.
>
Why did you avoid clear names?
>
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??
>
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
>
Can two prefixes belong to the same SAN? No specs given.
>
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
>
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
>
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>
>
No. Clear specs would have been nice, along with real DDL.
>
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
>
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
>
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
>
Etc.);
>
You will need error handling code for prefixes that do not match.
I need some help from all my Transact SQL Guru friends out there..
Here is the scenario in its most simplified form.. ..
I have two tables.. A(Lookup table) and B(Transaction Table)
TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...
TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)
I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...
Therefore, this is what I had wrote initially..
update B
set B.EmployeeLocationID =A.EmployeeLocationID
Quote:
Originally Posted by
>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null
This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...
For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...
Therefore this is the expected match for my examples based on
requirement..
"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..
Looking forward to hearing from someone ASAP.. Please help..
Best regards,
VM...Interesting. Maybe this will give you and angle to try.
UPDATE B
SET EmployeeLocationID =
(SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL
Roy Harvey
Beacon Falls, CT
On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:
Quote:
Originally Posted by
>Dear all,
>
>I need some help from all my Transact SQL Guru friends out there..
>
>Here is the scenario in its most simplified form.. ..
>
>I have two tables.. A(Lookup table) and B(Transaction Table)
>
>TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>
>TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
>first time)
EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>
>Therefore, this is what I had wrote initially..
>
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID
Quote:
Originally Posted by
>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
>'%'
>where B.EmployeeLocationID is null
>
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>
>Therefore this is the expected match for my examples based on
>requirement..
>
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>
>Looking forward to hearing from someone ASAP.. Please help..
>
>Best regards,
>
>VM...|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
Quote:
Originally Posted by
Quote:
Originally Posted by
>A(Lookup table) and B(Transaction Table) <<
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
Quote:
Originally Posted by
Quote:
Originally Posted by
>I hope you get where I am leading this to, from my examples.. <<
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.|||Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
Quote:
Originally Posted by
Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
>
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>
>
Quote:
Originally Posted by
Quote:
Originally Posted by
A(Lookup table) and B(Transaction Table) <<
>
Why did you avoid clear names?
>
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??
>
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
>
Can two prefixes belong to the same SAN? No specs given.
>
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
>
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
>
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>
Quote:
Originally Posted by
Quote:
Originally Posted by
I hope you get where I am leading this to, from my examples.. <<
>
No. Clear specs would have been nice, along with real DDL.
>
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
>
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
>
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
>
Etc.);
>
You will need error handling code for prefixes that do not match.
Replacement for Access Forms
Just wondering if anyone has any suggestions for a replacement for Access Forms once I move the tables etc to SQL 2005?
Does SQL 2005 have any form building functionality like Access?
Since you are comfortable with Access forms, you may wish to continue using Access for the client Appication -and use SQL Server for the data.
Check in the Access documentation about Access Data Projects.
Friday, March 23, 2012
Replace NULL with "-" using Trigger
Can a Trigger be created to automatically replace NULL values with "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to code in my application. I want that when an Insert Query is executed, a BEFORE/AFTER TRIGGER should be executed to replace the NULL values coming from Insert QUery to "-".
I also want to know whether to use a Trigger or a Function/Stored Procedure.
I'd simply add the logic to your T-SQL insert statement with the ISNULL function. If its wrapped in a stored procedure it may look like something like the following:-
Code Snippet
CREATE PROC Test
@.Var1 CHAR(1),
@.Var2 CHAR(1)
AS
INSERT INTO YourTable (Var1, Var2)
VALUES ( ISNULL(@.Var1, '-'), ISNULL(@.Var2, '-') )
Using an INSTEAD OF trigger may be possible but that would seem a little over complicated for this.
HTH!
Replace NULL with "-" using Trigger
Can a Trigger be created to automatically replace NULL values with
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>sql
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>sql
Replace NULL with "-" using Trigger
Can a Trigger be created to automatically replace NULL values with
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.
RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegr oups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>
|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegr oups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.
RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegr oups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>
|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegr oups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>
Replace NULL with "-" using Trigger
Can a Trigger be created to automatically replace NULL values with
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>
Tuesday, March 20, 2012
Repeating Textbox
I'm creating a form which will enter all the information from the database
onto the form when the user enters a paramter. The problem is that I can't
get it to display more than one form.
Let say that the user wants all information from a certain company on the
form such as employees. The user enters the company name as parameter, and
then click view. There are about 25 employees, and that should generate 25
forms.
So can anyone point me to how to go about setting the form so that it will
generate all 25 forms with each employee's information on that form?Read up on subforms.
Do the following.
1.Create a report that just shows data for a single employee. Use a query
parameter that accepts employee id. Test and make sure it works
2. Create a report that just lists the employee ids based on the company
query parameter
3. Embed the first report as a subreport, right click on the embedded
subreport and map the employee id parameter to employee id in the company
report.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"chang" <chang@.discussions.microsoft.com> wrote in message
news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
> I'm creating a form which will enter all the information from the database
> onto the form when the user enters a paramter. The problem is that I
can't
> get it to display more than one form.
> Let say that the user wants all information from a certain company on the
> form such as employees. The user enters the company name as parameter,
and
> then click view. There are about 25 employees, and that should generate
25
> forms.
> So can anyone point me to how to go about setting the form so that it will
> generate all 25 forms with each employee's information on that form?
>|||Thank you Bruce. This works and saves me a lot of time trying to figure it
out.
If I wanted to say do the same thing, but now not using parameter. Do I
just set it like what you went over without the paramter part? The reason is
I also need to create report that will allow the user to do a mass print of
all employees information within all the companies.
"Bruce L-C [MVP]" wrote:
> Read up on subforms.
> Do the following.
> 1.Create a report that just shows data for a single employee. Use a query
> parameter that accepts employee id. Test and make sure it works
> 2. Create a report that just lists the employee ids based on the company
> query parameter
> 3. Embed the first report as a subreport, right click on the embedded
> subreport and map the employee id parameter to employee id in the company
> report.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
> > I'm creating a form which will enter all the information from the database
> > onto the form when the user enters a paramter. The problem is that I
> can't
> > get it to display more than one form.
> >
> > Let say that the user wants all information from a certain company on the
> > form such as employees. The user enters the company name as parameter,
> and
> > then click view. There are about 25 employees, and that should generate
> 25
> > forms.
> >
> > So can anyone point me to how to go about setting the form so that it will
> > generate all 25 forms with each employee's information on that form?
> >
>
>|||Instead of using = use like and have the parameter value be %. Try it with
the employee report.
Bruce Loehle-Conger
"chang" <chang@.discussions.microsoft.com> wrote in message
news:57B79B63-0809-4FCB-AA90-9483EDEA7461@.microsoft.com...
> Thank you Bruce. This works and saves me a lot of time trying to figure
> it
> out.
> If I wanted to say do the same thing, but now not using parameter. Do I
> just set it like what you went over without the paramter part? The reason
> is
> I also need to create report that will allow the user to do a mass print
> of
> all employees information within all the companies.
>
> "Bruce L-C [MVP]" wrote:
>> Read up on subforms.
>> Do the following.
>> 1.Create a report that just shows data for a single employee. Use a query
>> parameter that accepts employee id. Test and make sure it works
>> 2. Create a report that just lists the employee ids based on the company
>> query parameter
>> 3. Embed the first report as a subreport, right click on the embedded
>> subreport and map the employee id parameter to employee id in the company
>> report.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "chang" <chang@.discussions.microsoft.com> wrote in message
>> news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
>> > I'm creating a form which will enter all the information from the
>> > database
>> > onto the form when the user enters a paramter. The problem is that I
>> can't
>> > get it to display more than one form.
>> >
>> > Let say that the user wants all information from a certain company on
>> > the
>> > form such as employees. The user enters the company name as parameter,
>> and
>> > then click view. There are about 25 employees, and that should
>> > generate
>> 25
>> > forms.
>> >
>> > So can anyone point me to how to go about setting the form so that it
>> > will
>> > generate all 25 forms with each employee's information on that form?
>> >
>>|||Bruce,
I did as what you mentioned about creating the first report with just one
information and then create that query to have a parameter.
I then create the second report with just the parameter(employee id).
I then embedded the first report into the second report and went to the
properties of the embedded report(the first one) and set the parameter.
This is the error that i get:
"An error occurred while executing the subreport 'subreport1': The
expressoin referenced a non-existing field in the fields collection."
I got nothing in the subreport field...not sure what I did wrong. Please
advise.
Thanks.
"Bruce L-C [MVP]" wrote:
> Instead of using = use like and have the parameter value be %. Try it with
> the employee report.
> Bruce Loehle-Conger
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:57B79B63-0809-4FCB-AA90-9483EDEA7461@.microsoft.com...
> > Thank you Bruce. This works and saves me a lot of time trying to figure
> > it
> > out.
> >
> > If I wanted to say do the same thing, but now not using parameter. Do I
> > just set it like what you went over without the paramter part? The reason
> > is
> > I also need to create report that will allow the user to do a mass print
> > of
> > all employees information within all the companies.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Read up on subforms.
> >> Do the following.
> >> 1.Create a report that just shows data for a single employee. Use a query
> >> parameter that accepts employee id. Test and make sure it works
> >> 2. Create a report that just lists the employee ids based on the company
> >> query parameter
> >> 3. Embed the first report as a subreport, right click on the embedded
> >> subreport and map the employee id parameter to employee id in the company
> >> report.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "chang" <chang@.discussions.microsoft.com> wrote in message
> >> news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
> >> > I'm creating a form which will enter all the information from the
> >> > database
> >> > onto the form when the user enters a paramter. The problem is that I
> >> can't
> >> > get it to display more than one form.
> >> >
> >> > Let say that the user wants all information from a certain company on
> >> > the
> >> > form such as employees. The user enters the company name as parameter,
> >> and
> >> > then click view. There are about 25 employees, and that should
> >> > generate
> >> 25
> >> > forms.
> >> >
> >> > So can anyone point me to how to go about setting the form so that it
> >> > will
> >> > generate all 25 forms with each employee's information on that form?
> >> >
> >>
> >>
> >>
>
>|||Bruce,
I did what you have, and I shouldn't say it didn't work. It does work, but
I can only get one contact per a parameter.
If possible, I want to get the parameter to dynamically display all company
and the user can select that company to have it display all the employees.
Thanks.
"chang" wrote:
> Bruce,
> I did as what you mentioned about creating the first report with just one
> information and then create that query to have a parameter.
> I then create the second report with just the parameter(employee id).
> I then embedded the first report into the second report and went to the
> properties of the embedded report(the first one) and set the parameter.
> This is the error that i get:
> "An error occurred while executing the subreport 'subreport1': The
> expressoin referenced a non-existing field in the fields collection."
> I got nothing in the subreport field...not sure what I did wrong. Please
> advise.
> Thanks.
> "Bruce L-C [MVP]" wrote:
> > Instead of using = use like and have the parameter value be %. Try it with
> > the employee report.
> >
> > Bruce Loehle-Conger
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:57B79B63-0809-4FCB-AA90-9483EDEA7461@.microsoft.com...
> > > Thank you Bruce. This works and saves me a lot of time trying to figure
> > > it
> > > out.
> > >
> > > If I wanted to say do the same thing, but now not using parameter. Do I
> > > just set it like what you went over without the paramter part? The reason
> > > is
> > > I also need to create report that will allow the user to do a mass print
> > > of
> > > all employees information within all the companies.
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> Read up on subforms.
> > >> Do the following.
> > >> 1.Create a report that just shows data for a single employee. Use a query
> > >> parameter that accepts employee id. Test and make sure it works
> > >> 2. Create a report that just lists the employee ids based on the company
> > >> query parameter
> > >> 3. Embed the first report as a subreport, right click on the embedded
> > >> subreport and map the employee id parameter to employee id in the company
> > >> report.
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "chang" <chang@.discussions.microsoft.com> wrote in message
> > >> news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
> > >> > I'm creating a form which will enter all the information from the
> > >> > database
> > >> > onto the form when the user enters a paramter. The problem is that I
> > >> can't
> > >> > get it to display more than one form.
> > >> >
> > >> > Let say that the user wants all information from a certain company on
> > >> > the
> > >> > form such as employees. The user enters the company name as parameter,
> > >> and
> > >> > then click view. There are about 25 employees, and that should
> > >> > generate
> > >> 25
> > >> > forms.
> > >> >
> > >> > So can anyone point me to how to go about setting the form so that it
> > >> > will
> > >> > generate all 25 forms with each employee's information on that form?
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >
onto the form when the user enters a paramter. The problem is that I can't
get it to display more than one form.
Let say that the user wants all information from a certain company on the
form such as employees. The user enters the company name as parameter, and
then click view. There are about 25 employees, and that should generate 25
forms.
So can anyone point me to how to go about setting the form so that it will
generate all 25 forms with each employee's information on that form?Read up on subforms.
Do the following.
1.Create a report that just shows data for a single employee. Use a query
parameter that accepts employee id. Test and make sure it works
2. Create a report that just lists the employee ids based on the company
query parameter
3. Embed the first report as a subreport, right click on the embedded
subreport and map the employee id parameter to employee id in the company
report.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"chang" <chang@.discussions.microsoft.com> wrote in message
news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
> I'm creating a form which will enter all the information from the database
> onto the form when the user enters a paramter. The problem is that I
can't
> get it to display more than one form.
> Let say that the user wants all information from a certain company on the
> form such as employees. The user enters the company name as parameter,
and
> then click view. There are about 25 employees, and that should generate
25
> forms.
> So can anyone point me to how to go about setting the form so that it will
> generate all 25 forms with each employee's information on that form?
>|||Thank you Bruce. This works and saves me a lot of time trying to figure it
out.
If I wanted to say do the same thing, but now not using parameter. Do I
just set it like what you went over without the paramter part? The reason is
I also need to create report that will allow the user to do a mass print of
all employees information within all the companies.
"Bruce L-C [MVP]" wrote:
> Read up on subforms.
> Do the following.
> 1.Create a report that just shows data for a single employee. Use a query
> parameter that accepts employee id. Test and make sure it works
> 2. Create a report that just lists the employee ids based on the company
> query parameter
> 3. Embed the first report as a subreport, right click on the embedded
> subreport and map the employee id parameter to employee id in the company
> report.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
> > I'm creating a form which will enter all the information from the database
> > onto the form when the user enters a paramter. The problem is that I
> can't
> > get it to display more than one form.
> >
> > Let say that the user wants all information from a certain company on the
> > form such as employees. The user enters the company name as parameter,
> and
> > then click view. There are about 25 employees, and that should generate
> 25
> > forms.
> >
> > So can anyone point me to how to go about setting the form so that it will
> > generate all 25 forms with each employee's information on that form?
> >
>
>|||Instead of using = use like and have the parameter value be %. Try it with
the employee report.
Bruce Loehle-Conger
"chang" <chang@.discussions.microsoft.com> wrote in message
news:57B79B63-0809-4FCB-AA90-9483EDEA7461@.microsoft.com...
> Thank you Bruce. This works and saves me a lot of time trying to figure
> it
> out.
> If I wanted to say do the same thing, but now not using parameter. Do I
> just set it like what you went over without the paramter part? The reason
> is
> I also need to create report that will allow the user to do a mass print
> of
> all employees information within all the companies.
>
> "Bruce L-C [MVP]" wrote:
>> Read up on subforms.
>> Do the following.
>> 1.Create a report that just shows data for a single employee. Use a query
>> parameter that accepts employee id. Test and make sure it works
>> 2. Create a report that just lists the employee ids based on the company
>> query parameter
>> 3. Embed the first report as a subreport, right click on the embedded
>> subreport and map the employee id parameter to employee id in the company
>> report.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "chang" <chang@.discussions.microsoft.com> wrote in message
>> news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
>> > I'm creating a form which will enter all the information from the
>> > database
>> > onto the form when the user enters a paramter. The problem is that I
>> can't
>> > get it to display more than one form.
>> >
>> > Let say that the user wants all information from a certain company on
>> > the
>> > form such as employees. The user enters the company name as parameter,
>> and
>> > then click view. There are about 25 employees, and that should
>> > generate
>> 25
>> > forms.
>> >
>> > So can anyone point me to how to go about setting the form so that it
>> > will
>> > generate all 25 forms with each employee's information on that form?
>> >
>>|||Bruce,
I did as what you mentioned about creating the first report with just one
information and then create that query to have a parameter.
I then create the second report with just the parameter(employee id).
I then embedded the first report into the second report and went to the
properties of the embedded report(the first one) and set the parameter.
This is the error that i get:
"An error occurred while executing the subreport 'subreport1': The
expressoin referenced a non-existing field in the fields collection."
I got nothing in the subreport field...not sure what I did wrong. Please
advise.
Thanks.
"Bruce L-C [MVP]" wrote:
> Instead of using = use like and have the parameter value be %. Try it with
> the employee report.
> Bruce Loehle-Conger
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:57B79B63-0809-4FCB-AA90-9483EDEA7461@.microsoft.com...
> > Thank you Bruce. This works and saves me a lot of time trying to figure
> > it
> > out.
> >
> > If I wanted to say do the same thing, but now not using parameter. Do I
> > just set it like what you went over without the paramter part? The reason
> > is
> > I also need to create report that will allow the user to do a mass print
> > of
> > all employees information within all the companies.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Read up on subforms.
> >> Do the following.
> >> 1.Create a report that just shows data for a single employee. Use a query
> >> parameter that accepts employee id. Test and make sure it works
> >> 2. Create a report that just lists the employee ids based on the company
> >> query parameter
> >> 3. Embed the first report as a subreport, right click on the embedded
> >> subreport and map the employee id parameter to employee id in the company
> >> report.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "chang" <chang@.discussions.microsoft.com> wrote in message
> >> news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
> >> > I'm creating a form which will enter all the information from the
> >> > database
> >> > onto the form when the user enters a paramter. The problem is that I
> >> can't
> >> > get it to display more than one form.
> >> >
> >> > Let say that the user wants all information from a certain company on
> >> > the
> >> > form such as employees. The user enters the company name as parameter,
> >> and
> >> > then click view. There are about 25 employees, and that should
> >> > generate
> >> 25
> >> > forms.
> >> >
> >> > So can anyone point me to how to go about setting the form so that it
> >> > will
> >> > generate all 25 forms with each employee's information on that form?
> >> >
> >>
> >>
> >>
>
>|||Bruce,
I did what you have, and I shouldn't say it didn't work. It does work, but
I can only get one contact per a parameter.
If possible, I want to get the parameter to dynamically display all company
and the user can select that company to have it display all the employees.
Thanks.
"chang" wrote:
> Bruce,
> I did as what you mentioned about creating the first report with just one
> information and then create that query to have a parameter.
> I then create the second report with just the parameter(employee id).
> I then embedded the first report into the second report and went to the
> properties of the embedded report(the first one) and set the parameter.
> This is the error that i get:
> "An error occurred while executing the subreport 'subreport1': The
> expressoin referenced a non-existing field in the fields collection."
> I got nothing in the subreport field...not sure what I did wrong. Please
> advise.
> Thanks.
> "Bruce L-C [MVP]" wrote:
> > Instead of using = use like and have the parameter value be %. Try it with
> > the employee report.
> >
> > Bruce Loehle-Conger
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:57B79B63-0809-4FCB-AA90-9483EDEA7461@.microsoft.com...
> > > Thank you Bruce. This works and saves me a lot of time trying to figure
> > > it
> > > out.
> > >
> > > If I wanted to say do the same thing, but now not using parameter. Do I
> > > just set it like what you went over without the paramter part? The reason
> > > is
> > > I also need to create report that will allow the user to do a mass print
> > > of
> > > all employees information within all the companies.
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> Read up on subforms.
> > >> Do the following.
> > >> 1.Create a report that just shows data for a single employee. Use a query
> > >> parameter that accepts employee id. Test and make sure it works
> > >> 2. Create a report that just lists the employee ids based on the company
> > >> query parameter
> > >> 3. Embed the first report as a subreport, right click on the embedded
> > >> subreport and map the employee id parameter to employee id in the company
> > >> report.
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "chang" <chang@.discussions.microsoft.com> wrote in message
> > >> news:9CAFDA8D-84F0-45FA-AADF-E3403992D1F8@.microsoft.com...
> > >> > I'm creating a form which will enter all the information from the
> > >> > database
> > >> > onto the form when the user enters a paramter. The problem is that I
> > >> can't
> > >> > get it to display more than one form.
> > >> >
> > >> > Let say that the user wants all information from a certain company on
> > >> > the
> > >> > form such as employees. The user enters the company name as parameter,
> > >> and
> > >> > then click view. There are about 25 employees, and that should
> > >> > generate
> > >> 25
> > >> > forms.
> > >> >
> > >> > So can anyone point me to how to go about setting the form so that it
> > >> > will
> > >> > generate all 25 forms with each employee's information on that form?
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >
Monday, March 12, 2012
repeating free form list item fields for each group
Hi, I have a nested report where i have some dept name and address fields placed in the free form list, below the address fields i have the related employee info in a table. i have grouped on the dept id for both the list and table. for a particular dept , when the related employee table information goes on to the next page i want to repeat the dept name and address fields on each page for the group.i cannot place the fields in the group header of the table, since there are a lot of fields at different positions .so i have kept in free form in the list region.It's basically the group header information, i wanted repeated on each page, but i have placed the fields above the table, in free form.Please help. Really apreciate it . Thanks.
You can nest rectangles and text boxes in group and table header cells. This means it is possible to create a free form layout in the table or group header.
For example, you can add a rectangle to the group header cell in your table and then add multiple text boxes or images to that rectangle at any position you desire.
|||Thanks a lot.Saturday, February 25, 2012
Reordering entries in form
I have a form with a few entries running in it, 1-10. Now, what I would like
if possible, is for the entries to be reorganised so that they are listed
alphabetically. So, when I open the form, instead of getting entry number
one, I get the entry beginning with A.
if possible, is for the entries to be reorganised so that they are listed
alphabetically. So, when I open the form, instead of getting entry number
one, I get the entry beginning with A.
I have resorted the columns in my table, however this ain't helped."Neil Greenough" <neilgreenough@.btopenworld.com> wrote in message
news:ckj98c$f20$1@.hercules.btinternet.com...
>I have a form with a few entries running in it, 1-10. Now, what I would
>like
> if possible, is for the entries to be reorganised so that they are listed
> alphabetically. So, when I open the form, instead of getting entry number
> one, I get the entry beginning with A.
> I have resorted the columns in my table, however this ain't helped.
Is this another Access question? :-)
In MSSQL, you could order the data with an ORDER BY when you retrieve it
from the database:
select col1, col2, col3
from table1
order by col2
I have no idea how you would do it in an Access form, though. You might want
to try in microsoft.public.access.forms.
Simon
Monday, February 20, 2012
Rendering URL in EXCEL format
Hi Everyone,
I am trying to diaply my report on the web form by pasting the URL
in ReportViewer1.ReportPath = " " inside the codes. My URL is
/web
Test/FirstReport&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false
It is working fine and diaplying the report, but when I change the
Format to Excel. It does not display the report properly. It gives me
an error that report cannot be found. Same thing with PDF, when I
change the format to PDF. IT gives me the same error.
"There was error opening the file. File does not exist". In excel
format if I save the file. The file is saved but when I want to open
the file from save/open dialog box it does not display properly.
Can anyone please let me know what the problem is.
Thanks,
Hi,
Excel rendering has been a problem since the Reporting Services is used.
With the retail version some versions was not supported. SP1 added some new supported versions. And SP2 is adding more.
You may be running into such a problem.
SP2 will support Excel 97 and later versions.
SP1 supports Excel 10 (Office 2002) or later
Eralper
http://www.kodyaz.com
Subscribe to:
Posts (Atom)