DataType Money

Please i need to display the money column in DataBase in an asp.net page but i get something like this 786.0000 how can i format it so that i get something like 786.00

Thanx

That kind of formatting is best done at the application level.|||

You could use the ToString("c") to display as currency on your page.

Double TotalCost = 786.0000;

lblCost.Text = TotalCost.ToString("c");

Your label should now be set to $786.00

|||

If you are binding it, you can use <%# Bind("YourColumnName","{0:c}") %> and will display $786.00
If you dont want to display the currency, repace c with your own format like #0.00 at it should display 786.00

Datasets, searching, complex queries.

Alright just starting out in ASP.NET and it's making my head spin, but I think I'm getting it.

My schema in brief : Images, Categories, and a table in the middle so we can have a many-to-many relationship between images and categories. My issue is searching. I can search by keyword, and limit it to categories. So, in pseudo sql..

SELECT ... WHERE keyword LIKE '%test%' and category_id in (1,5,20,66);

I made a variable for the keyword no problem. But how can I get this dynamic list? Or, is there another way about going this problem?

Where will you put your query in? StoredProcedure or Web? I think my two posts help you:

http://forums.asp.net/t/1162993.aspx

http://forums.asp.net/t/1158548.aspx

|||

I've been informed by the big shark guy that I recommend D-SQL a lot, but you are asking for it so here goes:

First off... beware SQL injection. Strip out ' and - from your search text. Do without.

CREATE PROCEDURE sp_search @.keyword varchar(50),
@.categorylist varchar(150) as

declare @.sql varchar(max)

set @.sql = 'SELECT ... WHERE keyword LIKE ''%' + @.keyword + '%'' and category_id in (' + @.categorylist + ')' -- BTW those are double single quotes, not double quotes

EXEC sp_executesql @.sql, N'@.keyword varchar(50), @.categorylist varchar(150)'

then call sp_search in your code, adding the keyword and category list strings as parameters. Does that help?

|||

che : i'll read those links now

Charles : Interesting. Will I be able to accomplish something like this with Oracle? That looks like SQL Server to me?

|||

Oh. Most folks here are SQL Server. I'm sure you can do dynamic SQL in oracle though - my boss who taught me D-SQL co-authored the Oracle Designer/2000 Handbook. The sp_executesql is a SQL serber built-in sp, and I don't know what the equivalent is in oracle:

http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a68022/dynsql.htm

|||

This is a little disappointing. Dynamic SQL seems really overkill for such a trivial problem.

Perhaps I should look at not using strongly typed Datasets? Build one on the fly? Would that be different/better?

|||

Many of the problems were have these days tend to be ones caused by the complexity of the frameworks we are working in. For example, to write a web page, I have to know C# (or VB), ASP, HTML, SQL, and .Net, and how they all interrelate. For one page. It used to be that all you needed was a front end and a database. Oh well, we get a lot in terms of scalability and functionality out of this stuff too - the complexity is market driven.

All philosophy aside, it seems to me that most searches are done with D-Sql. If you really want to make a mountain out of a molehill, you could create a search object that safely builds your query in C# and then access it from your search front end. The advantage of that would be that you could get a lot of re-use in the future. Searching is a non-trivial issue. The volume of work that has gone into searching makes it difficult to mine for simple one-off solutions.

sql

DataSet - Should be easy but I dont know how

I am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.

This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how? Any help would be awesome.

Sub Button1_Click(sender As Object, e As EventArgs)
?? = MyQueryMethod(txtPhone.Text)

Thanks,
Matt

Dim oDataSet as System.Data.DataSet = MyQueryMethod(txtPhone.Text)
If oDataSet = Nothing Then
 ' your dataset is jacked up
Else
 ' You got a dataset to work with
End If
' Release memory allocations to the variable.
oDataSet.Dispose()

|||Simply amazing! It's so easy once someone tells you how it is. Worked like a charm...Now I'm all jacked up!!!

Much Respect!!!!

Thanks,
Matt|||Well actually, it didn't quite do as I expected but I'm close. When I execute this code, I am expecting the dataset to have no records or one record, but either way the DataSet is never nothing (at least from my testing).

Maybe I'm just going about my problem the wrong way. I am trying to validate phone number so that I don't have someone enter a duplicate key in my database. So I run an SQL select statement that returns a dataset. The dataset should have one phone number or no phone number and this is how I'm trying to ward off any duplicate keys.

Is there an easier way to prevent a duplicate key entry? Sorry, I'm new at this stuff!!|||You can code as

If oDataSet.Tables(0).Rows.Count = 0 Then
 ' your have no records
Else
 'Response.Write(oDataSet.Tables(0).Rows(0)(0).ToString())
End If

HTH|||Perfect...thanks.

databases

