Re: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. - Database Discussion Boards

myNameIsRon4-Sep-06 22:36

4-Sep-06 22:36

Hi,
I'm working with MS Access 2000 database...

I would like to use SQL to create a new table and set a field to "Allow Zero Length" = Yes. Is this possible? I can't seem to get it to work.
CREATE TABLE NewTable (FieldName TEXT(30) ALLOWZEROLENGTH)

thanks,
Ron

Write XML File Using SQL Server 2000

FriendlySoluations4-Sep-06 21:41

FriendlySoluations

4-Sep-06 21:41

Dear Sir
Please tell me how can I write XML file using SQL?
(I need the syntax for the query which perform this task)

Thank You

MNG

Re: Write XML File Using SQL Server 2000

Marek Grzenkowicz4-Sep-06 22:55

Marek Grzenkowicz

4-Sep-06 22:55

You can use FOR XML clause to retrieve result in XML format. For example:

SELECT TOP 10 *<br />
FROM sysobjects<br />
FOR XML AUTO, ELEMENTS

But I don't know if it is possible to write this result straight to a file.

System.ArgumentException in accesing data from DatagridView

Rocky#4-Sep-06 20:49

Rocky#

4-Sep-06 20:49

hello every one!

well I got this query here, I'm really stuck in this error. I'm working oin a master detail form in .net 2.0 C# in Windows Forms. its a System.ArgumentException that I'm getting here. I've these dataGridview on the form. I've made a typed dataset for it. the tables are these

PurchBill(PurchBillID, VendID, Bookno, BillDate)

Purch(purchID,purchbillID,Serial, ModelID, PresentAt,PurchWarranty,PurchPrice)

ProdLine

ProdModel

and vu_manNames thats a view

I've set up seperate dataGridViews for Purch, ProdLine and ProdModel and for selecting the manufacturer name here. Look at this code

