/    Sign up×
Community /Pin to ProfileBookmark

Trouble with insert statement for Access database

Copy linkTweet thisAlerts:
Jan 02.2015

I am trying to add a record to an Access table, but it doesn’t seem to be working. I have the following INSERT statement:

INSERT INTO Issues (DateRequested, CustomerID, ComputerID, Issue, ItemsIncl) VALUES (#1/2/2015#, 8, 11, ‘Monitor stuck at 800×600’, ‘Monitor’)

I have other inserts with simply text and integer values that work fine just before this one.

I originally had the DateRequested value in quotes like the strings, but remembered the issue that Access has in making it a date value. But even after making this change, it still doesn’t work. Does anyone else see something that I’m missing?

Chris

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@ginerjmJan 02.2015 — I don't know what the issue is with access but did you try putting quotes around that date 'value'?

Also are you checking for an error code as a result of your call to query?
Copy linkTweet thisAlerts:
@chrscoteauthorJan 02.2015 — I am using a try-catch block when I run the query and I'm not getting any error messages back.

try {

$db->query($sqlIssue);

} catch (Exception $e) {

echo $e->getMessage();

}

I've tried with and without quotes around the date.
Copy linkTweet thisAlerts:
@chrscoteauthorJan 02.2015 — I just reopened the database and tried running the query again. This time, it [B][I]tried[/I][/B] to run the query, but I got the following error:
[code=html]
[B]Microsoft Access can't append all the records in the append query.[/B]
Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
[/code]
It then asks if I want to run the action query anyway with a Yes, No, and Help buttons.

Based on this message, I assume it thinks there is a foreign key violation, but each of the values I'm using for IDs are in their respective tables.

Chris
Copy linkTweet thisAlerts:
@ginerjmJan 02.2015 — I dont' think that points to a FK violation. I think it doesn't like that first date field and won't create another record with a 'missing' date because it already has one perhaps. Check your records. And do some research on how the date value must be formatted to go into an access table. Read your access manual.
×

Success!

Help @chrscote spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.15,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...