ACCESS 2000 and the 'Insert into' statement

I have a simple insert into query written in ACCESS 2000. I also have an ASP
page that takes a few fields and inserts then into an Access table via ADO
and an access query. Everything works fine UNLESS I do not use all of the
fields. Some of the fields are optional which include 3 text fields, 2
numeric fields and 2 date fields. How would I go about allowing those fields
that are empty to be passed into the Access query as have default values set
in the database?

My ASP Error:
Microsoft JET Database Engine (0x80040E14)
Syntax error in parameters clause. Make sure the parameter exists and that
you typed its value correctly.

PS: I have the database setup to allow zero-length fields in the text fields
and the other numeric and date fields are set to be non-required fields. I
have a feeling that this is ADO, not Access??


-David
LVL 2
orbitaltechAsked:
Who is Participating?
 
DxpertConnect With a Mentor Commented:
orbitaltech, did u try using numbers instead?
0
 
DxpertCommented:
Make sure that u specify the field names on your statement.

INSERT INTO table (Field1, Field2, Field3) VALUES ('1','2','3')

=:-)
0
 
DxpertCommented:
If it doesn't work post your code.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
weesiongCommented:
orbitaltech,

Please post your code, and it look like because the value is error.

This is sample correct sql:
Insert into table (name, age, birthday) values ('weesiong', 24, #01/01/2002#)

The name is char so need put single quotes
The age is number so no need single quotes
The birthday is date so need put ##

and more the times for char field you need replace single quotes to double single quotes, exp:

Name = Mr's Goh
The error one : Insert into table (name, age, birthday) values ('Mr's God', 24, #01/01/2002#)
Correct one : The error one : Insert into table (name, age, birthday) values ('Mr''s God', 24, #01/01/2002#)

You need to replace it:
Insert into table (name, age, birthday) values ('Replace(Mr's God,"'", "''")', 24, #01/01/2002#)

Regards,
Wee Siong



0
 
orbitaltechAuthor Commented:
I am afraid it's not that simple.. I an not a newbie to write SQL queries, just Access queries..

Here is the query itself:

PARAMETERS UserID IEEEDouble, DistributionCode IEEEDouble, CategoryCode IEEEDouble, ProjectManager Text ( 255 ) = "", ProjectSponser Text ( 255 ) = "", ProjectName Text ( 255 ), ProjectNumber Text ( 255 ), Budget Currency = 0, ExpendedDate Currency = 0, CompletionEstimate Currency = 0, ContractFileName Text ( 255 ), StartDate DateTime = null, FinishDate DateTime = null;


INSERT INTO Project_Detail_Header ( User_ID, Distribution_Code, Category_Code, Project_Manager, Project_Sponser, Project_Name, Project_Number, Budget, Expended_Date, Completion_Estimate, Contract_Filename, Start_Date, Finish_Date )
VALUES ([UserID], [DistributionCode], [CategoryCode], [ProjectManager], [ProjectSponser], [ProjectName], [ProjectNumber], [Budget], [ExpendedDate], [CompletionEstimate], [ContractFileName], [StartDate], [FinishDate]);

---------------------------------------------------
Like I said before, it works fine when I use all of the field names in a query. It fails with the above error only when I try to pass a null or '' to the last 6 fields.


-David
0
 
DxpertCommented:
What if you don't include the 6 field names, like this:

INSERT INTO Project_Detail_Header (User_ID, Distribution_Code, Category_Code, Project_Manager, Project_Sponser,
Project_Name, Project_Number)
VALUES ([UserID], [DistributionCode], [CategoryCode], [ProjectManager], [ProjectSponser], [ProjectName],
[ProjectNumber);


=:-)
0
 
orbitaltechAuthor Commented:
I would like to do that, but I have the query built in access and want to try this without building a dynamic query in ASP. Any one (or all) of the 6 fields can be omitted at any given time.
0
 
orbitaltechAuthor Commented:
I would like to do that, but I have the query built in access and want to try this without building a dynamic query in ASP. Any one (or all) of the 6 fields can be omitted at any given time.
0
 
fritz_the_blankCommented:
Why not use ado all the way?

'open a recordset called objRS

objRS.AddNew
   objRS("User_ID") = value1
   objRS("Distribution_Code") = value1
   objRS("Category_Code") = value2
   objRS("Project_Manager") = value3
   objRS("Project_Sponser") = value4
   objRS("Project_Name") = value5
   objRS("Project_Number") = value6
objRS.Update

That way, you don't need to worry about apostrophes, quoatation marks and etc.

0
 
KenAdneyCommented:
For what it's worth, I don't try to update the record set unless I have a value, for instance:

if request.form("R1") <> "" then rs.Fields("r1")    = request.form("R1")
0
 
DxpertCommented:
You guys are getting it wrong. He does not want to do it in asp. Otherwise my suggestion would work just fine. The query is pre-built in ACCESS. Read his comments.

=:-)
0
 
orbitaltechAuthor Commented:
Doing it fritz's way produces this:

ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

I have tried both the adLockOptimistic and adLockPessimistic locking types. Must be in access
0
 
DxpertCommented:
What about cursor type?
0
 
DxpertCommented:
If adOopenDynamic (2) doesn't work try adOpenStatic(3).


Like this:

  rstObj.open strSQL, cnxObj, 3, 3 'adOpenStatic, adLockOptimistic




0
 
orbitaltechAuthor Commented:
Dxpert:

Same error, regardless of locktype and cursortype
0
 
fritz_the_blankCommented:
When I open my recordset as follows, everything works fine:

objRS.Open strTableNameOrSQL,objConnection,adOpenKeyset,adLockOptimistic
0
 
orbitaltechAuthor Commented:
fritz,

and you are using Access 2000 and ASP right?

I can open the recordset it's adding a new line item to a table using the MyRS.UpDate() method that is the problem
0
 
escheiderCommented:
Set the Allow Zero Length = True on your Access table for each of the fields.  Then you can pass null or blank values to it.
0
 
orbitaltechAuthor Commented:
escheider:

  You should actually read the problem I wrote in the beginning. I already stated that I set those up.
0
 
fritz_the_blankCommented:
I am currently using Access 2002 with ASP. I have used the same code sucessfully with Access 2000 and with Access 97.

Fritz the Blank
0
 
orbitaltechAuthor Commented:
Fritz:
and you can enter a value of '' for a date field and it will accept it?
0
 
fritz_the_blankCommented:
Stand by.....
0
 
fritz_the_blankCommented:
The fields in my table include:

numCompany, numStaff, strSentItems, and datDate.

The default value for the datDate field is Now()

This works fine:

<%
call GetConnection()
call GetRecordSet("tblSentItems")
objRS.addNew
     objRS("numCompany")=3
objRS.Update

call ClearRecordset
call ClearConnection
%>


This does not:

<%
call GetConnection()
call GetRecordSet("tblSentItems")
objRS.addNew
     objRS("numCompany")=3
     objRS("datDate") = ""
objRS.Update

call ClearRecordset
call ClearConnection
%>

0
 
DxpertCommented:
Is the date the only field you are having problem with now? If not, try to isolate in which fields you are having problems. Run multiples steps, and start updating only 1 field, if works, add another one to your code.

=:-)
0
 
orbitaltechAuthor Commented:
what error do you get on the second one?
0
 
DxpertCommented:
In this case use NULL for date, create and if statement and check the value of the date variable:


<%

if not isDAte(datToday) then
   datToday = NULL
end if

call GetConnection()
call GetRecordSet("tblSentItems")
objRS.addNew
    objRS("numCompany")=3
    objRS("datDate") = datToday
objRS.Update

call ClearRecordset
call ClearConnection
%>

This Works.

=:-)
0
 
