Driver's SQLSetConnectAttr failed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sankari - 25 Jun 2008 08:50 GMT

here is a simple form. it works well in my system. I hosted my site. but i
get the error
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

/join.asp, line 57

i've highlighted the line 57. pl. help me. without this fix, i cannot tell
anybody abt my web site.

thx in advance

<%@ Language=VBScript %>
<%
option explicit
Response.Expires = -1
Server.ScriptTimeout = 600
%>
<%
    validated_form=true
If request("insrow") = "1" Then
Dim adoCon 'Holds the Database Connection Object
Dim objRS 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database
Dim execSQL
Dim member_name
Dim member_id
Dim occupation
Dim email_address
Dim comments
Dim Validated_Form
Dim EmailFrom
Dim EmailTo
Dim emailSubjects
Dim emailcc
Dim emailbcc
Dim bodytxt
Dim sch
Dim cdoconfig
Dim objsendmail
Dim dat
DIM bookmark
Dim msg1
Dim ssql
Dim rs11
Dim updated

        member_name = Request("mbr_name")
        session("mbr_name")= Request("mbr_name")
        occupation = Request("mbr_occ")
        session("occupation") = Request("mbr_occ")
        member_id = Request("mbr_id")
        session("member_id") = Request("mbr_id")
        email_address = Request("email_add")
        comments = Request("comments")
        session("commmets") = Request("mbr_id")

IF Len(request("mbr_name"))= 0 Or Len(request("mbr_id"))=0 Or
len(request("Email_add"))<6 OR InStr(request("Email_add"),"@")=0 Then
    msg1="Error in the form.Please fill in all the details"
    session("msg1")=msg1
    validated_form=False
Else

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.mode = 3

adoCon.Open "dsn=wa;database=regn","test","test"

Set objRS = Server.CreateObject("ADODB.Recordset")

ssql="Select email_address from regn where email_address='" &
request.form("email_add") & "'"
[COLOR=DimGray]objRS.Open ssql, adoCon,3,3[/COLOR]

If (not objRS.BOF) and (not objRS.EOF) then
        Validated_Form = False
        msg1="Email already exists"
        session("msg1")=msg1
ELSE

            dat=Date()
            If Len(request("mbr_occ"))=0 Then
            occupation="-"
            End If

            If Len(request("comments"))=0 Then
            comments="-"
            End If

