Tuesday, March 10, 2009

error: 28 - Server doesn't support requested protocol

After trying to setup SQL Server 2005 /2008Express, We may face the 'Error: 28':

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)"

Network protocols are disabled in SQL Server 2005 Express by default so we need to enable TCP/IP:

  • Launch the SQL Server Configuration Manager

  • Expand the "SQL Server 2005 Network Configuration" node

  • Left click the "Protocols for SQLEXPRESS" node to view the supported protocols

  • Right click the "TCP/IP" protocol and select "Enable"
  • SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

    Error when connecting to SQL server:

    Below are steps may try to help:
    1) Make sure your server name is correct, e.g., no typo on the name.
    2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
    3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
    4) Make sure SQL Browser service is running on the server.
    5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

    Thank You,
    Happy Coding

    Sunday, March 8, 2009

    Drop All tables in Oracle using SQL Statement

    Sometime for developer want drop all table, it might be quite troublesome. Most of us will direct delete the "Schema" to drop all tables.

    Here is the SQL Script:
    Begin

    for c in (select table_name from user_tables) loop
    execute immediate ('drop table '||c.table_name||' cascade constraints);
    end loop;
    End;

    Take note!!
    1. You need to log in to that user which you wanted to drop the tables
    2. Keep in mind, If run, it will not able to rollback

    Saturday, March 7, 2009

    UNMOUNTABLE_BOOT_VOLUME on Windows XP

    My friends show me this error when he tried to restart his XP. I search the net and quite hard to find. However i still managed to get it.

    Requirement:
    1. Windows XP installation CD

    Steps to rescure:
    1. Boot from the installation CD and Select R for repair
    2. I will prompt the 'Administrator' password. If you don't set the password, you just need to press 'Enter'
    3. Type 'chkdsk c: /r /p'
    4. It may wait for minutes. After completion, type 'fixmbr'
    5. The last is 'exit(to reboot)'

    Friday, March 6, 2009

    ExecuteReader: Connection property has not been initialized

    If you see this "ExecuteReader: Connection property has not been initialized" message. This problem occur when your command does not associate with any connection.

    Here is a snippet sample to recreate the error:

    Conn = New SqlConnection
    Conn.ConnectionString = connectionString
    Command = New SqlCommand(sql)
    Conn.Open()

    dgResults.DataSource = Comamnd.ExecuteReader()
    dgResults.DataBind()

    Conn.Close()

    "Error occur because the command is not associate with any connection"

    Below is the solution:

    1. Command = new SqlCommand(sql,Conn)

    2. Command = new SqlCommand(sql)
    Command.Connection = conn

    Tutorial 1: Load listView using database in VB.net

    Hi All,

    Below is an example to load the listview, and i believe it may help to reduce the coding on it.

    Step 1: Create a form, named it "Form1" and add a listview, named it "Listview1"


    step 2: Configure the listview's look by choosing "Details".


    step 3: Add the Some "columns' and a button, named it as "Button1". In this case, I add 4 columns which is to show the customer details


    step 4: Add a class, named it as "LoadListView.vb".



    step 5: Add the code to the LoadListview Class.

    Imports System.Data.SqlClient

    Public Class LoadListview

    Public Sub loadlistview(ByVal cs As String, ByVal ColumnNum As Integer, _
    ByVal listviewName As ListView, ByVal ssql As String, _
    ByVal ColumnName() As String)

    Dim columnData(8) As String
    Dim lvi As ListViewItem
    Dim command As New SqlCommand

    Dim dr As SqlDataReader
    Dim Conn As SqlConnection = New SqlConnection

    Dim count As Integer
    Dim i As Integer

    listviewName.BeginUpdate()
    'conn.Open()
    Try

    Conn.ConnectionString = cs
    Conn.Open()

    command = New SqlCommand(ssql, Conn)
    dr = command.ExecuteReader

    listviewName.Items.Clear() 'Clear the listview before adding

    While dr.Read()
    For i = 0 To ColumnNum
    columnData(i) = dr.GetValue(dr.GetOrdinal(ColumnName(i))).ToString
    Next

    lvi = New ListViewItem(columnData)
    listviewName.Items.Add(lvi)
    count = count + 1

    End While
    listviewName.EndUpdate() 'End of updating

    Catch ex As Exception
    MsgBox(ex.Message)
    Finally
    If Conn.State = ConnectionState.Open Then Conn.Close() 'Close the database connection if it is still open
    End Try
    End Sub

    End Class
    ====================================================
    Explaination:
    CS = Connection String for database
    ColumnNum = No of column(count from 0, if I had 4 columns so i will only write 3)
    ListviewName = Name of your listview(In my case is "ListView1")
    ssql = SQL statement
    ColumnName() = Array of the database column name, which are going to show at the list view

    Step 6: Add the Code to "Form1.VB"

    Public Class Form1

    Private connString As String = "Data Source=IBM-PC\SQLEXPRESS;Initial Catalog=CustomerSystem;Persist Security Info=True;User ID=CUST;Password=CUST"

    Private LLV As New LoadListview


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim sql As String = "select * from custmaster"
    Dim colname() As String = {"company", "custcode", "telephone", "address"}

    LLV.loadlistview(connString, 3, ListView1, sql, colname)
    End Sub
    End Class

    Step 7: Click the Button1 to see the result


    ========================================================================
    End of the Example

    From the given code, you can try to change to suit your purpose.

    Please leave comment, if you like it.

    Thursday, March 5, 2009

    Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection.

    I had an error that I believe a lot of new user for SQL server may face during create a new Database.

    Below is the resolution:


    Change the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)”
    to “Mixed Mode (Windows Authentication and SQL Server Authentication)”.

    Run following script in SQL Analyzer to change the authentication

    LOGIN sa ENABLE
    GO
    ALTER LOGIN sa WITH PASSWORD =
    GO

    OR

    In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties. On the General page, you may have to create and confirm a password for the sa login. On the Status page, in the Login section, click Enabled, and then click OK.