I'm rearranging the reply to hopefully make it a little easier to follow (my
fault, I sked the questions in the worng order)
Mr Beaton wrote:
characters gets replaced.
Response.Writing the form data succeeds as expected.
So you receive the result of Response.Write back from the xmlhttp request?
It's an invaluable tool that will make it much easier to troubleshoot
problems of this sort in the future. And, it's extremely easy to use. I
remember the first time I used it i did not even need to read the
documentation. You shuld really familiarize yourself with this tool.
...silly question, but... Isn't that what I have done? *blush*
I didn't know until you posted the code ...
Code provided above.
The accented characters I expect are limited to twelve, so I _could_
do a "manual" replace of them, but if there is one thing I have
I don't like doing that as well. The data should be stored as entered. OK,
let's look at the code:
For Each Field In Request.Form
Dim cmdText
cmdText = "INSERT INTO tblFoo (fldName, fldValue) VALUES (?, ?)"
Dim oCmd
Set oCmd = Server.CreateObject("ADODB.Command")
With oCmd
.CommandText = cmdText
.CommandType = 1
.Parameters.Append .CreateParameter("@1", 200, 1, 200, Field)
.Parameters.Append .CreateParameter("@2", 200, 1, 200,
Request.Form(Field))
Set .ActiveConnection = oConn
End With
oCmd.Execute
Next 'Field
This does not help as much as it could have. I have no idea what the
datatypes of fldName and fldValue are ... varchar? nvarchar?
You used 200 for the parameter datatype arguments. According to
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx,
this translates to adVarChar which is the proper type to use for SQL Server
varchar columns.
varchar is not an internationally -aware (unicode) datatype. If your column
has a datatype of varchar, then it will not be able to store you Swedish
characters. nvarchar is the datatype to use for unicode data.
So, let's say you knew this and your database columns actually are nvarchar.
In this case, your problem is due to your telling ADO, via the 200 argument,
to convert the string to ASCII, which results in the replacement of
non-ASCII characters. Your solution is to use 202 (adWVarChar) for the
parameter instead of 200. So try this instead:
'this part should be done outside the loop. It only needs to be done once:
Dim cmdText
cmdText = "INSERT INTO tblFoo (fldName, fldValue) VALUES (?, ?)"
Dim oCmd
Set oCmd = Server.CreateObject("ADODB.Command")
With oCmd
.CommandText = cmdText
.CommandType = 1
Set .ActiveConnection = oConn
.Parameters.Append .CreateParameter("@1", 202, 1, 200)
.Parameters.Append .CreateParameter("@2", 202, 1, 200)
End With
For Each Field In Request.Form
With oCmd
.Parameters("@1").Value= Field
.Parameters("@2").Value=Request.Form(Field)
.Execute ,,128
End With
Next 'Field
I am betting that the simpler technique of using a variant array will also
work. Try this code instead:
'this part should be done outside the loop. It only needs to be done once:
Dim cmdText, arParms
cmdText = "INSERT INTO tblFoo (fldName, fldValue) VALUES (?, ?)"
Dim oCmd
Set oCmd = Server.CreateObject("ADODB.Command")
With oCmd
.CommandText = cmdText
.CommandType = 1
Set .ActiveConnection = oConn
End With
For Each Field In Request.Form
arParms = array(Field,Request.Form(Field))
oCmd.Execute ,arParms,128
Next 'Field
In this case both parameters are strings. If you are using datetime or
numeric parameters instead, you should get more explicit. For example, if
passing a datetime value:
On Error Resume Next
dateparm=CDate(Request.Form(Field))
If err<>0 then
Response.Write Request.Form(Field) & _
"could not be converted into a date"
Response.End
End If
On Error GoTo 0
'then, depending on the technique you are using:
arParms = array(Field,dateparm)
' or
..Parameters("@2").Value=dateparm
HTH,
Bob Barrows
