Getting the corresponding primary key from MAX()?

I think this should be easy, but maybe my brain is just not working today,
can anyone offer suggestions?
I have a table, Table1 (simplified):
ID dollars
1 15
2 30
4 22
Using T-SQL, how can I get the ID value for the record with the highest
number in the dollars column?
"Select MAX(dollars) from Table1" gives me the actual highest value, but I
need to know the record from where that value came from.
Any thoughts?select ID from Table1 where dollars=(Select MAX(dollars) from Table1)
Note that unless there is a unique constraint on the dollars column,
you may get multiple rows back.|||That's what I needed...thanks.
<markc600@.hotmail.com> wrote in message
news:1134845143.777978.193910@.g14g2000cwa.googlegroups.com...
> select ID from Table1 where dollars=(Select MAX(dollars) from Table1)
> Note that unless there is a unique constraint on the dollars column,
> you may get multiple rows back.
>

Getting the correct totals

I am trying to create a report and get a specific total, but it is not working correctly. I have a group of workers and I am able to count how many Work Orders they have worked on in a given month, however when I try to get a total of how many they completed, I run into a problem because it is counting every date that they worked as 1 instead of counting the # of work orders. Someone mentioned using a Global variable, but I am unsure how to use this.Try a running total, or if your count is in your details area, you should be able to right click it and insert a summary (group level if desired and report level)

Getting table set up for comments

I am working on a project that lets visitors to my webpage post comments. I have there name, city, etc. stored into an sql database. This all works until I put text into the comments field of my form. I get this message when it is trying execute -- String or binary data would be truncated. I was wondering what data type to use, I have used char and varchar and neither one is working. Is there anything else I could try to fix this.Try Text data type if it is more than 8000 characters.

Getting SUM Function Result

Hello,

In the project I'm working on, I need to add up all rows data for one
column. So, I have this code:

$getprodcount = mysql_query("SELECT SUM(qty) FROM purchase");
$numproducts=$getprodcount;

Later on, I have this code: <?php print $numproducts; ?
What is being printed is Resource id #5...not the numeric value of what is
supposed to be a sum. What is wrong? I am assuming taht resource id #5 is a
pointer of some sorts to the number I am looking for, but how do you get
the actual sum number?

Thanks in advance!

--
Message posted via http://www.sqlmonster.comThis is an MSSQL group, not a MySQL group - you'll probably get a
better answer in a MySQL or PHP forum.

Simonsql

Getting sum for no.of hours

In my database one of my table contain no.of working hours column and the column is taken as nvarchar data is like 8:30,7:20,5:00 this,
problem is how to get total no of working hours, itried with sum(no.of working hours) it is not working ......

For some reason I cant think of an easier method. One way you could do is
split the values based on the ":" and sum the numbers individually. As in sum(8,7,5) and do a sum of minutes (30, 20)..etc and then convert the minutes into hours and add it to the hours later. It does sould a little round about but atleast that should get you started.|||

thanks for ur help ,its working fine...

sorry for late reply

Getting Started with SQL & XML direction please

