|
Mike what you pointed out could be the reason. But this need not be the so in this case. MS KB Article ID: 892451 might have been what you were referring to. The dump also occurs in case of excessive memory usage.
Thanks and Regards,
Aby
|
|
|
|
|
I am using xml(.) datatype in sql ser 2005
I want to get the value as string without using dataset
because the data is very huge, if i took by dataset the end values were truncated
i want to get the value directly inside a stored procedure and to pass the value as an input to openXML in the same stored procedure
Please guide me
-
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
Thank You Colin,
I'll try...
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
difference between delete, truncate and drop in sql server
-
|
|
|
|
|
|
Some memory there... 
|
|
|
|
|
Paddy Boyd wrote: Some memory there...
Mine or his?
|
|
|
|
|
Bit of both. As for me, i've just remembered that it's home time and i'm off for the long weekend...
|
|
|
|
|
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! ) 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
|
|
|
|
|
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
|
|
|
|
|
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! 
|
|
|
|
|
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  )
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
You can't use a calculated column for this as the source data for the calculation must come from the row which is to have the calculation applied.
You may wish to set up a trigger so that when any Request row is updated it recalculates the value in the Project table. This is a good solution if the PercentageOfProgress is accessed frequently and changes to the database are infrequent.
Alternatively you may wish to drop the column altogether and recalculate the percentage of progress manually when needed. This is a good solution when the data changes frequently, but the percentage of progress information is accessed infrequently.
|
|
|
|
|
Thank you very much for the reply,
In light of the information presented, I have realised that the calculation must be generated from SQL Statements, efficiently stored in Stored Procedures.
You have suggested 2 things for the frequencies of changes and accesses of the tables and field. My tables will be changed frequently, and the field will be access frequently as well. In anycase, I will play around with some SQL statements and see what I come up with. If you have anything further to suggest, it can only help =D
Thanks again. Much appreciated.
|
|
|
|
|
What is the relationship between Project and Request? I assume there is a ProjectID field in the Request table.
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
--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
|
|
|
|
|
Thanks for this reply,
The problem is solved, now I just have to integrate it appropriately in my program. Thanks a lot. Appreaciate the replies! =D
PS. Nice quote mate lol.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
