Database

Ted2102 wrote:

I am mainly doing this as an exercise to learn to use ADO .NET

-- Server query
SELECT Name, Age, Sex, Address, Phone, City
  FROM Person p
    JOIN Address a
      ON a.ID = p.ID
  ORDER BY p.Name

' After retreiving the data into a DataTable (dt)
Dim count as Integer = 0
Dim arr(( (dt.Rows.Count) * 2) - 1), 2) As String
For Each dr as DataRow In dt
  arr( count, 0) = dr("Name").ToString
  arr( count, 1) = dr("Age").ToString
  arr( count, 2) = dr("Sex").ToString
  count += 1
  arr( count, 0) = dr("Address").ToString
  arr( count, 1) = dr("Phone").ToString
  arr( count, 2) = dr("City").ToString
  count += 1
Next

create view view_name as <br />
select field_name as f1, field_name as f2, field_name as f3 from table_name

SELECT 
	CASE WHEN FieldName = 1 THEN 1 ELSE 0 END F1,
	CASE WHEN FieldName = 2 THEN 1 ELSE 0 END F2,
	CASE WHEN FieldName = 3 THEN 1 ELSE 0 END F3
FROM TableName

SELECT     TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone, 
                      dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance, 
                      dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber, 
                      dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox, 
                      dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode, 
                      dbo.ContactMaster.BmcCustKey, CASE WHEN dbo.ContactMaster.Role = 1 THEN 1 ELSE 0 END AS IsAdministrator, 
                      CASE WHEN dbo.ContactMaster.Role = 6 THEN 1 ELSE 0 END AS IsDecisionMaker, 
                      CASE WHEN dbo.ContactMaster.Role = 5 THEN 1 ELSE 0 END AS IsAccountingAP
FROM         dbo.ContactMaster INNER JOIN
                      dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
                      dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity LEFT OUTER JOIN
                      dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity LEFT OUTER JOIN
                      dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE     (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey

i.j.russell wrote:

Have you got Mixed Authentication set in the Sql Server Express instance? If so, can you connect using non-windows authentication?

i.j.russell wrote:

I noticed that the message said that it can't find instance HTPC; Is that the name of the instance or is it HTPC\SQLEXPRESS?

select
    EmployeeID,
    EmployeeName,
    YearStarted,
    case
        when YearStarted < 2007 then 'Veteran'
        else 'Spring Chicken'
    end NewColumn
from
    Employees