|
Hi,
I've written a stored procedure which should return a specific value and has an additional OUTPUT parameter. Here goes the code:
<br />
CREATE PROCEDURE dbo.Update_AddresseInfo<br />
<br />
(<br />
@UserID int,<br />
@DateModified DateTime,<br />
@ElementID int,<br />
@Street varchar(50),<br />
@City varchar(50),<br />
@ZIP varchar(50),<br />
@NewDateModified DateTime OUTPUT<br />
)<br />
<br />
AS<br />
declare @RetVal int<br />
declare @DateNow DateTime<br />
set @DateNow = GetDate()<br />
<br />
-- do some stuff <br />
<br />
UPDATE dbo.AdresseInfo SET<br />
Street = @Street, <br />
ZIP = @ZIP, <br />
City = @City, <br />
DateModified = @DateNow<br />
WHERE<br />
ID = @ElementID<br />
<br />
if @@ERROR = 0<br />
begin<br />
set @NewDateModified = @DateNow<br />
set @Retval = 0<br />
end<br />
else<br />
begin<br />
set @Retval = 2<br />
end<br />
<br />
-- log this event<br />
exec LogEvent @UserID, 1, 1<br />
<br />
select @Retval<br />
RETURN <br />
Unfortunately the NewDateModified does not contain what I expect, eventhough I've created a SqlParameter object with the correct Direction. I guess the problem is somewhere in my SQL code.
Any help is greatly appreceated.
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
At a guess, @@ERROR isn't being set to 0.
Have you tried setting the value outside the IF to see if it gets returned?
Michael
CP Blog [^]
|
|
|
|
|
Hi Michael,
thanks for your response. Yes, I've thought about this as well. But even putting it outside the IF doesn't yield correct results. I'm posting an extract of the code which is actually executing the SP here. Maybe I'm just blind...
<br />
int nResult = 0;<br />
DateTime dtResult = new DateTime(0);<br />
SqlCommand cmd = new SqlCommand(ProcName, Open());<br />
<br />
try {<br />
cmd.CommandType = System.Data.CommandType.StoredProcedure;<br />
<br />
-- do stuff and and all params...<br />
cmd.Parameters.Add(new SqlParameter("ElementID", ElementID));<br />
cmd.Parameters.Add(new SqlParameter("DateModified", DateModified));<br />
<br />
SqlParameter prmNewDtModif = new SqlParameter("NewDateModified", dtResult);<br />
prmNewDtModif.Direction = System.Data.ParameterDirection.Output;<br />
cmd.Parameters.Add(prmNewDtModif);<br />
<br />
nResult = (Int32) cmd.ExecuteScalar();<br />
<br />
}<br />
<br />
I've just put the stuff in which seems to be relevant. Variable names are selfexplanatory I guess.
Any ideas?
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Matthias Steinbart wrote:
SqlParameter prmNewDtModif = new SqlParameter("NewDateModified", dtResult);
I'm not au-fait with all the ins and outs of ADO.NET but shouldn't the Parameter name match the stored procedure?
i.e @NewDateModified rather than NewDateModified
Michael
CP Blog [^]
|
|
|
|
|
Post your ADO code where you get NewDateModified out of the command object...
DTrent
|
|
|
|
|
Hi DTrent!
Thanks for your reply. Your sole proposal has answered my question ("out of the command object..."???  )
I assumed, that the value gets automatically assigned once the SqlCommand.ExecuteScalar() call returns. I didn't know that I have to retrieve the SqlParameter.Value property and assign it then to the variable which I passed to that parameter. Seems kind of weird to me, in the end I'm asking why am I assigning the variable in the first place?
For those interested, here the code:
<br />
int nResult = 0;<br />
DateTime dtResult = new DateTime(0);<br />
SqlCommand cmd = new SqlCommand(ProcName, Open());<br />
<br />
try {<br />
cmd.CommandType = System.Data.CommandType.StoredProcedure;<br />
<br />
-- do stuff and and all params...<br />
cmd.Parameters.Add(new SqlParameter("ElementID", ElementID));<br />
cmd.Parameters.Add(new SqlParameter("DateModified", DateModified));<br />
<br />
SqlParameter prmNewDtModif = new SqlParameter("NewDateModified", dtResult);<br />
prmNewDtModif.Direction = System.Data.ParameterDirection.Output;<br />
cmd.Parameters.Add(prmNewDtModif);<br />
<br />
nResult = (Int32) cmd.ExecuteScalar();<br />
<br />
}<br />
<br />
<br />
<br />
<br />
if (dtResult < dtSomething) {<br />
}<br />
Thanks for your help!
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Does anyone know how to automate a trace that constrains on the DBID?
I have created two scripts, one to create a job that is sceduled to start when the sql agent starts. It contains one step that executes the stored procedure that contains the trace definition and creates and starts the trace on the target server and database.
The problem is when I constrain on the dbid everything gets created and the job starts successfully but nothing is written to the trace file. The only events captured by this trace are login, logout and failed logins. The scripts work if I remove the dbid constraint.
D
dsa
|
|
|
|
|
Hello, not even sure if this is the right forum for this question but here
goes..
I'm writing a NET Data Provider for our DBMS and got everything working
except one thing. Populating a DataGrid..
All other parts of the Data Provider framework are all interface-based and
therefore rather easy to implement/override as needed, but the DataTable
isn't!
So when my TTDataTable (derived from DataTable) gets notified over TCP/IP
that a cell has changed it's value and I update the DataTable using
something like:
Rows[updateRow.RowId][cell.Ordinal] = cell.Value == null ? DBNull.Value : cell.Value;
Sooner or later the application will throw an exception somwhere in
System.Windows.Forms.dll whith no callstack at all, very ennoying.. 
This happens I _think_ is because the DataTable implementation does not do
something like this when firing the event:
<br />
<someevent>Handler eventTarget = <someevent>;<br />
if ( eventTarget != null )<br />
{<br />
if ( eventTarget.Target is ISynchronizeInvoke )<br />
{<br />
ISynchronizeInvoke target = eventTarget.Target as ISynchronizeInvoke;<br />
target.BeginInvoke( eventTarget, new object[]{ this<,more params if needed> } );<br />
}<br />
else<br />
{<br />
eventTarget( this, e );<br />
}<br />
One solution would be to throw the event myself in the OnRowChanged method,
but unfortunatly the onRowChangedDelegate (and also the rest of the event)
seems to be private, so...
Another solution would be that I have totally missunderstood how to properly
deriving from a DataTable and populating/updating it from another thread and
if so, I really hope someone here can show me how.
Or *gasp* could it be a bug/bad coding/feature from MS? o.O
BTW when not adding the TTDataTable to a grid but instead simply sinking the
events and tracing the events using Debug.WriteLine or to a console, it
works, thats why I think it's a "updating a form from the wrong
thread"-bug..
I have tried this
<br />
public class TTDataTable : System.Data.DataTable<br />
{<br />
.<br />
.<br />
.<br />
private void Process( UpdateRow updateRow )<br />
{<br />
System.Diagnostics.Debug.WriteLine( "UpdateRow" );<br />
foreach ( CellInformation cell in updateRow.Cells )<br />
{<br />
Rows[updateRow.RowId][cell.Ordinal] = cell.Value == null ? DBNull.Value : cell.Value;<br />
}<br />
}<br />
}<br />
and
<br />
protected override void OnRowChanged(System.Data.DataRowChangeEventArgs e)<br />
{<br />
System.Data.DataRowChangeEventHandler eventTarget = onRowChangedDelegate;<br />
if ( eventTarget != null )<br />
{<br />
if ( eventTarget.Target is ISynchronizeInvoke )<br />
{<br />
ISynchronizeInvoke target = eventTarget.Target as ISynchronizeInvoke;<br />
target.BeginInvoke( eventTarget, new object[]{ this, e } );<br />
}<br />
else<br />
{<br />
eventTarget( this, e );<br />
}<br />
}<br />
}<br />
Problem with the above is this error: D:\Projects\Abaris\TrueTime Solutions
AB\TT 7.0\CLR\Abaris\TrueTimeSolutions\Clients\Data
Provider\TTDataTable.cs(139): 'System.Data.DataTable.onRowChangedDelegate'
is inaccessible due to its protection level
Or in a nutshell, as a .NET Data Provider, how do I make it possible to
display data from our eventbased Realtime DBMS in a plain vanilla DataGrid?
I know I can also write our own datagrid, but why reinvent the wheel...
TIA/
Michael Hulthin
|
|
|
|
|
<%
dim rs
dim sql
dim classid
dim nclassid
classid=request("class_id")
nclassid=request("nclass_id")
set rs=server.CreateObject("adodb.recordset")
sql="select * from brand where class_id=classid and nclass_id=nclassid"
rs.Open sql,conn,1,1
do while not rs.EOF
%>
<%=rs("brand_name")%>
<% rs.MoveNext
loop
rs.Close
%>
help me,which one can help me? please
|
|
|
|
|
Is there any one who can make it perfect for on my question?
|
|
|
|
|
NewPureLand wrote:
Is there any one who can make it perfect for on my question?
You have to ask a better question. Just posting some code and saying help me is not enough. What is wrong with the code? What error is happening? What is it supposed to do? And, what does it do?
Answer these questions and you might give enough information for someone to help you quickly.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Take a look at your select statement. classid and nclassid will be evaluated as literals making them part of the string. Your select statement would then look like "select * from brand where class_id=classid..." instead of "select * from brand where class_id=5..." which is what you really want. Be sure to force the evaluation of these variables before executing your query.
|
|
|
|
|
hhow toexecute the functions
jairaj
|
|
|
|
|
See EXECUTE in SQL Server Online Book.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
Actually, you don't EXECUTE functions. You do that to Stored Procedures.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
According to Sql Server online book ,it does that too.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
Are you sure? I've checked the books online and I cannot see anything about running functions with the EXECUTE COMMAND. I also created a sample, on the Northwind database, to try out.
CREATE FUNCTION dbo.AllProducts()
RETURNS TABLE
AS
RETURN(SELECT * FROM PRODUCTS)
Then I tried to execute it like this:
EXECUTE dbo.AllProducts
I got an error message
Server: Msg 2809, Level 18, State 1, Line 1
The request for procedure 'AllProducts' failed because 'AllProducts' is a function object.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Its very strange,I have not test it myself before but this is from my Online Book for EXECUTE command:
Executes a scalar-valued, user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
To run a User Defined Function (or UDF) you need to do something like:
SELECT * FROM dbo.MyFunctionName()
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Depends on the type of function, that is, what it returns. If it is a value you can just use:
set @MyValue = MyFunction(MyParameter)
If it returns a table, you would probably use it in a FROM or where you would normally specify a table.
Rocky <><
www.HintsAndTips.com - Now with GMail Queue
www.MyQuickPoll.com - 2004 Election poll is #33
www.GotTheAnswerToSpam.com
"We plan for the future, we learn from the past, we live life in the present!"
|
|
|
|
|
I use ado.net do something to mdb database. I found out the mdb database's size was been increased when it run some time.I can't confirm what it is cause.
It is my class,
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace RoadLib
{
///
/// NdnDataBase の概要の説明です。
///
public class NdnDataBase
{
protected string m_strConnectionString;
private OleDbConnection m_connection = null;
private OleDbTransaction m_transaction = null;
///
/// NdnDataBase の概要説明です
///
public NdnDataBase()
{
//
// TODO: コンストラクタ ロジックをここに追加してください。
//
m_connection = null;
}
///
/// NdnDataBase の概要説明です
///
///
public NdnDataBase(string strConnectionString)
{
m_strConnectionString = strConnectionString;
m_connection = null;
}
///
/// DBコネクションを取得します
///
public OleDbConnection connection
{
get { return m_connection; }
}
///
/// DBトランザクションを取得します
///
public OleDbTransaction transaction
{
get { return m_transaction; }
}
///
/// データベースを開くために使用接続する
///
public string connectionString
{
get { return m_strConnectionString; }
set { m_strConnectionString = value;}
}
///
/// 指定のデータベースでセッションを開く
///
///
public OleDbConnection DBOpen()
{
if(m_connection == null)
{
try
{
//データベース接続パラメータを取得
if (m_strConnectionString == "")
{
return null;
}
m_connection = new OleDbConnection(m_strConnectionString);
//DBオープン
m_connection.Open();
}
catch(Exception err)
{
//NdnPublicFunction.WriteLog(m_strLogFileName, err.Message);
m_connection = null;
throw (err);
}
}
return m_connection;
}
///
/// データベースでセッションを閉じる
///
///
public void DBClose()
{
try
{
//DBクローズ
if (m_connection != null)
{
m_connection.Close();
}
}
catch
{
}
finally
{
//初期値を設定
m_transaction = null;
m_connection = null;
}
}
///
/// SQLステートメントを実行
///
///
///
///
///
public bool ExecSql(string strCommandText, OleDbConnection connection, OleDbTransaction transaction)
{
bool bReturn = false;
OleDbCommand dbCommand = new OleDbCommand(strCommandText, connection, transaction);
try
{
dbCommand.ExecuteNonQuery();
bReturn = true;
}
catch( Exception err)
{
throw (err);
}
return bReturn;
}
///
/// データベースでトランザクションを開始します
///
///
public bool BeginTrans()
{
bool bReturn = false;
try
{
if (m_connection != null)
{
m_transaction = m_connection.BeginTransaction();
bReturn = true;
}
}
catch (Exception err)
{
throw err;
}
return bReturn;
}
///
/// データベースでトランザクションをコミットします
///
///
public bool CommitTrans()
{
bool bReturn = false;
try
{
if (m_transaction != null)
{
m_transaction.Commit();
bReturn = true;
}
}
catch (Exception err)
{
throw err;
}
finally
{
m_transaction = null;
}
return bReturn;
}
///
/// データベースでトランザクションをロールバックします
///
///
public bool RollbackTrans()
{
bool bReturn = false;
try
{
if (m_transaction != null)
{
m_transaction.Rollback();
bReturn = true;
}
}
catch (Exception err)
{
throw err;
}
finally
{
m_transaction = null;
}
return bReturn;
}
}
}
I use it to operate mdb database.
for example:
NdnDataBase database = new NdnDataBase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\nara.mdb;User Id=adminassword=;");
try
{
database.DBOpen();
database.BeginTrans();
database.ExecSql("Delete from ttt");
...
database.ExecSql("Insert into ttt values(1, 2)", database.connection, database.transaction);
database.CommitTrans();
...
}
catch
{
database.RollbackTrans();
}
finally
{
database.DBClose();
}
it operation is in a timer event.
At last when the mdb database's size be to some size, the database is bad.
|
|
|
|
|
I'm not reading throught all that code. You have to be more specific.
While scrolling, however, I did notice that you are sending commands to the database that modify the contents of the database. So of course the file is going to expand. Typically databases files only ever expand until you explicitly run some sort fo compression or free space removal command on them.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Im just reading an ms press book about ado.net
here it says
"if teh connection is ".closed" the connection is returned to the pool and ready for reuse..
if the connection is ".disposed" the object is deleted from memory and cannot be reused by the pool"
is this really true?
i thought it was just the .net object wrapping the native connection that was killed and removed when disposing.
isn the actual pooling handled by oledb internally or something?
//Roger
|
|
|
|
|
IIRC the connection pool is internal to your ASP.NET application, not the database engines (some database engines don't support native pooling anyway). So if you dispose of a connection it will be released from the pool. If you just close the connection, it just gets stored in the pool until something wants a connection with the same connection string. This is much quicker than actually going back to the server and requesting a connection from there.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
