Return Available appointments

I am upgrading an application that another developer wrote. Basically, it i
s
an application used to display appointments / request appointments. The new
request is to display available appointments. That is where I need help in
figuring out how to return available time slots.
Here is what the table looks like(Again, I did not write it or develop it so
far)
CREATE TABLE [dbo].[Meetings] (
[appt_ID] [int] IDENTITY (1, 1) NOT NULL ,
[appt_StartDT] [datetime] NOT NULL ,
[appt_RequesterName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[appt_RequestEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[appt_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[appt_Room] [int] NOT NULL ,
[appt_EndDT] [datetime] NOT NULL ,
[appt_State] [int] NOT NULL ,
[appt_attendees] [int] NULL
) ON [PRIMARY]
Some sample data with column names next to the values
apptID - 1177
appt_StartDt - 11/21/2003 8:00:00 AM
appt_requesterName - User Name
appt_requesterEmail- Email@.company.com
appt_Name - Acme Electric
appt_Room - 11
appt_EndDT - 11/21/2003 12:00:00 PM
appt_State - 1
appt_attendees - 8
Currently, I display all the appointments on the web page reading the
database. With the new change, I would like to be able to see the available
appointments
so users could enter in a date range or appt_room to see what is available
and then make appointments. My problem is the query to return the avaiable
appointments.
Please let me know if you have any questions.
TIA.Hi,
Welcome to use MSDN Managed Newsgroup!
Would you please give me a expected data row for "avaiable appointments"?
How to define this? For example, given a date range, list all the meetings
in this data range?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
Thanks for the response. Yes, you are right, for a given range of days,
list all available appointments.
Here is more information.
The user would have the choice to enter day/ range of days to check for
available appointments. So if the user would want to make an appointment
2
ws from now, then the user would select that w and the result would be
all available appointments in that w. Appointments can be a full hour, 1
5
mins or a complete day.
For example:
Available appointments for 11/30/2005 are
1. Room1 9:30 AM to 10:30 PM
2. Room 1 1:45 PM to 2:15 PM
3. Room 2 9:00 AM to 10:00 AM
If the user selected a range(11/30/2005 - 12/1/2005) , then
Available appointments for 11/30/2005 are:
1. Room 1 time
2. Room 3 time
3. Room 4 time
Avaliable appointments for 12/1/2005 are:
1. Room 1 time
Hope this is helpful. Please let me know if you need more information or
have any questions.
TIA.
"Michael Cheng [MSFT]" wrote:

> Hi,
> Welcome to use MSDN Managed Newsgroup!
> Would you please give me a expected data row for "avaiable appointments"?
> How to define this? For example, given a date range, list all the meetings
> in this data range?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi,
Thanks for your clarification.
What's your concern? Below is a sample about how to select all available
for two ws later (2005-11-28 ~ 2005-12-4)
SELECT [appt_Room] , [appt_StartDT], [appt_EndDT]
FROM Meetings
WHERE @.P1 <= [appt_StartDT] AND @.P2 +1 > [appt_EndDT]
--@.P1, @.P2 indicate the inputing data ranges
I guess the key point might be you will have to make a "sub table" for each
date. If I have misunderstood your concern, please feel free to point it
out.
With T-SQL statements only, I am afraid it is not possible to list by date
like below
Available appointments for 11/30/2005 are
..
Avaliable appointments for 12/1/2005 are:
..
To accomplish this, you should impliment this with .NET in your asp.net
pages or winform.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
The SQL below will return the appt_room, start and end dates from the
meetings table whether or not if the appointment is available.
I am expecting the result to be only of the available appointments and
then have a break down by day.
Could you please clarify on the sub table for each date thing that you
mentioned.
Hope this is been clear.
Thanks.
"Michael Cheng [MSFT]" wrote:

> Hi,
> Thanks for your clarification.
> What's your concern? Below is a sample about how to select all available
> for two ws later (2005-11-28 ~ 2005-12-4)
> SELECT [appt_Room] , [appt_StartDT], [appt_EndDT]
> FROM Meetings
> WHERE @.P1 <= [appt_StartDT] AND @.P2 +1 > [appt_EndDT]
> --@.P1, @.P2 indicate the inputing data ranges
> I guess the key point might be you will have to make a "sub table" for eac
h
> date. If I have misunderstood your concern, please feel free to point it
> out.
> With T-SQL statements only, I am afraid it is not possible to list by date
> like below
> Available appointments for 11/30/2005 are
> ...
> Avaliable appointments for 12/1/2005 are:
> ...
> To accomplish this, you should impliment this with .NET in your asp.net
> pages or winform.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi,
I am sorry that I get .
Could you provide the the sample data and the expected results? I am not
sure for what situation the the appointment will be not available.
If you want to list the appointments day by day, you will have to implement
this with business logic instead of in T-SQL only.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Retrieving total number of pages

How do I - inside my report, display the total number of pages? I
looked at the expression functions and couldn't see anything to do
that.="Page " & Globals!PageNumber & " of " & Globals!TotalPages
Reeves
"Doogie" wrote:
> How do I - inside my report, display the total number of pages? I
> looked at the expression functions and couldn't see anything to do
> that.
>

Retrieving ntext column value skips values.

I have the unfortunate task of dealing with an ntext column. I have to updat
e
part of the contents but first I was just trying to display the contents in
Query Analyzer using a script from page 61-62 of the Guru's transact sql
book. Well, the script does print out a few characters, skip a few, print a
few, skip a few, . . . It appears that accessing an ntext column is quite
different than accessing a text column. BOL is not very helpful on this.
Also, READTEXT only displays a few characters at a time no matter what the
chunk size is set to, so I can't tell if the problem is Query analyzer or
something else. Does anyone have a source for useful info in dealing with
ntext?
thanks,
MichaelSnake wrote:
> I have the unfortunate task of dealing with an ntext column. I have
> to update part of the contents but first I was just trying to display
> the contents in Query Analyzer using a script from page 61-62 of the
> Guru's transact sql book. Well, the script does print out a few
> characters, skip a few, print a few, skip a few, . . . It appears
> that accessing an ntext column is quite different than accessing a
> text column. BOL is not very helpful on this. Also, READTEXT only
> displays a few characters at a time no matter what the chunk size is
> set to, so I can't tell if the problem is Query analyzer or something
> else. Does anyone have a source for useful info in dealing with
> ntext?
> thanks,
> Michael
Can you post the code you are running. There shouldn't be a problem
reading the data from an ntext column.
I tried a test using the pubs.pub_info table which I recreated as
pub_info2 using an ntext column. QA does have a display setting for the
max number of character per column to display. Or it could be that there
are line breaks in the ntext that are not displaying correctly in the QA
grid. Try using text output and see if that helps.
create table dbo.pub_info2 (pub_id char(4) not null, logo image null,
pr_info ntext)
go
ALTER TABLE [dbo].[pub_info2] ADD CONSTRAINT [UPKCL_pubinfo2] PRIMARY
KEY CLUSTERED
(
[pub_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pub_info2] ADD FOREIGN KEY
(
[pub_id]
) REFERENCES [publishers] (
[pub_id]
)
GO
insert into pub_info2 select * from pub_info
go
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info2 pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
select @.ptrval
READTEXT pub_info2.pr_info @.ptrval 0 25
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com|||>I have the unfortunate task of dealing with an ntext column. I have to
>update
> part of the contents but first I was just trying to display the contents
> in
> Query Analyzer
See http://www.aspfaq.com/2445 for some help on using UPDATETEXT.
I don't think you will need READTEXT to do what you want, see the following
(note though that it will create bogus carriage returns every 4000th
character, but existing control characters (CHAR(10,13,9 etc)) will still be
displayed correctly):
CREATE TABLE data
(
id INT UNIQUE,
txt TEXT
)
GO
SET NOCOUNT ON
DECLARE @.foo NVARCHAR(4000)
SELECT @.foo = REPLICATE('a',4000)
-- make one far > 4000 and a small one
EXEC('INSERT data(id,txt) SELECT 1,N'''+@.foo+@.foo+@.foo+@.foo+@.foo+'''')
INSERT data(id,txt) SELECT 2,N'foobar'
DECLARE
@.dLen INT,
@.nRows INT,
@.i INT,
@.rowID INT,
@.curLine NVARCHAR(4000)
SET @.rowID = 1 -- change this to see the other result
SELECT
@.i = 0,
@.dLen = DATALENGTH(txt),
@.nRows = (@.dLen / 4000) + 1
FROM data WHERE id=@.rowID
WHILE @.i < @.nRows
BEGIN
SELECT @.curLine = SUBSTRING(txt, (4000*@.i)+1, 4000) FROM data WHERE id =
@.rowID
PRINT @.curLine
SET @.i = @.i + 1
END
DROP TABLE data

Retrieving multiple rows from data base.

OK here's my question. I want to retrieve from my database employee table all those employees with the name eg. Smith and display them in a list. Can anyone give me any pointers please. I'm using VB 2005 Express Edition. So far this is what I have but it only seems to return 1 row when I know there are more than one entries with the name I am inputting

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim searchString AsString

searchString = Me.SearchStringBox.Text

Try

Dim filter AsString

filter = "LastName LIKE '" & searchString & "'"

Dim search() As System.Data.DataRow

search = myCDDataSEt.ClientData.Select(filter)

If search.Length > 0 Then

'no code as yet

Else

MessageBox.Show("The client " & searchString & "is not in the database")

EndIf

Catch ex As Exception

MessageBox.Show(ex.Message)

EndTry

EndSub

All of the SQL that I see in this is LastName LIKE... If you want help with the SQL, you need to print it out and let us look at it. Otherwise, we need to move the thread over to a VB programming group to look at.|||Sorry thought I was in another forum. !

Retrieving image from SQL database

Ok, again, I'm reasonably new to this. I've been trying to display an image stored in SQL in a ASP.NET page. Pretty simple stuff I would have thought. I've read countless examples of how to do this online, and many of them use the same method of displaying the image, but none seem to work for me. The problem seems to lie in the following line of code:

Dim imageDataAsByte() =CByte(command.ExecuteScalar())

Which always returns the error: Value of type 'Byte' cannot be converted to '1-dimensional array of Byte'.

Here's the rest of my code, hope someone can help. It's doing my head in!

Imports System.Data.SqlClient

Imports System.Data

Imports System.Drawing

Imports System.IO

PartialClass _ProfileEditor

Inherits System.Web.UI.Page

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

'Get the UserID of the currently logged on user

Dim NTUserIDAsString = HttpContext.Current.User.Identity.Name.ToString

Session("UserID") = NTUserID

Dim PhotoAs Image =Nothing

Dim connectionAsNew SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)

Dim commandAs SqlCommand = connection.CreateCommand()

command.CommandText ="SELECT Photograph, ImageType FROM Users WHERE UserID = @.UserID"

command.Parameters.AddWithValue("@.UserID", NTUserID)

connection.Open()

Dim imageDataAsByte() =CByte(command.ExecuteScalar())Dim memStreamAsNew MemoryStream(Buffer)

Photo = Image.FromStream(memStream)

EndSub

EndClass

Onwww.SingingEels.com we use images (and other files like zip files, source code etc) in our database, and we show them through an ASP.NET page just like you're trying to do.

There are a few things though with the above that are an issue:

scottishfruit:

Dim imageDataAsByte() =CByte(command.ExecuteScalar())

The problem here (as your compiler is trying to tell you) is that you are trying to assign a BYTE to a BYTE_ARRAY object... to put it in human terms... a BYTE is a pair of shoes... and a BYTE_ARRAY is a shoe store... so when your friend asks you where the nearest shoe store is, and you pointed at your shoes, he yells at you :)

That's what the compiler is doing... so the long and the short of it is... you need toCAST the results from the ExecuteScalar function to a BYTE_ARRAY... like this:

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte()) <-- (I haven't done VB in a very long time, but I think that's right).

Ok, to "read" that in human speak you would say: "Create a variable named 'imageData' which happens to be an array of bytes and assign it the value of whatever comes from the fuction 'command.ExecuteScalar()' which I know is also a byte array."

I'm sure 100 people have probably posted quick answers already, but if not... let me know if this solves your problem. (or if I lost you all together)

|||

'hey buddy chk out these link>>>

http://aspalliance.com/articleViewer.aspx?aId=140

http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp

i hope it will help u>>>

have a great day!

mark the post as answer if it helped u>>

|||

Awesome! That did the trick! (and your shoe analogy was pretty cool too)

Alas I now have another problem. This line:

Photo = Image.FromStream(memStream)

Is giving me this error: System.ArgumentException: Parameter is not valid.

Once again, I've googled this to pieces and there are heaps of solutions, but none that actually work!

Here's my code again:

Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Imports System.IO

Partial Class _ProfileEditor

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim NTUserID As String = HttpContext.Current.User.Identity.Name.ToString

Session("UserID") = NTUserID

Dim Photo As Image = Nothing

Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("PeopleConnectionString").ConnectionString)

Dim command As SqlCommand = connection.CreateCommand()

command.CommandText = "SELECT Photograph, ImageType FROM Users WHERE UserID = @.UserID"

command.Parameters.AddWithValue("@.UserID", NTUserID)

connection.Open()

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte())

Dim memStream As New MemoryStream(imageData)

Photo = Image.FromStream(memStream)

End Sub

End Class

|||

Well, this isn't really an answer to your "what's with the argument exception" error... but I think you're almost done... there's no need to create a MemoryStream, or an Image... at this point, you already have the image data (as you so nicely named your variable)... so all that remains is for you to send that data out to the user!

scottishfruit:

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte())

Dim memStream As New MemoryStream(imageData)

Photo = Image.FromStream(memStream)

End Sub

End Class

Change the above... to this:

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte())

Response.OutputStream.Write(imageData, 0, imageData.Length)

Response.AddHeader("content-type", "image/jpeg")

Response.End()

End Sub

End Class

I know the image might not always be a JPEG... so you can leave that part out, but it's fine to use even if the image is a GIF, BMP or whatever... but if you choose to leave it out, some browsers may not appreciate that you are trying to send a picture of some kind :)

Enjoy (and when you're done... mark one of these posts as the "answer" so the thread is closed)

Retrieving data from SqlDataSource (old school)

Hey,

I need to retrieve info from a database and display it using a repeater control. No problems there! But, I need to add data before displaying it, and I don't mean add data to the database but rather to the repeater control. For eaxample:

I have a simple database containing two fields: [date] and [event]. The repeater will display these events in a monthly view. That is: the repeater will have 31 rows and the events will be displayed next to the day it happens. Now if there's nothing happening a certain day then I need to add that day manually because it will not be bound, right! See my problem?

In other words, how do i loop through a records when using the SqlDataSource?

Thanks,
Bj?rn Andersson

You can use the split function (Described in this forum somewhere on one implementation of it), or create a table with 31 rows in it, and left join it to your query. Then you'll get back 31 rows (or more) every time.

Retrieving data from SQL server table to display on button on datagrid table.

I have nine type of buttons,

EnrollAmtBTM

PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button

this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION

THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITION

protectedvoid CollectAgencyInformation()

{

WebLibraryClass ConnectionFinanceDB;

ConnectionFinanceDB =new WebLibraryClass();

string SQLCONDITION ="";

string RUN_SQLCONDITION ="";

SessionValues ValueSelected =null;

int CollectionCount = 0;if (Session[Session_UserSPersonalData] ==null)

{

ValueSelected =new SessionValues();

Session.Add(Session_UserSPersonalData, ValueSelected);

}

else

{

ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]);

}

ProcPaymBTM.Visible =false;PaymenLstBTN.Visible =false;

Dataviewlisting.ActiveViewIndex = 0;

TreeNode SelectedNode =new TreeNode();

SelectedNode = AgencyTree.SelectedNode;

SelectedAgency = SelectedNode.Value.ToString();

Agencytxt.Text = SelectedAgency;

Agencytxt2.Text = SelectedAgency;

Agencytxt3.Text = SelectedAgency;

DbDataReader CollectingDataSelected =null;

try

{

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency +"'");

}

catch

{

}

DataTable TableSet =new DataTable();

TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);

int IndexingValues = 0;foreach (DataRow DataCollectedRowin TableSet.Rows)

{

if (IndexingValues == 0)

{

SQLCONDITION ="where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() +"'";

}

else

{

SQLCONDITION = SQLCONDITION +" OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() +"'";

}

IndexingValues += 1;

}

SQLCONDITION = SQLCONDITION +")";

ConnectionFinanceDB.DisconnectToDatabase();

if (Dataviewlisting.ActiveViewIndex == 0)

{

Dataviewlisting.ActiveViewIndex += 1;

}

else

{

Dataviewlisting.ActiveViewIndex = 0;

}

SelectedAgency = SQLCONDITION;

ValueSelected.CONDITION = SelectedAgency;

?? this is where i use to get count where in other buttons and are displayed... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the view

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

try

{

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

catch

{

}

////this is myCollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg.

"SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List"

here is the function

publicint CollectedFinaceDataCount(String SQLStatement)

{

int DataCollection;

DataCollection = 0;

try

{

SQLCommandExe = FinanceConnection.CreateCommand();

SQLCommandExe.CommandType = CommandType.Text;

SQLCommandExe.CommandText = SQLStatement;

ConnectToDatabase();

DataCollection = (int) SQLCommandExe.ExecuteScalar();

DisconnectToDatabase();

}

catch (Exception ex)

{

Console.WriteLine("Exception Occurred :{0},{1}",

ex.Message, ex.StackTrace.ToString());

}

return DataCollection;

}

So here mu requirement request is to display only the value fronm the view i have against the agency selected

Please help ASAP

Thanks

Santosh

I am getting to display the values

But the problem is that the table has 9 type of payments,

enrollment, placement, WPR, retention 1 month, retention 3 month ,retention 6 month, replacement bonus, satis complete,

Now there are different amouht agains the agency and type of payment above,

So i need do write a for loop can anyone help me

My statement is below ans SQL condition as mentioned in earlier code above posted fetches the value agains each agency, but not against each payment.

my buttons are

EnrollAmtBTM.Text

WPRAmtBTM.Text

PlacAmtBTM.Text

SatisCompAmtBTM.Text

Reten1AmtBTM.Text

Reten3AmtBTM.Text

Reten6AmtBTM.Text

EnrollBonusAmtBTM.Text

and finally

RePlacBonusAmtBTM.Text

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

try /////this is where i need the C# for loop

{

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

catch

{

}

|||

I am trying to something like this using switch case but still being a newbie i have no idea please help.

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

switch(Dataviewlisting.GetType(Payment_Description).ToString() )

{

case ("Enrollment(5 Days)"):

EnrollAmtBTM.Text = CollectionCount.ToString();

break;case ("Placement"):

PlacAmtBTM.Text = CollectionCount.ToString();

break;

case ("Work Participation"):

PlacAmtBTM.Text = CollectionCount.ToString();

break;case ("Satisfactory Complete"):

PlacAmtBTM.Text = CollectionCount.ToString();

break;

default:

case ("Enrollment(5 Days)"):break;

}

|||

Can any body help me with FOR LOOP for this wolode objective of fetching data the whole view has 81 records....9 type agains each type of payment..so i need for loop

Like

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

Foreach........

If statement

then EnrollAmtBTM.Text= value blah blah ...

something like thsi for the first and second posts i hav made here

|||

I am using something like this but still no luck can anybody help...ASAP

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

foreach (V_Payment_Amount_List Rowsin DetailDataList.Rows)

{

if (Rows.EnrollAmtBTM.Text =="EnrollAmtBTM".ToString())

{

Rows["PaymentDescription"] = EnrollAmtBTM.Text;

}

}

// try

// {

// CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

// EnrollAmtBTM.Text = CollectionCount.ToString();

// }

// catch

// {

// }

Thanks,

George

|||

I am trying something like this too but still there is error.My syntax itself is wrong or I am not sure please help,

As you may know from the very first post what i am trying to do...or please ask me if any doubt??

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

foreach(DataRow Paymentin TABLE1.Rows)

{

if (Rows.EnrollAmtBTM.Text == Payment["Enrollment(5 Days)"].ToString())

{

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

}

|||

I am trying to do like this

collecting the data in datatable then displaying then against the payment description but

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT Payment_Amount_Budgeted,Payment_Description,Project_ID FROM V_Payment_Amount_List") + SQLCONDITION;

DataTable Payment =new DataTable();

int CollectionCount = 0;

Payment.Load(CollectingDataSelected, LoadOption.Upsert);

foreach (DataRow DataCollectedRowin Payment.Rows)

{

if (e.Row.Cells[2].Text == Payment["Payment_Description"].ToString()) //here i need help for bringing in the type of payment ie. "enrollment" placement etc to display on EnrollAmtBTM.Text and PlacNotPaidBTM.Text respectively

{

EnrollAmtBTM.Text = CollectionCount.ToString();

}

}

|||

still error is comming

when i use the following code

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData(("SELECT Payment_Amount_Budgeted,Payment_Description,Project_ID FROM V_Payment_Amount_List") + SQLCONDITION);

DataTable Payment =new DataTable();

int CollectionCount = 0;///pointing here

Payment.Load(CollectingDataSelected, LoadOption.Upsert);

foreach (DataRow DataCollectedRowin Payment.Rows)

{

if (DataCollectedRow.ToString() == Payment["Payment_Description"].ToString())

{

PaymentData.ToString() = DataCollectedRow["Enrollment(5 Days)"].ToString();

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(CollectingDataSelected);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

}

|||

I resolved this by the following code fetching the data to a table payment and then using for loop against all 9 type of payments.

ConnectionFinanceDB.DisconnectToDatabase();

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT Payment_Amount_Budgeted, Payment_Description FROM dbo.V_Payment_Amount_List " + SQLCONDITION);DataTable Payment =new DataTable();

Payment.Load(CollectingDataSelected, LoadOption.Upsert);

foreach (DataRow DataCollectedRowin Payment.Rows)

{

if (DataCollectedRow["Payment_Description"].ToString() =="Enrollment(5 Days)")

{

EnrollAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Placement")

{

PlacAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Work Participation")

{

WPRAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="30 days Retention")

{

Reten1AmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="3 Months Retention")

{

Reten3AmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="6 Months Retention")

{

Reten6AmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Enrollment Bonus")

{

EnrollBonusAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Re-Placement Bonus")

{

RePlacBonusAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Satisfactory Complete")

{

SatisCompAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

}

Retrieving Data from database

Hi,
I have relatively less experience to SQL. I had a question. Say I have to
display certain records on a datagrid. this datagrid is dependent on these
parameters.
suppose a user enters a partial value in a text box. eg: "12"
I make use of "like" feature(...partID like '12%') in the query and it
retrieved "3" records from the database.
using the resultset I have to retrieve 5 records prior and 5 records after
the "original" set of results(3)... and display the total records (5+3+5 = 13) on the datagrid.
whats the best way of doing this... I have no clue of how to do this... hope
I have conveyed my idea properly...
Place advice,
Stephen> using the resultset I have to retrieve 5 records prior and 5 records after
> the "original" set of results(3)... and display the total records (5+3+5 => 13) on the datagrid.
You need to define what "prior" and "after" mean. Perhaps you are used to
Excel or Access, but in SQL Server, a table is an unordered set of rows. To
obtain the 5 rows "before" and "after" a certain row, you need to tell us
how you determine which rows come before and after...
--
http://www.aspfaq.com/
(Reverse address to reply.)

Retrieving an Image from SQL Server

Hello and thanks for taking a moment. I am trying to retrieve and display an image that is stored in SQL Server 2000. My aspx code is as follows:

<HTML>
<HEAD>
<title>photoitem</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="DataGrid3" HorizontalAlign='Left' runat="server" AutoGenerateColumns="true"
Visible="True">
<Columns>
<asp:TemplateColumn HeaderText="Image">
<ItemTemplate>
<asp:Image
Width="150" Height="125"
ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "InventoryItemPhoto")) %>'
Runat=server />
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</HTML>

-----------------------------------------------------------------------------------My code behind file is below VS 2003 does not like my datareader. It says the following:

'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'Items'

If there are any suggestions as to what I am doing wrong I would appreciate the input. - Jason

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;

namespace ActionLinkAdHoc
{
/// <summary>
/// Summary description for photoitem.
/// </summary>
public class photoitem : System.Web.UI.Page
{
string connStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
protected System.Web.UI.WebControls.DataGrid DataGrid3;
private void Page_Load(object sender, System.EventArgs e)
{
// Get the querystring ID
string item =Request.QueryString["ID"];
int ID=Convert.ToInt32(item);

SqlConnection dbConn5 = new SqlConnection(connStr);
SqlCommand sqlCom5 =new SqlCommand("sp4TWRetrieveItemPhotos");
sqlCom5.Connection = dbConn5;
sqlCom5.CommandType = CommandType.StoredProcedure;
sqlCom5.Parameters.Add("@.ID", SqlDbType.Int);
sqlCom5.Parameters["@.ID"].Value =ID;
dbConn5.Open();
SqlDataReader myDataReader;
myDataReader = sqlCom5.ExecuteReader(CommandBehavior.CloseConnection);
DataGrid3.DataSource = sqlCom5.ExecuteReader();
DataGrid3.DataBind();
while(myDataReader.Read())
{
Response.ContentType = myDataReader.Items("JPEG");
Response.BinaryWrite(myDataReader.Items("InventoryItemPhoto"));
}
dbConn5.Close();

}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
}

The problem is with your code here:

myDataReader.Items("JPEG")

The .Items property is VB syntax. You need to use C# syntax - myDataReader("JPEG")

That should clear up your compile problem.

Retrieving an Image from SQL Server

Hello and thanks for taking a moment. I am trying to retrieve and display an image that is stored in SQL Server 2000. My aspx code is as follows:

<HTML>
<HEAD>
<title>photoitem</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="DataGrid3" HorizontalAlign='Left' runat="server" AutoGenerateColumns="true"
Visible="True">
<Columns>
<asp:TemplateColumn HeaderText="Image">
<ItemTemplate>
<asp:Image
Width="150" Height="125"
ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "InventoryItemPhoto")) %>'
Runat=server />
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</HTML>

-----------------------------------------------------------------------------------My code behind file is below VS 2003 does not like my datareader. It says the following:

'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'Items'

If there are any suggestions as to what I am doing wrong I would appreciate the input. - Jason

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;

namespace ActionLinkAdHoc
{
/// <summary>
/// Summary description for photoitem.
/// </summary>
public class photoitem : System.Web.UI.Page
{
string connStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
protected System.Web.UI.WebControls.DataGrid DataGrid3;
private void Page_Load(object sender, System.EventArgs e)
{
// Get the querystring ID
string item =Request.QueryString["ID"];
int ID=Convert.ToInt32(item);

SqlConnection dbConn5 = new SqlConnection(connStr);
SqlCommand sqlCom5 =new SqlCommand("sp4TWRetrieveItemPhotos");
sqlCom5.Connection = dbConn5;
sqlCom5.CommandType = CommandType.StoredProcedure;
sqlCom5.Parameters.Add("@.ID", SqlDbType.Int);
sqlCom5.Parameters["@.ID"].Value =ID;
dbConn5.Open();
SqlDataReader myDataReader;
myDataReader = sqlCom5.ExecuteReader(CommandBehavior.CloseConnection);
DataGrid3.DataSource = sqlCom5.ExecuteReader();
DataGrid3.DataBind();
while(myDataReader.Read())
{
Response.ContentType = myDataReader.Items("JPEG");
Response.BinaryWrite(myDataReader.Items("InventoryItemPhoto"));
}
dbConn5.Close();

}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
}

This seems to be a duplicate post, let's focus on the other one

Retrieving all user rights in SQL 2000/2005

How to retrieve all users (local and domain) in SQL and display there
rights in roles, SUID, database, etc.?Hello,
Take a look into sp_helplogins and sp_helprotect system stored procedures
in books online.
Thanks
Hari
<paul.leistra@.gmail.com> wrote in message
news:1175670638.835844.157430@.p77g2000hsh.googlegroups.com...

> How to retrieve all users (local and domain) in SQL and display there
> rights in roles, SUID, database, etc.?
>

Retrieve Images from SQL db: Code Problem.

Hi,
I want to get an image from a sql server database and display it with an asp:image control. I use C# in MS Visual Studio .Net 2005 and Sql server 2005.
All I've done is:

// and Page Display_image.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
try
{

SqlConnection Con = new SqlConnection(
"server=localhost;" +
"database=;" +
"uid=;" +
"password=;");

System.String SqlCmd = "SELECT img_data FROM Image WHERE business_id = 2";

System.Data.SqlClient.SqlCommand SqlCmdObj = new System.Data.SqlClient.SqlCommand(SqlCmd, Con);

Con.Open();

System.Data.SqlClient.SqlDataReader SqlReader = SqlCmdObj.ExecuteReader(CommandBehavior.CloseConnection);

SqlReader.Read();

System.Web.HttpContext.Current.Response.ContentType = "image/jpeg";

// I write this:
System.Web.HttpContext.Current.Response.BinaryWrite((byte[])SqlReader["img_data"]);

// Or this:
//System.Drawing.Image _image = System.Drawing.Image.FromStream(new System.IO.MemoryStream((byte[])SqlReader["img_data"]));

//System.Drawing.Image _newimage = _image.GetThumbnailImage(100, 100, null, new System.IntPtr());

//_newimage.Save(System.Web.HttpContext.Current.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);

Con.Close();

}
catch (System.Exception Ex)
{

System.Web.HttpContext.Current.Trace.Write(Ex.Message.ToString());
}
}

Both work the same way. I mean the image is displayed well but when I view code of the web page I see this:

...
GIF89aå o ÷ó å݉97)HS ¢ ?x L£0¬B´ç¬D(áâK? ?ô² –I€8`È® û l1ã?#K?L( ?*\X±‰ Î"«Mè± ??
...

TOO MUCH characters before the html tag. And any code of the master page used for this page does not work as well!

Can anyone help me with this? I've tried for 2 days but I still fail.

Thanks.

This should help:

Dim

drAs SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

Response.AddHeader(

"Content-type", dr("MimeType"))

Response.AddHeader(

"Content-Disposition","inline; filename=""" & dr("Filename") &"""")Dim buffer()AsByte = dr("Data")Dim blenAsInteger =CType(dr("Data"),Byte()).Length

Response.OutputStream.Write(buffer, 0, blen)

Response.End()

|||Hello Motley,

I've tried your comment, but it stays the same. The image is displayed, but plenty of charaters in the page source still appears.

I guess it's because of the 'response.outputstream.write()' command, so all the image's byte data has been writen to the page's code.

Can you find any way to replace 'outputstream.write()' or some changes to stop those disgusting characters?

Thank you for your help,
maivangho.

Retrieve Image from SqlServer

hi all

i hav a database with some images...images r stored in binary data form

i want to retrieve that image from database and display it in a details View

im inserting images to database like this

public

void OnUpload(Object sender,EventArgs e)

{

int len = Upload.PostedFile.ContentLength;byte[] pic =newbyte[len];

Upload.PostedFile.InputStream.Read (pic, 0, len);

SqlConnection connection =newSqlConnection ("integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbAsoftWeb;Data Source=ASP");try

{

connection.Open ();

SqlCommand cmd =newSqlCommand("insert into tblStock " +"(Image, Image_Data,[DESC],PRICE,BAL_QTY,PV) values (@.pic, @.text, @.lblProd, @.lblPrice, @.lblPV, @.lblQty) ", connection);

cmd.Parameters.Add(

"@.pic", pic);

cmd.Parameters.Add(

"@.text", Comment.Text);

cmd.Parameters.Add(

"@.lblProd", txtProdName.Text);

cmd.Parameters.Add(

"@.lblPrice", txtPrice.Text);

cmd.Parameters.Add(

"@.lblPV", txtPV.Text);

cmd.Parameters.Add(

"@.lblQty", txtQty.Text);

cmd.ExecuteNonQuery ();

}

finally

{

connection.Close ();

}

im able to get the image in to form but i want to display it in a detailsview

how cani do it..can any one explain me

thanks in advance

Hari

Hi

Check those articles:

Image_In_DetailsView

Image_in_Details_View_part_2

Hope it helps.

Retrieve Count from stored procedure and display in datagrid.

Hi Guys,

I have a sql procedure that returns the following result when I execute it in query builder:

CountE ProjStatus

6 In Progress

3 Complete

4 On Hold

The stored procedure is as follow:

SELECT COUNT(*) AS countE, ProjStatus
FROM PROJ_Projects
GROUP BY ProjStatus

This is the result I want but when I try to output the result on my asp.net page I get the following error:

DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.

Source Error:

Line 271: </asp:TemplateColumn>Line 272: <asp:TemplateColumn>Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>Line 274: </asp:TemplateColumn>Line 275: </columns>

My asp.net page is as follows:

<script runat="server">

Dim myCommandPSAsNew SqlCommand("PROJ_GetProjStatus")

' Mark the Command as a SPROC

myCommandPS.CommandType = CommandType.StoredProcedure

Dim numasinteger

num =CInt(myCommand.ExecuteScalar)

'Set the datagrid's datasource to the DataSet and databind

Dim myAdapterPSAsNew SqlDataAdapter(myCommandPS)

Dim dsPSAsNew DataSet()

myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS

dgProjSumm.DataBind()

myConnection.Close()

</script>

<asp:datagridid="dgProjSumm"runat="server"

BorderWidth="0"

Cellpadding="4"

Cellspacing="0"

Width="100%"

Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"

Font-Size="xx-small"

AutoGenerateColumns="false">

<columns>

<asp:TemplateColumnHeaderText="Project Summary"HeaderStyle-Font-Bold="true">

<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem,"ProjStatus" ))%></itemtemplate>

</asp:TemplateColumn>

<asp:TemplateColumn>

<itemtemplate> <%# DataBinder.Eval(Container.DataItem,"Count" )%></itemtemplate>

</asp:TemplateColumn>

</columns>

</asp:DataGrid>

Please help if you can Im havin real trouble here.

Cheers

Since you have the count aliased as countE, it should be:

<%# DataBinder.Eval(Container.DataItem, "countE" )%>

|||

Thanks for pointing that out, I changed it and get the same error, any other ideas, do I have to create an output parameter or something.

Cheers

|||

Why are you using ExecuteScalar and a DataSet? Also, where is the SQL Connection as I didn't see that in your code?

Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
myCommandPS.CommandType = CommandType.StoredProcedure

Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()

Retrieve and display image inside an html file (stored in database) in binary format

Hi All,

I am not sure whether this is the right place to post this question. But I am unable to figure out what is the best solution to retrieve and display an image in a html file(stored in varbinary(max) column). I have a list of images in the file and I am supposed to display them. Can anybody please let me know what is the best way to do this?

Thanks a lot!!

HTML image links need to point to a URL. This means you can't just insert the image into the web form. If you have the images stored in a database, you will need to add another web page that will act like the image.

All you'll need in the new webpage is some code to get the image from the database as a byte array. Then write that out that image in the On_PageLoad as follows:

 
Dim img()As Byte' You'll need to add a function that will load the image into the byte array. It will pass in the id passed through the URL. img=LoadImage(Request.QueryString("id"))' Output the image and set the content type to jpeg Response.ContentType ="image/jpeg" Response.Expires = -1 Response.BinaryWrite(img) Response.End()
 

If the above was in a page called getImage.aspx you could link to it like this

<img src="http://pics.10026.com/?src=getImage.aspx?id=2" />

Hopefully that can get you pointed in the right direction. Let us know if you have any other questions.