• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

INSERT Statement into SQL Server

I am trying to run this statement to insert a record into SQL Server but I am getting an error: Record Update Error on #. Does anyone know how to correct this SQL statement and get rid of the error?

SQL = "INSERT INTO [Chart Location] (MRN, FName, LName, DOB, Area, AttPhy, Visit, [Date], UserID, [Timestamp]) values('" + sMRN + "', '" + sFirst + "', '" + sLast + "', #" + sBirthDate + "#, '" + sArea + "', '" + sAttPhy + "', '" + sVisit + "', #" + sDate + "#, 'HL7 Sock', #" & Now() & "#)"
0
stevensc
Asked:
stevensc
1 Solution
 
BlackDiamondCommented:
The # signs are an MS Access'ism.  You should not need them when making a call directly to SQL server.  Try putting the dates inside single ticks like your other values.  SQL server will convert the strings to datetime values before it does the insert.
0
 
BlackDiamondCommented:
Give it a try this way

SQL = "INSERT INTO [Chart Location] (MRN, FName, LName, DOB, Area, AttPhy, Visit, [Date], UserID, [Timestamp]) values('" + sMRN + "', '" + sFirst + "', '" + sLast + "', '" + sBirthDate + "', '" + sArea + "', '" + sAttPhy + "', '" + sVisit + "', '" + sDate + "', 'HL7 Sock', '" & Now() & "')"
0
 
Anthony PerkinsCommented:
You also may have better luck with the date columns if you use a yyyy-mm-dd format, as in:
Format$(Now(), "YYYY-MM-DD")

Anthony
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
soferstamCommented:
It also could give this error when you trying to insert not acceptable value.For example with wrong datatype or null for requered column.
0
 
DanRollinsCommented:
Hi stevensc,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept BlackDiamond's comment(s) as an answer.

stevensc, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
Computer101Commented:
Comment from expert accepted as answer

Computer101
E-E Admin
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now