Keith wrote:
I am getting the following error when I run my page I just created.
The first time I run the page I get this error, if I run the page a
second time I get no error and everything works fine. This is the
case everytime.
The error is:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]
The OLE DB Provider for ODBC Drivers has been deprecated. Instead, you you
should use the native oledb provider for sql server: SQLOLEDB. Your
connection string should look something like this:
sConn = "Provider=SQLOLEDB;" & _
"Data Source = your_server;" & _
"User ID = xxxxxxx;" & _
"Password=xxxxxxx"
Error converting data
type varchar to numeric.
/business_advice_main/security/login_success.asp, line 65
The code that is running is as follows (line 65 is the last line
where the insert command is executed).
<%
Session("svUserID") =
(RS_UserID_Check.Fields.Item("SECURITY_User_ID").V alue)
%>
<%
set Audit_Login = Server.CreateObject("ADODB.Command")
Audit_Login.ActiveConnection = MM_Business_Advice_STRING
Audit_Login.CommandText = "INSERT INTO dbo.SYS_Login_History
(LOGIN_User_ID) VALUES ('" + Replace(Audit_Login__varUserID, "'",
"''") + "' ) "
You should use "&" for string concatenation.
Audit_Login.CommandType = 1
Audit_Login.CommandTimeout = 0
Audit_Login.Prepared = true
Audit_Login.Execute()
%>
You are going to too much trouble here. You do not need to use an explicit
command object. Instead, use a connection object:
Dim cn, sSQL
Set cn = Server.CreateObject("ADODB.Connection")
cn.open MM_Business_Advice_STRING
sSQL= "INSERT INTO dbo.SYS_Login_History " & _
"(LOGIN_User_ID) VALUES ('" & _
Replace(Audit_Login__varUserID, "'","''") & "' ) "
'for debugging:
Response.write sSQL
'comment out the above line when debugging is complete
cn.Execute sSQL,,129
'129 is the result of adding two options:
'1 (adCmdText) and 128 (adExecuteNoRecords)
'Always tell ADO when you are not expecting records so
'it does not create a recordset behind the scenes
So, when you run this, you need to check the result of "response.write sSQL"
when the error occurs and make sure it contains what you expect it to
contain.
HTH,
Bob Barrows
PS. It is not a good idea to use dynamic sql. If you have not heard of SQL
Injection attacks, give the SQL Injection FAQ at
www.sqlsecurity.com a read.
You should know that escaping the single quote is not a foolproof means of
preventing sql injection. Using parameterized stored procedures is the
safest approach.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