Hello everyone,
I am working on a project and I need some help getting started in the
right direction. But first let me give you some background on what I
want to do, I work for an ambulance company and our ambulances are
equipped with GPS receivers and the data of the LAT and LONG are
transmitted and stored in a MS-SQL 2000 DB. This data is transmitted
every ~30 seconds as the unit drives around the county. The data is
then used in a 32-bit client application in our main office.
I want to build a web page that our field supervisors can monitor from
their SUV's, the locations of the ambulances (via a laptop and wireless
card).
I have played a little with Google and Yahoo Map API's and I think I
like the Yahoo best. I have built a static xml file with three
ambulances and the LAT and LONG for each and was able to display in the
information on the webpage map
(http://api.maps.yahoo.com/maps/v1/a...s.com/route.xml).
The problem is that I am not sure how to create this document
dynamically when a user hits the web site. As the XY of the ambulances
are constantly changing it can not be a static xml document.
I am not sure what tools I need to make this happen and how to go about
it.
I am reading all I can about javascripts, Yahoo API and XML and my head
is starting to spin a little. I think I need a push in the right
direction.
Thanks all who might reply!
Jim P
Largo, FlOn 29 Sep 2005 12:01:08 -0700, Jim wrote:

> Hello everyone,
> I am working on a project and I need some help getting started in the
> right direction. But first let me give you some background on what I
> want to do, I work for an ambulance company and our ambulances are
> equipped with GPS receivers and the data of the LAT and LONG are
> transmitted and stored in a MS-SQL 2000 DB. This data is transmitted
> every ~30 seconds as the unit drives around the county. The data is
> then used in a 32-bit client application in our main office.
> I want to build a web page that our field supervisors can monitor from
> their SUV's, the locations of the ambulances (via a laptop and wireless
> card).
> I have played a little with Google and Yahoo Map API's and I think I
> like the Yahoo best. I have built a static xml file with three
> ambulances and the LAT and LONG for each and was able to display in the
> information on the webpage map
> (http://api.maps.yahoo.com/maps/v1/a...s.com/route.xml).
> The problem is that I am not sure how to create this document
> dynamically when a user hits the web site. As the XY of the ambulances
> are constantly changing it can not be a static xml document.
> I am not sure what tools I need to make this happen and how to go about
> it.
> I am reading all I can about javascripts, Yahoo API and XML and my head
> is starting to spin a little. I think I need a push in the right
> direction.
> Thanks all who might reply!
> Jim P
> Largo, Fl
In the webpage, you could first "SELECT" the latest data from the table by
using "FORXML" clause. This should give you the XML string with the data
you need. If needed you can format the xml using xsl transform or
something. Then you could call the yahoo maps api page and pass the XML
string as the "xmlsrc" querystring parameter.
HTH,
Ayyappan Nairsql

Getting Started with SQL & XML direction please

Hello everyone,
I am working on a project and I need some help getting started in the
right direction. But first let me give you some background on what I
want to do, I work for an ambulance company and our ambulances are
equipped with GPS receivers and the data of the LAT and LONG are
transmitted and stored in a MS-SQL 2000 DB. This data is transmitted
every ~30 seconds as the unit drives around the county. The data is
then used in a 32-bit client application in our main office.
I want to build a web page that our field supervisors can monitor from
their SUV's, the locations of the ambulances (via a laptop and wireless
card).
I have played a little with Google and Yahoo Map API's and I think I
like the Yahoo best. I have built a static xml file with three
ambulances and the LAT and LONG for each and was able to display in the
information on the webpage map
(http://api.maps.yahoo.com/maps/v1/an...com/route.xml).
The problem is that I am not sure how to create this document
dynamically when a user hits the web site. As the XY of the ambulances
are constantly changing it can not be a static xml document.
I am not sure what tools I need to make this happen and how to go about
it.
I am reading all I can about javascripts, Yahoo API and XML and my head
is starting to spin a little. I think I need a push in the right
direction.
Thanks all who might reply!
Jim P
Largo, Fl
On 29 Sep 2005 12:01:08 -0700, Jim wrote:

> Hello everyone,
> I am working on a project and I need some help getting started in the
> right direction. But first let me give you some background on what I
> want to do, I work for an ambulance company and our ambulances are
> equipped with GPS receivers and the data of the LAT and LONG are
> transmitted and stored in a MS-SQL 2000 DB. This data is transmitted
> every ~30 seconds as the unit drives around the county. The data is
> then used in a 32-bit client application in our main office.
> I want to build a web page that our field supervisors can monitor from
> their SUV's, the locations of the ambulances (via a laptop and wireless
> card).
> I have played a little with Google and Yahoo Map API's and I think I
> like the Yahoo best. I have built a static xml file with three
> ambulances and the LAT and LONG for each and was able to display in the
> information on the webpage map
> (http://api.maps.yahoo.com/maps/v1/an...com/route.xml).
> The problem is that I am not sure how to create this document
> dynamically when a user hits the web site. As the XY of the ambulances
> are constantly changing it can not be a static xml document.
> I am not sure what tools I need to make this happen and how to go about
> it.
> I am reading all I can about javascripts, Yahoo API and XML and my head
> is starting to spin a little. I think I need a push in the right
> direction.
> Thanks all who might reply!
> Jim P
> Largo, Fl
In the webpage, you could first "SELECT" the latest data from the table by
using "FORXML" clause. This should give you the XML string with the data
you need. If needed you can format the xml using xsl transform or
something. Then you could call the yahoo maps api page and pass the XML
string as the "xmlsrc" querystring parameter.
HTH,
Ayyappan Nair

getting rid of comma separated column data

working on a transformation, and one of the things i'm trying to get
rid of are columns with comma separated values as lists of data. my god
is that annoying.
anyway, i need an insert and/or update query that will break up the
following comma value problem example (keyword being example, please
don't XXXXX about keys or relationships, it really has nothing to do
with my question):
create table foo (
fooid int identity(1, 1) not null ,
foolistofthingoldids varchar(500)) -- relate to thingoldid
create table things (
thingoldid int identity(1, 1) not null ,
thingnewid uniqueidentifier not null)
-- and this is the new table to replace the comma values in foo
create table foothings (
fooid int not null,
thingnewid uniqueidentifier not null)
it should be noted that i have a user defined split function that can
turn a comma separated list of values into a single column table, with
one row per comma separated item.
the trick is finding a way to use it in a set based operation to fill
foothings with rows, based on the relationships implied in the current
foo table's foolistofthingoldids property. i'm looking for something
like this:
INSERT INTO foothings (fooid, thingnewid)
SELECT f.fooid, t.thingnewid
FROM foo AS f
RIGHT JOIN split(f.foolistofthingoldids, ',') AS l
RIGHT JOIN things AS t ON l.value = t.thingoldid
except, one that actually compiles and works. but this is a sketch of
where i have been leading my train of thought.
thanks in advance for any help!
jasonCourtesy of Kass:
http://www.users.drew.edu/skass/sql...unction.sql.txt
-oj
"jason" <iaesun@.yahoo.com> wrote in message
news:1128945919.133895.197370@.g47g2000cwa.googlegroups.com...
> working on a transformation, and one of the things i'm trying to get
> rid of are columns with comma separated values as lists of data. my god
> is that annoying.
> anyway, i need an insert and/or update query that will break up the
> following comma value problem example (keyword being example, please
> don't XXXXX about keys or relationships, it really has nothing to do
> with my question):
> create table foo (
> fooid int identity(1, 1) not null ,
> foolistofthingoldids varchar(500)) -- relate to thingoldid
> create table things (
> thingoldid int identity(1, 1) not null ,
> thingnewid uniqueidentifier not null)
> -- and this is the new table to replace the comma values in foo
> create table foothings (
> fooid int not null,
> thingnewid uniqueidentifier not null)
> it should be noted that i have a user defined split function that can
> turn a comma separated list of values into a single column table, with
> one row per comma separated item.
> the trick is finding a way to use it in a set based operation to fill
> foothings with rows, based on the relationships implied in the current
> foo table's foolistofthingoldids property. i'm looking for something
> like this:
> INSERT INTO foothings (fooid, thingnewid)
> SELECT f.fooid, t.thingnewid
> FROM foo AS f
> RIGHT JOIN split(f.foolistofthingoldids, ',') AS l
> RIGHT JOIN things AS t ON l.value = t.thingoldid
> except, one that actually compiles and works. but this is a sketch of
> where i have been leading my train of thought.
> thanks in advance for any help!
> jason
>|||Take a look at:
http://www.sommarskog.se/arrays-in-sql.html

> don't XXXXX about keys or relationships, it really has nothing to do
> with my question):
What is the point of writing something like that rather than just
posting the keys and constraints? To pretend that keys have nothing to
do with a problem in SQL is like saying that foundations have nothing
to do with building a house. Keys are fundamental to any data
manipulation problem. I can't figure out much just from a list of
column names and many people probably won't bother to try so I
recommend that if you want a fuller answer you include primary and
foreign keys at least.
David Portas
SQL Server MVP
--|||hmm, maybe i wasn't clear. i already have a user defined function to do
the table creation from comma separated list. i'm just having trouble
with how to use it in the statement i listed.
but the link is useful for the user defined function sers, thanks.|||i find it burdensom and unnecessary, in recreating a simplified version
of a schema to solve something syntactic. at least in some cases. in
this specific case, i'm simply struggling with how to make use of a
user defined function to get data out of an aribitrary comma separated
list, and into a column on another table.
i've read the link you posted several ws ago to come up with the
user defined function that i already mentioned having. perhaps i was
not clear with my question, but i don't need the creation of a table
from a comma separated value. i've had and used that function for a
little while now.
currently i'm trying to figure out how to make use of it in this
specific set-based statement.|||What you're asking for is a recursion. You can do so in sql2k as a cursor or
CTE in Yukon.
-oj
"jason" <iaesun@.yahoo.com> wrote in message
news:1128947946.090802.222680@.f14g2000cwb.googlegroups.com...
> hmm, maybe i wasn't clear. i already have a user defined function to do
> the table creation from comma separated list. i'm just having trouble
> with how to use it in the statement i listed.
> but the link is useful for the user defined function sers, thanks.
>|||i'll look up those topics, thanks. do you have any links or other
keywords that might propel me on my search?|||In the absence of any proper data structure to work with, here's a
solution to a similar problem that may help you:
http://groups.google.co.uk/group/mi...c314d5ea4ef14f5
David Portas
SQL Server MVP
--