fritz_the_blankCommented:
Error Type:
Provider (0x80020005)
Type mismatch.
/dev/esamailer/testinsert.asp, line 20

line 20:
objRS("datDate") = ""

So if you just leave that off, you should be set.

Fritz the Blank
0
 
fritz_the_blankCommented:
Even if I remove the default value from the datDate field, the following still works fine:

<%
call GetConnection()
call GetRecordSet("tblSentItems")
objRS.addNew
    objRS("numCompany")=3
objRS.Update

call ClearRecordset
call ClearConnection
%>
0
 
orbitaltechAuthor Commented:
mabye, totally different error

All in all, it is known that what I am trying to do (using an Access query and ADO/ASP) is impossible. I HAVE to build it dynamically in order to get it working?
0
 
DxpertCommented:
If the field date is the only problem you can use NULL as i explained before.

0
 
fritz_the_blankCommented:
I am not sure that I follow your question.

I think that this is what you are looking for:

objRS.addNew
     objRS("datDate")= null
objRS.Update
0
 
fritz_the_blankCommented:
So you can do one of two things:

1) explicitly refer only to those values that you wish to update

2) explicitly list all values, but set dates to null as in the code above.

Fritz the Blank
0
 
DxpertCommented:
fritz_the_blank, What' the diff. between your code above and mine 4 comments before?


