[RESOLVED] 80040e14 error

[mib300]

Hello guys,

i am new to the VB i hv jst wirtten a scripts for inserting the data from local system to db sqlserver 2008. It was working fine but from today it start giving the following error which i hav attached.
So, i need a help on it to solve the issue.

Thanks & Regards,
MIB.

[joshAU]

No idea off hand... but I'd have a look here:

http://tutorials.aspfaq.com/8000xxxx...14-errors.html

"You either used a reserved word as a column or alias name (or used a column name that begins with a number or non-alphanumeric character), didn't delimit a value properly, or really have a syntax error.

It looks like you can get the 80040E14 error for a variety of reasons...
I guess 80040E14 means "it didnt work"

possible causes include:
a genuine syntax error
attempting to insert null values into a column that doesnt allow nulls.
an argument data type text is invalid for argument
a permissions error
an invalid object name.
and more...

you may need to post the script, or at least the bit that errors, to provide futher suggestions.

Hope this helps.
josh

[mib300]

Thanx for ur prompt reply Josh. I have checked the link u have given but the issue exist please find below the script

'-----------------
sub processFile(path, fil)
Dim f, f1, fc, sql
'print "File: " & fil & " Path: " & path & " name: " & fil.name
if right(lcase(fil),4)<>".jpg" then exit sub ' a simple filter for JPGs only

' --- change references to columns path, name, add_date to suit your table
sql= " select count(*) from " & MyTableName & " where path = '" & path & "' and name = '" & fil.name & "'"
if getSql(sql)= 0 then ' add record
sql = "insert into " & MyTableName & " (path, name, add_date) values ('" & path & "', '" & fil.name & "', getdate() )"
ADO.execute(sql)
end if
End sub

'-------------

function getSql(sql)
dim recset
Set recset = ADO.execute(sql)
If not recset.EOF Then getSql=recset(0)
end function

'--------
'sub print(s)
' wscript.echo s
'end sub


Thanks In Advance,
MIB.

[joshAU]

Hi again.
Sorry, I'm about to head to bed, after 1:30 am here...

I'm not really sure, but a few suggestions....
I'll try to have another look tomorrow in case someone more learned has not already answered...

firstly, I'd declare all those statements as the datatype they are, otherwise i guess they will all be declared as variants:

Dim f, f1, fc, sql

change to:

Dim f as ? string?, integer, double? whatever it is,
and the same for the next two variables, f1, fc...
Dim SQL as string
and so on.

also check what type of data each column you are entering is correct, whether you are trying to input any null data into a column that doesnt support nulls, etc.

I'm not familiar with scripting, so I'm in unfamiliar territory.
I guess you cannot use debug.print sql for example, but you can add msgbox sql or msgbox whatever data you suspect is causing the error, so you can see what the data actually is, prior to trying to insert it into the database...

have you changed passwords or security settings on the database, or any table?

Does MyTableName contain the full name to the database table? ie databasename.dbo.tablename?

just some ideas...

Sorry I cannot provide more help at the moment...
goodnight.

[si_the_geek]

Welcome to VBForums

Unfortunately joshAU's advice about variables is not apt, because VBScript does not allow data types (it would be better if it did!).


The advice of using something like msgbox sql is very good tho, and I strongly recommend doing it... I suspect that you will find that the value of either path or fil.name contains the ' character (probably followed by an s), which would cause this issue.

That (and other issues) can be avoided by using parameters.

For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).

[joshAU]

Thanks for correcting me there Si!
As I said, I'm not familiar with scripting!

mib300, listen to Si, he knows what he's talking about.

[mib300]

Dear Josh n Seek i have checked as per your suggestions given but also i am facing the same error so please hlp me out to fix it.

[si_the_geek]

We can't help any further unless you give us more information.


Have you followed any of the suggestions? (such as msgbox sql)

What happened when you did? (eg: what was the SQL statement)

[mib300]

I am sending the whole script this script is written for copy data from the local computer and insert the same details in the table. If the script is not correct can u guys suggest me new script which can fulfill my requirement.

' Create a windows scheduler entry to run the script as often as required
' cscript -nologo PATH_WHERE_SCRIPT_STORED\jpgs_to_db.vbs
'------------------------
option explicit

dim TopFolder, MyDSN, MyTablename
dim FSO, ADO

TopFolder="C:\MIB\Dropbox" ' <<---- change to suit your server
MyDSN="T1" ' <<--- an ODBC DSN that points to your DB
MyTablename="MIB" ' <<--- change to suit your tablename
' create table jpg_files(path varchar(50), name varchar(30), add_date datetime)
' then in sub processFile change column-name references if necessary to suit
Set FSO = CreateObject("Scripting.FileSystemObject")
Set ADO = CreateObject("ADODB.Connection")
ADO.ConnectionString= "DSN=" & MyDSN & ";Uid=sa;Pwd=********;"
ADO.open

ScanFolder topfolder

ADO.close

'------------------

sub ScanFolder(path)
Dim folder, subFolders, fldr, subfiles, fil

'print "Fold: " & path
Set folder = fso.GetFolder(path)

set subfiles = folder.files
For Each fil in subfiles

processFile path, fil
next

' --- process subfolders of current folder
Set subFolders = folder.SubFolders
For Each fldr in subFolders
call scanfolder(fldr)
Next
End sub

'-----------------
sub processFile(path, fil)
Dim f, f1, fc, sql
'print "File: " & fil & " Path: " & path & " name: " & fil.name
if right(lcase(fil),4)<>".jpg" then exit sub ' a simple filter for JPGs only

' --- change references to columns path, name, add_date to suit your table
sql= " select count(*) from " & MyTableName & " where path = '" & path & "' and name = '" & fil.name & "'"
if getSql(sql)= 0 then ' add record
sql = "insert into " & MyTableName & " (path, name, add_date) values ('" & path & "', '" & fil.name & "', getdate() )"
ADO.execute(sql)
end if
End sub

'-------------

function getSql(sql)
dim recset
Set recset = ADO.execute(sql)
If not recset.EOF Then getSql=recset(0)
end function

'--------
'sub print(s)
' wscript.echo s
'end sub


Thanks In Advance
MIB.

[joshAU]

As si has said, have you tried msgbox sql? Post the full SQL string that gets sent, that's the only logical starting place...

[mib300]

Thanx alot josh n si as the issue ws wit the structure of the path and its solved.

[joshAU]

No worries.
Glad to hear you fixed it.
Remember to mark this thread as RESOLVED. (Thread tools, Mark Thread resolved).
josh