Access Person Cannot Write Insert Query In Alpha

[kingcarol]

OK, I am too used to the query genies in Access and have trouble rewriting sql queries that will work in Alpha.

In Access, the SQL view of an INSERT query is below. In my query, I am appending (or inserting) multiple records with a JOB_CODE of H to the same
table but with a JOB_CODE from the form I'm on. In other words, I am copying all records from H job to another job.
I named the field to append to JOB_CODE JOB with reference to Forms!FrmJobSetupNewJobs!JOB_CODE. Here's the SQL view from Access:

INSERT INTO TblJobsPriorityMeasures ( JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM )
SELECT Forms!FrmJobSetupNewJobs!JOB_CODE AS JOB, TblJobsPriorityMeasures.MEASURE, TblJobsPriorityMeasures.QUANTITY, TblJobsPriorityMeasures.PRIORITY_NUM
FROM TblJobsPriorityMeasures
WHERE (((TblJobsPriorityMeasures.JOB_CODE)="H"));

In Alpha, the above syntax does not work, of course, so I have tried several iterations, but not surprisingly, none of them work.
Here's one of them:

dim jobCode as c 
jobCode = e.session.specsJobCode

dim sqlInsertStatement as c

dim args as sql::arguments
args.add("JOB_CODE",jobCode)
args.add("HJob","H")

sqlInsertStatement = <<%sqltxt%
INSERT INTO TblJobsPriorityMeasures (JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM) VALUES (SELECT :JOB_CODE as JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM FROM TblJobsPriorityMeasures WHERE JOB_CODE = :HJob)
%sqltxt%

cn.Execute(sqlInsertStatement,args)   (etc., etc.)

Any suggestions?

[lvasic]

Re: Access Person Cannot Write Insert Query In Alpha

This works for me...

sql1 = "Insert into employees (DateCreate, FirstName, LastName) Values(:DateCreate,:FirstName,:LastName)"

flag = e.cn.execute(sql1,args)

[kingcarol]

Re: Access Person Cannot Write Insert Query In Alpha

Thank you, Ivasic. I have used a simple single record insert like you showed several times successfully in Alpha. But this is now a more complex insert query where I need to insert multiple records that are copies of other records selected from a table with a WHERE clause, etc.

[chadbrown]

Re: Access Person Cannot Write Insert Query In Alpha

dim jobCode as c
jobCode = e.session.specsJobCode

dim sqlInsertStatement as c

dim args as sql::arguments
args.add("JOB_CODE",jobCode)
args.add("HJob","H")----------------why is there "" around the H

sqlInsertStatement = <<%sqltxt%
INSERT INTO TblJobsPriorityMeasures (JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM) VALUES (SELECT :JOB_CODE as JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM FROM TblJobsPriorityMeasures WHERE JOB_CODE = :HJob)
%sqltxt%

cn.Execute(sqlInsertStatement,args) (etc., etc.)


Also does the sqltxt need to be evaluated?
where are the open cn and close?

[lvasic]

Re: Access Person Cannot Write Insert Query In Alpha

If I understand what you're doing. Inserting into the same table.
The SQL should be like this:

insert into TblJobsPriorityMeasures
(JOB_CODE,MEASURE,QUANTITY,PRIORITY_NUM)
Select JOB_CODE = 'B',MEASURE,QUANTITY,PRIORITY_NUM
From TblJobsPriorityMeasures where JOB_CODE = 'A'

[kingcarol]

Re: Access Person Cannot Write Insert Query In Alpha

I just tried this (without arguments and removing them from execute statement) and I get an error message that there are too few parameters. (jobCode is a variable established earlier)

INSERT INTO TblJobsPriorityMeasures
 (JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM) 
SELECT JOB_CODE = jobCode, MEASURE, QUANTITY, PRIORITY_NUM 
From TblJobsPriorityMeasures where JOB_CODE = 'H'

Chad - I was just evaluating the statement because earlier when I learned how to do a simple insert query, Selwyn had given me an example where the statement was evaluated. In this thread, I left out the code about opening and closing the connection, etc., because I just wanted to show the part of the code that had the issue. I have been using double quotes around character arguments in a lot of my arguments for sql queries, and they have worked. I don't know for sure if they are needed or not now. I think when I didn't have them in prior days, I got an error, but who knows... my brain is fried with this.

[chadbrown]

Re: Access Person Cannot Write Insert Query In Alpha

INSERT INTO TblJobsPriorityMeasures (JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM)
VALUES (SELECT JOB_CODE ='jobCode', MEASURE, QUANTITY, PRIORITY_NUM )
From TblJobsPriorityMeasures where JOB_CODE = 'H'

