SQL INSERT

[ydbc]

Hi,

Please help.

I am attemping to INSERT INTO a simple table. All I want to do is INSERT
INTO a text field. This is the syntax I'm using:

Dim NewCol As String
NewCol = Me.txtNewColName
DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) VALUES (" &
NewCol & " )"

I keep getting "Enter parameter value". I'm really stuck.

Thanks in anticipation.

 

[John Spencer]

You are missing the text delimiters.

DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) VALUES (""" &
NewCol & """ )"

OR

DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) VALUES ('" &
NewCol & "' )"

OR


DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) VALUES (" &
Chr(34) & NewCol & Chr(34) & ")"
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Stefan Hoffmann]

hi,

I am attemping to INSERT INTO a simple table. All I want to do is INSERT
INTO a text field. This is the syntax I'm using:

Dim NewCol As String
NewCol = Me.txtNewColName
DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) VALUES ("&
NewCol& " )"

Rewrite it:

Dim sql As String

sql = "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) " & _
"VALUES (" & txtNewColName.Value & " )"
MsgBox sql
DoCmd.RunSQL sql

This will show you your error.

I keep getting "Enter parameter value". I'm really stuck.

As your value is a string, you need to enclose it into quotation marks.

E.g.

Public Function SqlQuote(AValue As Variant, _
Optional ADelimiter As String = "'") As String

If IsNull(AValue) Then
SqlQuote = "NULL"
Else
SqlQuote = ADelimiter & _
Replace(AValue, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter
End If

End Function

use it as

sql = "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) " & _
"VALUES (" & SqlQuote(txtNewColName.Value) & " )"


mfG
--> stefan <--

 

[Sparker]

'Surround the string value with single quotes just inside the double quotes
like this:

DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) " & _
"VALUES ('" & Me.txtNewColName & "');"