Getting Return Value from system SPROC

I'm trying to learn how to write/use system sprocs for the 1st time. SYSTEM
SPROC below is a working sproc I have in the master db that returns a 1 if a
file exists and 0 if it doesn't. If I run QA CODE 1, my SYSTEM SPROC returns
the correct 1 or 0.
However, if I use my sproc as in QA CODE 2 to act on the returned value, I
get syntax errors.
How can I write QA CODE 2 so I can use an IF statement to test and act on
the returned value of my SYSTEM SPROC?
-- SYSTEM SPROC ************
CREATE procedure [dbo].[usp_FileExists]
@.physname nvarchar(260)
as
SET nocount ON
DECLARE @.i int
EXEC master..xp_fileexist @.physname,@.i out
SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END
-- QA CODE 1 ****************
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
-- QA CODE 2 ****************
IF EXEC master.dbo.usp_FileExists @.physname =
'D:\data\sql_data\myData_Data.MDF' = 0
PRINT 'Does not Exist 'You cannot directly use a SProc inside an if condition.
If you really want to call within the if condition, then create a function.
But remember that the function works because its an extended proc.
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
But again, unless you have a pressing reason to call it within the if
condition, I would suggest you call the proc the same way as the extended
proc is called within it..
Like this..
DECLARE @.RC int
DECLARE @.physname nvarchar(260)
set @.physname = 'D:\data\sql_data\myData_Data.MDF'
EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
if(@.rc = 0)
PRINT 'Does not Exist '
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||I need a little more explanation. I tried your code on my "System Sproc" and
it just returns the correct 1 or 0, not the PRINT message. So why would the
last IF test be ignored?
Should my "System Sproc" be an extended system Sproc?
If you know of any books that teach system and extended sprocs please let me
know. I'd really like to learn things like when I have to use a function vs.
sproc.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
> You cannot directly use a SProc inside an if condition.
> If you really want to call within the if condition, then create a
> function.
> But remember that the function works because its an extended proc.
> CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
> returns int
> as
> begin
> DECLARE @.i int
> EXEC master.dbo.usp_FileExists @.physname =
> 'z:\data\sql_data\myData_Data.MDF'
> ,@.i out
> return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
> end
> IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
> PRINT 'Does not Exist '
> But again, unless you have a pressing reason to call it within the if
> condition, I would suggest you call the proc the same way as the extended
> proc is called within it..
> Like this..
> DECLARE @.RC int
> DECLARE @.physname nvarchar(260)
> set @.physname = 'D:\data\sql_data\myData_Data.MDF'
> EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
> if(@.rc = 0)
> PRINT 'Does not Exist '
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||You do NOT want a 'extended' stored procedure. That is a different animal to
tally.
---
Here is a slightly revised version of the FileExists function (master..xp_Fi
leExist is an 'UnDocumented' system function. There is no guarantee it will
be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be co
nsistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx
.gbl...
>I need a little more explanation. I tried your code on my "System Sproc" an
d
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
> Should my "System Sproc" be an extended system Sproc?
>
> If you know of any books that teach system and extended sprocs please let
me
> know. I'd really like to learn things like when I have to use a function v
s.
> sproc.
>
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>
>|||thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OeixErImGHA.4696@.TK2MSFTNGP05.phx.gbl...
You do NOT want a 'extended' stored procedure. That is a different animal
totally.
---
Here is a slightly revised version of the FileExists function
(master..xp_FileExist is an 'UnDocumented' system function. There is no
guarantee it will be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be
consistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I need a little more explanation. I tried your code on my "System Sproc"
>and
> it just returns the correct 1 or 0, not the PRINT message. So why would
> the
> last IF test be ignored?
> Should my "System Sproc" be an extended system Sproc?
> If you know of any books that teach system and extended sprocs please let
> me
> know. I'd really like to learn things like when I have to use a function
> vs.
> sproc.
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>|||scott wrote:
> I need a little more explanation. I tried your code on my "System Sproc" a
nd
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
I think you ran all of the code together, so that the print statement is
inside the function. Try this instead:
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF' ,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
Run that, then run this:
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '

> Should my "System Sproc" be an extended system Sproc?
>
NO!!!

Getting out of form

I am trying to add records to SQL database, thru <form> </form> on click of button, The system is working fine, Databse table is getting updated correctly.

But screen displays the same form again while i want it to open another asp.net page i.e. buy.aspx and also generate an e-mail automatically.

I m stuck. Please help.

pcg

Relevent code of mine is as below.

<script runat="server">

void addtosalelist(Object sender, EventArgs e)
{
...
...
dbConnection.Open();
...
...
dbConnection.Close();
}

<form action="buy.aspx" method="post" runat="server">
...
...
...
<asp:Button id="button1" onclick="addtosalelist" runat="server" Text="Submit"></asp:Button>

</form>

After adding to the database do:

Response.Redirect("buy.aspx");

|||

If your submit button event handler is addtosalelist then:

using System.Net.Mail;

public void addtosalelist(object sender, EventArgs e)
{
AddToDatabase();
SendEmail();
Response.Redirect("Buy.aspx");
}

private void AddToDatabase()
{
//Add to the database
}

private void SendEmail()
{
//Send your email

string subject ="This is the subject";
string body ="This is the body";
MailMessage mm =newMailMessage("From@.test.com","to@.test.com", subject, body);
mm.IsBodyHtml =true;//Or false for plain text

SmtpClient client =newSmtpClient();
client.Host ="192.168.0.1";//IP address of mail server
client.Port = 25;//Port number
client.Send(mm);
}

|||

pcg:

<script runat="server">

void addtosalelist(Object sender, EventArgs e)
{
...
...
dbConnection.Open();
...
...
dbConnection.Close();

Response.Redirect("buy.aspx", false);

}

<form action="buy.aspx" method="post" runat="server">
...
...
...
<asp:Button id="button1" onclick="addtosalelist" runat="server" Text="Submit"></asp:Button>

</form>

|||

Dear DavidKiff,

While the Response. Redirect(); works fine, SendEmail() does not compile.

There are two problems.

1. The body of the message is to use four variables, two i.e. name and email address are to be picked from form text boxes, and one from database and one passwordto be generated. It does not compile with error New Line in constant.

2. It does not accept the MailMessage

pl see if u can help.

pcg

|||

Dear Mr. OmerKamal,

Thanks, itworks.

pcg

Getting image from DB error?

Unsure why I am getting such an error when the image is there and thesearch feature for the site is not working so that doesn't help so I'mhoping some out there can offer why I maybe getting this and help mewith getting the image from the DB.

I use the code from the starter app for retrieving an image from the DB and I get the error message:
Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'

Here is the code and I am getting the error on the red line (Return New MemoryStream(CType(result, Byte()))):

Public Overloads Function GetPhoto(ByVal UserName As String) As Stream
command.CommandText = "sp_Themes_GetUserThemeImage"
command.Parameters.Add("@.UserName", SqlDbType.VarChar, 50)
command.Parameters(0).Value = UserName

Dim result As Object = command.ExecuteScalar
Try
If result Is Nothing Then
Dim path As String =HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings("siteImageDirectory"))
path = (path + "noimageav.gif")

Return New FileStream(path, FileMode.Open, FileAccess.Read,FileShare.Read)
Else
Return New MemoryStream(CType(result, Byte()))
End If
Catch e As ArgumentNullException
Return Nothing
End Try
End Function

--this is the code from the imagehandler.ashx page--
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest

Dim userName As String
Dim stream As IO.Stream = Nothing
If ((Not (context.Request.QueryString("UserName")) Is Nothing) _
AndAlso (context.Request.QueryString("UserName") <> "")) Then
userName = [Convert].ToString(context.Request.QueryString("UserName"))
stream = (New PhotoManager).GetPhoto(userName)

'Get the photo from the database, if nothing is returned, get thedefault "placeholder" photo
'If (stream Is Nothing) Then
' stream = (New PhotoManager).GetDefaultPhoto()
'End If
' Write image stream to the response stream
Dim buffersize As Integer = (1024 * 16)
Dim buffer() As Byte = New Byte((buffersize) - 1) {}
Dim count As Integer = stream.Read(buffer, 0, buffersize)

Do While (count > 0)
context.Response.OutputStream.Write(buffer, 0, count)
count = stream.Read(buffer, 0, buffersize)
Loop
End If
End Sub

--this is how I call it from the image.aspx page--

<img src='imageHandler.ashx?username=<%# Eval("UserName") %>'style="border:2px solid white;height:40px;" alt='Thumbnail.' /
Thanks for all your help.

The image data is a null, I think. Try doing:

IF result=system.DBNULL.value then
Return nothing
ELSE
Return New MemoryStream(CType(result, Byte()))
ENDIF

|||Well, I changed things which works but still the image will not show up.

I was using the photoManager.vb file which was part of one of the starter apps but I eliminated a step and came up with this:

If ((Not (context.Request.QueryString("UserName")) Is Nothing) _
AndAlso (context.Request.QueryString("UserName") <> "")) Then
userName = [Convert].ToString(context.Request.QueryString("UserName"))
Dim connection As SqlConnection
Dim command As SqlCommand
Dim reader As SqlDataReader

connection = NewSqlConnection(ConfigurationManager.ConnectionStrings("owcConnectionString").ConnectionString)
command = New SqlCommand
command.Connection = connection
command.CommandType = CommandType.StoredProcedure
connection.Open()

'get image from db
command.CommandText = "sp_Themes_GetUserThemeImage"
command.Parameters.Add("@.UserName", SqlDbType.VarChar, 50)
command.Parameters(0).Value = userName

Try
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Do While (reader.Read)
If IsDBNull(reader.GetValue(0)) Then
Dim path As String =HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings("siteImageDirectory"))
path += "noimageav.gif"
stream = New FileStream(path, FileMode.Open, FileAccess.Read,FileShare.Read)
Dim buffersize As Integer = (1024 * 16)
Dim buffer() As Byte = New Byte((buffersize) - 1) {}
Dim count As Integer = stream.Read(buffer, 0, buffersize)
context.Response.OutputStream.Write(buffer, 0, count)
Exit Do
Else
context.Response.ContentType = reader.Item(1).ToString
context.Response.BinaryWrite(reader.Item(0))
End If
Loop
Catch e As Exception
context.Response.Write(e)
Finally
command.Dispose()
connection.Dispose()
End Try
End If

