ACCESS 2000 and the 'Insert into' statement

Make sure that u specify the field names on your statement.

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

=:-)

If it doesn't work post your code.

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

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

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);


=:-)

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.

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.

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.

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")

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.

=:-)

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

What about cursor type?

If adOopenDynamic (2) doesn't work try adOpenStatic(3).


Like this:

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

Dxpert:

Same error, regardless of locktype and cursortype

When I open my recordset as follows, everything works fine:

objRS.Open strTableNameOrSQL,objConnection,adOpenKeyset,adLockOptimistic

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

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.

escheider:

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

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

Fritz:
and you can enter a value of '' for a date field and it will accept it?

Stand by.....

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
%>

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.

=:-)

what error do you get on the second one?

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.

=:-)

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

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
%>

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?

If the field date is the only problem you can use NULL as i explained before.

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

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

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
%>

orbitaltech, whichever way you choose (INSERT or opening a recordset) you are going to set the value of date to NULL if is blank.

=:-)

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

Dxpert:

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

can you post the current code that you are trying to use. include connection and recordset settings.

=:-)

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

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.

can you post the current code that you are trying to use. include connection and recordset settings?

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

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

MyRS.ActiveConnection = MyConn

You should stay away from storing Connections and Recordsets in Application variables. There are a lot o articles on microsoft about this practice.

I know... this is quick and dirty and just for testing...

Anyhow.. any suggestions on my issue at hand?

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.

=:-)

Expended_Date is an integer, not a date... not my design

Did u try to remove some of the fields to test?

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???

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.

lol.. sigh...

Here's some info from MS:

http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q225899&

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

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.

post our current code?

//your

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

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

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

Please maintain your open questions:

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

Anthony

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!

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

Thanks
Anthony

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

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!

we're glad it worked for u!

=:-)