[kingcarol]

Re: Access Person Cannot Write Insert Query In Alpha

Finally got it to work! I had to use argument for the job code AND it needed Select :JOB_CODE AS JOB_CODE

dim jobCode as c 
jobCode = e.session.specsJobCode

dim sqlInsertStatement as c

dim args2 as sql::arguments
args2.add("JOB_CODE",jobCode)

sqlInsertStatement = "INSERT INTO TblJobsPriorityMeasures (JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM) SELECT :JOB_CODE AS JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM From TblJobsPriorityMeasures where JOB_CODE = 'H'"

cn.Execute(sqlInsertStatement,args2)

[kingcarol]

Re: Access Person Cannot Write Insert Query In Alpha

Also, just went and put the argument back in for getting the H job code for the where part of the sql statement and that worked, too. (I'm told it's best to use arguments for all criteria.) So, really I was just messing up in my first example by using VALUES in the sql statement. Plain English, I wanted to copy records that had a JOB_CODE of 'H' to another JOB_CODE that had been captured as a session variable.

dim jobCode as c 
jobCode = e.session.specsJobCode

dim sqlInsertStatement as c

dim args2 as sql::arguments
args2.add("JOB_CODE",jobCode)
args2.add("HJob","H")

sqlInsertStatement = "INSERT INTO TblJobsPriorityMeasures (JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM) SELECT :JOB_CODE AS JOB_CODE, MEASURE, QUANTITY, PRIORITY_NUM From TblJobsPriorityMeasures where JOB_CODE = :HJob"

cn.Execute(sqlInsertStatement,args2)

[lvasic]

Re: Access Person Cannot Write Insert Query In Alpha

Also, just to clarify... This code will update the entire database. If you have 100 records with "H" this will insert 100 new records...

[kingcarol]

Re: Access Person Cannot Write Insert Query In Alpha

Yes, Ivasic, that's absolutely right. In my case, a builder is copying spec measurements from job to another. There is also a count query prior to the insert query that checks to make sure that the job being copied to has no spec measurements. If there are none, then the insert query is allowed. If some are found, the insert query is not allowed.

[KSquared]

Re: Access Person Cannot Write Insert Query In Alpha

I read with interest Carol�s thread about having trouble writing an INSERT query in Alpha 5, as I have been struggling with this for a few weeks now (learning a lot, fixing a lot along the way, but at the end of the road and not sure how to proceed for here). Ultimately I am looking to execute an insert statement from another set of tables with joins and parameters. However, I have worked back to a more basic query for troubleshooting and can�t even get this to work.
Here is my code (in the AfterSearch event):

function AfterSearch as v (SearchDataSubmitted as P, Args as p, PageVariables as p)
with PageVariables
dim aa as p 
dim TimePeriod AS D
dim SearchID as c
dim EmpID AS C
dim numrec as c
TimePeriod=SearchDataSubmitted.TimePeriodDate
EmpID="FAH600"
SearchID="vHOL"
aa = args.rtc.A_AjaxResponses
aa[].text =	 "alert('EmpID: "+ EmpID +" TimePeriod: "+TimePeriod+"SearchID: "+ SearchID+"');"
aa[..].id = 100  'the order in which AjaxResponses are sent to the browser

'Insert a new record into a remote SQL database.
'DIM a connection variable
dim cn as SQL::Connection
'Dim a SQL arguments object, create arguments and set their values
dim K_args as sql::arguments

K_args.add("EmpID",EmpID)
K_args.add("SearchID",SearchID)
K_args.add("TimePeriod",TimePeriod)

dim sqlInsertStatement as c 
sqlInsertStatement = "INSERT INTO TimeEntryTEMP(EmpID,SearchID,TimePeriodDate) SELECT :EmpID as EmpID,:SearchID as SearchID,:TimePeriod as TimePeriodDate from tblTimeEntry"

cn.Execute(sqlInsertStatement,K_args)
cn.close()

end with
end function

I also tried inserting values only :

sqlInsertStatement = "INSERT INTO TimeEntryTEMP(EmpID,SearchID,TimePeriodDate) ('FAH600','VSICK','1/15/2027')"

Finally I also tried SELECT DISTINCT.

This hasn�t worked; nor has using the Genies/portable SQL.
I�ve re-written the grid to be very basic (on a table);
added in the variable msg box to show me what the variables are being populated with;
I have also manually added records to the TimeEntryTEMP table on the control panel with no problem;
Have added records via the grid itself .. no problem.
I can also write the INSERT query in SQL Express without issue..
Other things to note: the 3 fields are indeed primary keys and don�t allow nulls; have checked for duplicates, etc and they are fine.


