Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Inserting Into a Table Problems using SQL

Posted on 2000-04-13
17
Medium Priority
?
231 Views
Last Modified: 2010-05-02
"INSERT into InterfaceCondition(CustomerID,`Customer Name`,`Group ID`," & _
       "`Class ID`,`Name Of Template`,`Template Name`,`Interface Number`," & _
       "`Interface Description`,`Message Group`,`Field Seperators`,`Case Sensitive Check`,`Duplicate Message Supression`," & _
       "`Supress Messages Matching Condition`,`Supress Identical Messages`,`Supress Time Interval`," & _
       "`Accept Messages After Every`,`Counter Threshold`,`Counter Reset After`,`Agent MSI Divert Messages`," & _
       "`Server MSI Divert Messages`,`Agent MSI Copy Messages`,`Server MSI Copy Messages`,`Immediate Local Automatic Actions`," & _
       "`Service Name`,`Instruction Text`,Instruction) " & _
       " values('" & _
       Text1.Text & "', '" & _
       Text2.Text & "', '" & _
       Text3.Text & "', '" & _
       Text4.Text & "', '" & _
       Text5.Text & "', '" & _
       Text5.Text & "', '" & _
       Text7.Text & "', '" & _
       Text9.Text & "', '" & _
       Text10.Text & "', '" & _
       Text11.Text & "', '" & _
       Combo1.Text & "', '" & _
       Combo2.Text & "', '" & _
       Combo3.Text & "', '"


  sql2 = Combo4.Text & "', '" & _
       Text12.Text & "', '" & _
       Text14.Text & "', '" & _
       Text13.Text & "', '" & _
       Text15.Text & "', '" & _
       Combo5.Text & "', '" & _
       Combo6.Text & "', '" & _
       Combo7.Text & "', '" & _
       Combo8.Text & "', '" & _
       Combo9.Text & "', '" & _
       Text16.Text & "', '" & _
       Combo10.Text & "', '" & _
       Text17.Text & "')"
sql3 = sql & sql2
'On Error GoTo errorstop
 dbaddress.Execute sql3


I had to use two variables because the line was to long!!! The problem i am getting is that it is saying `Name Of Template` does not exist in the table and i have checked it and it does !!!! I do not know whether i have done something wrong but i have checked over and over and have a complete mind blank !!!
0
Comment
Question by:Slightly_Imperfect
[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
  • 8
  • 5
  • 2
  • +1
17 Comments
 
LVL 1

Expert Comment

by:detiege
ID: 2711223
Have you tried to make a Debug.Print(sql) to see what are in this buffer.

You can try to use this buffer into an  SQL program.

Execute it and see the result or the error message.

It's often more explicit !
0
 

Expert Comment

by:andcu
ID: 2711286
Unless I have miscounted, I believe that you are inserting more values than you have declared fields
0
 

Expert Comment

by:andcu
ID: 2711288
Also, I really wouldn't have fields with spaces
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Slightly_Imperfect
ID: 2711316
I make it 26 a piece !!! i am inserting in other tables with fields with spaces using either [field name] `field name` seems to work me myself i would not have spaces but it is somebody elses datatbase i am populating !!!!! i have tryed debug and i get

INSERT into InterfaceCondition([CustomerID],[Customer Name],[Group ID],[Class ID],[Name Of Template],[Template Name],[Interface Number],[Interface Description],[Message Group],[Field Seperators],[Case Sensitive Check],[Duplicate Message Supression],[Supress Messages Matching Condition],[Supress Identical Messages],[Supress Time Interval],[Accept Messages After Every],[Counter Threshold],[Counter Reset After],[Agent MSI Divert Messages],[Server MSI Divert Messages],[Agent MSI Copy Messages],[Server MSI Copy Messages],[Immediate Local Automatic Actions],[Service Name],[Instruction Text],[Instruction])  values('EE0001', 'EE0001', 'AP', 'OPC', 'ClearView EE AP ITO opcmsg(1/3)', 'ClearView EE AP ITO opcmsg(1/3)', '1212', 'Default Interception of messages', '1212', '1212', 'YES', 'YES', 'YES', 'YES', '121', '12', '1212', '122', 'YES', 'YES', 'YES', 'YES', 'YES', '2121', 'YES', '121212')

i have replaced ` ` with [ ]  just to see if that was the problem but it still falls over on [Name of Template] even when i test and join the spaces up it still fails !!!!!  

cheers guys
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2711332
If you use SQL 6.5, you may check on case sensitivity
Anyway, i would prefer to use [Name of Template] to the use of ´Name of Template´
0
 
LVL 1

Expert Comment

by:detiege
ID: 2711348
Have you tried this buffer into an other SQL-program ?

Have you had the self error ?
0
 

Author Comment

by:Slightly_Imperfect
ID: 2711381
The self error ?  

i am gonna give it a try this minute in sql builder see wheither it works !!!!

fingers crossed !!!

:^)
0
 

Author Comment

by:Slightly_Imperfect
ID: 2711544
Adjusted points from 10 to 30
0
 

Author Comment

by:Slightly_Imperfect
ID: 2711545
This did not work !!!! it still cannot find the field name within the table and i have checked umpteen times to see if i have spelt it correctly !!!! i do not kno what is happening !!



