We help IT Professionals succeed at work.

SQL statement too long?

aspguy123
aspguy123 asked
on
Medium Priority
366 Views
Last Modified: 2008-02-01
Do you guys know what is wrong with this?:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name too long

/check_my_stats.asp, line 284

and line 284 is my SQL statement:

strSQL = "INSERT INTO Tblstats (IPAddress) VALUES ('" & recIP & "')"

Thanks,
a s p   g u y
Comment
Watch Question

the error wouldnt be occuring at that line, as you're just assigning it to a string... its probly the next Connection.Execute line... can we see more of your code?
CERTIFIED EXPERT
Top Expert 2005

Commented:
From the error, it sounds more like your connection string. Would you post that as well?

Fritz the Blank

Author

Commented:
                   'Setup our Connection to the database
                    Dim strSQL
                    Set objRS = Server.CreateObject("ADODB.Connection")
                    objRS.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
                          "DBQ=c:\inetpub\wwwroot\stats.mdb" &_
                          "Uid=Admin;" &_
                          "Pwd=CENSORED;"
                  'strSQL = "INSERT INTO Tblstats (IPAddress, OS, Codename, Browser, BrowserDetails, CSS, JavaScript, ScreenSize, Language, Colors, FontSmooth, Referer, HistoryLength, Plugins) VALUES ('" & recIP & "','" & recOS & "','" & recCodename & "','" & recOS & "','" & recBrowser & "','" & recBrowserDetails & "','" & recCSS & "','" & recJavaScript & "','" & recScreenSize & "','" & recLanguage & "','" & recColors & "','" & recFontSmooth & "''" & recReferer & "','" & recHistoryLength & "','" & recPlugins & "',)"
                  strSQL = "INSERT INTO Tblstats (IPAddress) VALUES ('" & recIP & "')"
                  objRS.Open strSQL, objConn
                ObjRS.Close
             Set objRS = Nothing

Author

Commented:
Ooops this code is the correct one:

                    'Setup our Connection to the database
                    Dim strSQL
                    Set objRS = Server.CreateObject("ADODB.Connection")
                    objRS.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
                          "DBQ=c:\inetpub\wwwroot\stats.mdb" &_
                          "Uid=Admin;" &_
                          "Pwd=CENSORED;"

                  strSQL = "INSERT INTO Tblstats (IPAddress) VALUES ('" & recIP & "')"
                  objRS.Open strSQL, objConn
                ObjRS.Close
             Set objRS = Nothing

Author

Commented:
And actually I looked back and it was this line that was giving the error:

objRS.Open strSQL, objConn
Hi,

can u please substitute ur connection string withe the following format,

Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

if u have copied the code and pasted it then i suppose a ";" is missing after "DBQ=c:\inetpub\wwwroot\stats.mdb"

AND

can u please try with

set conn = server.createobject("adodb.connection")
connect_string = "Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;
conn.ConnectionString = connect_string
conn.Open

All the Best
I really recommend that you use the following for your connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\inetpub\wwwroot\stats.mdb;" & _
"Uid=Admin;Pwd=CENSORED;"

This would work on my IIS4 and IIS5 servers.  The string above also is for Access 2000 and above.  For 97 or less use "Provider=Microsoft.Jet.OLEDB.3.5;" instead.
CERTIFIED EXPERT

Commented:
I think I found the error:

"DBQ=c:\inetpub\wwwroot\stats.mdb" &_
"Uid=Admin;"

will produce this:

"DBQ=c:\inetpub\wwwroot\stats.mdbUid=Admin;"

No wonder the data source name is too long...
CERTIFIED EXPERT

Commented:
And, bculver98, it's not polite to lock the question like that...
CERTIFIED EXPERT

Commented:
bculver98,

You are here long enough to know EE policy. The policy here is to post everyhing in comments. Questionner will then have the option to accept a comment as an answer. Posting as a proposed answer will lock the question and thus restrict the exposure to more experts. We know you are new here and thus is forgiven. With the current version of EE, you do not have the option to convert your answer to a comment but this can be done by the questioner himself. I advise the questionner to reject his answer so that this question will be released to more experts. You can however accept his comment again if you find it is really helpful.

Thanks
hongjun
CERTIFIED EXPERT
Commented:
From your code provided, you are trying to insert a record into a table. One problem I found is that objConn is not initialized. One naming convention for ADODB.Connection objects is using objConn and objRs for ADODB.Recordset. Try the below modified coding. Another problem is that you forget to include a semi-colon after your dbq=c:\...

'Setup our Connection to the database
Dim objConn
Dim strSQL

strSQL = "INSERT INTO Tblstats (IPAddress) VALUES ('" & recIP & "')"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" &_
             "DBQ=c:\inetpub\wwwroot\stats.mdb;" &_
             "Uid=Admin;" &_
             "Pwd=CENSORED;"
objConn.Execute strSQL
objConn.Close
Set objConn = Nothing


hongjun
CERTIFIED EXPERT

Commented:
Please note that your connection string can be substituted by the below if your root is at c:\inetpub\wwwroot\

objConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" &_
             "DBQ=" & Server.MapPath("stats.mdb") & ";" &_
             "Uid=Admin;" &_
             "Pwd=CENSORED;"

Note of the use of Server.MapPath.

hongjun

Author

Commented:
You rock man!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.