Database Discussion Boards

snodgrass1-Sep-04 4:28

1-Sep-04 4:28

Hi All,
I am writing a multithreaded application in Visual C++ 6.0, which uses ADO to connect to either an access or sql database.
My question is, since many of my threads want access to the database, for both reading and writing, what do I need to do to thread safe it? Can I just have one global _ConnectionPtr and allow all the threads to use this, or do I need to wrap it in a section?

Many Thanks,

trigger problem?

sumank31-Aug-04 19:59

sumank

31-Aug-04 19:59

How can we update or change the content of a text file using triggers? If anyone have solved this problem earlier please help me.
Thanks

ADO.net - Data source doesn't recognise my dataset

savagerx31-Aug-04 6:53

savagerx

31-Aug-04 6:53

I have created a regular dataset using the following statement:

[CODE]
DataSet myDS = new DataSet();
. //initializing code to create the neccessary
. //tables, columns and constraints
.
[/CODE]

but then when I created a datagrid and attempt to assign my dataset and datamember with my dataset and datatable respectively via the properties menu, the editor failed to recognize my dataset!

I have tried to check my dataset and verified that it's okay.

What puzzles me are the following questions

Firstly, why doesn't the controls recognize the dataset?
If I have used the Visual Studio.net's GUI editor to create the dataset and define the tables and columns there, the datasource property actually recognizes it. Further investigation reveals that it is a class that inherits from dataset(correct me if I'm wrong). Therefore, if I intend to code my own dataset of this sort, what should I do and what interface should I derive from?

I thank you for your patience for reading up to this point. And I really do appreciate if anyone here could help.
My thanks in advance.
Confused | :confused:

The confused one

Re: ADO.net - Data source doesn't recognise my dataset

Rocky Moore31-Aug-04 20:05

Rocky Moore

31-Aug-04 20:05

I beleive it has to be added to your form to be seen by the wizards. If you use a custom dataset this can be a pain. Depending on the application, you can build your datasets into a dll and then add them as a component on your toolbar where you then drop the on your form and that seems to work well. Not sure of other methods to make them see them.

I think it would be a great feature if there was some attribute that could be added to any collection that would allow the wizard to see it and add it to a form without having to go through hoops. For me, I usually put all my datasets and data managers into a Data abstraction layer, so most of the time the wizards are useless unless I use the dll method above.

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!"

Stored Proc not returning my Output

matthias s.31-Aug-04 3:51

matthias s.

31-Aug-04 3:51

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

Re: Stored Proc not returning my Output

Michael P Butler31-Aug-04 4:45

Michael P Butler

31-Aug-04 4:45

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 [^]

Re: Stored Proc not returning my Output

matthias s.31-Aug-04 4:55

matthias s.

31-Aug-04 4:55

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 />
                //  add the DateTime output param<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 />
			//	catch stuff omitted...<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

Re: Stored Proc not returning my Output

Michael P Butler31-Aug-04 5:07

Michael P Butler

31-Aug-04 5:07

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 [^]

Re: Stored Proc not returning my Output

bnieland31-Aug-04 5:00

bnieland

31-Aug-04 5:00

Post your ADO code where you get NewDateModified out of the command object...

DTrent

Solution found!

matthias s.31-Aug-04 5:19

matthias s.

31-Aug-04 5:19

Hi DTrent!

Thanks for your reply. Your sole proposal has answered my question ("out of the command object..."??? Smile | :)

)

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 />
                //  add the DateTime output param<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 />
			//	catch stuff omitted...<br />
			<br />
			//	To make the code work, uncomment the following line.<br />
			//	Omitting the following line, was actually the problem<br />
			//	but I thought, that the value (cause it got passed to the SqlParameter Ctor)<br />
			//	gets assigned automatically.<br />
			<br />
			//	dtResult = (DateTime) prmNewDtModif.Value;<br />
			<br />
			//	start using the dtResult variable<br />
			if (dtResult < dtSomething) {<br />
				//	go do stuff...<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

Trace automation

Deb22230-Aug-04 11:43

Deb222

30-Aug-04 11:43

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

Deriving from DataTable and adding rows in a different thread while displaying the table in a DataGrid.

Michael Hulthin30-Aug-04 5:37

Michael Hulthin

30-Aug-04 5:37

Hello, not even sure if this is the right forum for this question but here
goes.. Smile | :)

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.. Wink | ;)

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. Smile | :)

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 />
       // This will make DataTable fire ColumnChanging and ColumnChanged events. How can I send them myself?<br />
       // I know I can shut of the eventdispatching from DataTable by using BeginLoadData, LoadDataRow and EndLoadData. But how do I send the events myself?<br />
    }<br />
  }<br />
}<br />

and

<br />
protected override void OnRowChanged(System.Data.DataRowChangeEventArgs e)<br />
{<br />
  // TODO:  Add TTDataTable.OnRowChanged implementation<br />
  // base.OnRowChanged (e);<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

SQL question

NewPureLand28-Aug-04 18:00

NewPureLand

28-Aug-04 18:00

<%
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

Re: SQL question

NewPureLand28-Aug-04 19:38

NewPureLand

28-Aug-04 19:38

Is there any one who can make it perfect for on my question?

Re: SQL question

Colin Angus Mackay28-Aug-04 22:55

Colin Angus Mackay

28-Aug-04 22:55

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!

Re: SQL question

mike.hall@lehigh.edu30-Aug-04 2:46

mike.hall@lehigh.edu

30-Aug-04 2:46

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.

functions

jairajanbrothers27-Aug-04 23:42

jairajanbrothers

27-Aug-04 23:42

hhow toexecute the functions

jairaj

Re: functions

Mazdak27-Aug-04 23:51

Mazdak

27-Aug-04 23:51

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

Re: functions

Colin Angus Mackay28-Aug-04 2:04

Colin Angus Mackay

28-Aug-04 2:04

Actually, you don't EXECUTE functions. You do that to Stored Procedures.

Re: functions

Mazdak28-Aug-04 3:10

Mazdak

28-Aug-04 3:10

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

Re: functions

Colin Angus Mackay28-Aug-04 4:31

Colin Angus Mackay

28-Aug-04 4:31

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.

Re: functions

Mazdak28-Aug-04 4:40

Mazdak

28-Aug-04 4:40

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

Confused | :confused:

Mazy

"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji

Re: functions

Colin Angus Mackay28-Aug-04 2:04

Colin Angus Mackay

28-Aug-04 2:04

To run a User Defined Function (or UDF) you need to do something like:

SELECT * FROM dbo.MyFunctionName()

Re: functions

Rocky Moore31-Aug-04 19:55

Rocky Moore

31-Aug-04 19:55

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!"

Mdb question

jzb27-Aug-04 23:40

jzb

27-Aug-04 23:40

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.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.