If anyone sees what I am missing, I would so appreciate it! I definitely feel stupid,and this week I have learned how valuable this forum is, so I�m hoping someone out there will see what I cannot! Any advice would be so appreciated � hope to get some sleep this week!

[lvasic]

Re: Access Person Cannot Write Insert Query In Alpha

I put the code here in the event (onBeforeSQLCommandExecute)
The code will look like this. Are you updating one record?
sqlInsertStatement =
INSERT INTO TimeEntryTEMP(EmpID,SearchID,TimePeriodDate)
Values(:EmpID,:SearcID,:TimePeriod)

[chadbrown]

Re: Access Person Cannot Write Insert Query In Alpha

sqlInsertStatement = "INSERT INTO TimeEntryTEMP(EmpID,SearchID,TimePeriodDate) ('FAH600','VSICK','1/15/2027')"

Date format in sql needs to be '2027/1/15' also where is the values?

Try this instead

INSERT INTO TimeEntryTEMP(EmpID,SearchID,TimePeriodDate) VALUES('FAH600','VSICK','2027/1/15')

[KSquared]

Re: Access Person Cannot Write Insert Query In Alpha

You guys are great: thank you for your quick replies! I have to say, though, i am still stuck:

Here are both code functions that i just tried:

function AfterSearch as v (SearchDataSubmitted as P, Args as p, PageVariables as p)
with PageVariables
dim aa as p 
dim TimePeriod AS D
dim SearchID as c
dim EmpID AS C
dim numrec as c
TimePeriod=SearchDataSubmitted.TimePeriodDate
EmpID="FAH600"
SearchID="vHOL"
'Insert a new record into a remote SQL database.
'DIM a connection variable
DIM cn as SQL::Connection

aa = args.rtc.A_AjaxResponses
aa[].text =	 "alert('EmpID: "+ EmpID +" TimePeriod: "+TimePeriod+"SearchID: "+ SearchID+"');"
aa[..].id = 100  'the order in which AjaxResponses are sent to the browser

'Dim a SQL arguments object, create arguments and set their values
DIM K_args as sql::arguments

K_args.add("EmpID",EmpID)
K_args.add("SearchID",SearchID)
K_args.add("TimePeriod",TimePeriod)

dim sqlInsertStatement as c 
sqlInsertStatement = "INSERT INTO TimeEntryTEMP(EmpID,SearchID,TimePeriodDate) VALUES(:EmpID,:SearchID,:TimePeriod)"

cn.Execute(sqlInsertStatement,K_args)
cn.close()

end with

function AfterSearch as v (SearchDataSubmitted as P, Args as p, PageVariables as p)
with PageVariables
dim aa as p 
dim TimePeriod AS D
dim SearchID as c
dim EmpID AS C
dim numrec as c
TimePeriod=SearchDataSubmitted.TimePeriodDate
EmpID="FAH600"
SearchID="vHOL"
'Insert a new record into a remote SQL database.
'DIM a connection variable
DIM cn as SQL::Connection
aa = args.rtc.A_AjaxResponses
aa[].text =	 "alert('EmpID: "+ EmpID +" TimePeriod: "+TimePeriod+"SearchID: "+ SearchID+"');"
aa[..].id = 100  'the order in which AjaxResponses are sent to the browser
'Dim a SQL arguments object, create arguments and set their values
DIM K_args as sql::arguments
K_args.add("EmpID",EmpID)
K_args.add("SearchID",SearchID)
K_args.add("TimePeriod",TimePeriod)
dim sqlInsertStatement as c 
sqlInsertStatement = sqlInsertStatement = "INSERT INTO TimeEntryTEMP(EmpID,SearchID,TimePeriodDate) ('FAH600','VSICK','1/15/2027')"

cn.Execute(sqlInsertStatement,K_args)
cn.close()

end with
end function

No luck on either of them. Ultimately, what i am trying to get to is that whenever the user uses the search drop down box, a query is run against a few tables in my db (not the grid source);a few filters are applied, then the resultset (could be 0-40 records) is appended to the table that the grid is sourced against. So i think i have to leave the code in AfterSearch: do you think this is part of the issue? I also wondered abut the date format, but there has to be something else i think, b/c hardcoding the date as above didn't update my table. (and again i can go to control panel and update it np, so i think the SQL design is ok?)

THank you so much! It helps to validate - i have no idea what the issue could be - will try the port sql flag next, i wonder if i am missing a major setting somewhere as well. such a Rookie!