I am very new to asp.net, but am having trouble trying to link the asp.net page that i have created to an sql database that has been set up on a local system. Am actually trying to use the sql control to extract info from the database. Would appreciate any ideas.For Binding SQL Data to a DataGrid (or another control) try this link.
I am also new and have been studying asp.net since about last Feb. For information the internet can't be beat, but for structure and clarity, a good book works for me. Linking your controls to sql database is discussed in detail, step by step, in "Sam's Teach Yourself ASP.net in 24 hrs." I learned a lot and still grab it for reference.

Database/Query Design Help

I am designing an ASP.NET app that can be used to keep track ofattendance at office hours for a class. The purpose of this isthat we need to know if a student is attending office hours bydifferent people (so that we can flag them as "in trouble"). Idon't know if I have chosen the best database design, and I'm lost asto how to accomplish a query I need.
I have a table HoursAttendance that has the following design.
Column_Name Data_Type Length Allow_Nulls
TA char 4 n
Date smalldatetime 4 n
Start smallint 2 n
End smallint 2 n
Student1 bit 1 y
Student2 bit 1 y
Student3 bit 1 y
Student4 bit 1 y
Student5 bit 1 y
I chose to have the students as columns because the students don'tchange, and then you add rows of office hours. If students arethe rows, then you would be adding columns as the semester continuedwhich I thought was odder...? I'm completely open to suggestionson Database Design, because I really wasn't sure.
Ok, so now I need useful queries. The one that I am stuck on (andalso the first one besides select * from HoursAttendance) is that Iwant the names of Students who have attended more than x officehours. So I need something like
select <column name> from HoursAttendance where count(sum(<column name>)) > x
Granted a better table design could help with this. I'mrelatively new to design, so constructive criticism is desired please

You can't run a query like that if the students are columns. My suggestions:

Students
---
StudentID int
StudentLastName varchar(25)
StudentFirstName varchar(25)
HoursAttendance
------
HoursAttendanceID int
StudentID int
TA char(4)
AttendanceDate smalldatetime
Hours tinyint
Select StudentID, Sum(Hours)
From HoursAttendance
Group By StudentID
Having Sum(Hours) > x
Hope that helps!
Marcie

|||This is why I didn't go with something like what you suggested: It seems with the HoursAttendance table set up like that that it should be broken into an Hours table and an Attendance table.
This seems like way too many tables lol!!
Oh well, I guess.|||Could be -- what would you put in the Hours table vs. the Attendance table? For the most part, trying to cram everything into one table leads to a poor database design (and is not normalized).

This seems like way too many tables lol!!

Scoff...at work I work on a system with several hundred tables, this is nothing!
Marcie|||

There are 9 TA's that hold weekly office hours. I have a TA table and a DefaultHours table. The DefaultHours table has when the TA normally holds hours (I use this to set selectedValue in time drop down lists. 5-20 students attend each office hour. So that means there are 5-20 rows that have TA, Date, Start, End in common. Which is where I was referring to moving that to a separate table. *shrug*

|||Sure, you could put that in a separate table then:
OfficeHours
----
OfficeHourID int
TA char(4)
Date smalldatetime
Start int
End int
Attendance
----
OfficeHourID
StudentID
Marcie|||I'm just not sure as to what would be the "best" (most efficient in terms of performance and space)
|||Having the extra tables *saves* space because data isn't repeated in any one table. (Normalization) The performance difference between having one HoursAttendance table and splitting them into two would not even be measurable.
Marcie

Database 'XXX' does not exist

Hi.
I'm trying to make a asp.net page to connect to a olap cube.
When i trying to connect to my Analysis Server I receive the error:
Database 'XXX' does not exist.
My config is
-SQL Server 2000 Enterprise Edition SP3
-Analysis Services SP3
-MDAC 2.8
i already added <identity impersonate ="true" /> to web.config
My connection string is "Provider=MSOLAP.2;Data
Source=localhost;Initial Catalog=XXX;"
Thanksare you sure that the 'XXX' database exits?
what are the roles of this cube?
what is the authentication you use?
Message posted via http://www.droptable.com|||i'm sure that my database exists because i can use the MDX application (the
basic example provided with the installation of Analysis Services) to
execute queries.
I would like to succeed in querying the basic example of olap cube FoodMart
2000 in asp.net.
I use anonymous authentification
"allison via droptable.com" wrote:

> are you sure that the 'XXX' database exits?
> what are the roles of this cube?
> what is the authentication you use?
> --
> Message posted via http://www.droptable.com
>|||is IIS and AS running on the same box? otherwise this could be a classic
2-hop problem. The reason why the database might not exist is that you are
coming in anonymously and the NT Anonymous account might not have access to
the database.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Leclerc" <DavidLeclerc@.discussions.microsoft.com> wrote in message
news:FE7DA5F8-E258-4820-BB68-F3FA253ACB8E@.microsoft.com...
> i'm sure that my database exists because i can use the MDX application
(the
> basic example provided with the installation of Analysis Services) to
> execute queries.
> I would like to succeed in querying the basic example of olap cube
FoodMart[vbcol=seagreen]
> 2000 in asp.net.
> I use anonymous authentification
>
> "allison via droptable.com" wrote:
>|||Thanks, it's worked