INSERT Statement into SQL Server

Posted on 2002-07-15
Last Modified: 2010-05-02
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() & "#)"
Question by:stevensc

Expert Comment

ID: 7155600
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.

Accepted Solution

BlackDiamond earned 300 total points
ID: 7155607
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() & "')"
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7155652
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")

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!


Expert Comment

ID: 7155898
It also could give this error when you trying to insert not acceptable value.For example with wrong datatype or null for requered column.
LVL 49

Expert Comment

ID: 8012509
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

Expert Comment

ID: 8096033
Comment from expert accepted as answer

E-E Admin

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

773 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