Which works just fine except that I am still not getting the image fromthe DB to show up. I don't get any error messages or anything andwhen I debug and walk through, it walks through just fine but no imagefrom the DB.

Can anyone offer some help on this?

thanks|||

You don't need to/shouldn't be looping (Do While reader.read), since you can't output multiple images that way -- Change it to "If reader.read then ...". You aren't supplying a contenttype if your image is null. If your noimageav.gif is larger than 16k, you'll truncate it. I would also issue a response.clear before you set the contenttype, and response.end after you finish writing.

Personally, I would probably just issue a response.redirect instead of opening the file, reading it and dumping it in the case that you don't have an image, but that's me.

|||Point well taken but I'm only outputting a single image and onlyretrieving a single image so no concerns about multiple images. Also, if the image in the DB is null, it then retrieves thenoimageav.gif which never changes in size and it also isn't in the DBso it is not the problem. The problem is the is the image that isstored in the DB is not showing up.

When it hits these 2 lines of code, I would expect it to return the image but it doesn't. Just shows blank.

context.Response.ContentType = reader.Item(1).ToString
context.Response.BinaryWrite(reader.Item(0))

Even though there is data. Verified this when debugged.

Any other ideas?

thanks
|||

Grab fiddler (www.fiddler.com), and with that you can see exactly what the server is sending back to you.

