How to read long text field from the SQL Database

[VMM]

Hello.

I have a little problem with reading information from SQL server database's long text field. There are 150 numbers in that field, each number in its own line. The problem is that I can't find a way to read it properly with asp. Properly means here that I can get each number in its own line in browser too. Currently it is just printing all the 150 numbers to one line in the browser. The aim is to form conditions to each 150 numbers. That is - making "if commands" to check, does the number fit into certain number span.. (like for example 10-100).

I'm currently reading the information from the table with "do while not xxx.EOF" loop.

Does anybody know how to manage this situation..?

Thanks for advance.

[BaldEagle]

Are the numbers delimited in any way (ie, commas or spaces)? If they are you can use Split() to create an array of those numbers and then perform your conditional checks on each element of the array.

BaldEagle

[VMM]

Hello..

Thank you BaldEagle for a good advise. I have now looked into this split function and it looks like it suites my purposes.

However I have some problems with implementing it. I think the problem is that I dont know how to put the variable into the split function.

This is how I read the information from the DB:

select * from database
Set pointer = cnn.execute(SQL)
do while not pointer.EOF

response.write & pointer("data1") &
response.write & pointer("data2") &

pointer.movenext
loop

The data1 field in the database contains numbers (long text field) from 1 to 150.

I have tried to put the data1 variable to the split function with poor results. From example:

dim a

a = split(data1)... etc does not work...

Does anybody know what I do wrong???

[Brandoe85]

With the split function you'll have to specify what you're splitting the string on, and then it will return an array of the values, heres a quick basic example:

<%

dim myArr, strSplitString, i

strSplitString = "this,is,some,comma,separated,text,for,a,asp,test"

myArr = Split(strSplitString, ",")

for i = Lbound(myArr) to Ubound(myArr)
	Response.Write myArr(i) & "<br>"
Next

%>

[VMM]

Hello..

Yes, that's true that I have to specify the string I'm splitting. But I hoped that I could, in some way, place the information from the SQL string into a new variable and then split the information in the new variable.

s = pointer("data1").value
ss = split(s, " ")

???? something like this ????

well, anyway I will try to do this in something like this way, please tell me if I'm doing something wrong.

Than you for all your help.

[BaldEagle]

s = pointer("data1").value
strSplitString = "this,is,some,comma,separated,text,for,a,asp,test"

ss = split(s, " ")
myArr = Split(strSplitString, ",")

You are correct in your assumption. Look at the code Brandoe85 provided you. This code is in fact splitting a variable's contents. The only difference is this variable is literal and you want to make that variable from a database field. I've shown both versions of the code here to show which lines are contextually equivalent.

BaldEagle

[jaywhy13]

Originally Posted by VMM

Hello.

I have a little problem with reading information from SQL server database's long text field. There are 150 numbers in that field, each number in its own line. The problem is that I can't find a way to read it properly with asp. Properly means here that I can get each number in its own line in browser too. Currently it is just printing all the 150 numbers to one line in the browser. The aim is to form conditions to each 150 numbers. That is - making "if commands" to check, does the number fit into certain number span.. (like for example 10-100).

I'm currently reading the information from the table with "do while not xxx.EOF" loop.

Does anybody know how to manage this situation..?

Thanks for advance.

Lemme ask you this... is it ONE field with all the 150 numbers on different lines? Or is it different fields, each with 150 numbers on different lines?

[jaywhy13]

Originally Posted by Brandoe85

With the split function you'll have to specify what you're splitting the string on, and then it will return an array of the values, heres a quick basic example:

<%

dim myArr, strSplitString, i

strSplitString = "this,is,some,comma,separated,text,for,a,asp,test"

myArr = Split(strSplitString, ",")

for i = Lbound(myArr) to Ubound(myArr)
	Response.Write myArr(i) & "<br>"
Next

%>

You could also split by the "\n" character right? Sounds like the \n is the delimeter here

[Brandoe85]

Yes you could, I was just using commas as an example.

[VMM]

Hello,

and thank you all for your valuable help!! Yes, I managed to get the information into a new variable with s = pointer("data1").value.

I have made a C++ program that sends information into the db. and the program puts each number in its own row with \n (linefeed). The problem now is that I cant get the asp program understand that \n.

I have tryed like this:

qt=Chr(10)
s = pointer("data1").value
ss=split(s, qt)
etc..

I mean that I have tryed to express the NewLine in the db for the asp program with hexadecimal marks, and also with octal marks....but without success.

After failing to get this work I changed the C++ program to put : mark to the beginning of each new line...but it failed also..Also removing the \n mark from the c++ code failed... Every time, in my examples, the same error occurs:

--> response object, asp 0106 (0x80020005) an unhandled data type was encountered.

jaywhy13, There are 150 numbers in different lines in the same field.

[Brandoe85]

It seems to work for me without the Chr() function applied.

<%

dim strSplitString, myArr, i

strSplitString = "this\nis\nsome\nasp\nsplit\ntesting"
myArr = Split(strSplitString, "\n")

for i = Lbound(myArr) to Ubound(myArr)
	Response.Write myArr(i) & "<br>"
Next

%>

[VMM]

Originally Posted by Brandoe85

It seems to work for me without the Chr() function applied.

<%

dim strSplitString, myArr, i

strSplitString = "this\nis\nsome\nasp\nsplit\ntesting"
myArr = Split(strSplitString, "\n")

for i = Lbound(myArr) to Ubound(myArr)
	Response.Write myArr(i) & "<br>"
Next

%>

Yes, but I think that the "\n" in your case does not mean linefeed mark. You have the letters \n in the beginning of each split, so it works.

In the database I have the information like this:
data1
------
1
2
3
4
5
etc... the c++ code puts each of the numbers in its own line with the \n. The \n is valid in c++, but I think that the VBScript does not understand it. Currenty in the asp site these numbers are shown like this:
1 2 3 4 5 6 7 8 9 10... ´

And if I try to put each number for example in a table with asp with:
s = pointer("data1").value
ss=split(s, " ")

, the vbscript does not understand the space [ss=split(s, " ")] between the numbers. And that's why I have tryed to express the linefeed for the vbscript with the hexadecimal and with octal marks...without success.

[NancyJ]

have you tried

select * from database
Set pointer = cnn.execute(SQL)
while not pointer.EOF
  response.write replace(pointer("data1"), vbcrlf, "<br>")
pointer.movenext
wend

or if you want to split it for processing later

select * from database
Set pointer = cnn.execute(SQL)
while not pointer.EOF
   arrData = split(pointer("data1"), vbcrlf)
   for i lbound(arrData) to ubound(arrData)
      response.write arrData(i) & "<br>"
   next
   pointer.movenext
wend

[VMM]

I managed to get this work:

s = pointer("data1".value)
ss = split(s, " ")
for i = lbound(ss) to ubound(ss)
response.write ss(i)
next

Yes, this is the way you have advised me to do. The problem was on the c++ side. I altered the code that inserts the information into the database, in such a way, that it makes a normal space between the information + the new line. In this way the vbscript understood where to split the string.

Thank you all for your help..

[Brandoe85]

I'm glad you've gotten it to work. I was assuming that was the problem, but I had no way to test any data for sure. But i'm glad it works now!