Database

Private Sub PopulateFindList(ByVal strField As String, ByVal strWhat As String)<br />
        With Me.ListFind<br />
            .DataSource = Nothing<br />
            .Items.Clear()<br />
        End With<br />
        Cursor.Current = Cursors.WaitCursor<br />
        ceConn = New SqlCeConnection(conSqlConnection)<br />
        ceConn.Open()<br />
        Dim strSQL As String = "SELECT " & strField & " FROM Customer WHERE " & strField & _<br />
            " LIKE '%" & strWhat & "%'" & " GROUP BY " & _<br />
            strField & " ORDER BY " & strField & " ASC"<br />
        Dim ceCmd As SqlCeCommand = New SqlCeCommand(strSQL, ceConn)<br />
        ceReader = ceCmd.ExecuteReader(CommandBehavior.CloseConnection)<br />
        If ceReader.Read Then<br />
             While ceReader.Read()<br />
                    ListFind.Items.Add(Mid(CStr(ceReader(strField)), 1, 30))<br />
             End While<br />
        End If<br />
        Cursor.Current = Cursors.Default<br />
        ceReader.Close()<br />
        ceReader = Nothing<br />
        ceConn.Close()<br />
        ceConn = Nothing<br />
      End Sub

MrRedLion wrote:

If ceReader.Read Then
While ceReader.Read()
ListFind.Items.Add(Mid(CStr(ceReader(strField)), 1, 30))
End While
End If

Paddy Boyd wrote:

Some memory there...

CREATE PROCEDURE sp_GetAssignedDetails<br />
@roleName nVarChar(50), <br />
@division nVarChar(50)<br />
<br />
AS<br />
IF @division = 'North'<br />
BEGIN<br />
<br />
--Get ActorName and ActorLogon if division is North<br />
SELECT    dbo.Actor.ActorName, dbo.ActorRole.ActorLogon<br />
FROM      dbo.Actor INNER JOIN<br />
          dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE     dbo.ActorRole.RoleName = @roleName and<br />
          dbo.ActorRole.North = '1'<br />
END<br />
<br />
--Get ActorName and ActorLogon if division is South<br />
ELSE IF @division = 'South' <br />
BEGIN<br />
<br />
SELECT    dbo.Actor.ActorName, dbo.ActorRole.ActorLogon<br />
FROM      dbo.Actor INNER JOIN<br />
          dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE     dbo.ActorRole.RoleName = @roleName and<br />
          dbo.ActorRole.South = '1'<br />
END<br />
<br />
--Get ActorName and ActorLogon if division is West<br />
ELSE IF @division = 'West' <br />
BEGIN<br />
<br />
SELECT    dbo.Actor.ActorName, dbo.ActorRole.ActorLogon<br />
FROM      dbo.Actor INNER JOIN<br />
          dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE     dbo.ActorRole.RoleName = @roleName and<br />
          dbo.ActorRole.West = '1'<br />
END<br />
<br />
RETURN<br />
GO

CREATE PROCEDURE sp_GetAssignedDetails
@roleName nVarChar(50), 
@division nVarChar(50),
@actorName nVarChar(50) OUTPUT,
@actorLogon nVarChar(5) OUTPUT
AS

IF @division = 'North'
BEGIN
  --Get ActorName and ActorLogon if division is North
  SELECT @ActorName = dbo.Actor.ActorName
    FROM dbo.Actor INNER JOIN
      dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
    WHERE dbo.ActorRole.RoleName = @roleName
      and dbo.ActorRole.North = '1'

  SELECT @actorLogon = dbo.Actor.ActorLogon
    FROM dbo.Actor INNER JOIN
      dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
    WHERE dbo.ActorRole.RoleName = @roleName
      and dbo.ActorRole.North = '1'
END
--Get ActorName and ActorLogon if division is South
ELSE IF @division = 'South' 
BEGIN
  SELECT @actorName = dbo.Actor.ActorName
    FROM dbo.Actor INNER JOIN
      dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
    WHERE dbo.ActorRole.RoleName = @roleName
      and dbo.ActorRole.South = '1'

  SELECT @actorLogon = dbo.Actor.ActorLogon
    FROM dbo.Actor INNER JOIN
      dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
    WHERE dbo.ActorRole.RoleName = @roleName
      and dbo.ActorRole.South = '1'
END
--Get ActorName and ActorLogon if division is West
ELSE IF @division = 'West' 
BEGIN
  SELECT @actorName = dbo.Actor.ActorName
    FROM dbo.Actor INNER JOIN
      dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
    WHERE dbo.ActorRole.RoleName = @roleName 
      and dbo.ActorRole.West = '1'

  SELECT @actorLogon = dbo.Actor.ActorLogon
    FROM dbo.Actor INNER JOIN
      dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
    WHERE dbo.ActorRole.RoleName = @roleName 
      and dbo.ActorRole.West = '1'
END

select *,  (select count(*) as CompletedRequests 
			from request
			where request.projectid = project.projectid 
			and Progress = 'Completed'
			group by ProjectID) / cast(TotalRequests as decimal) as PercentageOfProgress 
from project left join (select count(*) as TotalRequests, projectid 
			from request 
			group by ProjectID) as tbl
on project.projectid = tbl.projectid