Database

4-Aug-06 8:50

select SUBSTRING(dmv2_data_text, 40, DATALENGTH(DMV2_DATA_TEXT))
from dmv2 WHERE DMV2_RCD_TYPE = 'R'

When I run this query sometimes there's a carriage return sometimes there isn't. Is there a way to inspect the first char and see if there is a CR and if so shift the whole string over one more space to remove it?

Thanks

Tom Wright
tawright915@gmail.com

Eric Dahlvang4-Aug-06 9:12

4-Aug-06 9:12

SELECT CASE WHEN SUBSTRING(dmv2_data_text, 40, 1) = CHAR(13)
THEN SUBSTRING(dmv2_data_text, 42, DATALENGTH(DMV2_DATA_TEXT)) 
ELSE SUBSTRING(dmv2_data_text, 40, DATALENGTH(DMV2_DATA_TEXT)) 
END
FROM dmv2 WHERE DMV2_RCD_TYPE = 'R'

If it is a carraige return, then add two (carriage return + line feed).

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

Tom Wright7-Aug-06 4:26

7-Aug-06 4:26

nope still didn't do it. I may have to just live with is and remove it in my code instead of thru SQL.

Thanks

Tom Wright
tawright915@gmail.com

Eric Dahlvang7-Aug-06 5:05

7-Aug-06 5:05

TableName:MyTestTable
ColumnName:TestCol

Two Records:
1='

This<br />
is a test

'

2='

This is also<br />
a test

SELECT '--' + CASE WHEN SUBSTRING(TestCol, 5, 1) = CHAR(13)
THEN SUBSTRING(TestCol, 7, 256) 
ELSE SUBSTRING(TestCol, 5, 256) 
END
FROM MyTestTable

Result:

--is a test<br />
-- is also<br />
a test<br />

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

Tom Wright7-Aug-06 5:15

7-Aug-06 5:15

Okay, I still could not get it to work. So I copied the data from Query Analyzer and pasted into a hex editor and saw that the char before my string is a hex 20...a space. So I tried the query you suggested only make the CHAR(13) to a CHAR(20) and CHAR(32)...still did not work.

Could there be something else there and the system sees and is not reading it right?

Thanks for the help

Tom Wright
tawright915@gmail.com

Eric Dahlvang7-Aug-06 5:24

7-Aug-06 5:24

I assume you've tried LTRIM()...

Tom Wright wrote:
Could there be something else there and the system sees and is not reading it right?

Try this to find out:

select ASCII(SUBSTRING(dmv2_data_text, 40, 1)) AS AsciiCode<br />
from dmv2 WHERE DMV2_RCD_TYPE = 'R'

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

Tom Wright7-Aug-06 7:00

7-Aug-06 7:00

That did it. The correct code was a 10. Strange.

Thanks for the help

Tom Wright
tawright915@gmail.com

Eric Dahlvang7-Aug-06 7:20

7-Aug-06 7:20

Glad it worked.
FYI:

13 = carriage return<br />
10 = line feed

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

Tom Wright7-Aug-06 7:21

7-Aug-06 7:21

Eric, mind if I ask you another question.

I'm calling a SP like this from my code:
myConnection.Open();
SqlCommand myCommand = new SqlCommand("EXEC LA_SP_NYSPINRESPONSE '"+ recType +"', 'P', '"+ Encoding.UTF8.GetString(ByteBuffer, 0, ByteBuffer.Length) +"', " + TrackNumber, myConnection);
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();

This works great however on one of repsonses that I'm writing out there is a single quote in the data, thus causing a problem when passing this data to my SP. It thinks it's end quote to a string. Would parameters be better, to pass data that way then to concante it like I'm doing?

Thanks

Tom Wright
tawright915@gmail.com

Eric Dahlvang7-Aug-06 8:04

Multiple Tables and Reltionships

7-Aug-06 8:04

Check this out: http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp[^]

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

klayalloy4-Aug-06 5:16

klayalloy

4-Aug-06 5:16

I've been trying to learn ADO.NET for a few weeks now. I know most of the components and what they do, but i'm not sure how they all work together. I'm trying to write a very simple app, but I'm not getting it to work. I have 4 tables (Customers, Orders, Products and OrderDetails). There are 3 relationships (Cust to Order, Order to Details and Prod to Details). I set up a windows for with 2 listbox's and a datagrid. I want to be able to populate the first listbox with the customers, click a customer and display their orders in the 2nd listbox. I've got my program this far. Now I want to click on an order and display the the order details (custID, OrderID, ProductID, ProductName, ProductQTY). I've looked online for tutorials and read through a couple of ebooks, but they mostly talk about 1 table or 2 tables max and maybe only 1 relation. Any help would be greatly appreciated.

Re: Multiple Tables and Reltionships

Rob Graham4-Aug-06 5:26

Rob Graham

4-Aug-06 5:26

Not sure what exactly is confusing you, but the principle is the same as it was for the firs pair of listboxes (you filled the second from orders using the custToOrder relation and the custId of the selected row in the first. Now fill the third from OderDetails using the OderToDetails relation and custId,OrderId from the selected row in the second...).

Rollback?

psamy4-Aug-06 3:05

psamy

4-Aug-06 3:05

Hi all,
My application uses 2 different databases.
so rollback condition here very complex.

example.
1. insert into tableA ---DB1 (points db1)
2. insert into tableA ---DB2 (points db2)

if second qury fails i want to rollback with respective databases.
How can i do that?

Re: Rollback?

Ennis Ray Lynch, Jr.4-Aug-06 3:54

Ennis Ray Lynch, Jr.

4-Aug-06 3:54

Are you using .NET? If so you can append a transation object to the command for each database and roll back or commit using them. But I think MS has some sort of DTC which I know nothing about.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Rollback? [modified]

Eric Dahlvang4-Aug-06 3:55