execSQL= "insert into regn
(member_name,occupation,member_id,email_address,comments,date_ins) values ('"
& member_name & "','" & occupation & "', '" & member_id & "', '" &
email_address & "', '" & comments & "','" & dat & "')"

adoCon.execute(execSQL)

sub objmail(emailSubjects,emailTo,emailFrom,bodyTxt)

sch = "http://schemas.microsoft.com/cdo/configuration/" 'new

                Set cdoConfig = Server.CreateObject("CDO.Configuration") 'new

                With cdoConfig.Fields 'new
                .Item(sch & "sendusing") = 2 ' cdoSendUsingPort 'new
                .Item(sch & "smtpserver") ="10.126.121.50" 'new (add your smtp server
address here
                .Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 25

                .update 'new
                End With 'new

                Set objSendMail = Server.CreateObject("CDO.Message")

                Set objSendMail.Configuration = cdoConfig 'new
                objSendMail.From = emailFrom
                objSendMail.To = emailTo
                objSendMail.Subject =emailSubjects
                objSendMail.HTMLBody =bodyTxt

                Set objSendMail = Nothing
                Set cdoConfig = Nothing 'new
End sub

EmailFrom="xxxx@yahoo.com"
EmailTo=email_address
emailSubjects="Welcome"

bodytxt="Welcome"

Call objmail(emailSubjects,emailTo,emailFrom,bodyTxt)
Set cdoConfig=Nothing
Set objSendMail=Nothing
msg1="Welcome " & member_name
session("msg1")=msg1
updated=true
End If
End if
End if

%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>

<body>



<% if updated=true Then %>

<table width=400 height=100 border=0 cellspacing=0 cellpadding=0>
<form name="regn" method="post" action="join.asp">
<input type="hidden" name="insrow" value="1">
<tr height=3><Td><font face=terminal size=2 color=black>Name
</font></td><td><input type=text size=40 name="mbr_name"></td></tr>
<tr height=3><TD><font face=terminal size=2 color=black>Role</td><td> <input
type=text name="mbr_occ" size=40 ></td></tr>
<tr height=4><td><font face=terminal size=2 color=black>Login ID</td><td>
<input type=text size=40 name="mbr_ID" ></td></tr>
<tr height=4><td><font face=terminal size=2 color=black> Email </td><td>
<input type=text name='email_add' size=40> </td></tr>
<tr height=4><td valign=center><font face=terminal size=2
color=black>Comments </td><td><textarea cols="30" rows="5"
name="comments"></textarea></td></tr>
<tr><td colspan=2 align=center><input type="submit" value="Submit"></td></tr>
</form>
</table>

<%else%>
<table width=400 height=100 border=0 cellspacing=0 cellpadding=0>
<form name="regn" method="post" action="join.asp">
<input type="hidden" name="insrow" value="1">
<tr height=3><Td><font face=terminal size=2 color=black>Name
</font></td><td><input type=text size=40 name="mbr_name"
value=<%=session("mbr_name")%>></td></tr>
<tr height=3><TD><font face=terminal size=2 color=black>Role</td><td> <input
type=text name="mbr_occ" size=40 value=<%=session("occupation")%>></td></tr>
<tr height=4><td><font face=terminal size=2 color=black>Login ID</td><td>
<input type=text size=40 name="mbr_ID"
value=<%=session("member_id")%>></td></tr>
<% if Validated_Form = False Then
response.write("<tr height=4><td><font face=terminal size=2 color=black>
Email </td><td> <input type=text name='email_add' size=40 class='red' value=
""" & request.form("email_add") & """> </td></tr>")
Else
%>

<tr height=4><td><font face=terminal size=2 color=black>Email </td><td>
<input type=text name="email_add" size=40></td></tr>
<%End if%>
<tr height=4><td valign=center><font face=terminal size=2
color=black>Comments </td><td><textarea cols="30" rows="5" name="comments"
value=<%=session("comments")%>></textarea></td></tr>
<tr><td colspan=2 align=center><input type="submit" value="Submit"></td></tr>
</form>
</table>

</body>
<%End if%>

</html>

Reply to this Message

Bob Barrows [MVP] - 25 Jun 2008 13:15 GMT

> here is a simple form. it works well in my system. I hosted my site.
> but i get the error
[quoted text clipped - 6 lines]
> i've highlighted the line 57. pl. help me. without this fix, i cannot
> tell anybody abt my web site.

I cannot find it. Can you please snip out everything that is not
relevant?
In the meantime, perhaps your error is covered here:
http://www.aspfaq.com/show.asp?id=2009

Seeing that you are using ODBC, I can recommend that you switch to using
the native OLE DB provider:
http://www.aspfaq.com/show.asp?id=2126

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers (tokens):
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36
562fee7804e

Personally, I prefer using stored procedures:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9
d4409dc1701?hl=en

Signature

Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reply to this Message

Daniel Crichton - 25 Jun 2008 16:14 GMT

sankari wrote on Wed, 25 Jun 2008 00:50:01 -0700:

> here is a simple form. it works well in my system. I hosted my site.
> but i get the error
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'

> [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

> /join.asp, line 57

> i've highlighted the line 57. pl. help me. without this fix, i cannot
> tell anybody abt my web site.

> thx in advance

> ssql="Select email_address from regn where email_address='" &
> request.form("email_add") & "'"

This is bad - search Google for SQL Injection.

> [COLOR=DimGray]objRS.Open ssql, adoCon,3,3[/COLOR]

So this is the highlighted line? It looks vaguely like BBCode or something
similar, this is Usenet and it's pretty much all just plain text. Are you
sure this is the right line? I'd expect that error to occur at your
Connection object open method.

Why are you using an optimistic lock and a static cursor when you're not
editing the recordset rows and don't need to move backwards in the
recordset?

What happens if you change this to

objRS.Open ssql, adoCon

so it uses the default read only lock and forward only cursor? Do you still
get an error?

Back to the connection object - this is likely where the error actually is
on the .Open line

> Set adoCon = Server.CreateObject("ADODB.Connection")
> adoCon.mode = 3
>
> adoCon.Open "dsn=wa;database=regn","test","test"

What happens if you drop the adoCon.mode = 3 line? What database are you
using? If it's SQL Server or MS Access this is definitely not needed, and
it's a provider specific property so not all databases and providers will
support it.

If dropping the .Mode line doesn't solve it, how about doing as Bob
suggested and not using ODBC, but instead using the appropriate OLE DB
provider? If you tell us which database you're using it would help.

> execSQL= "insert into regn
> (member_name,occupation,member_id,email_address,comments,date_ins)
> values ('"
> & member_name & "','" & occupation & "', '" & member_id & "', '" &
> email_address & "', '" & comments & "','" & dat & "')"

> adoCon.execute(execSQL)

Again, look up SQL Injection. Use a parameterised procedure. The validation
you do on the variables used in the above code is rudimentary at best, and
certainly doesn't prevent even the simplest of injection attacks.

Signature

Dan

Reply to this Message

Webmaster Forum / ASP / Database Access / June 2008

Driver's SQLSetConnectAttr failed