private void saverecord()
{
try
{

int n = 0;
if (flagUpdate == false)
{
for (int row = 0; row < dataGridViewPurch.Rows.Count; row++)
{
DataRow dr = dsPurch1.Purch.NewRow();
dr["PurchBillID"] = sqlInsertCommandPurch.Parameters["@purchBillID"].Value = dsPurch1.PurchBill[this.BindingContext[dsPurch1, "PurchBill"].Position].PurchBillID;

MessageBox.Show("Value of Serial \n" + dataGridViewPurch.Rows[row].Cells["Serial"].Value.ToString());

dr["Serial"] = sqlInsertCommandPurch.Parameters["@Serial"].Value = dataGridViewPurch.Rows[row].Cells["Serial"].Value.ToString();
dr["PurchPrice"] = sqlInsertCommandPurch.Parameters["@PurchPrice"].Value = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchPrice"].Value.ToString());
MessageBox.Show("Value of ModelID: " + dsPurch1.ProdModel[this.BindingContext[dsPurch1, "ProdModel"].Position].ModelID.ToString());
dr["PurchWarranty"] = sqlInsertCommandPurch.Parameters["@Purchwarranty"].Value = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchWarranty"].Value.ToString());

dr["modelID"] = sqlInsertCommandPurch.Parameters["@ModelID"].Value = dsPurch1.ProdModel[this.BindingContext[dsPurch1,"ProdModel"].Position].ModelID ;

dsPurch1.Purch.Rows.Add(dr);
//MessageBox.Show("Hello");
n += daPurch.Update(dsPurch1, dsPurch1.Purch.ToString());

//refreshDataSet();

}
MessageBox.Show("Number of records inserted: " + n.ToString());
dsPurch1.AcceptChanges();

}
else
{
//this code is for the updating a record.

for (int row = 0; row < dataGridViewPurch.Rows.Count; row++)
{

if (dataGridViewPurch.Rows[row].Cells["PurchPrice"].Value.ToString().Length != 0)
{
dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].BeginEdit();
sqlUpdateCommandPurch.Parameters["@PurchBillID"].Value = dsPurch1.PurchBill[this.BindingContext[dsPurch1, "PurchBill"].Position].PurchBillID = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchBillID"].Value.ToString());

//sqlUpdateCommandPurch.Parameters["@PurchBillID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchBillID = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchBillID"].Value.ToString());
sqlUpdateCommandPurch.Parameters["@PresentAt"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PresentAt = 1;
sqlUpdateCommandPurch.Parameters["@ModelID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].ModelID = Convert.ToDecimal(dataGridViewProdModel.SelectedRows[0].Cells["ModelID"].Value.ToString());

for (int col = 0; col < 3; col++)
{
if (col == 0)
sqlUpdateCommandPurch.Parameters["@PurchPrice"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchPrice = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchPrice"].Value.ToString());
else if (col == 1)
sqlUpdateCommandPurch.Parameters["@Serial"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].Serial = dataGridViewPurch.Rows[row].Cells["serial"].Value.ToString();
else
sqlUpdateCommandPurch.Parameters["@Purchwarranty"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchWarranty = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchWarranty"].Value.ToString());

sqlUpdateCommandPurch.Parameters["@Original_PurchID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchID;
sqlUpdateCommandPurch.Parameters["@Original_purchBillID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchBillID;
sqlUpdateCommandPurch.Parameters["@Original_purchPrice"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchPrice;
sqlUpdateCommandPurch.Parameters["@Original_Serial"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].Serial;
sqlUpdateCommandPurch.Parameters["@Original_PurchWarranty"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchWarranty;
sqlUpdateCommandPurch.Parameters["@Original_PresentAt"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PresentAt;
sqlUpdateCommandPurch.Parameters["@Original_ModelID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].ModelID;

MessageBox.Show("Position\n" + this.BindingContext[dsPurch1, "Purch"].Position.ToString());

dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].EndEdit();
n += daPurch.Update(dsPurch1, "Purch");
dsPurch1.AcceptChanges();

MessageBox.Show("Number of records updated" + n.ToString());

}
}
else
{
MessageBox.Show("Invalid Serial no");
}

}

}

//btnNavFirst.Enabled = btnNavNext.Enabled = btnNavPrev.Enabled = btnLast.Enabled = true;
}
catch (DBConcurrencyException ex)
{
MessageBox.Show("Zero records affected.\n\n" + ex.ToString());
}
catch (SystemException ex)
{
MessageBox.Show("The source table could not be found.\n\n" + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

=======================================================================
this is meant to save and update as necessary and I’m getting the error on the line I’ve made bold and it says that

System.ArgumentException: Column names Serial can not be found
Parameter name: columnName
At system.Windows.Forms.DataGridViewCellCollection.get_Item(String columnName)

I’ve set up the datamember property to u know the child of PurchBill that is the purch table. I think there are a few errors in the datagridView class (maybe).

Plz help me out of this, I think I’m getting crazy here
Thanks in advance

What I don’t understand is that I’ve done the same work for anoter form in the same fashion and its working fine. It seems to me that there is no property named with these columns here but why is this happenin? And what should I do abt it?

Rocky

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

uwhockey4-Sep-06 10:10

uwhockey

4-Sep-06 10:10

Hello,

I am trying to do a query from 2 tables, which I have done million of times before, but I haven't been able to find a solution to this error - where I resolved in other cases but in this case ran out of ideas:

"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
Here is sp:

SELECT
tc.TXT_CTR_NUMERO_SGF,
REPLACE (tc.DEC_CTR_VLR_PI , ',' , '.') + REPLACE (tc.DEC_CTR_VLR_ADITIVO , ',' , '.') AS Total_PI_ADIT,
convert(varchar(30), Sum(convert(decimal(20,2), REPLACE(mr.DEC_MDR_VLR_PI, ',' , '.')))) VALOR_MEDIDO,

tc.COD_ID_CTR,
tc.COD_ID_TOB,
tc.DEC_CTR_VLR_SALDO_MEDIDO
FROM
tbContrato tc
LEFT JOIN tbMedicaoResumo mr
ON tc.COD_ID_CTR = mr.COD_ID_CTR
GROUP BY
tc.TXT_CTR_NUMERO_SGF, tc.DEC_CTR_VLR_PI, tc.DEC_CTR_VLR_ADITIVO,
tc.COD_ID_CTR, tc.COD_ID_TOB, tc.DEC_CTR_VLR_SALDO_MEDIDO

I have tried to convert all the text, nvarchar, etc to varchar and used like instead of =, but the problem seems to be with the grouping part!

A solution would be greatly appreciated.

Reza

Re: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Marek Grzenkowicz4-Sep-06 23:02

Marek Grzenkowicz

4-Sep-06 23:02

Have you tried:

...<br />
GROUP BY<br />
CAST(tc.TXT_CTR_NUMERO_SGF AS varchar(8000),...

Re: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

uwhockey5-Sep-06 5:20

uwhockey

5-Sep-06 5:20

Actually did, well the convert, but didn't seem to recognize it in group clause.

I did find a solution though by breaking it into parts. Seems like when you have an agragate function inside a sp, the grouping part gets very picky, so I left for more experts Wink | ;-)

Re: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Member 965-Sep-06 6:51

Member 96

5-Sep-06 6:51

Interesting, I never thought of doing that before. I've always known you can't group by ntext and image fields but never considered casting it like that, just coded around it.

Migrating SQL server 2000 database to SQL server 2005 with Japanese alphabets

VaibhavJ4-Sep-06 4:38

VaibhavJ

4-Sep-06 4:38

Hello everyone !!!

I want to migrate SQL Server 2000 database onto SQL Server 2005 database.

The original SQL Server 2000 database tables contain some Japanese characters and I need to
transfer them as well to SQL Server 2005 database.

For this purpose I have done following things.

1. Exported all tables data to a excel sheet. (by choosing Microsoft Excel 97/2000)
2. But when I try to import this data to SQL server 2005 I am getting following error

It verifies it. (error)
Message
Error 0xc02020f6: data flow task: The character-string data type of the Unicode form and the character-string data types of the form other than Unicode cannot be converted by the row "Description".
(SQL Server import and export wizard)

Error 0xc004706b: "Component" conversion of the data flow task ahead- tblCMSTypeMaster "(26)" failed in the verification, and the verification state "VS_ISBROKEN" was returned.
(SQL Server import and export wizard)

Error 0xc004700c: data flow task: It failed in the verification because of one or more
components.
(SQL Server import and export wizard)

Error 0xc0024107: data flow task: The error occurred while verifying the task.
(SQL Server import and export wizard)

I guess this problem is related to importing of Japanese characters.
I am stuck with this problem. Any solution/suggestion on this issue is greatly appreciated.

Thanks in advance.

Thanks & regards,
Vaibhav

layout query

srinandan..4-Sep-06 2:06

srinandan..

4-Sep-06 2:06

Hi all

i have a table Student
id FName LName
-----------------------------
1 Rahit singh
2 Mohit null
3 Sanjay malhotra
4 Pankaj null

i want a sql query which will return the following layout

id Name
-----------------------------
1 Rahit singh
2 Mohit NA
3 Sanjay Malhotra
4 Pankaj NA

please any one help me Smile | :)

thanks in advance...

thanks n regards,
Srinandan

Re: layout query

SeMartens4-Sep-06 2:32

SeMartens

4-Sep-06 2:32

Hi srinandan,

which dialect of sql are you using exactly, because you have to write a case-statement I think to get this done with NA...

e.g. for Sybase:

<br />
SELECT id, (FName + ' ' + (CASE WHEN (LNAME is null) THEN 'NA' ELSE LNAME END)) AS NAME <br />
FROM Student order by name<br />

(For Sybase there is also a function named IsNull to perform a null check with an assignment of a default value if the tested one is null)

Hope this helps you a bit,

regards
Sebastian

Re: layout query

srinandan..4-Sep-06 2:40

srinandan..

4-Sep-06 2:40

Thanks a lot to you Sebastian,
you have solve my problem. Smile | :)

thanks n regard,
srinandan

Which one's the best----Relational or non-relational DB structure?

kamalika_kk4-Sep-06 0:07

kamalika_kk

4-Sep-06 0:07

Though I might be asking a very foolish question, but it's important for me to know what's the current trend and the reason for that.
My question is-----
What is better-----
1. Tables with relations (pk and fks created through script during design time) maintained at the DB.
2. Tables without relations(pk and fks) should be maintained at the DB and accessed with appropriate query as and when needed?

Please some one help me with this.....I heard that the 2nd method is the current trend...if it is so then why is it?

Re: Which one's the best----Relational or non-relational DB structure?

Colin Angus Mackay4-Sep-06 0:51

Colin Angus Mackay

4-Sep-06 0:51

kamalika_kk wrote:
1. Tables with relations (pk and fks created through script during design time) maintained at the DB.
2. Tables without relations(pk and fks) should be maintained at the DB and accessed with appropriate query as and when needed?

All your tables should have a primary key.

If you have related tables then they should have foreign keys.

kamalika_kk wrote:
I heard that the 2nd method is the current trend...if it is so then why is it?

That is news to me.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: Which one's the best----Relational or non-relational DB structure?

Paul Conrad4-Sep-06 7:18

Paul Conrad

4-Sep-06 7:18

Colin Angus Mackay wrote:
kamalika_kk wrote:
I heard that the 2nd method is the current trend...if it is so then why is it?

That is news to me.

Same here. Haven't even considered using tables with out relations. Any data model I've worked with has some kind of relationship between entities.

That's no moon, it's a space station. - Obi-wan Kenobi

sql 2000 installing problem

yazan_zahi3-Sep-06 22:27

yazan_zahi

3-Sep-06 22:27

a service control operation failed for the mssqlserver service :2
the system cannot find the file specified
can you help fast pls

Re: sql 2000 installing problem

yahao9-Oct-06 23:58

yahao

9-Oct-06 23:58

plz see sqlsetup.log for detail information in %windows% folder.

mod div

TAREQ F ABUZUHRI3-Sep-06 22:02

TAREQ F ABUZUHRI

3-Sep-06 22:02

i want to make sql statment that use mod % Div /

i have tow colum

tt | time
---------
1 | 1
2 | 2
3 | 9

i want to make list for all record if (time % 5 = 0)

* how can make this

Palestine

Re: mod div

Eric Dahlvang5-Sep-06 3:23

Eric Dahlvang

5-Sep-06 3:23

SELECT * FROM MyTableName WHERE time % 5 = 0

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

Upgrading an SQL Database via one click

Martin J Evans3-Sep-06 22:02

Martin J Evans

3-Sep-06 22:02

I have three salesmen out on the road demonstrating our latest product on their laptops - an asp.net (1.1) application installed locally on their machine that talks to a SQL Server database instance also installed on their machine.(MSDE 2000)

I would like to send upgrades to them and make the process as simple as possible for them (I can't sell - they can't program!). Carrying out the upgrade via one click would be ideal.

The updates could be anything from appending a single stored procedure to creating new tables etc.

Can anyone suggest a good way of doing this?

Many thanks in advance. Smile | :)

Re: Upgrading an SQL Database via one click

Eric Dahlvang5-Sep-06 3:20

Eric Dahlvang

5-Sep-06 3:20

I'd write a little .NET exe that has all sql database updates within it. Have a field in a table that keeps track of the database version number. In your little DBUpdate.exe, first thing you do is check the version of their database (If the table or field you keep the version number in isn't there, then you know to start at the beginning...and create the table or field). Then, just run through the updates that are less than their database version number, and update their database version number as you go.

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

how to (stored procedures)

yazan_zahi2-Sep-06 22:28

yazan_zahi

2-Sep-06 22:28

how to execute tow procedure in one procedure
his job is to add data

Re: how to (stored procedures)

Michael P Butler2-Sep-06 23:02

Michael P Butler

2-Sep-06 23:02