Question:
I am trying to insert a record into the database but it doesn’t seem to be working correctly due to the way the database is set up.
<%@ Language=VBScript %>Deleted HTML Code<%Set Conn = Server.CreateObject("ADODB.Connection")Conn.open "xxx " (deleted due to security reasons)SQL = "INSERT INTO usr (usr_wwid, usr_nm, usr_ful_nm, usr_cor_adm, usr_hm_fctry) VALUES (" &_"'" & Request.Form("wwid") + "', "&_"'"& Request.Form("idsid") + "', "&_"'"& Request.Form("name") + "', "&_"'"& Request.Form("cor_adm") + "', "&_"'"& Request.Form("hm_factory") + "')"Set RS = Conn.Execute(SQL)set rs=nothingDeleted Error Handling Code
I get this error message:
Disallowed implicit conversion from datatype 'varchar' to datatype 'int' Table: 'calypso.dbo.usr', Column: 'usr_wwid' Use the CONVERT function to run this query.
The data should just go straight into the database but somehow it doesn't seem to be working. What went wrong here? Do you know how I can use the convert function in this case?
Answer:
You are trying to put a single quote (') around all the values in your SQL statement?even around the numeric ones. Remove the single quotes from around the numeric fields and you should be all set. Change:
&_"'" & Request.Form("wwid") + "', "&_
To:
&_"" & Request.Form("wwid") + ", "&_
and so on for all numeric fields. Use the single quote only for text and date fields in SQL Server.