>>

<%

if not isDAte(datToday) then
  datToday = NULL
end if

call GetConnection()
call GetRecordSet("tblSentItems")
objRS.addNew
   objRS("numCompany")=3
   objRS("datDate") = datToday
objRS.Update

call ClearRecordset
call ClearConnection
%>
0
 
DxpertCommented:
orbitaltech, whichever way you choose (INSERT or opening a recordset) you are going to set the value of date to NULL if is blank.

=:-)
0
 
fritz_the_blankCommented:
DXpert,

Sorry about that; there is no difference; I didn't read your post. I just read

 "All in all, it is known that what I am trying to do (using an Access query and ADO/ASP) is impossible.
I HAVE to build it dynamically in order to get it working? "

and wanted to give him the two possiblilities as I saw them.

Fritz the Blank
0
 
orbitaltechAuthor Commented:
Dxpert:

  I have tried setting the values to Null and I get the same error as stated above...
0
 
DxpertCommented:
can you post the current code that you are trying to use. include connection and recordset settings.

=:-)
0
 
fritz_the_blankCommented:
I don't get it. Are you saying that

objRS.AddNew
  objRS("User_ID") = null
  objRS("Distribution_Code") = null
  objRS("Category_Code") = null
  objRS("Project_Manager") = ""
  objRS("Project_Sponser") = ""
   objRS("Expended_Date") = null
  objRS("Project_Name") = ""
  objRS("Project_Number") = ""
objRS.Update


doesn't work?

Fritz the Blank
0
 
orbitaltechAuthor Commented:
yup.

Here is the error:

ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
0
 
DxpertCommented:
can you post the current code that you are trying to use. include connection and recordset settings?
0
 
orbitaltechAuthor Commented:
code:

MyConn = Server.CreateObject("ADODB.Connection");
     var ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\sample.mdb;";
     MyConn.CursorLocation = 3;    
     
     MyConn.Open(ConnString);

Set MyRS = Server.CreateObject("ADODB.Recordset")
MyRS.LockType = 2
MyRS.CursorLocation = 3
MyRS.ActiveConnection = Application("DB_Connection")


MyRS.AddNew
MyRS("User_ID") = 1
MyRS("Distribution_Code") = 0
MyRS("Catergory_Code") = 0
MyRS("Project_Manager") = "" 
MyRS("Project_Sponser") = "" 
MyRS("Project_Name") = "" 
MyRS("Project_Number") = "" 
MyRS("Budget") = 0
MyRS("Expended_Date") = 0
MyRS("Completion_Estimate") = 0
MyRS("Contract_Filename") = "" 
MyRS("Start_Date") = Null
MyRS("Finish_Date") = Null
MyRS.Update

0
 
DxpertCommented:
MyRS.ActiveConnection = Application("DB_Connection") should be :

MyRS.ActiveConnection = MyConn
0
 
DxpertCommented:
You should stay away from storing Connections and Recordsets in Application variables. There are a lot o articles on microsoft about this practice.
0
 
orbitaltechAuthor Commented:
I know... this is quick and dirty and just for testing...

Anyhow.. any suggestions on my issue at hand?
0
 
DxpertCommented:
MyRS("Expended_Date") = 0 should be

MyRS("Expended_Date") = NULL
....

As i said before try to isolate which fields are giving you problems.


do this first:

MyRS.AddNew
MyRS("User_ID") = 1
MyRS("Distribution_Code") = 0
MyRS("Catergory_Code") = 0
MyRS("Project_Manager") = "" 
MyRS("Project_Sponser") = "" 
MyRS("Project_Name") = "" 
MyRS("Project_Number") = "" 
MyRS("Budget") = 0

MyRS.Update

if it works add more fields 'til you get to the one that is giving you errors.

=:-)
0
 
orbitaltechAuthor Commented:
Expended_Date is an integer, not a date... not my design
0
 
DxpertCommented:
Did u try to remove some of the fields to test?
0
 