Like I said, i would reponse.clear before, and response.end around those 2 lines of code just to make sure the headers are getting cleared out, and not getting clobbered after.

Make sure that you close the browser you are trying to pull the image with regularly because IE seems to cache the contenttype of a page, and it's rather hard to get it to change to the new type sometimes.

|||That link doesn't have anything on it related to what you mentioned.

I did do a response.clear and response.end in my latest code but still the same thing.

So far, I've tried about 3 different ways for displaying an image froma DB. 1st from a book, second from the starter app and 3rd froman online source and none of them worked. At 1st I was thinkingthat the data but that doesn't seem to be the case.

Right now, I've come to a complete and total stand still in trying to solve this.

Any ideas out there about what it may be?

thanks

Getting error working with Transfer SQL Server Objects Task

I was trying to transfer a SQL Server 2000 database to SQL Server 2005 using SQL Server Objects Task. However, The following error message was encountered:

"[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".“

can anyone suggest me what is the error here?

Is there any other way possible to achieve this(transfer of database). The objects to be transferred are tables, views, stored procedures, user data types, and user-defined functions. The logins need not to be transferred.

The output we are Getting is:-

Start Debugging:

SSIS package "unittest2.dtsx" starting.

Error: 0xC002F325 at SQL Server-Objekte kopieren, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

Task failed: SQL Server-Objekte kopieren

Warning: 0x80019002 at unittest2: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "unittest2.dtsx" finished: Failure.

The program '[4272] unittest2.dtsx: DTS' has exited with code 0 (0x0).

You might try posting this in the SSIS forum.

Getting error working with Transfer SQL Server Objects Task

I was trying to transfer a SQL Server 2000 database to SQL Server 2005 using SQL Server Objects Task. However, The following error message was encountered:

"[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".“

can anyone suggest me what is the error here?

Is there any other way possible to achieve this(transfer of database). The objects to be transferred are tables, views, stored procedures, user data types, and user-defined functions. The logins need not to be transferred.
The output we are Getting is:-

Start Debugging:

SSIS package "unittest2.dtsx" starting.

Error: 0xC002F325 at SQL Server-Objekte kopieren, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

Task failed: SQL Server-Objekte kopieren

Warning: 0x80019002 at unittest2: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "unittest2.dtsx" finished: Failure.

The program '[4272] unittest2.dtsx: DTS' has exited with code 0 (0x0).

I think this is likely better for the management tools forum - they own the transfer objects wizard and tasks ...

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

Donald