Showing posts with label matter. Show all posts
Showing posts with label matter. Show all posts

Monday, March 19, 2012

How to cast the value in C# resulted from max() command in SQL Server Developer

Could anyone help of how to cast the value in C# resulted from max() command. To complicate the matter, from the query result I see in the Microsoft SQL Server Management Studio, if there are records the value is number. But if there are no records, the value is NULL. How do I handle these two possible different conditions?

I have tried:

- stringtest = (string)reader["MaxOrderID"];

- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);

- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();

all failed. And what do I do if the value is NULL. And also how do I do to cope with these two different possible conditions?

For example, I have the following code:

command.CommandText ="Select max(OrderID) as 'MaxOrderID' from [Order]";command.CommandType =CommandType.Text;

command.Connection = conn;

command.Connection.Open();

reader = command.ExecuteReader();

reader.Read();

? orderID = ?reader["MaxOrderID"];

Select MAX(ISNULL(OrderID,0) as MaxOrderID from [Order] try this statment and retest your three statments again...

- stringtest = (string)reader["MaxOrderID"];

- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);

- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();

|||

I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.

I can see that it should be 0.

|||

hi dedyandy,

dedyandy:

I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.

what you are getting is correct, max will return value if there's any record in table else it wont return anything i.e. its a null. you can either use 1 as default value in case there are no records or in case you've procedure then you can check something like

if @.@.Rowcount = 0 Select 1 as 'MaxOrderID'

thanks,

satish.

|||

Thanks Satish. Yes, I will use Count function first. If there are records, I will use AVG function, else return 1.

Andy.

|||

cheersYes

thanks,

Satish.

Friday, February 24, 2012

How to build search at website?

Hi! Maybe Iask simple question. But for me it's not so. The matter's I plan organizesearch at my website. It has catalog of firm's and good's in SQL Server. I planto build dictionary table with two fields' search_item and link. But problem isthat I should put all search variant's in field search_item(name of firm, nameof boss, name of good etc.) Maybe there's another concept to build search?Thank's for any answer.

Andrey.

You could take a look at FREETEXT in SQL Server. Also SQL 2005 has some % "closeness" matching (sorry I can't remember its name off the top of my head).|||

Thank you. It'suseful for me. But I'm interesting more in concepts how to build search in mycase.
Andrey.

|||

There is a recent post where someone is trying to do something kinda similiar, it's titled "No result from stored procedure". It's a stored procedure that basically allows that person to search almost any field in their row of things and get back what they want.

Unless you really want to build a search engine, where you put in a word, and it comes back with all kinds of stuff you really didn't want (Like most search engines). The above approach is better in many cases where you know what it is you want to search (Like title, author, price), rather than search everything for a specific word.

|||I would say it depends on how and what you're going to search. If you've got a limited number of fields then going along the lines you've suggested is probably going to be ok, although it can soon get messy when you consider OR or AND, sounds-like, wildcards etc. I know in the past I've used XML to form up the search condition. That way you can create a nice generic search engine, save you favourite searches, etc. In SQL you then process the SQL and support the different types of search logic.