Database Discussion Boards

michanne121-Jul-04 18:22

21-Jul-04 18:22

Just my 2 cents....

Sql S 2000?
I would put this part into a table valued function:

For sex I would use and in (i.e WHERE ttester.sexid in(@sexid):
IF @sexid = '*' SET @sexid = 'm,f'
That would narrow it down a bit

BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID

Then I would call the function:

SELECT Distinct dayspast,etc*
From dbo.myfunction(@sexid,@vars...etc) f
WHERE not exists (
SELECT tTester.TesterID (don't need distinct or group.)
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
and f.testerid = tTester.TesterID
)
AND
f.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = f.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = f.TesterID)
order by f.LastName

Choosing a new db technology

KMerker21-Jul-04 1:50

KMerker

21-Jul-04 1:50

To All,

I have written a database application using MS Visual C++ 6.0 and MFC. I am using the CDao classes (and using the Jet 3.5 engine/MS Access 97).

The application seems to work very well and I have many installations of the application and satisfied customers.

Now I am switching to Visual Studio .NET. I’d like to continue to use MFC and write my code using Visual C++, however, I have read that DAO is becoming obsolete and when I compile using .NET I get the ‘deprecated’ warning whenever the compiler sees a reference to DAO.

So I need to switch to a new DB technology. I liked DAO because I could directly read and write to an Access database without having to register the DB as an ODBC.

Is there any recommendation on the correct technology to use? The database/application needs to allow for multiple users from multiple computers. It is a client/server type of application and must initially read and write to an Access database. The long term plan is to go to an SQL Server so this should be taken into consideration.

Thank You

Re: Choosing a new db technology

Colin Angus Mackay21-Jul-04 3:37

Colin Angus Mackay

21-Jul-04 3:37

KMerker wrote:
It is a client/server type of application and must initially read and write to an Access database. The long term plan is to go to an SQL Server so this should be taken into consideration.

Is there any particular reason why it must read/write to Access?

Given that you've been using Access as your database server until now and you want to move to SQL Server I'd suggest MSDE which uses the SQL Server engine, but is somewhat limited (no GUI admin tools, limited numbers of users and so on). You can also link it to MS Access if necessary, and you can use SQL Server's admin tools to connect to an MSDE database.

Does this help?

"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

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

Re: Choosing a new db technology

darkbyte24-Jul-04 10:38

darkbyte

24-Jul-04 10:38

I highly recommend using ADO. It almost as simple as DAO and it offers greater flexibility.

Regarding the need for ODBC, I don't believe is it required for ADO to work. But i might confuse things with DSN. Regardless, you can use Microsoft Jet to connect directly to ADO.

Its the same technology as used by .NET or even VBScript/JScript on web servers. I've learnt to use it mostly with MSDN examples. The concept seems obscure at first, but its COM+ related.

in-place editing

bora3ee21-Jul-04 1:15

bora3ee

21-Jul-04 1:15

hi everyone

my problem is that i can't update any field in the datalist ..i use the update event handeler ...when i try to update any field ...the field remains with its initial value...and when i debug the page...i noticed that the value of
((TextBox)cell[x].cotrol[0]).text is equal to ""..that it can't feel the value i insert...how can i solve this..

thx alot

three doubts :(

xcavin20-Jul-04 2:09

xcavin

20-Jul-04 2:09

1.can i create a table from stored proc?
2.can i insert/delete/update a table from sp?
3.wots that extended sp can do that ordinary sp cannot?

NOTE: I dont have access to SQL server of any type.

Re: three doubts :(

Michael Potter20-Jul-04 2:55

Michael Potter

20-Jul-04 2:55

xcavin wrote:
NOTE: I dont have access to SQL server of any type.

Then what do you care?

1) Yes
2) Yes
3) Extended sp can access non-TSQL code and therefore system resourses other than the SQL Server itself.

Re: three doubts :(

xcavin20-Jul-04 6:06

xcavin

20-Jul-04 6:06

Michael Potter wrote:
Then what do you care?

Learning SQL programing without access to SQL server.
Coz nothing can stop me.;)

