Get all rows for paticular column in one column.

For getting records returned by a sql query in one column  comma seperated we can use 

SELECT GROUP_CONCAT(columnname) AS columnname FROM tablename WHERE condition .

This query will return all query results for paticular column in one column comma seperated.

Use Mysql stored procedure in Vb.net

To use mysql stored procedure in vb.net we have to create command object and pass it text as call sp_name


            Dim cmd As New OdbcCommand()
            cmd.Connection = myConnection
            cmd.CommandText = " CALL getcatid(?)"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@catname1", "Kitchen")
            cmd.Parameters("@catname1").Direction = ParameterDirection.Input

Merge Particular column that is repeating in Gridview

Protected Sub GridView1_RowDataBound(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound

     
        For rowIndex As Integer = GridView1.Rows.Count - 2 To 0 Step -1
            Dim gviewRow As GridViewRow = GridView1.Rows(rowIndex)
            Dim gviewPreviousRow As GridViewRow = GridView1.Rows(rowIndex + 1)
            Dim cellCount As Integer = 0//replace 0 with column index
            If gviewRow.Cells(cellCount).Text = gviewPreviousRow.Cells(cellCount).Text Then
                If gviewPreviousRow.Cells(cellCount).RowSpan < 2 Then
                    gviewRow.Cells(cellCount).RowSpan = 2
                Else
                    gviewRow.Cells(cellCount).RowSpan = gviewPreviousRow.Cells(cellCount).RowSpan + 1
                End If
                gviewPreviousRow.Cells(cellCount).Visible = False
            End If
        Next



    End Sub

Bind DropdownList using arraylist,hashtable,array

Binding with array List(vb) :

Dim AnimalArrayList as new ArrayList()
AnimalArrayList.Add("Dog")
AnimalArrayList.Add("Cat")
AnimalArrayList.Add("Elephant")
AnimalArrayList.Add("Lion")
AnimalArrayList.Add("Cat")
MyDropDownList.DataSource = AnimalArrayList
MyDropDownList.DataBind()

 Binding with Hashtable(c#)  :

Hashtable hTable = new Hashtable();

        hTable.Add("1", "Hashtable Item 1");

        hTable.Add("2", "Hashtable Item 2");

        hTable.Add("3", "Hashtable Item 3");

        hTable.Add("4", "Hashtable Item 4");



        dropHashtable.DataSource = hTable;

        dropHashtable.DataBind();

 Binding with Array(c#,vb)  :

int[] Hour = new int[24];

for (int i=0; i <=24, i++)

{

ddlHour.Items.add(Hour[i]);

}

How to take backup of database using mysql command line

Taking backup from mysql command Line is quite simple .Just follow following steps and its done.

1)Open command prompt(run as administrator).
2)Move to the bin directory of mysql server.
3) Type following command
mysqldump -u root -p [your password] [database name for backup] -r ["filename"]
4)Dont forget to add double quotes for file name.

Note:If you dont specify path of file ,then it will go in default directory(Programfiles\Mysql\mysqlserver5.\bin).

Ajax Autocomplete

  To get autocomplete in textbox just drag a textbox to a form

Add toolkitscriptmanager.Then click on smart tag and select "Add Auto complete page method".


Now in Getcompletion list function add code for getting data from database.


 <System.Web.Services.WebMethodAttribute()> <System.Web.Script.Services.ScriptMethodAttribute()>
    Public Shared Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer, ByVal contextKey As String) As String()
        Dim names As New ArrayList
        Dim db As New dataBase
        db.dbOpenConnection()
        Dim query As String = "Select username from test "
        db.cmd.CommandText = query
        Dim dr As OdbcDataReader
        dr = db.cmd.ExecuteReader
        While dr.Read()
            names.Add(dr("username").ToString())
        End While
        db.dbCloseConnection()


        Return (
            From m As String In names
            Where m.StartsWith(prefixText, StringComparison.CurrentCultureIgnoreCase)
            Select m).Take(count).ToArray()
    End Function


You can add styles to autocomplete

 <style>
For list shown
   .AutoExtender
        {
            font-family: Verdana, Helvetica, sans-serif;
            font-size: .8em;
            font-weight: normal;
            border: solid 1px #006699;
            line-height: 20px;
            padding: 10px;
            background-color: White;
            margin-left:10px;
        }
        .AutoExtenderList
        {
            border-bottom: dotted 1px #006699;
            cursor: pointer;
            color: Maroon;
        }
        .AutoExtenderHighlight
        {
            color: White;
            background-color: #006699;
            cursor: pointer;
        }
</style>

Add one div above textbox to show list
  <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
        </asp:ToolkitScriptManager>
        <br />
        <div ID="divwidth"></div>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:AutoCompleteExtender ID="TextBox1_AutoCompleteExtender" runat="server"
            CompletionInterval="100" DelimiterCharacters="" Enabled="True"
            MinimumPrefixLength="1" ServiceMethod="GetCompletionList" ServicePath=""
            TargetControlID="TextBox1" UseContextKey="True"
            CompletionListItemCssClass="AutoExtenderList"
            CompletionListCssClass="AutoExtender"
            CompletionListHighlightedItemCssClass="AutoExtenderHighlight"
            CompletionListElementID="divwidth"></asp:AutoCompleteExtender>

'System.Drawing.Image' converted to 'System.Drawing.Icon in Windows Form Application

Here Is a simple solution when you get this error.You get this error when you are trying to change icon at runtime .Here is a simple Solution to that.

Dim img As Image = 'Whatever your image is...
Dim bm As Bitmap = img
Dim hIcon As IntPtr = bm.GetHicon
Dim TheIcon As Icon = Icon.FromHandle(hIcon)