Update database with multiple rows of dataset - Database Discussion Boards

yarp16-Jun-06 23:32

16-Jun-06 23:32

Thanks for your detailed answer Garth,
Since my needs are rather basic I will most probably only use SQL basic commands. So I think I can cope with those SQLite limitations.
Also although I will design it for multi-users use it'll mostly be used by single user.
For the driver, yes it is a choice in itself but the thiner layer, the better.

> your p3DO explorer ... - wish I knew how to do some ... - not enough time
Yes it takes a lot of time, fortunately (or not Wink | ;)

I'm single and I love 2D/3D graphics. This gives me the will to go ahead.
Looking for the proper library/component is a hard part of the project (I made severall costly mistakes in the past).

Yarp
http://www.senosoft.com/

Re: SQLite for small software

Dinuj Nath17-Jun-06 20:03

Dinuj Nath

17-Jun-06 20:03

You can try out Firebird. It can be used both as standalone or as a full server. I think you can convert a standalone database to a server very easily. For more check http://www.firebirdsql.org/[^]

--
Don't take life seriously because you can't come out of it alive.
-Warren Miller
(From Monty²[^] bio)

Re: SQLite for small software

yarp18-Jun-06 7:13

yarp

18-Jun-06 7:13

Thank you for your advice. I already used Interbase in the past, it is a good database but not light enough for what I intend to do.
I would most probably choose it for professional clients running on a network (I've read SQLite is not good for transactionnal network access), but my needs are too basic even for Interbase.
Note - I don't say for FireBird since I don't know it.

Yarp
http://www.senosoft.com/

TCP/IP and Pipes for and ODBC Connection [modified]

FlorianS16-Jun-06 7:43

FlorianS

16-Jun-06 7:43

Hi,

I have a problem with and app that is connected to an SQL 2000 database
The user got a new server dual CPU 3.2 GHz Pentium 4 with 2G memory and the app is slower compare with the old machine a dual CPU 755 MHz Pentium 3 with 512 memory.

If you use the TCP/IP connection in ODBC driver, the new machine is very slow.
If you use Name Pipe in ODBC, both machine a doing well.
The old machine it is working well with both TCP/IP and Pipes.

Does anybody have any idea why it is such a big difference between TCP/IP and pipes for the new machine? The different is from 1 minute to 10 minutes for an operation.

Any thoughts are welcome

Thanks,

Florian

-- modified at 15:01 Friday 16th June, 2006

Re: TCP/IP and Pipes for and ODBC Connection

Garth J Lancaster16-Jun-06 20:14

Garth J Lancaster

16-Jun-06 20:14

my guess is Named-Pipes and ODBC are better at buffering, while TCP/IP works to a point then TCP/IP becomes saturated and network performance decreases.

(but there's a lot of variables here)

'g'

Selecting rows by earliest date

Stefan R16-Jun-06 3:37

Stefan R

16-Jun-06 3:37

I have a table "tbl_order" with to the fields "customer_number" and "date". there can be multiple rows with the same customer_number, but the date fields are allways unique.

these or potential rows in my table:

cust_no | date | item
---------------------------
21 2006-01-06 shovel
21 2006-03-05 axe
21 2006-05-05 widget
45 2006-05-01 fish tank
45 2006-05-02 ...

For every cust_no i want to retreive only the row with the earliest date. How can this be done?

what i want is this:
------------------------
21 2006-01-06 shovel
45 2006-05-01 fish tank

Any help would be greatly appreciated

/ Stefan

Re: Selecting rows by earliest date

Colin Angus Mackay16-Jun-06 4:06

Colin Angus Mackay

16-Jun-06 4:06

SELECT q.cust_no, earliest_date, item
FROM tbl_order as t
INNER JOIN 
(
    SELECT cust_no, MIN(date) as earliest_date
    FROM tbl_order
    GROUP BY cust_no
) AS q ON t.cust_no = q.cust_no AND t.date = q.earliest_date

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Update database with multiple rows of dataset

amittinku16-Jun-06 2:56

amittinku

16-Jun-06 2:56

//---------Below is code:----------------------------------------------------

string conn_str="Provider=MSDAORA.1;User ID=navuser2;Data Source=orcl;Password=navuser2";
OleDbConnection conn=new OleDbConnection(conn_str);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "select * from test";
cmd.Connection = conn;
OleDbDataAdapter adap=new OleDbDataAdapter(cmd);
DataSet dtset=new DataSet();
adap.Fill(dtset,"test");
string ins= "insert into TEST values(30,'900')";
DataTable tab1;
tab1=dtset.Tables["test"];
DataRow r1;
r1=tab1.NewRow();
adap.InsertCommand=new OleDbCommand(ins,conn);
tab1.Rows.Add(r1);
adap.Update(dtset,"test");
conn.Close();
//------------------------------------------------------------------------

1.The above code is working fine for insert statements. How to use the above code in case if we are trying to update database with more than 1 updated row of dataset. e.g. instead of using "insert into TEST values(30,'900')";, use "update test set emp=100". Here we donot know how many rows will be updated in dataset.

SQL Server 2005 Expiration Date

Brendan Vogt15-Jun-06 23:51

Brendan Vogt

15-Jun-06 23:51

Hi,

I'm running SQL Server evaluation version, and it sould ne close to the 6 month expiry date. How do I check when it expires?

Regards,
ma se

how do i read a fuzzy date from MS SQL? [modified]

feline_dracoform15-Jun-06 9:25

feline_dracoform

15-Jun-06 9:25

*solved* this is what you get for posting questions at 8:30pm local time *sleepy*

after some more digging i have noticed a second column that has a int, 0, 1 or 2, and it exactly correlates with the "fuzzy date". so a second flag is being used to achieve this

***

i am trying to convert data from one system (stored in MS SQL) into a different system stored in MySQL.

in the MS SQL some records are stored with a "fuzzy date", they only has a year, no day and no month. we have the same concept the new system, where we store the date as 00/00/1988

using a SQL client on MySQL with one of these dates i am told it is 01/01/1988, but if i issue the same query on the MySQL command line i am told the date is 00/00/1988, so i have to distrust what clients are telling me.

in the MS SQL database i have tried "enterprise manager version 8" and it is telling me the date is 01/01/1988, but the software (i have NO access to any source code for the old system) reports the date as "1988", no day and no month.

either the old system uses some flag somewhere to say "this is a fuzzy date" or the MS SQL client is "protecting" me from the invalid data.

in MSSQL the column is of type "datetime", and i have found dates that show as "01/01/1985" and "03/2001" in the old system, so i can rule out the theory that you cannot record data on the 1st January.

any pointers or bright ideas would be most welcome. i know something about MySQL but am learning MSSQL as i go along Roll eyes | :rolleyes:

zen is the art of being at one with the two'ness

-- modified at 15:34 Thursday 15th June, 2006

Re: how do i read a fuzzy date from MS SQL?

Farhan Noor Qureshi15-Jun-06 9:36

Farhan Noor Qureshi

15-Jun-06 9:36

Look for ISDATE(), CAST(), CONVERT() DATEPART() and DATENAME() functions in BOL.

Farhan Noor Qureshi

^{if (this == this) thow this;}

Record Set in ESQL

itkid15-Jun-06 2:59

itkid

15-Jun-06 2:59

Hi,
I am passing the customer id to DataBase and I want it should return the recordset. Can you pls let me know how to do it in ESQL. I am new to ESQL. It would be grate if you also let me know how to access each individual field.

Thanks Smile | :)

Re: Record Set in ESQL

Farhan Noor Qureshi15-Jun-06 9:39

Farhan Noor Qureshi

15-Jun-06 9:39

Does this "ESQL" thing come with any sort of docs?

Farhan Noor Qureshi

^{if (this == this) thow this;}

To disable unique constraint

neha301315-Jun-06 2:44

neha3013

15-Jun-06 2:44

Hi all,

Please help me its very urgent for me..

How we can disable and enable the unique constraint for an index.
I have tried ALTER Table_name disable CONSTRAINT constraint_name; But it is giving error no such constraint is present..

This unique constraint is on index...and not on single index it is on multiple index...

Please try to solve my problem

Re: To disable unique constraint

Chris Meech15-Jun-06 2:58

Chris Meech

15-Jun-06 2:58

This link[^] gives a pretty good explanation for all uses of constraints.

Chris Meech

I am Canadian. [heard in a local bar]

When no one was looking, every single American woman between the ages of 18 and 32 went out and got a tatoo just above their rumpus. [link[^]]

Re: To disable unique constraint

Farhan Noor Qureshi15-Jun-06 10:30

Farhan Noor Qureshi

15-Jun-06 10:30

What DB are you using?

Farhan Noor Qureshi

^{if (this == this) thow this;}

Re: To disable unique constraint

neha301315-Jun-06 20:47

neha3013

15-Jun-06 20:47

DB is oracle

Combining two data base of the same schema

militiaware14-Jun-06 22:09

militiaware

14-Jun-06 22:09

i have a data base called DBTest on machine one and contains a table called DTTable1
and i have the same database DBTest on machine Tow.

now i filled the data into DTTable1 on machine one from "A" to "K"
and i filled the data from "L" to "Z" on machine Two.

What i want is to Combine the data from DTTable1 in the Machine One with the DTTable1 on Machine Two.

How that can be done? Frown | :(

Faris Madi
Nothing Comes Easy (N.C.E.)

Re: Combining two data base of the same schema

r.stropek15-Jun-06 0:20

r.stropek

15-Jun-06 0:20

Hi!

It depends if you want to combine the content within a SQL statement without transferring the data permanently or if you want to create a third table that physically contains the combined data.

If you need a query and you do not want to tranfer the data

1. create a linked server from machine one to two (or the other way round; you can do this in SQL Server management studio (2005) or Enterprise Manager (2000)
2. select * from DBTest.dbo.DTTable1 union all two.DBTest.dbo.DTTable1

As an alternative to 1 you can also use OPENQUERY or OPENROWSET (see SQL Server Books Online for details).

If you want to move the combined data into a third table use DTS (SQL 2000) or SSIS (SQL 2005). With these tools it is quite easy to move data from one table into another.

Hope this helps.

Regards,
Rainer.

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

How to change date format

MikeRT14-Jun-06 20:20

MikeRT

14-Jun-06 20:20

Hi,
I am new for database query. I am using Ms Access to store data. In Ms Access the date is in format of MM-DD-YYYY. I want to retrive that date in DD-MM-YYYY format. Can any body tell me how to convert this date format.
Thanks in advance.

Mike Cry | :((

Re: How to change date format

Eric Dahlvang15-Jun-06 3:55

Eric Dahlvang

15-Jun-06 3:55

Format$(mydate,"dd-mm-yyyy")

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Alter Tables

myNameIsRon14-Jun-06 19:02

myNameIsRon

14-Jun-06 19:02

Hi,
I'm wondering if it's possible to join ALTER TABLE statements so I don't have to have a bunch of ExecuteNonQuery commands.

ALTER TABLE Products ALTER COLUMN ItemNumber TEXT(15)
ALTER TABLE ItemsSold ALTER COLUMN ItemNumber TEXT(15)

thanks,
Ron

Re: Alter Tables

r.stropek15-Jun-06 0:29

r.stropek

15-Jun-06 0:29

As far as I know this is not possible.

Regards,
Rainer.

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

Re: Alter Tables

myNameIsRon15-Jun-06 14:39

myNameIsRon

15-Jun-06 14:39

Thanks Rainer.

Ron

Re: Alter Tables

Eric Dahlvang15-Jun-06 4:47

Eric Dahlvang

15-Jun-06 4:47

You could create this stored procedure, then just execute it with a delimited list of table names:

CREATE PROCEDURE AlterMyTables 
@cTablesToAlter varchar(500)
AS

declare @cSQL varchar(100)
declare @nLoc int
declare @nLoc2 int

select @nLoc = 1

while (@nLoc<len(@cTablesToAlter))
begin
	select @nLoc2 = CHARINDEX(',',@cTablesToAlter,@nLoc + 1 )

	if (@nLoc2=0)
	  	select @nLoc2 = len(@cTablesToAlter) +1
	
	select @cSQL = 'ALTER TABLE ' + SUBSTRING(@cTablesToAlter,@nLoc,@nLoc2-@nLoc) + ' ALTER COLUMN ItemNumber TEXT'
  	exec(@cSQL)

	select @nLoc = @nLoc2 +1
end

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

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