Re: three doubts :(

Colin Angus Mackay21-Jul-04 3:40

Colin Angus Mackay

21-Jul-04 3:40

xcavin wrote:
Learning SQL programing without access to SQL server.

Then how will you know you are learning correctly?

Why now download MSDE and use that? Or if you want to learn all the GUI tools also there is also a 120 day trial you can download also.

DataSet.Merge with remoting object

Usur19-Jul-04 20:38

Usur

19-Jul-04 20:38

Hi I'm going to try to explain what i'm trying to do and if someone is able to give pointers on what i'm doing wrong i would be very grateful.

The app has one background task (service) and one configure application.
The background service loads a typed dataset from a sql server on startup and copies it to the remoting object with DataSet.Merge() wich exposes this over remoting.

settings.TrendTags = sql.GetTrendTags();
RemoteSettings.TrendTags.Merge(settings.TrendTags);
RemotingServices.Marshal(RemoteSettings, "Trend");

This seams to work ok. After Merge() both datatables count property returns the same. (2)

I then load the DataSet in the client app and copies it to a local dataset for modification
Reason for not using the remoting object directly is the inability to see properties while debugging.

settings = (SrvSettings)Activator.GetObject(typeof(SrvSettings) , "tcp://localhost:4188/Trend");
LocalSettings.TrendTags.Merge(settings.TrendTags);

Also this works ok. LocalSettings Contains the full recordset.
Then the user edits the recordset with something like.

TrendTable.TrendTagsRow tr = (TrendTable.TrendTagsRow)LocalSettings.TrendTags.TrendTags.NewRow();
tr.SomeProperty = "Something";
LocalSettings.TrendTags.TrendTags.Rows.Add(tr);

LocalSettings.TrendTags.TrendTags.Count now reports one more.

I then try to apply this change to the remoting object with

settings.TrendTags.Merge(LocalSettings.TrendTags);
I've tried some different things but DataTable, DataSet GetChanges ApplyChanges() etc...
but i cannot with merge get the added row to go into settings wich is the remoting object.
I've done the exact same thing twice before with success but this time it refuse to work.

Anyone know why?

Fredrik Högberg

Missing System.Data.OracleClient namespace- what am I missing?

ThomasH119-Jul-04 12:36

ThomasH1

19-Jul-04 12:36

Hey guys, what am I missing here? System.Data.OracleClient gets installed with .NET Framework 1.1, correct? If I set up a "using System.Data.OracleClient;" in my C# code, I get an error message that says "The type or namespace name 'OracleClient' does not exist in the class or namespace 'System.Data' (are you missing an assembly reference?)".

I looked in C:\windows\Microsoft.NET\Framework\v1.1.4322, and yes, there is a System.Data.OracleClient.dll file. So I think I'm missing something easy... anybody come across this yet?

Oh the PC is Windows XP Pro, and I'm using Visual Studio 2003 Enterprise Architect, with .NET framework 1.1 - the 1.0 framework has never been installed on this PC (the computer is one month new). If, inside of the VS.NET IDE, I start typing "using System.Data.", Intellisense only shows me choices of "Common, Odbc, OleDb, SqlClient, SqlTypes". OracleClient isn't on the Intellisense list, but even if I force-type it, I still get that build error.

Thanks!

-Thomas

Re: Missing System.Data.OracleClient namespace- what am I missing?

VenkatFor.NET21-Jul-04 7:22

VenkatFor.NET

21-Jul-04 7:22

Looks like you have not added the reference to System.Data.OracleClient.dll. Check it out

Bhaskara

Re: Missing System.Data.OracleClient namespace- what am I missing?

ThomasH122-Jul-04 14:06

ThomasH1

22-Jul-04 14:06

Yep, that was the problem. Just had to right-click "references" in the project window, and say "Add"!

-Thomas

mdf/ldf file names for a given db?

dmbf1b519-Jul-04 10:59

dmbf1b5

19-Jul-04 10:59

Hi,

I'm in a situation where my VB.NET code will know the name of a database, and can connect to it, but needs to get the mdf and ldf file names for that database. Is there a property of some ADO.NET object that will tell me the file names? Or maybe a query to run to get the file names?

Thanks
David

Re: mdf/ldf file names for a given db?

Colin Angus Mackay21-Jul-04 3:56

Colin Angus Mackay

21-Jul-04 3:56

In the master database there is a system table called sysdatabases which gives the full path to the primary file of each database. In each database itself there are two system tables that would be useful to you the first is sysfilegroups which joins to sysfiles. It is sysfiles that actually contains the list of files for the database.

If you need more information the SQL Server books on-line should fill in all the details you need.

Does this help?

Re: mdf/ldf file names for a given db?

dmbf1b521-Jul-04 18:20

dmbf1b5

21-Jul-04 18:20

That's exactly what I needed, thanks!

David

convert access database to mysql

Arindra19-Jul-04 9:38

Arindra

19-Jul-04 9:38

is there some way to convert a access database to a mysql ? I am not interested in expensive tools , as i can not pay for this .

Re: convert access database to mysql

ThomasH119-Jul-04 12:42

ThomasH1

19-Jul-04 12:42

The only technical thing I know about Access is that it never should have been written. Even MSDE is better than Access! To be safe, I'd export the Access database as a CSV file, manually create your schema inside MySql, and then tell MySql to import the CSV file. It's a little more work then a fancy point & click tool, but you know your data will go through perfectly.

-Thomas

Re: convert access database to mysql

Alexander Wiseman20-Jul-04 7:08

Alexander Wiseman

20-Jul-04 7:08

Hi,

Take a look at this: http://solutions.mysql.com/software/?item=98[^]. It's from the MySQL website. Although the tool is shareware, you might be able to convert with the demo version. I haven't looked so I'm not sure if they disable it in the demo version.

In any case, there are tools, so if that doesn't work, you can look elsewhere on the MySQL website or on Google.

Sincerely,
Alexander Wiseman

Est melior esse quam videri
It is better to be than to seem

Please help, its very urgent ( retrieving datetime )

deep_george_zach19-Jul-04 8:15

deep_george_zach

19-Jul-04 8:15

Consider the following:

Database name - junkDB
Table name - junk
Fields - 1) slno char(10)
2) name char(20)
3) message char(200)
4) date_time datetime

