Solved

ACCESS 2000 and the 'Insert into' statement

Posted on 2002-04-04
64
281 Views
Last Modified: 2010-08-05
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
0
Comment
Question by:orbitaltech
  • 24
  • 21
  • 13
  • +4
64 Comments
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
Make sure that u specify the field names on your statement.

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

=:-)
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
If it doesn't work post your code.
0
 
LVL 7

Expert Comment

by:weesiong
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 10

Expert Comment

by:KenAdney
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
What about cursor type?
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
If adOopenDynamic (2) doesn't work try adOpenStatic(3).


Like this:

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




0
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
Dxpert:

Same error, regardless of locktype and cursortype
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
When I open my recordset as follows, everything works fine:

objRS.Open strTableNameOrSQL,objConnection,adOpenKeyset,adLockOptimistic
0
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 4

Expert Comment

by:escheider
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
escheider:

  You should actually read the problem I wrote in the beginning. I already stated that I set those up.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
Fritz:
and you can enter a value of '' for a date field and it will accept it?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
Stand by.....
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
what error do you get on the second one?
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
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
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
If the field date is the only problem you can use NULL as i explained before.

0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
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
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
Dxpert:

  I have tried setting the values to Null and I get the same error as stated above...
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
can you post the current code that you are trying to use. include connection and recordset settings.

=:-)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
can you post the current code that you are trying to use. include connection and recordset settings?
0
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
MyRS.ActiveConnection = Application("DB_Connection") should be :

MyRS.ActiveConnection = MyConn
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
You should stay away from storing Connections and Recordsets in Application variables. There are a lot o articles on microsoft about this practice.
0
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
I know... this is quick and dirty and just for testing...

Anyhow.. any suggestions on my issue at hand?
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
Expended_Date is an integer, not a date... not my design
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
Did u try to remove some of the fields to test?
0
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
lol.. sigh...
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
post our current code?
0
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
//your
0
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
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
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 7

Expert Comment

by:weesiong
Comment Utility
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
 
LVL 10

Accepted Solution

by:
Dxpert earned 200 total points
Comment Utility
orbitaltech, did u try using numbers instead?
0
 
LVL 2

Author Comment

by:orbitaltech
Comment Utility
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
 
LVL 10

Expert Comment

by:Dxpert
Comment Utility
we're glad it worked for u!

=:-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now