Database

26-Oct-06 10:26

Hi All,
Glad to be here!
Very strange results from DataReader... I'm using the code from MS examples to populate ListBox with items from SQL 2000 CE 2.0 data file. And if "n" is number of records in database I always get "n-1" records from DataReader!
Otherwise, DataSet returns all needed records with the same SQL string!
I'm on VB.Net 2005
Thanks in advance!

Eric Dahlvang26-Oct-06 11:19

Eric Dahlvang

26-Oct-06 11:19

Can you provide an example of the code populating the ListBox with a DataReader?

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

MrRedLion26-Oct-06 19:03

26-Oct-06 19:03

Hope its help...

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

Colin Angus Mackay27-Oct-06 1:10

27-Oct-06 1:10

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

Your problem is here. Every time you call Read() it moves onto the next row in the result set. Your If statement is effectively skipping the first row. All you need is the While loop because it will never loop if the very first call of Read() returns false (indicating there are no rows returned). So, remove the If statement.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

MrRedLion27-Oct-06 1:43

how to use sql to read the arabic languag

27-Oct-06 1:43

Thank You Colin,
I'll try...

ashraf hakiem25-Oct-06 21:36

ashraf hakiem

25-Oct-06 21:36

hello all ,
whene i write data in webapplication and i want to this dat6a to recording in the data base and this data are in arabic languag this data recorded in data base but the word which is arabic recorded lik this ??????? and i want the data bas to read the arabic languag

Re: how to use sql to read the arabic languag

MrRedLion26-Oct-06 10:39

difference between delete, truncate and drop in sql server

26-Oct-06 10:39

I beleive, You should check International Settings of the PC where You run your application! I'm working with Hebrew data and it looks quite well!

NICE TO MEET25-Oct-06 21:10

NICE TO MEET

25-Oct-06 21:10

difference between delete, truncate and drop in sql server

-

Re: difference between delete, truncate and drop in sql server

Colin Angus Mackay25-Oct-06 23:15

Re: difference between delete, truncate and drop in sql server

25-Oct-06 23:15

You asked this about a month ago[^] what was wrong with the answers you received then?

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Paddy Boyd27-Oct-06 3:58

Paddy Boyd

27-Oct-06 3:58

Some memory there... Big Grin | :-D

Re: difference between delete, truncate and drop in sql server

Colin Angus Mackay27-Oct-06 6:28

Re: difference between delete, truncate and drop in sql server

27-Oct-06 6:28

Paddy Boyd wrote:
Some memory there...

Mine or his?

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Paddy Boyd27-Oct-06 6:31

Paddy Boyd

27-Oct-06 6:31

Bit of both. As for me, i've just remembered that it's home time and i'm off for the long weekend...

Returning more than one value from a stored procedure? [modified]

Belfast Child25-Oct-06 1:36

Belfast Child

25-Oct-06 1:36

Hi,
I'm quite new to stored procedures but have been fine with them up until now(OUTPUT params?? - If that's what I even need! Confused | :confused:

) when i need to return more that one value to a dataset in my C# code ie. @actorName and @actorLogon.

I thought I could do something like this but I get a syntax error on last line...
(Msg 102, Level 15, State 1, Procedure sp_GetAssignedDetails, Line 60
Incorrect syntax near ','.)
I've tried a few different ways but something tells me this is not the way to go.

Here's the code - Any help appreciated.

CREATE PROCEDURE sp_GetAssignedDetails<br />
@roleName nVarChar(50), <br />
@division nVarChar(50),<br />
@actorName nVarChar(50) OUTPUT,<br />
@actorLogon nVarChar(5) OUTPUT<br />
<br />
AS<br />
IF @division = 'North'<br />
BEGIN<br />
<br />
--Get ActorName and ActorLogon if division is North<br />
SET @actorName= ( SELECT    dbo.Actor.ActorName<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 />
<br />
SET @actorLogon= ( SELECT    dbo.Actor.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 />
SET @actorName= ( SELECT    dbo.Actor.ActorName<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 />
<br />
SET @actorLogon= ( SELECT    dbo.Actor.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 />
SET @actorName= ( SELECT    dbo.Actor.ActorName<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 />
<br />
SET @actorLogon= ( SELECT    dbo.Actor.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 />
<br />
RETURN  @actorName, @actorLogon<br />
GO

ps. What kind of value would this return if I didn't create any Output parameters and just went through the select statement on it own and had a RETURN at the end??

-- modified at 8:03 Wednesday 25th October, 2006

Re: Returning more than one value from a stored procedure?

Belfast Child25-Oct-06 2:14

Belfast Child

25-Oct-06 2:14

...or would this be better??

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

Re: Returning more than one value from a stored procedure?

Elina Blank25-Oct-06 2:19

Elina Blank

25-Oct-06 2:19

You do not have to put a RETURN statement (at least when using SQL 2005)

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

Re: Returning more than one value from a stored procedure?

Elina Blank25-Oct-06 2:18

Elina Blank

25-Oct-06 2:18

In order to return the value from stored procedure, it is enought to declare a parameter as an OUTPUT.
in your C# code use
ParameterDirection.Output when adding this parameter.
You can retrieve value with the Value property of the created parameter.

Usually, at the end of the execution of the stored procedure, it returns number of affected rows, so in case of a SELECT clause the return is 0 (but I am not sure, I understood your last question
correctly Sigh | :sigh:

)

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

Re: Returning more than one value from a stored procedure?

Scott Serl25-Oct-06 8:00

Scott Serl

25-Oct-06 8:00

The most efficient way to return values is with OUTPUT parameters. When the code returns, the parameter values will have the values in them; just check the cmd.Parameters("@actorName").Value and cmd.Parameters("@actorLogin").Value properties.

If you did not have any output parameters and did the selects the way you had them, then the return value would have been 0 (zero), I think. If you just had regular SELECT statements without any OUTPUT parameters, you could have ruturned 2 result sets, each with 1 value in them. You would then use a DataReader to read the value from the first result set, then call .NextResult (or something like that, I can't remember), and then read the value out of the second result set.

OUTPUT parameters are much more efficient.

Notice that I changed your SET statements to just SELECT. The SET statements will work, but the SELECT way is more common among db developers.

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

Scott

Re: Returning more than one value from a stored procedure?

Belfast Child30-Oct-06 22:43

Belfast Child

30-Oct-06 22:43

Cheers Scott,

That was very informative.

Yeah, unfortunately I'm not a DB developer.
Just trying to think this stuff out for myself as we have no DB expertise in work.

From the above code I'm trying to sequentially chose the above @actorName so that the workload is spread evenly amongst employees from the different divisions. At the moment it is picking the first one off the database table.

Are there any handy stored proc functions that allows a sequential generator of names so that each @actorName in the database will get picked from their appropraite divisons??

Any help would be apprecaited.

Kind Regards,
BC

Calculated Field

Dayekh25-Oct-06 0:00

Dayekh

25-Oct-06 0:00

Hi,

This is the senario:

TABLES
Project and Request

Schema
Project(ProjectID, ....., PercentageOfProgress)
Request(RequestID, ....., Progress[Pending, On Going, Completed])

The Progress field in the Project table is to be calculated. It should be a percentage based on the total numbers of records (Which are 'Completed' in the Request table.

If anyone could push me in the right direction, that would be great. Thank you.

Re: Calculated Field

Colin Angus Mackay25-Oct-06 0:57