?
Solved

SQL-INSERT statement

Posted on 2002-07-25
11
Medium Priority
?
278 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 80 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month17 days, 11 hours left to enroll

831 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