orbitaltechAuthor Commented:
I don't know how to explain this error any better: I cannot even use the MyRS.UpDate method.. AT ALL!! Any time that I try (using one field to all of them) it gives me the same error:

ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected
locktype.


Mabye my database is not setup right???
0
 
DxpertCommented:
Now that you know that you have to do the query in asp, why don't you try to use the INSERT STATEMENT instead?

Just set the Date variables to NULL before inserting them.

0
 
orbitaltechAuthor Commented:
lol.. sigh...
0
 
DxpertCommented:
0
 
fritz_the_blankCommented:
A long shot here..

What are the permissions on the directory housing the database? Do the IUSR and IWAM accounts have change priveldges?


Try changing the values to
MyRS.LockType = 3
MyRS.CursorLocation = 2


Fritz the Blank
0
 
orbitaltechAuthor Commented:
ok.. after trying everything except the thing that works, I have narrowed it down to something. using the link from Dxpert, here is what happens:

this error occurs whenever I try to use any lock type whatsoever. If I take the lock type off, it works (minus the actual problem I am having above)

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
0
 
DxpertCommented:
post our current code?
0
 
DxpertCommented:
//your
0
 
orbitaltechAuthor Commented:
Dim ConnString, Uploader, File, MyConn
Dim MyRS, MyCmd, MyPrm
Dim User, UserID, AuthLevel

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\sample.mdb;"
Set MyConn = Server.CreateObject("ADODB.Connection")
Set MyRS = Server.CreateObject("ADODB.Recordset")

MyConn.Open(ConnString)

User = Split(Request.ServerVariables("AUTH_USER"),"\")
UserID = User(UBound(User))

MyRS.Open "Select User_ID, Authority_Level from Users where User_Name = '" & UserID & "'",MyConn,adOpenDynamic, adLockUnspecified

UserID = MyRS("User_ID")
AuthLevel = MyRS("Authority_Level")
MyRS.Close()


'Objects go Bye-Bye
MyConn.Close()
Set MyRS = Nothing
Set MyConn = nothing
0
 
DxpertCommented:
Do you specify the values for :
adOpenDynamic and adLockUnspecified in your code?

if not try:
MyRS.Open "Select User_ID, Authority_Level from Users where User_Name = '" & UserID & "'",MyConn,2,
-1
0
 
fritz_the_blankCommented:
Unless you are including the adovbs.inc, then you have to use the numbers rather than the constants. I am sure you know that, but just in case you might have forgotten.

Did you try the settings that I posted above?

Fritz the Blank
0
 
Anthony PerkinsCommented:
Please maintain your open questions:

Downloading and registering components in an asp page Date: 11/16/2000 01:56PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=11892458
Dragging Forms Date: 06/30/2001 12:02PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20143915
Lost Microsoft Document Date: 06/09/2001 09:33AM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=winprog&qid=20132478

Anthony
0
 
orbitaltechAuthor Commented:
Anthony,

  I have maintained those questions. Now it's your turn. I flagged them for deletions a LONG time ago. If they are still there then it's your fault!

0
 
Anthony PerkinsCommented:
If that is the case, I suggest you post a message in Community Support (http://www.experts-exchange.com/commspt) to get it resolved.

Thanks
Anthony
0
 
weesiongCommented:
orbitalech,

MyRS.AddNew
MyRS("User_ID") = 1
MyRS("Distribution_Code") = 0
MyRS("Catergory_Code") = 0
MyRS("Project_Manager") = "" 
MyRS("Project_Sponser") = "" 
MyRS("Project_Name") = "" 
MyRS("Project_Number") = "" 
MyRS("Budget") = 0
MyRS("Expended_Date") = 0
MyRS("Completion_Estimate") = 0
MyRS("Contract_Filename") = "" 
MyRS("Start_Date") = Empty
MyRS("Finish_Date") = Empty
MyRS.Update

No sure what big different use Null and Empty, and try to reinstall the MDAC in your server. Suggest use the MDAC 2.5.

So if you try this:
MyRS.AddNew
MyRS("User_ID") = 1
MyRS.Update

Just add 1 field, will got any error? Just line by line to testing it, if still error, means the ADO problem and no script problem.

Regards,
Wee Siong
0
 
orbitaltechAuthor Commented:
yea, they worked... I actually had to make MyRS("x") = "Null" instead of null for whatever screwed up reason

I will post points sometime today. Thanks all, you've been great helps!
0
 
DxpertCommented:
we're glad it worked for u!

=:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.