By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Error in SQL INSERT INTO statement.

P: 23
Table name:All_Table
I have a string value in string:strdata
A column in Table:login
How can i insert the string value in column"login"?
I tried this following code,but give syntax error msg.
Expand|Select|Wrap|Line Numbers
  1. strIns = "INSERT INTO All_Table
  2. (login) VALUES " & strdata "
Apr 10 '12 #1

✓ answered by NeoPa

In VBA that would be something like :
Expand|Select|Wrap|Line Numbers
  1. strVar = "INSERT INTO [All_Table] ([Login]) " & _
  2.          "VALUES ('" & strSomeString & "')"

Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,357
The correct syntax for an insert statement is
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tableName (fieldName)
  2. VALUES ('field value')
Apr 10 '12 #2

P: 23
Thank you.
When my field values are in string then what will be the syntax.
Apr 10 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
That is the syntax for a string.
Apr 10 '12 #4

NeoPa
Expert Mod 15k+
P: 31,419
In VBA that would be something like :
Expand|Select|Wrap|Line Numbers
  1. strVar = "INSERT INTO [All_Table] ([Login]) " & _
  2.          "VALUES ('" & strSomeString & "')"
Apr 11 '12 #5

100+
P: 759
Declare a Public variable strData in a Public module (Of course you must remove the declaration from your actual module if it is not a public one)
In the same module design a function fGetData()

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Public strData As String
  3.  
  4. Public fGetData() As String
  5.     fGetData = strData
  6. End Function

Then use Rabbit's approach:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tableName (fieldName)
  2. VALUES (fGetData())
Apr 11 '12 #6

NeoPa
Expert Mod 15k+
P: 31,419
I'm sorry to say Mihail, that while that approach may well work functionally, it is generally considered a clumsy one. Instead of tidying the design, it spreads out where the various elements can be found. Not an approach to recommend :-(

Furthermore, and possibly even more important a point, it is introducing a potentially considerable delay into the query. SQL queries are optimised to the data. If a VBA function is introduced into the mix in the WHERE clause then each record of input must be extracted (thereby bypassing most of the said optimisations) and processed through VBA code which is, at least partially, interpreted. Relatively speaking, this is like taking a bus to win a F1 Grand Prix. Far better to work out the correct SQL first in VBA, in a single process, then execute it at top speed.
Apr 11 '12 #7

100+
P: 759
Good to know, NeoPa.
I provide a solution; I don't say that is the best one :)
Apr 11 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.