Monthly Archives: January 2012

Amazon Alternative to selling Book

Amazon.com is the best side to sell your books online. However amazon does charges you commission for every book sold. This could be hefty if you are selling a couple of book. There is an alternative site available. The Textbooks.org allows you to sell your books at a less commission and possibly free if it is less than certain maximum amount. Check it out at textbook.org.

Difference by SQL NULL and empty string ‘ ‘

What is the difference between null and  ” (empty string)?

Null is actually a value which means something is empty (confirmed empty not just left blank).

” mean a string that has not value in it.

ALTER PROCEDURE test_null
-- Add the parameters for the stored procedure here
@ID int = null,
@name varchar(25) = null
AS
BEGIN

print 'Actual value of Name: '
print @name;

if (@name = '')
print 'Name : empty string';
else
print 'Name : Not empty string';

if (@name is null) // note @name = null is incorrect syntax but will not show any error
print 'Name : null';
else
print 'Name : Not null';

END
GO

///////////////////////////////////////////////////////////////

exec test_null @id=12

exec test_null @name=null

[Solved] Procedure or function xxx has too many arguments specified – ASP.NET

If you are getting this error in your .NET application (AsP.NET for example), it says obvious that you are passing too many parameters to the stored Procedure. However there is a scenario where this error actually makes you look in the wrong direction. Example is below

you have sqldataSource which inserts a customer into the database for example. Let say a customer is renewed and you want to store the ID of the old customer in a field “OLDID”. Now your stored procedure looks like this

InsertCustomer(
@ID int,
@name varchar(25),


@OldID int
)

In ASP.NET When you bind a value to the @OldID parameter, according to the logic you should be actually doing this

<asp/textbox id="txtOldID" text= Text='’

Note that I am updating txtOLDID field through backend programming in C# in page_load function. Now everything looks according to the book. You need to pass @oldID to the stored procedure, there is a field @oldID and you are binding your textbox to that field. But it does not work and instead throughs this exception.

In this case the error is misleading, the fix is rather change the stored procedure.

How to fix this?

Change the stored procedure so that @OldID parameter reads as @ID. Down in the store procedure, use the correct logic where there this ID needs to be inserted. Since we want to use it in place of OLDID, the logic should be follow

insert into customer (OLDID) values(ID)

Now in the ASP.NET application, change the binding to ID field rather than OLDID and everything should work fine. It is rather bizarre error.

this will give you the following error. Everything is according to the book.

How to set automatic bid in Ebay?

To set automatic bid in ebay, login, go to the item you want to bid. Do not enter you bid yet. Click the bids link which show history of the bids. At the right bottom corner, enter your bid in “Your maximum bid” and there you go, you have set your automatic bid. If another bidder goes higher than you, you bid will automatically increase, unless it reaches your highest bid. At that point you will get an email from ebay that your bid had bean beaten. At that point you can increase your maximum bid or bid manually yourself.

System.Data.SqlClient.SqlException: Error converting data type varchar to float.

If you are getting the above .NET error, one reason for this is wrong case statement. Here is an example


create table #table1 (
id int not null primary key identity,
category varchar(10),
amount float null
)

insert into #table1 values(‘A’,23)
insert into #table1 values(‘A’,23)
insert into #table1 values(‘B’,NULL)
insert into #table1 values(‘B’,0)
insert into #table1 values(‘C’,NULL)
insert into #table1 values(‘C’,NULL)
insert into #table1 values(‘D’,0)
insert into #table1 values(‘D’,0)

select * from #table1 — works

select category, sum1 — works
from
(select category, SUM(Round(ISNULL(amount,0),0)) as Sum1 from #table1
group by category) D

select category, sum2 = — does not work
case Sum1
when ‘A’ then Sum1 * 1 — Error converting data type varchar to float
when ‘B’ then Sum1 * 2
when ‘C’ then Sum1 * 3
when ‘D’ then Sum1 * 4
else Sum1
end
from
(select category, SUM(Round(ISNULL(amount,0),0) ) as Sum1 from #table1
group by category) D

 

I get System.Data.SqlClient.SqlException: Error converting data type varchar to float. for this from my C# code when i access the “does not work” query. You can see there is a problem here, the CASE statement is wronglyl written. The correct statement should be


select category, sum2 = -- does not work
case Category -- Fixed the stetement here
when 'A' then Sum1 * 1 -- no error
when 'B' then Sum1 * 2
when 'C' then Sum1 * 3
when 'D' then Sum1 * 4
else Sum1
end
from
(select category, SUM(Round(ISNULL(amount,0),0) ) as Sum1 from #table1
group by category) D