MySQL: How to reset AUTO_INCREMENT value of a table

In mysql, to reset value of an AUTO_INCREMENT column in a table, use following query,

ALTER TABLE tablename AUTO_INCREMENT = 1

Poll: What is Your Preferred Browser?

ASP.NET – Function to Fill Month, Date and Year Dropdown lists

Recently I was developing an application where I required to display three drop-down lists Month, Date and Year for accepting users Date of Birth. I have created three functions which will fill these three drop-down list. I have used ASP.NET 3.5 (c#) to create this example application, download it and use those functions in your application.

Though I asked my client to add a calendar control instead but he was not ready, so I have created these functions to easily fill these drop-down lists.

Let me know if you have any difficulty or problem in using these functions in your application.

Download Demo Application

.NET – Translate color in .net from HTML colors

I got to know about a new function using which we can get colors from HEX color in .net as below:

lblMyLabel.ForeColor =  System.Drawing.ColorTranslator.FromHtml("#FF6633");

I have used it for Label control, whereas you can use it in the same way for other controls. This is really helpful function for me, I thought it may be useful for other too, so sharing it here

Google Chrome – StumbleUpon submit and stumbling buttons

This is for all of you (including me) who want a StumbleUpon toolbar in Google Chrome. I am big fan of StumbleUpon, I spent hours on clicking that small stumble button in firefox. Firefox is awesome but Chrome is faster.

StumbleUpon recently made a virtual toolbar for Chrome, but that’s not enough for a stumbleupon addict like me, because going on thier ite again and again to stumble is time consuming rather than a small stumble button on your browser window. So I just applied some simple tricks (may be you aware with those) and its done. Basically I need two things from StumbleUpon for chrome, which are

• A button in the bookmark bar with which I can stumble and
• another button in the bookmark bar with which I can submit the website (which I am viewing currently) to StumbleUpon.

I have achieved both of these. StumbleUpon provides a url code http://www.stumbleupon.com/s/ when you enter this url in browser, it will search and display a new website. To start stumbling in chrome just add a new page to bookmark bar, by right clicking on bookmark bar and sleect “Add Page” option. Then in the Name type anything (I typed “Stumble”) you like and in the next URL field enter http://www.stumbleupon.com/s/ URL, click OK to save and you’re done. Now click this button again and again and StumbleUpon will suggest new websites.

Now the next is submit current website to StumbleUpon, to achieve this add a new page in the bookmark bar (same as above) Type name (Submit to Stumble) and in th URL field type following javascript code,

javascript:document.location.href=’http://www.stumbleupon.com/s/’

+document.location.href;

(Type the above code in single line, I have divide it to fit in my blog’s content area)

Suppose you’re visiting the http://www.google.com/ then just click Submit to Stumble in button bookmark bar and StumleUpon will open the submit interface for you

This works for me, very well. I am waiting for StumbleUpon toolbar from StumbleUpon developers, but till then not worries, I have the two button which I use mostly. Take your time guyz.

ASP.NET – How to send email in asp.net 2.0

Emails are the important part of any web application to make an interaction between website and end users. I got many queries from my friends for “How to send emails in ASP.NET”, so I have created a sample email sending application in asp.net to give you an idea about email sending in asp.net. So take a look at the how to make an application which will send emails.

First of all setup your mail server settings in web.config file, so that you’ll need not to define the mail server settings every time in code. In the web.config file define the <system.net> tag to define the mail server settings as below:

<!–Mail Server–>

<system.net>
<mailSettings>
<smtp deliveryMethod=”Network”>
<network host=”your host name” port=”25″ userName=”" password=”" defaultCredentials=”false”/>
</smtp>
</mailSettings>
</system.net>

In the above settings change your host name in <network> tag. Port number usually default 25, but it depends on your mail server settings, add you username and password for mail server.

After setting up your mail server settings in web.config file, create a page from where you want to send emails. I have created default.aspx page from where I’ll send emails. In this page I have created a sample interface for sending emails, with some common fields, from, to, subject, and body of email with a Send Mail button. On click of the button I am executing the following code.

if (sendMail(txtfrom.Text, txtto.Text, txtsubject.Text, txtbody.Text))
{
lblmsg.Text = “Mail Sent”;
}
else
{
lblmsg.Text = “Some error has been occured, please try again!”;
}

In the above code I am executing a sendmail function in if condition, sendmail function returns a bool type (true or false) value. If emails sent successfully then it will return true else return false. On the basis of that bool value returned by the sendmail function I am displaying the error message if email sending is failed. Sendmail function is defined in this default.aspx file, just download the sample app below and look by yourself, if have any problem please comment below.

This is a simple mail sending application with minimal options you can also use CC, BCC, attachments etc, just explore more options from this (http://www.systemnetmail.com/) site which is specially made for describing the email sending method and function in ASP.NET 2.0.

Download the sample application here: DOWNLOAD

Fun – website development hip-hop style

Introduction

Hi, I found this video while surfing on YouTube and its pretty interesting, will tell you all the basic concept of developing a website in today’s world. Watch and listen it carefully (lyrics are included below). Have fun

Lyrics

Your site design is the first thing people see
it should be reflective of you and the industry
easy to look at with a nice navigation
when you can’t find what you want it causes frustration
a clear Call to action to increase the temptation
use appealing graphics they create motivation
if you have animation
use with moderation
cause search engines can’t index the information
display the logos of all your associations
highlight your contact info that’s an obligation
create a clean design you can use some decoration
but to try to prevent any client hesitation
every page that they click should provide and explanation
should be easy to understand like having a conversation
when you design the style go ahead and use your imagination
but make sure you use correct color combinations
do some investigation, look at other organizations
but don’t duplicate or you might face a litigation
design done, congratulations but it’s time to start construction
follow these instructions when you move into production
your Photoshop functions then slice that design
do your layout with div’s make sure that it’s aligned
please don’t use tables even though they work fine
when it come to indexing they give searches a hard time
make it easy for the spiders to crawl what you provide
remove font type, font color and font size
no background colors, keep your coding real neat,
tag your look and feel on a separate style sheet
better results with xml and css
now you making progress, a lil closer to success
describe your doctype so the browser can relate
make sure you do it great or it won’t validate
check in all browsers, I do it directly
gotta make sure that it renders correctly
some use IE, some others use Flock
some use AOL, I use Firefox
title everything including links and images
don’t use italics, use emphasis
don’t use bold, please use strong
if you use bold that’s old and wrong
when you use CSS, you page will load quicker
client satisfied like they eating on a snicker
they stuck on your page like you made it with a sticker
and then they convert now that’s the real kicker
make you a lil richer, your site a lil slicker
design and code right man I hope you get the picture
what I’m telling you is true man it should be a scripture
if it’s built right you’ll be the pick of the litter
everyone will want to follow you like twitter
competition will get bitter and you’ll shine like glitter
if you trying to grow your company will get bigger
design and code right man can you get with it

Fun – Wedding In SQL Style

Introduction

Smile please, hey guys I received this query through email from one of my friend. It looks great and funny; I am sharing it with you. Dedicated to all techies

Wedding Sql Query

CREATE PROCEDURE MyMarriage
BrideGroom Male (25),
Bride Female (22)
AS
BEGIN
SELECT Bride FROM India_Brides
WHERE FatherInLaw = ‘Millionaire’
AND Count(Car) > 20   AND HouseStatus=’ThreeStoreyed’
AND BrideEduStatus IN (B.TECH ,BE ,Degree ,MCA ,MBA)
AND HavingBrothers = NULL
AND Sisters = NULL
SELECT Gold, Cash, Car, BankBalance FROM FatherInLaw
UPDATE MyBankAccout SET MyBal = (MyBal + FatherInLawBal)
UPDATE MyLocker SET MyLockerContents = (MyLockerContents + FatherInLawGold)
INSERT INTO MyCarShed VALUES (’BMW’)
END
GO

Then the wife writes the below query:

DROP Husband;
COMMIT;

Have a nice & smiley day!

SQL – Step By Step Installation of SQL Server 2008

Step by step installation of Microsoft SQL server 2008.

read more | digg story

ASP.NET – Connecting asp.net to mysql database

Introduction

MySQL is the most scalable and widely used database server on web. Mostly applications I have seen are using mysql as a database server due to its fast speed (I suppose), and easy to use features, and the most important thing is its open source. You need not to take any license for it, its totally FREE and also easy to setup. In this article we’ll learn how to connect mysql database with asp.net.

Why to connect asp.net with mySql

The question is why to connect mysql with asp.net if we already having a giant sql with asp.net. So the answer is “As I am an asp.net developer, mostly I worked with SQL, but as the popularity of mysql, force me to just give a try to mysql. So I am curious to know that can we connect mysql with asp.net. To get answer of my curiosity I installed wapserver (which contains php, mysql and apache) it in my system, and found that mysql is almost same as sql and easy to use.

One feature which I have noticed about mysql and its best according to me and in comparison of SQL is FULLTEXT searching in mysql. FULLTEXT searching is quite easy and fast in mysql than SQL server. In mysql just create a FULLTEXT type index and here you are ready to use it, whereas in SQL you have to create FULLTEXT by populating it which will take too much time if you have around 4lakh records in your table. In mysql it’s a matter of just 10-15 seconds. Just create FULLTEXT index and you are ready to search your table.

I am not saying that due to some of the easy to use features of mysql, you need to move from sql to mysql. Both are right in their area’s, SQL Server is also a very good database server which is mostly used in many large software’s, in data ware houses etc. You can’t ignore SQL. I am also not going to work with mySql for always. But the point is if you are a web developer you should be aware with latest in market, and I thought let’s try to connect mysql with asp.net.

How to install mysql and how to create a table?

As we are going to connect mysql server with asp.net, first we need a mysql database. To install mysql I have used wapserver which is a package of php, mysql server and apache server , downloaded the latest version of WAMP Server from http://www.wampserver.com and install it.

Normally IIS Server is installed in ASP.NET developers PC’s, so, make user that if your IIS is running the stop IIS server because mysql uses apache (apache will be installed by wapserver setup) server to run, And what I seen is IIS and Apache will not able to run simultaneously. So better to stop IIS Server

Then open your browser and type http://localhost/phpmyadmin/ and press enter. It will open the phpmyadmin interface which we use to interact with mysql server. It looks like as below;

After opening it create a database, using the Create new database, Enter your database name and click create button.

Above action will create a database and give you a information message something like below:

Now create tables in your database using the “Operations” tab given in the top navigation bar

Clicking the “Operations” tab will open the interface like below, enter the table-name and the number of fields you would like to have in the table. My table name is “users” and it will contain two columns “Id” and “Name”, click “Go” button after entering the name and fields.

Interface like below will open. Enter the field name and select the proper data type you want.

In SQL we have Identity type of columns, here in mysql we have the same ting but with different name, they are called as “auto_increment” columns, in the above interface when you scroll your browser horizontally you’ll get the following filed ”Extra”, select the “auto_increment” option from the Extra field. Then select the primary key radio button, because in mysql a table can only have 1 auto_increment column and it must be your primary key.

After filling all the details properly click on save, this will create your database table named “users”

To insert record in your table click o the Insert tab in the top navigation bar. Which opens an interface for entering records, enter 4-5 records in your table.

So, we have created a mysql table. Now we have to fetch data from this table in asp.net application.

What we need to connect asp.net with mysql

As we know Microsoft is not providing support for connecting to a mysql database server in Visual Studio .NET. But I personally want to appeal Microsoft to provide embedded (as they are providing for sql) support for mysql in .net framework.

So To connect ASP.NET with mySql you need some external DLL which I have downloaded from a website (don’t remember the site name may be its mysql’s official site) and the DLL size is around 300KB which contains all methods that are used to connect mysql and asp.net. You can download latest mysql-.net connector DLL from here: http://dev.mysql.com/downloads/connector/net/ .

As I said it’s only 300KB, so I think including a 300KB DLL will not affect very much to your application.

MySQL connection methods are almost same like SQL connection methods. Just you have to put MySQL prefix in place of SQL (for example: SqlConnection, MySqlConnection and SqlDataAdapter is MySqlDataAdapter).

Steps of connecting mysql with asp.net

Follow the following steps to accomplish our task;

Here I am making connection string on click of a button. Normally in ASP.NET 2.0 we create the connection string in web.config file. So don’t worries it’s same, as we are creating the ConnectionStrings for sql in web.config, same method will be applied for mysql also. Create the connection string in web.config and then fetch and connect it using MySqlConnection class.

1. Download latest MySQL connector DLL for .NET from here http://dev.mysql.com/downloads/connector/net/.
2. Include the DLL in your application, under Bin folder. As I have done below

Right click on solution explorer of your project and click on “Add Reference”.

“Add Refrence” option will open the following dialog box, sleek the MySQL DLL and click OK

Then you’ll notice bin folder is added in your application with mysql DLL

Then after include this in your web page code behind file, as I have done below.

3.  Including DLL in our code to use.

using MySql.Data;
using MySql.Data.MySqlClient;

I am demonstrating it here in ASP.NET (C#), But almost everything is same in VB, just little bit syntax difference.

After including the DLL in your code, make connection as done below.

4. Making connection

MySqlConnection mysqlCon = new MySqlConnection("server=localhost; user; password=; database=qs_dev;");

Using above code you can connect a mysql database with asp.net; put your server name, user id, password and database name. In my example I am fetching data from a table and displaying it in a asp.net grid view control. After making connection, now we are going to fetch the data from mysql table and will store that database in the dataset and bind it with grid view.

5. Executing the query using MySQLDataAdater to get the data from database

MySqlDataAdapter mysqlAdap = new MySqlDataAdapter("SELECT Id, Name FROM users", mysqlCon);

6. Creating dataset object and filling that database with data

DataSet ds = new DataSet();mysqlAdap.Fill(ds, "users");

7. Binding dataset with grid view (my grid view name is grvGrid)

grvGrid.DataSource = ds.Tables["users"];
grvGrid.DataBind();

After following all the above given steps, run your application and you are connected and ready to use your data stored in mysql. In mySql most of the queries are same as SQL queries, but still there are many differences in syntax and the ay of using them. But by googling you can get the proper syntax of mysql.

In my demo application I have two buttons

1. Execute MySql
2. Clear data from grid view

1^st button will fill the data in the grid view and 2^nd will clear the data from grid view. Now you can make use of mySql connection according to your application needs. You can download the example from here and if having any trouble in understanding the example the let me know via commenting here on this article.

Download

This is all about the connecting mysql with asp.net, it’s quite long but half knowledge is always leads to big errors. I have tried my best to explain all things, if you face any problem in understanding this example, just leave your comment here.

Thanks