Re: To disable unique constraint - Database Discussion Boards

Update database with multiple rows of dataset

amittinku16-Jun-06 2:56

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

Re: Alter Tables

myNameIsRon15-Jun-06 14:38

myNameIsRon

15-Jun-06 14:38

Thanks Eric, I'll give it a try.

Ron

'Best practices' info needed [modified]

Gary Wheeler14-Jun-06 10:49

Gary Wheeler

14-Jun-06 10:49

I'm working on my first data base project. We're recording some historical information from a product, and it seems natural to go ahead an put it in a data base for later retrieval.

Here are some of the things I'm considering:

ODBC: I want independence from the actual DBMS. I'll provide a 'default' data base with the product, but want to give the customer the option of having us record our information directly in his data base.

SQL Server 2005 Express: I'm looking at this for my 'default' data base. It's free, and the install appears to be easy.

MFC: Yeah, I know MFC is horribly old school, but this is an add-on to an existing MFC-based product. .NET is out of the question. We're developing using VS.NET 2003, by the way.

Service: I don't know if it makes a difference, but this will probably be implemented as a service.

Do you guys have any sources or suggestions for 'best practices', tips, or "do's and don'ts"?

Software Zen: delete this;

Re: 'Best practices' info needed

Colin Angus Mackay14-Jun-06 11:10

Colin Angus Mackay

14-Jun-06 11:10

Gary Wheeler wrote:
ODBC: I want independence from the actual DBMS

I'd ignore ODBC, and go for implementing a provider model. You build a DAL (Data Access Layer) that you can swap out and replace with a new one if you change your database. You are likely to have to change a lot of code anyway even if you do go the ODBC route. Different databases use different dialects of SQL so any advantages of using a generic connection like ODBC or OleDB are usually just a matter of perception rather than of reality.

The way I usually go about a DAL is to have it handle the database connections internally (an abstract base class), getting its settings from a configuration file. The public methods on the DAL classes are just proxies to the stored procedures and there is very little processing going on other than marshalling datatypes.

The abstract base class has some generic functionality that calls the stored procedures and returns the data. The public methods will take parameters and pass them to the stored procedure. The results are then returned from the method (as generic types - not database specific types).

This way, if you change your database all you need to do is change the DAL classes.

If you write your DAL against SQL Server 2005 Express Edition then it will work without change if you migrate to a fuller SQL Server edition.

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

Re: 'Best practices' info needed

Gary Wheeler15-Jun-06 1:18

Gary Wheeler

15-Jun-06 1:18

Thanks, Colin Rose | [Rose]

.

I was hoping that, since I'm only inserting data into the data base, that ODBC was sufficiently portable. If I don't issue SQL I construct myself, would that be sufficient?

Colin Angus Mackay wrote:
If you write your DAL against SQL Server 2005 Express Edition then it will work without change if you migrate to a fuller SQL Server edition.

That was part of the appeal to SQL Server 2005 Express.

Software Zen: delete this;

DAO - Stored Procedure Execution

KSMANN14-Jun-06 9:32

KSMANN

14-Jun-06 9:32

Hello Everyone

Iam working(porting) on a legacy application which uses MFC DAO based classes to access MS-Access. I need to execute stored procedure on the SQL Server database how can I do that using CDaoQueryDef?

Thanks in advance for your time and help.

regards,
KS

System.Data.DBConcurrencyException

IMC200614-Jun-06 7:30

IMC2006

14-Jun-06 7:30

I have a app with a grid and am working on updating data from the grid to the data base. the update is triggered off a menu item. the code is as follows:

try
{
// New data entered to Contract QA_Setup table
DataAccess da = new DataAccess();

string sproc = "prc_QA_Setup_upd";
string dbAction = "Update";
string tableName = "QA_Setup";

// Update to find grid changes
//ug.Update();
ug.UpdateData();

// to retrieve ds with modified rows to grid
ds.GetChanges(DataRowState.Modified);

// Call to DAL to update QA_Setup with new data
da.updateDB(ds, sproc, dbAction, tableName);
}

This passes to the DAL and tries to run:

private void prepareUpdate(string sproc, string tableName)
{
// Create parameters for the QA_Setup dataset via Sqlcommand
SqlCommand cmd = new SqlCommand(sproc, conn);

// call set cmd properties
defineParams(tableName, cmd);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sproc;
//RunProc(sproc);
//RunProc(cmd);

dap.UpdateCommand = cmd;
}

However I get a System.Data.DBConcurrencyException, what am I missing?

sqldataadapter sqlcommandbuilder stripping off identity column HELP???

isgrom14-Jun-06 7:16

isgrom

14-Jun-06 7:16

I'm retrieving data from a mdb table and inserting it into a sqlserver table. The tables source and destination tables are identical. The mdb table has a PK that is set to autonum in the mdb and identity=yes in sql server.

The tables in the sql server are all empty. I want to simple insert all the data from the datatable into the sql server table including the original PK and autonum values.

I'm using a sqldataadapter and a commandbuilder to automatically gen the insert statements. But the cb's strips off the pk field from its statement. I basically want to invoke the adapters.update method against the datatable and have all the rows inserted. I've played around with SET IDENTITY_INSERT table ON but still the same issue. Seems to be that the commandbuilder is querying the schema of the table and noting the pk is an identiy column and is stripping it out of the insert statement.

PS I've already thought about manually generating the sql statements but its very slow as I have to cycle through each row and do this.

Dim cnn As New SqlConnection("server=glenn;database=test1;uid=sysadm;pwd=sysadm")
Dim adp As SqlDataAdapter
Dim cbd As SqlCommandBuilder

strSql = ""
strSql = "SET IDENTITY_INSERT " & c_strTable & " ON"
cmd = New SqlCommand(strSql, cnn)
cnn.Open()
cmd.ExecuteNonQuery()

adp = New SqlDataAdapter("select * from table", cnn)
cbd = New SqlCommandBuilder(adp)
Console.WriteLine(cbd.GetInsertCommand.CommandText) ' missing pk column? why?

adp.update(datatable)

Thanks
Glenn

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