Locations of visitors to this page
Onteora Software - March 2008

Onteora Software

Ken Tucker's Blog

About the author

Author Name is someone.
E-mail me Send mail

Recent posts

Recent comments

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2008

DataGridViewCombobox AutoComplete

Here is a quick example on using an autocomplete combobox in the DataGridView.  In this example I load all the possible values for the combobox into a AutoCompleteStringCollection and make that the DataGridViewComboBox's datasource.  In the editingControl showing event you need to set the ComboBox's DropDownStyle, and the auto complete settings.

 

Imports System.Data.SqlClient

Public Class Form1
    Dim scAutoComplete As New AutoCompleteStringCollection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As String
        Dim da As SqlDataAdapter
        Dim conn As SqlConnection
        Dim ds As New DataSet
        strConn = "Server = .\SQLEXPRESS;Database = NorthWind; Integrated Security = SSPI;"
        conn = New SqlConnection(strConn)
        da = New SqlDataAdapter("Select * from [Orders]", conn)
        da.Fill(ds, "Orders")
        DataGridView1.DataSource = ds.Tables("Orders")

        Dim cmd As New SqlCommand("Select CustomerID From customers", conn)
        Dim dr As SqlDataReader
        conn.Open()
        dr = cmd.ExecuteReader
        Do While dr.Read
            scAutoComplete.Add(dr.GetString(0))
        Loop
        conn.Close()

        Dim dgvcbc As New DataGridViewComboBoxColumn
        With dgvcbc
            .DataPropertyName = "CustomerID"
            .DataSource = scAutoComplete
            .HeaderText = "Customer ID"
        End With
        DataGridView1.Columns.Remove("CustomerID")
        DataGridView1.Columns.Insert(1, dgvcbc)
    End Sub

    Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        If DataGridView1.CurrentCell.ColumnIndex = 1 AndAlso TypeOf e.Control Is ComboBox Then
            With DirectCast(e.Control, ComboBox)
                .DropDownStyle = ComboBoxStyle.DropDown
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.CustomSource
                .AutoCompleteCustomSource = scAutoComplete
            End With
        End If
    End Sub

End Class

kick it on DotNetKicks.com

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Categories: DataGridView | VB
Posted by Ken Tucker on Wednesday, March 26, 2008 6:01 AM
Permalink | Comments (0) | Post RSSRSS comment feed

Uploading a Database to Dotster

The easiest way to upload a database to dotster is to use the sql hosting toolkit.   Visual studio 2008 installs the sql hosting toolkit for you otherwise you need to download and install from the link. 

 

Steps to do this

1) Create a database in the dotster control panel.

2) In the server explorer create a link to the database you want to upload. 

3) Right click on the database and select publish to provider in the wizard make sure you select sql 2000 as the target database schema.

4) On the codeplex website they use to have a webpage available to use to help run the script to create your database.  Now they have a webservice for this.   I kind of think the webservice is over kill to just publish 1 database.  So create a c# website which targets the .net framework 2.0

5) Add the script file you created in step 3 to the website.

6) Add the following code to webpage

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Web;
using System.IO;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

namespace WebApplication3
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string fileName = Server.MapPath(@"<<YOUR_SCRIPTFILE>>.SQL");

            // Connection string to the server you want to execute against
            string connectionString = @"Server=<<YOUR_SERVER>>;User ID=<<YOUR_USERNAME>>;Password=<<YOUR_PASSWORD>>;Initial Catalog=<<YOUR_DATABASE>>";

            // Timeout of batches (in seconds)
            int timeout = 600;

            SqlConnection conn = null;
            try
            {
                Response.Write(String.Format("Opening file {0}<BR>", fileName));

                // read file
                using (StreamReader sr = new StreamReader(new FileStream(fileName, FileMode.Open)))
                {
                    Response.Write("Connecting to SQL Server database...<BR>");

                    // Create new connection to database
                    conn = new SqlConnection(connectionString);

                    conn.Open();

                    while (!sr.EndOfStream)
                    {
                        StringBuilder sb = new StringBuilder();
                        SqlCommand cmd = conn.CreateCommand();

                        while (!sr.EndOfStream)
                        {
                            string s = sr.ReadLine();
                            if (s != null && s.ToUpper().Trim().Equals("GO"))
                            {
                                break;
                            }

                            sb.AppendLine(s);
                        }

                        // Execute T-SQL against the target database
                        try
                        {
                            cmd.CommandText = sb.ToString();
                            cmd.CommandTimeout = timeout;

                            cmd.ExecuteNonQuery();

                        }
                        catch (Exception ex)
                        {
                            Response.Write(sb.ToString().Replace("\n", @"<br/>"));
                            Response.Write(ex.Message.ToString() + @"<br />");
                        }
                    }

                }
                Response.Write("T-SQL file executed successfully");
            }
            catch (Exception ex)
            {
                Response.Write(String.Format("An error occured: {0}", ex.ToString()));
            }
            finally
            {
                // Close out the connection
                //
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                    catch (Exception e1)
                    {
                        Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e1.ToString()));
                    }
                }
            }

        }
    }
}

 

Uploasd the website to dotster and open the webpage and your database should be copied to the new database.  Make sure you delete the website after you created the database to prevent someone from accidently undo changes to the database after you uploaded it.

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Categories: Asp | C# | Sql
Posted by Ken Tucker on Wednesday, March 12, 2008 10:17 PM
Permalink | Comments (0) | Post RSSRSS comment feed