Using Arrays to write data to SQL Server

Using Arrays to write data to SQL Server

am 01.02.2007 16:04:45 von Brett_A

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.com
email2@domain.com
email3@domain.com
email4@domain.com

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="VBSCRIPT" 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.CreateObject("ADODB.Connection")
Conn.Open "driver={SQL Server};server=aaaaaaa;"
SQLStmt = "INSERT INTO tbl_advertisers (email_address) "
SQLStmt = SQLStmt & "VALUES ('" & varArray(I) & "')"
Set RS = Conn.Execute(SQLStmt)
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.count> 0 then
response.write "Database Errors Occured" & "

"
response.write SQLstmt & "

"
for counter= 0 to conn.errors.count
response.write "Error #" & conn.errors(counter).number & "

"
response.write "Error desc. -> " & conn.errors(counter).description &
"

"
next
else
end if
Conn.Close
set conn=nothing

Next 'I
%>


Thanks.

Brett

Re: Using Arrays to write data to SQL Server

am 01.02.2007 16:36:22 von John Blessing

"Brett_A" wrote in message
news:1170342285.718922.275540@l53g2000cwa.googlegroups.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.com
> email2@domain.com
> email3@domain.com
> email4@domain.com
>
> 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="VBSCRIPT" 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.CreateObject("ADODB.Connection")
> Conn.Open "driver={SQL Server};server=aaaaaaa;"
> SQLStmt = "INSERT INTO tbl_advertisers (email_address) "
> SQLStmt = SQLStmt & "VALUES ('" & varArray(I) & "')"
> Set RS = Conn.Execute(SQLStmt)
> 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.count> 0 then
> response.write "Database Errors Occured" & "

"
> response.write SQLstmt & "

"
> for counter= 0 to conn.errors.count
> response.write "Error #" & conn.errors(counter).number & "

"
> response.write "Error desc. -> " & conn.errors(counter).description &
> "

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

Re: Using Arrays to write data to SQL Server

am 01.02.2007 17:07:48 von Brett_A

On Feb 1, 10:36 am, "John Blessing"
wrote:
> "Brett_A" wrote in message
>
> news:1170342285.718922.275540@l53g2000cwa.googlegroups.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.com
> > ema...@domain.com
> > ema...@domain.com
> > ema...@domain.com
>
> > 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="VBSCRIPT" 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.CreateObject("ADODB.Connection")
> > Conn.Open "driver={SQL Server};server=aaaaaaa;"
> > SQLStmt = "INSERT INTO tbl_advertisers (email_address) "
> > SQLStmt = SQLStmt & "VALUES ('" & varArray(I) & "')"
> > Set RS = Conn.Execute(SQLStmt)
> > 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.count> 0 then
> > response.write "Database Errors Occured" & "

"
> > response.write SQLstmt & "

"
> > for counter= 0 to conn.errors.count
> > response.write "Error #" & conn.errors(counter).number & "

"
> > response.write "Error desc. -> " & conn.errors(counter).description &
> > "

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

Re: Using Arrays to write data to SQL Server

am 12.02.2007 00:35:44 von Farshad Hemmati

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" wrote in message
news:1170342285.718922.275540@l53g2000cwa.googlegroups.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.com
> email2@domain.com
> email3@domain.com
> email4@domain.com
>
> 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="VBSCRIPT" 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.CreateObject("ADODB.Connection")
> Conn.Open "driver={SQL Server};server=aaaaaaa;"
> SQLStmt = "INSERT INTO tbl_advertisers (email_address) "
> SQLStmt = SQLStmt & "VALUES ('" & varArray(I) & "')"
> Set RS = Conn.Execute(SQLStmt)
> 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.count> 0 then
> response.write "Database Errors Occured" & "

"
> response.write SQLstmt & "

"
> for counter= 0 to conn.errors.count
> response.write "Error #" & conn.errors(counter).number & "

"
> response.write "Error desc. -> " & conn.errors(counter).description &
> "

"
> next
> else
> end if
> Conn.Close
> set conn=nothing
>
> Next 'I
> %>
>
>
> Thanks.
>
> Brett
>