Using Arrays to write data to SQL Server

**John Blessing** · Feb 1 '07, 03:45 PM

Re: Using Arrays to write data to SQL Server

"Brett_A" <brettatkin@gma il.comwrote in message
news:1170342285 .718922.275540@ l53g2000cwa.goo glegroups.com.. .

I'm trying to write data from a form using a text box (textarea) that
has a return after each item. For example:
>
email1@domain.c om
email2@domain.c om
email3@domain.c om
email4@domain.c om
>
I'm getting the data written to the SQL server table fine, but every
item after the first is getting written with a special character
preceeding the email address that looks like a little box. What is
it, why is their there and how do I remove it?
>
Here is my code:
>
<%@LANGUAGE="VB SCRIPT" CODEPAGE="1252" %>
<%
Dim varItems, varArray, I
varItems = Request("email_ address")
varArray = Split(varItems, chr(13))
>
For I = LBound(varArray ) To UBound(varArray )
>
'write data to database
>
Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "driver={SQ L Server};server= aaaaaaa;"
SQLStmt = "INSERT INTO tbl_advertisers (email_address) "
SQLStmt = SQLStmt & "VALUES ('" & varArray(I) & "')"
Set RS = Conn.Execute(SQ LStmt)
set rs=nothing
>
If err.number>0 then
response.write "VBScript Errors Occured:" & ""
response.write "Error Number=" & err.number & ""
response.write "Error Descr.=" & err.description & ""
response.write "Help Context=" & err.helpcontext & ""
response.write "Help Path=" & err.helppath & ""
response.write "Native Error=" & err.nativeerror & ""
response.write "Source=" & err.source & ""
response.write "SQLState=" & err.sqlstate & ""
end if
IF conn.errors.cou nt0 then
response.write "Database Errors Occured" & ""
response.write SQLstmt & ""
for counter= 0 to conn.errors.cou nt
response.write "Error #" & conn.errors(cou nter).number & ""
response.write "Error desc. -" & conn.errors(cou nter).descripti on &
""
next
else
end if
Conn.Close
set conn=nothing
>
Next 'I
%>
>
>
Thanks.
>
Brett

I supect you have a vbcrlf - Chr(13) & Chr(10) at the end of each line,
your split leaves behind the Chr(10)

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.room-booking-software.com - Schedule rooms & equipment bookings
for your meeting/class over the web.
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook, find/replace,
send newsletters

**Brett_A** · Feb 1 '07, 04:15 PM

Re: Using Arrays to write data to SQL Server

On Feb 1, 10:36 am, "John Blessing"
<blessij@**REMO VE**THIS**gmail .comwrote:

"Brett_A" <brettat...@gma il.comwrote in message
>
news:1170342285 .718922.275540@ l53g2000cwa.goo glegroups.com.. .
>
>
>

I'm trying to write data from a form using a text box (textarea) that
has a return after each item. For example:

>

ema...@domain.c om
ema...@domain.c om
ema...@domain.c om
ema...@domain.c om

>

I'm getting the data written to the SQL server table fine, but every
item after the first is getting written with a special character
preceeding the email address that looks like a little box. What is
it, why is their there and how do I remove it?

>

Here is my code:

>

<%@LANGUAGE="VB SCRIPT" CODEPAGE="1252" %>
<%
Dim varItems, varArray, I
varItems = Request("email_ address")
varArray = Split(varItems, chr(13))

>

For I = LBound(varArray ) To UBound(varArray )

>

'write data to database

>

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "driver={SQ L Server};server= aaaaaaa;"
SQLStmt = "INSERT INTO tbl_advertisers (email_address) "
SQLStmt = SQLStmt & "VALUES ('" & varArray(I) & "')"
Set RS = Conn.Execute(SQ LStmt)
set rs=nothing

>

If err.number>0 then
response.write "VBScript Errors Occured:" & ""
response.write "Error Number=" & err.number & ""
response.write "Error Descr.=" & err.description & ""
response.write "Help Context=" & err.helpcontext & ""
response.write "Help Path=" & err.helppath & ""
response.write "Native Error=" & err.nativeerror & ""
response.write "Source=" & err.source & ""
response.write "SQLState=" & err.sqlstate & ""
end if
IF conn.errors.cou nt0 then
response.write "Database Errors Occured" & ""
response.write SQLstmt & ""
for counter= 0 to conn.errors.cou nt
response.write "Error #" & conn.errors(cou nter).number & ""
response.write "Error desc. -" & conn.errors(cou nter).descripti on &
""
next
else
end if
Conn.Close
set conn=nothing

>

Next 'I
%>

>

Thanks.

>

Brett

>
I supect you have a vbcrlf - Chr(13) & Chr(10) at the end of each line,
your split leaves behind the Chr(10)
>
--
John Blessing
>
http://www.LbeHelpdesk.com- Help Desk software priced to suit all
businesseshttp://www.room-booking-software.com- Schedule rooms & equipment bookings
for your meeting/class over the web.http://www.lbetoolbox.com- Remove Duplicates from MS Outlook, find/replace,
send newsletters

That fixed it John, thanks.

Brett

**Farshad Hemmati** · Feb 11 '07, 11:45 PM

Re: Using Arrays to write data to SQL Server

The reason you are getting that symbol is because you are splitting the text
incorrectly. By splitting it with the Chr(13), you are forgetting that a new
line is Chr(13) + Chr(10).

The best way is to replace that line with the following:
varArray = Split(varItems, vbNewLine)

Your problem should be fixed.

"Brett_A" <brettatkin@gma il.comwrote in message
news:1170342285 .718922.275540@ l53g2000cwa.goo glegroups.com.. .

I'm trying to write data from a form using a text box (textarea) that
has a return after each item. For example:
>
email1@domain.c om
email2@domain.c om
email3@domain.c om
email4@domain.c om
>
I'm getting the data written to the SQL server table fine, but every
item after the first is getting written with a special character
preceeding the email address that looks like a little box. What is
it, why is their there and how do I remove it?
>
Here is my code:
>
<%@LANGUAGE="VB SCRIPT" CODEPAGE="1252" %>
<%
Dim varItems, varArray, I
varItems = Request("email_ address")
varArray = Split(varItems, chr(13))
>
For I = LBound(varArray ) To UBound(varArray )
>
'write data to database
>
Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "driver={SQ L Server};server= aaaaaaa;"
SQLStmt = "INSERT INTO tbl_advertisers (email_address) "
SQLStmt = SQLStmt & "VALUES ('" & varArray(I) & "')"
Set RS = Conn.Execute(SQ LStmt)
set rs=nothing
>
If err.number>0 then
response.write "VBScript Errors Occured:" & ""
response.write "Error Number=" & err.number & ""
response.write "Error Descr.=" & err.description & ""
response.write "Help Context=" & err.helpcontext & ""
response.write "Help Path=" & err.helppath & ""
response.write "Native Error=" & err.nativeerror & ""
response.write "Source=" & err.source & ""
response.write "SQLState=" & err.sqlstate & ""
end if
IF conn.errors.cou nt0 then
response.write "Database Errors Occured" & ""
response.write SQLstmt & ""
for counter= 0 to conn.errors.cou nt
response.write "Error #" & conn.errors(cou nter).number & ""
response.write "Error desc. -" & conn.errors(cou nter).descripti on &
""
next
else
end if
Conn.Close
set conn=nothing
>
Next 'I
%>
>
>
Thanks.
>
Brett
>

Using Arrays to write data to SQL Server

Using Arrays to write data to SQL Server

Comment

Comment

Comment