Solved

ACCESS 2000 and the 'Insert into' statement

Posted on 2002-04-04
64
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 24
  • 21
  • 13
  • +4
64 Comments
 
LVL 10

Expert Comment

by:Dxpert
ID: 6918411
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
ID: 6918415
If it doesn't work post your code.
0
 
LVL 7

Expert Comment

by:weesiong
ID: 6918432
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 2

Author Comment

by:orbitaltech
ID: 6918434
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
ID: 6918457
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
ID: 6918475
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
ID: 6918481
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
ID: 6918484
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
ID: 6918522
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
ID: 6918529
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
ID: 6918539
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
ID: 6918566
What about cursor type?
0
 
LVL 10

Expert Comment

by:Dxpert
ID: 6918575
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
ID: 6918592
Dxpert:

Same error, regardless of locktype and cursortype
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6918647
When I open my recordset as follows, everything works fine:

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

Author Comment

by:orbitaltech
ID: 6918659
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
ID: 6918662
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
ID: 6918665
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
ID: 6918685
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
ID: 6918691
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
ID: 6918694
Stand by.....
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6918712
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
ID: 6918721
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
ID: 6918722
what error do you get on the second one?
0
 
LVL 10

Expert Comment

by:Dxpert
ID: 6918734
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
ID: 6918739
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
ID: 6918744
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
ID: 6918745
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
ID: 6918765
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
ID: 6918771
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
ID: 6918774
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
ID: 6918786
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
 
LVL 10

Expert Comment

by:Dxpert
ID: 6918797
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
ID: 6918799
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
ID: 6918807
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
ID: 6918818
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
ID: 6918821
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
ID: 6918841
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
ID: 6918847
can you post the current code that you are trying to use. include connection and recordset settings?
0
 
LVL 2

Author Comment

by:orbitaltech
ID: 6918852
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
ID: 6918864
MyRS.ActiveConnection = Application("DB_Connection") should be :

MyRS.ActiveConnection = MyConn
0
 
LVL 10

Expert Comment

by:Dxpert
ID: 6918871
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
ID: 6918882
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
ID: 6918885
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
ID: 6918892
Expended_Date is an integer, not a date... not my design
0
 
LVL 10

Expert Comment

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

Author Comment

by:orbitaltech
ID: 6918931
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
ID: 6918945
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
ID: 6918964
lol.. sigh...
0
 
LVL 10

Expert Comment

by:Dxpert
ID: 6918994
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6919060
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
ID: 6919210
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
ID: 6919218
post our current code?
0
 
LVL 10

Expert Comment

by:Dxpert
ID: 6919219
//your
0
 
LVL 2

Author Comment

by:orbitaltech
ID: 6919225
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
ID: 6919237
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
ID: 6919525
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
ID: 6919571
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
ID: 6919590
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
ID: 6919722
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
ID: 6919806
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
ID: 6920682
orbitaltech, did u try using numbers instead?
0
 
LVL 2

Author Comment

by:orbitaltech
ID: 6920805
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
ID: 6920816
we're glad it worked for u!

=:-)
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

734 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