the 4th field is of type datetime. (eg: 07/20/2004 5:30:45 PM)

i am connecting to the database using VC++ (SDK), using the #import (msado15.dll) statement.

when i retrieve the date_time field, i get a floating point value (somewhat like this 37842.4578131)

can someone help me with this. how can i retrieve the datetime field properly.

thankyou,
Deep George Zachariah

Re: Please help, its very urgent ( retrieving datetime )

Colin Angus Mackay19-Jul-04 9:10

Colin Angus Mackay

19-Jul-04 9:10

deep_george_zach wrote:
i get a floating point value (somewhat like this 37842.4578131)

Possibly the number of days since 1/1/1901

Re: Please help, its very urgent ( retrieving datetime )

deep_george_zach19-Jul-04 16:04

deep_george_zach

19-Jul-04 16:04

is there any way to get the datetime directly....

if no, then how do i convert the floating point number into date...

any idea ??

thankyou
Deep George

Re: Please help, its very urgent ( retrieving datetime )

vikram attiganal19-Jul-04 20:00

vikram attiganal

19-Jul-04 20:00

Use the Format() method to format it in whatever way you want. Like Format("%Y-%m-%d") formats it to Year, Month and Day format. Format method is member of CTime class.

Urgent : Running SQL Script files through a SqlCommand object?

matthias s.19-Jul-04 7:37

matthias s.

19-Jul-04 7:37

Hi there,

I have generated some SQL script-files using the SQL-Enterprise Manager (for
creation of tables, stored procedures, etc.).

When running those script files through the isql or osql utility, I have the
possibility to capture the output to a file, which is sort of importand to
me.

What I'd like to do is to write a small utility (Windows app in c#) which
will execute my sql-script-files on the SQL server. Again, I need to capture
the output. Can this be done using an SqlCommand-object providing the
contents of each individual script-file as the command text or do I have to
use isql. In both ways I'd like to know how it is possible to capture the
output.

The output is actually only required if there have been errors during
batch-execution.

Any help is greatly appreceated. Thanks in advance.

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: Urgent : Running SQL Script files through a SqlCommand object?

dishanf19-Jul-04 17:00

dishanf

19-Jul-04 17:00

isql has output parameter : /o

public int Exec( string sCommand )
{
int nResult = 0;

m_oInstallProcess = new Process();
m_oInstallProcessInfo = new ProcessStartInfo("cmd");

m_oInstallProcessInfo.UseShellExecute = false;
m_oInstallProcessInfo.RedirectStandardInput = true;
m_oInstallProcessInfo.RedirectStandardOutput = true;
m_oInstallProcessInfo.RedirectStandardError = true;
m_oInstallProcessInfo.CreateNoWindow = true;

m_oInstallProcess.StartInfo = m_oInstallProcessInfo;

m_oInstallProcess.Start();

m_oCommandWriter = m_oInstallProcess.StandardInput;
m_oResultReader = m_oInstallProcess.StandardOutput;
m_oErrorReader = m_oInstallProcess.StandardError;

m_oCommandWriter.AutoFlush = true;

m_oCommandWriter.WriteLine( sCommand );

m_oCommandWriter.Close();
m_oCommandWriter = null;

string s = m_oResultReader.ReadToEnd();
m_sErrorMsg = m_oErrorReader.ReadToEnd();

if( s.IndexOf("Cannot open input file") > 0 )
throw new Exception( "Command:\r\n"+sCommand+"\r\nError:\r\nCannot open input file!" );
else if( m_sErrorMsg.Trim().Length > 0 )
throw new Exception( "Command:\r\n"+sCommand+"\r\nError:\r\n"+m_sErrorMsg+"" );
else
System.Threading.Thread.Sleep( 10 );

return nResult ;

}

D!shan

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