INSERT statement error

  • Thread starter Thread starter Sylvain Lafontaine
  • Start date Start date
S

Sylvain Lafontaine

strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES (" & strJcn & "," & strParcTag & ")"

If one of these variable contains a string value, for example strParcTage,
then delimite it with single quotes and take the precaution of replacing all
encased single quotes with two single quotes:


strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES (" & strJcn & ",'" & Replace (strParcTag, "'", "''") & "')"
 
I'm trying to add a record to a table and am getting Error 8152:
String or binary data would be truncated.

Here is my statement written with VBA in an Access project.
Both datatypes are nvarchar

Private Sub cmdCreateJob_Click()

Dim strJcn As String
Dim strParcTag As String
Dim strSql As String

strJcn = 999999
strParcTag = Me.cboEquipID.Column(0)

strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES ('strJcn','strParcTag')"

DoCmd.RunSQL strSql

End Sub

Can anyone see what I'm doing wrong? Thanks.
 
solved it myself. Here is the change that workd :

strJcn = "'" & 999999 & "'"
strParcTag = "'" & Me.cboEquipID.Column(0) & "'"

strSQL = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES (" & strJcn & "," & strParcTag & ")"
 
Sylvan, first thanks for answering, you have solved a lot of problems for me
in the past. I don't understand what the Replace function accomplishes in
your example. As you can see from my re-post, I got it to work on my own but
I would like to have a better understanding of why you did what you did.
Knowing where to put single and double quotes has always kicked my ass. I
looked up the replace function in BOL and understand how it works, I just
don't see why it is needed.

Sylvain said:
strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES (" & strJcn & "," & strParcTag & ")"

If one of these variable contains a string value, for example strParcTage,
then delimite it with single quotes and take the precaution of replacing all
encased single quotes with two single quotes:

strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES (" & strJcn & ",'" & Replace (strParcTag, "'", "''") & "')"
I'm trying to add a record to a table and am getting Error 8152:
String or binary data would be truncated.
Click to expand...
[quoted text clipped - 19 lines]
Can anyone see what I'm doing wrong? Thanks.
Click to expand...
Click to expand...
 
Ok I got everything to work up until I add the datOpenDate field. It only
puts 12:00 AM in the record when I put 1 mar 06 in the form. Here is what I
have so far:
I have tried formatting the datefield but no go. Thanks,
strJcn = "'" & NextJCN() & "'"
strParcTag = "'" & Me.cboEquipID.Column(0) & "'"
strPerfWc = "'" & Me.cboPWC & "'"
strRptby = "'" & Me.cboReportedBy.Column(0) & "'"
strDisc = "'" & Me.txtDisc & "'"
datOpenDate = “’” & Me.txtOpenDate & “’”

strSQL = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY,
ESR_OPEN_DATE)" _
& " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "" _
& "," & strRptby & ", " & datOpenDate & " )"

DoCmd.RunSQL strSQL

Sylvain said:
strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES (" & strJcn & "," & strParcTag & ")"

If one of these variable contains a string value, for example strParcTage,
then delimite it with single quotes and take the precaution of replacing all
encased single quotes with two single quotes:

strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES (" & strJcn & ",'" & Replace (strParcTag, "'", "''") & "')"
I'm trying to add a record to a table and am getting Error 8152:
String or binary data would be truncated.
Click to expand...
[quoted text clipped - 19 lines]
Can anyone see what I'm doing wrong? Thanks.
Click to expand...
Click to expand...
 
Back
Top