|
You should use ADO.NET. You will also use SQL because ADO.NET is a way of sending SQL from a .NET application to a database.
|
|
|
|
|
Well, then there is NJDX, which is beyond ADO and SQL.
Damodar Periwal
Software Tree, Inc.
Simplify Data Integration
http://www.softwaretree.com
|
|
|
|
|
|
i not sure wat this error mean it just not working with my SQL
ODBC: Msg 0, Level 19, State 1<br />
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.<br />
<br />
Connection Broken
Here is my SQL command, the underline code which i added then the error came out. B4 add the command so working correctly
SELECT Query1.bizdate, TSuper,TSoft, THard, (TSoft+ THard+ TSuper) as TOTAL ,<br />
((TSoft+ THard+ TSuper)-(TsCost+ThCost+TsuCost))as GP <br />
FROM <br />
(SELECT bizdate, SUM(totalsales+returnamt) AS TSoft ,sum(totalcost+returncost)as TsCost FROM tot_item WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17) <br />
GROUP BY bizdate) AS Query1<br />
<br />
LEFT JOIN (SELECT bizdate, SUM(totalsales+returnamt) AS THard ,sum(totalcost+returncost)as ThCost FROM tot_item WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29) <br />
GROUP BY bizdate) AS Query2 ON Query1.bizdate = Query2.bizdate<br />
LEFT JOIN (SELECT bizdate, sum(totalsales+returnamt) AS TSuper ,sum(totalcost+returncost)as TsuCost FROM tot_item WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90) <br />
GROUP BY bizdate) AS Query3 ON Query1.bizdate = Query3.bizdate<br />
<br />
where query1.bizdate>= '2006-10-01' and query1.bizdate<= '2006-10-15'<br />
order by query1.bizdate
|
|
|
|
|
Contact Microsoft support. There's a bug somewhere in your copy of SQL Server which is causing an error to occur in the database engine.
You should first ensure that you're running one of the supported service packs. For SQL Server 2000 this is SP3a or SP4, for SQL Server 2005 it's either the original (RTM) release or SP1.
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
