Solved

SQL-INSERT statement

Posted on 2002-07-25
11
275 Views
Last Modified: 2010-05-02
Hello...can anyone help me write this sql statement in VB?

INSERT INTO SECTOR[SECTOR ID]
VALUES(text1.text)

--------------------------------
TABLE NAME = SECTOR
FIELD NAME = SECTOR ID
VALUE = GET FROM TEXT3
0
Comment
Question by:aidanie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7179088
try it this way:

"INSERT INTO SECTOR[SECTOR ID] VALUES(" & text1.text & ")"

 as a string variable

Post the Code that you are trying to use.
0
 

Expert Comment

by:andyleece
ID: 7179176
Arthur_Wood, shouldn't the field name be in () rather than [] bracket? i'm not sure abt that but i use () all along. Is [] also acceptable? and a string variable should be inside ''.

INSERT INTO SECTOR (SECTOR ID) VALUES('" & text1.text & "')

aidanie, ur values should get from text1 or text3?
0
 

Author Comment

by:aidanie
ID: 7179238
andyleece,

value get from text1...

i try this code...but the value is not inserted into the table..
0
Industry Leaders: 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!

 

Expert Comment

by:andyleece
ID: 7179271
Arthur_Wood, shouldn't the field name be in () rather than [] bracket? i'm not sure abt that but i use () all along. Is [] also acceptable? and a string variable should be inside ''.

INSERT INTO SECTOR (SECTOR ID) VALUES('" & text1.text & "')

aidanie, ur values should get from text1 or text3?
0
 

Expert Comment

by:andyleece
ID: 7179279
sorry, refresh made my comment being post again, ignore it.

anyway, did u execute the query?

SQL = INSERT INTO SECTOR (SECTOR ID) VALUES('" & text1.text & "')
db.execute(SQL)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7179314
Since the column name "SECTOR ID" has a space in it it needs to be in square brackets, as follows:
"INSERT INTO SECTOR ([SECTOR ID]) VALUES('" & text1.text & "')"

Anthony
0
 

Accepted Solution

by:
Divvy earned 20 total points
ID: 7179454
TABLE NAME = SECTOR
FIELD NAME = SECTOR_ID
VALUE = GET FROM TEXT3

Well, I hope it should be no problem then:

sql = "insert SECTOR(SECTOR_ID)values(" & _
       sqlValue(text3.Text) & ")"


0
 

Expert Comment

by:Divvy
ID: 7179458
And Then, Use This Function:

Public Function sqlValue(ParamArray F() As Variant) As String
  Dim t As String, i As Byte
  Dim s As String
  t = ""
  For i = 0 To UBound(F)
         t = t & "'" & Replace(F(i), "'", "''") & "'"
         't = t & "'" & f(i) & "'"
      If i <> UBound(F) Then
         t = t + ","
      End If
  Next i
  sqlValue = t
End Function

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7180246
Divvy,

I suspect you hit the answer button by mistake.  If not please refrain from doing so, you have been here long enough to know better.

Thanks,
Anthony
0
 

Expert Comment

by:andyleece
ID: 7184185
aidanie, try acperkins's comment, put the field name inside [] bracket.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7186980
sorry, but yes, the [Sector ID] shoult also be encosed in (...).  The [...] are REQUIRED because of the embedded blank in the name of the field...Access DOES NOT LIKE BLANKS in field names, when used elsewhere (such as in SQL statements.

so the line should have read:

INSERT INTO SECTOR ([SECTOR ID]) VALUES('" & text1.text & "')
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

718 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