getting type error when inserting parameterized query with vbscript in classic asp

Question

edit still recieving type mismatch error but updated code with help from comments -updated code shown below

I am new to ASP and VBScript. I am trying to insert post data into a SQL database, however, I want to ensure the query is sterilized so I am trying to use a parameterized query. When I execute the query I get this error.

Microsoft VBScript runtime error '800a000d'

Type mismatch

my code looks like this

Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode = 3
conn.open "Provider=SQLOLEDB;Data Source=xxx.xxx.xxx.xxx,xxxx;
database=db_example;uid=user;pwd=password;"

Dim oCmd 
set oCmd= Server.CreateObject("ADODB.Command") 

Dim sSQL 
sSQL = "INSERT INTO tbl_Application (Expenses, Name, Why) VALUES (?, ?, ?);"
oCmd.CommandText = sSQL
oCmd.ActiveConnection= conn

Dim uPar 
uPar = oCmd.CreateParameter("Expenses",200,1,255,session("Expenses")) 
oCmd.Parameters.Append(uPar)

Dim vPar 
vPar = oCmd.CreateParameter("Name",200,1,255,session("Name")) 
oCmd.Parameters.Append(vPar)

Dim wPar 
wPar = oCmd.CreateParameter("Why",200,1,255,session("Why")) 
oCmd.Parameters.Append(wPar)

Dim oRS 
oRS = oCmd.Execute()

I have tried typing the data as a varchar and a char. Please let me know if you have a fix for my problem. Also, I am intending to insert more than three pieces of data on the site - is there a better way than going through and making a parameter manually for each column?

you have to use Dim for declaring variables using vbscript. msdn.microsoft.com/en-us/library/t7zd6etz%28v=vs.84%29.aspx. var is used in jscript — Flakes
– Flakes, Commented Jul 11, 2013 at 6:20
thanks, switched to dim, however, wouldn't let me define the Dim on declaration. Aka I have to write Dim xyz and then on second line xyz = "something" — Vincent Gustav
– Vincent Gustav, Commented Jul 11, 2013 at 15:53
yes, you have to do it in a new line; or you can do: Dim xyz : xyz = "something" — Flakes
– Flakes, Commented Jul 12, 2013 at 4:58
check this for the createparameter method, notice the use of set msdn.microsoft.com/en-us/library/windows/desktop/… — Flakes
– Flakes, Commented Jul 12, 2013 at 5:01

Cheran Shunmugavel · Accepted Answer · 2013-07-11 02:52:02Z

1

The documentation for the CreateParameter method state that:

If you specify a variable-length data type in the Type argument, you must either pass a Size argument or set the Size property of the Parameter object before appending it to the Parameters collection; otherwise, an error occurs.

129 corresponds to adChar, which is a variable-length data type and therefore requires you to pass a Size argument. Usually, you should use the defined length of the column in the database, but I have found that just using the length of the value I'm passing works also.

answered Jul 11, 2013 at 2:52

Cheran Shunmugavel

8,4691 gold badge35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vincent Gustav Over a year ago

Mmmm good to know, however, after adding in size property still throwing the type mismatch error - Provider error '80020005'. Any other ideas. Changed parameter code to this Dim uPar uPar = oCmd.CreateParameter("Expenses",200,1,255,session("Expenses")) oCmd.Parameters.Append(uPar)

Collectives™ on Stack Overflow

getting type error when inserting parameterized query with vbscript in classic asp

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related