Slightly_Imperfect
asked on
Inserting Into a Table Problems using SQL
"INSERT into InterfaceCondition(Custome rID,`Custo mer 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 !!!
"`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 !!!
Unless I have miscounted, I believe that you are inserting more values than you have declared fields
Also, I really wouldn't have fields with spaces
ASKER
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([Custom erID],[Cus tomer 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
INSERT into InterfaceCondition([Custom
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
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´
Anyway, i would prefer to use [Name of Template] to the use of ´Name of Template´
Have you tried this buffer into an other SQL-program ?
Have you had the self error ?
Have you had the self error ?
ASKER
The self error ?
i am gonna give it a try this minute in sql builder see wheither it works !!!!
fingers crossed !!!
:^)
i am gonna give it a try this minute in sql builder see wheither it works !!!!
fingers crossed !!!
:^)
ASKER
Adjusted points from 10 to 30
ASKER
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
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
can you provide us the output of
EXEC sp_help InterfaceCondition
(SQL 6.5 / SQL 7)
EXEC sp_help InterfaceCondition
(SQL 6.5 / SQL 7)
ASKER
I am using access the database !!!!!
sorry did i not mention this !!!!
sorry did i not mention this !!!!
Slightly imperfect section you choose then (there is an Access section in EE).
Which version of access then to be complete?
Which version of access then to be complete?
Maybe you show us the first rows of a SELECT * FROM InterfaceCondition
ASKER
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 !!!!
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 !!!!
ASKER
This is what the code looks like now !!
sql = "INSERT into InterfaceCondition([Custom erID],[Cus tomer 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([Custom erID],[Cus tomer Name],[Group ID],[Class ID],[Template Name],[Interface Number],[InterfaceDesc],[M essage 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^)
sql = "INSERT into InterfaceCondition([Custom
"[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([Custom
did not put any data in this time but you get the drift huh !!!
cheers guys
8^)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :(
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 :(
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 !