Please Help I am ripping my hair out !!! i tryed i query in SQL Builer just a select one and it got the field no problems this is what i cannot understand !!!!!

To the Answerer i will give the 30 points and my gold filling :^)

mike
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2711584
can you provide us the output of
EXEC sp_help InterfaceCondition
(SQL 6.5 / SQL 7)
0
 

Author Comment

by:Slightly_Imperfect
ID: 2711646
I am using access the database !!!!!

sorry did i not mention this !!!!

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2711749
Slightly imperfect section you choose then (there is an Access section in EE).

Which version of access then to be complete?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2711758
Maybe you show us the first rows of a SELECT * FROM InterfaceCondition
0
 

Author Comment

by:Slightly_Imperfect
ID: 2711810
i am using vb to populate the access database !!!

this is the select statement from SQL Builder!!

ps i have taken out the offending field and it is failing on interface description now !!!! so i have changed it to interfacedesc still dont work !!!

just incase you came back to me saying this was wrong !!! :^)

SELECT CustomerID, `Customer Name`, `Group ID`, `Class ID`,
    `Template Name`, `Interface Number`, `Interface Name`,
    InterfaceDesc, `Message Group`, `Field Seperators`,
    `Case Sensitive Check`, `Duplicate Message Suppression`,
    `Suppress Messages Matching Condition`,
    `Suppress Identical Messages`, `Suppression Time Interval`,
    `Accept Messages after Every`, `Counter Threshold`,
    `Counter Reset After`, `Agent MSI Divert Messages`,
    `Server MSI Divert Messages`, `Agent MSI Copy Messages`,
    `Server MSI Copy Messages`,
    `Immediate Local Automatic Actions`, `Service Name`,
    `Instruction Text`, Instruction
FROM InterfaceTemplate


this works strange huh !!!!
0
 

Author Comment

by:Slightly_Imperfect
ID: 2711831
This is what the code looks like now !!

sql = "INSERT into InterfaceCondition([CustomerID],[Customer Name],[Group ID]," & _
       "[Class ID],[Template Name],[Interface Number]," & _
       "[InterfaceDesc],[Message Group],[Field Seperators],[Case Sensitive Check],[Duplicate Message Supression]," & _
       "[Supress Messages Matching Condition],[Supress Identical Messages],[Supress Time Interval]," & _
       "[Accept Messages After Every],[Counter Threshold],[Counter Reset After],[Agent MSI Divert Messages]," & _
       "[Server MSI Divert Messages],[Agent MSI Copy Messages],[Server MSI Copy Messages],[Immediate Local Automatic Actions]," & _
       "[Service Name],[Instruction Text],[Instruction]) " & _
       " values('" & _
       Text1.Text & "', '" & _
       Text2.Text & "', '" & _
       Text3.Text & "', '" & _
       Text4.Text & "', '" & _
       Text5.Text & "', '" & _
       Text7.Text & "', '" & _
       Text9.Text & "', '" & _
       Text10.Text & "', '" & _
       Text11.Text & "', '" & _
       Combo1.Text & "', '" & _
       Combo2.Text & "', '" & _
       Combo3.Text & "', '"


  sql2 = Combo4.Text & "', '" & _
       Text12.Text & "', '" & _
       Text14.Text & "', '" & _
       Text13.Text & "', '" & _
       Text15.Text & "', '" & _
       Combo5.Text & "', '" & _
       Combo6.Text & "', '" & _
       Combo7.Text & "', '" & _
       Combo8.Text & "', '" & _
       Combo9.Text & "', '" & _
       Text16.Text & "', '" & _
       Combo10.Text & "', '" & _
       Text17.Text & "')"
sql3 = sql & sql2
'On Error GoTo errorstop
 dbaddress.Execute sql3


this is the sql statment it produces

INSERT into InterfaceCondition([CustomerID],[Customer Name],[Group ID],[Class ID],[Template Name],[Interface Number],[InterfaceDesc],[Message Group],[Field Seperators],[Case Sensitive Check],[Duplicate Message Supression],[Supress Messages Matching Condition],[Supress Identical Messages],[Supress Time Interval],[Accept Messages After Every],[Counter Threshold],[Counter Reset After],[Agent MSI Divert Messages],[Server MSI Divert Messages],[Agent MSI Copy Messages],[Server MSI Copy Messages],[Immediate Local Automatic Actions],[Service Name],[Instruction Text],[Instruction])  values('EE0001', 'EE0001', 'AP', 'OPC', 'ClearView EE AP ITO opcmsg(1/3)', '', 'Default Interception of messages', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')


did not put any data in this time but you get the drift huh !!!


cheers guys

8^)

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 120 total points
ID: 2711849
Your table is called InterfaceTemplate or InterfaceCondition?????
0
 

Author Comment

by:Slightly_Imperfect
ID: 2711965
Cheers for that i am off to the opticians tonight. That is what it was a wrong table name duh !!!!!!!

cheers for that angel !!!!

and where do you want me to send this gold filling too

8^)

The little probs are always the worst !!!! cannot belive i missed that !!!!


I HANG MY HEAD IN SHAME :(
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

604 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