May 10, 2002 at 11:54 am
Can anyone help me convert a two-dimensional array into a working SQL insert statement?
I currently have the following ASP statement:
if not IsEmpty(messageData) then
iRecFirst = LBound(messageData, 2)
iRecLast = UBound(messageData, 2)
iFieldFirst = LBound(messageData, 1)
iFieldLast = UBound(messageData, 1)
end if
For I = iRecFirst To iRecLast
msg_to_rpt_tbl= "insert into #msg" & temp_name & "(msgno, msgid, msgname, msgparse, msgstored, msgencode) values("
For J = iFieldFirst to iFieldLast
if messageData(J,I) = "" then
set messageData(J,I) = " "
end if
if J=0 then
msg_to_rpt_tbl=msg_to_rpt_tbl & chr(34) & trim(messageData(J,I)) & chr(34)
else if J<=4 then
msg_to_rpt_tbl=msg_to_rpt_tbl & ", " & chr(34) & trim(messageData(J,I)) & chr(34)
else if J=5 then
msg_to_rpt_tbl=msg_to_rpt_tbl & ", " & chr(34) & trim(messageData(J,I)) & chr(34) & ")"
end if
end if
end if
Next ' J
DataConn.Execute(msg_to_rpt_tbl)
Next ' I
When I run the page, I get this error:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The name 'A800' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
But if I response.write out my insert statement, then copy/paste it into query analyzer, it runs perfectly. The statement is as follows:
insert into #msg0B7FF(msgno, msgid, msgname, msgparse, msgstored, msgencode) values("B704", "ABSTAT", "A** B** S*** R***", "False", "False", "False")
This is my first attempt at writing an array to a sql table. Can anyone please tell me what I'm doing wrong here?
Thanks a million
May 10, 2002 at 4:32 pm
Just so I understand the error what does "A800" have to do with the query? Your output looks fine so mush be something I am missing here, although I would change " as your way of quoting text to ' . It could be the driver not liking it with the ".
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 10, 2002 at 4:57 pm
quote:
Just so I understand the error what does "A800" have to do with the query? Your output looks fine so mush be something I am missing here, although I would change " as your way of quoting text to ' . It could be the driver not liking it with the "."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 10, 2002 at 5:01 pm
That's the thing: "A800" is a value that I'm trying to enter into my table. It's nothing more than that. I've tried to rearrange my insert statement so that the second value falls first in my insert statement, but I get the same error.
I've tried using the single apostrophe, but I have one contained within my values, so the asp page fails at that point. I put in the " to get beyond that and now I'm faced with this error.
Do you know of any way to get beyond that?
Thanks for your help, by the way 😀
May 11, 2002 at 9:59 am
When you submit your value use
REPLACE(valuehere,"'","''")
Since we do this a lot we created a function and call on the page.
Ex.
Function FixStr(ValFix)
FixStr = REPLACE(ValFix,"'","'')
End Function
Then we call in our insert
SQLStr = "insert into tblx (ValueA, ValueB) Values ('" & FixStr(Request("InputA")) & "','" & FixStr(Request("InputB")) & "')"
sqlConn.Execute (SQLStr)
When you use ' to quote text when you have an ' inside it you have to ' ' or double 's to make sure you don't break out. I believe with " around your text it is being treated as quoted identifiers and thus thin of "A800" as identifying a column name, if I remember correctly. By using the standard ' to quote text you can be sure this is not the issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 21, 2002 at 7:43 am
Thank you very much!! This worked great.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply