ASP Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsProgrammingASP Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old December 3rd, 2003, 06:21 PM
vyntage vyntage is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2003
Posts: 3 vyntage User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question Visual Basic / SQL help

hello everyone,
I am wondering why I am getting a run time error on the INSERT INTO statement.

Thanks for any and all help!!




PHP Code:
Private Sub Form_Load()

Dim objAccess As Access.Application
Dim cnConnection 
As ADODB.Connection
Set objAccess 
= New Access.Application

objAccess
.Visible True
objAccess
.OpenCurrentDatabase "Path to database.mdb"True
objAccess
.DoCmd.TransferSpreadsheet acImportacSpreadsheetTypeExcel9"tblTemp""Path to excel.xls"False"A1:A10"
Set objAccess Nothing


Set cnConnection 
= New ADODB.Connection
cnConnection
.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Path to database.mdb;"
cnConnection.Open

cnConnection
.Execute "INSERT INTO sensor SELECT F1 FROM tblTemp"

cnConnection.Execute "Drop table tblTemp"
cnConnection.Close
Set cnConnection 
Nothing

End Sub 

Reply With Quote
  #2  
Old December 3rd, 2003, 11:56 PM
numbernine numbernine is offline
Up To His Eyes In Ads
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2002
Location: Chicago
Posts: 160 numbernine User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 m 25 sec
Reputation Power: 16
Maybe try...
"INSERT INTO sensor (F1) SELECT F1 FROM tblTemp"

Reply With Quote
  #3  
Old December 5th, 2003, 01:09 AM
vyntage vyntage is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2003
Posts: 3 vyntage User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
1 more question

Thanks for the help!

I have 1 more question.

When go to insert the date and the old sensor information into a log I get the following Run-time error

Syntax error (missing operator) in query expression '12/5/2003 1:05:43 AM'.

I have all my Fields in both tables set up right. I get this error even when I go to pass a string.



Here is my code.

Dim currTime As Date
currTime = Now

cnConnection.Execute "INSERT INTO LOG VALUES (" & currTime & ", SELECT * FROM Sensors)"


Thank You again for any and all help!!

Reply With Quote
  #4  
Old December 5th, 2003, 09:31 AM
numbernine numbernine is offline
Up To His Eyes In Ads
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2002
Location: Chicago
Posts: 160 numbernine User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 m 25 sec
Reputation Power: 16
In SQL, when you insert records. Your syntax is going to look like this.

INSERT INTO [TableName]
([Field1], [Field2])
VALUES
('value1', 'value2')

If you want to insert from one table to the other, your syntax will look like this

INSERT INTO [TableName]
([Field1], [Field2])
SELECT Field1, Field2 FROM [Table2]

So for your code, you'll want to specify the field name in parentheses after the table name. Also you'll want to be single quotes around the value (currTime)

So...

"INSERT INTO LOG (TimeField) VALUES ('" & currTime & "')"

Reply With Quote
  #5  
Old December 8th, 2003, 12:32 AM
vyntage vyntage is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2003
Posts: 3 vyntage User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
ok, here is my problem now.

I can get the currTime to be entered into the Sensor Table. And I can get the Sensor Numbers and values to be loaded into the sensor table, but I have to perform 2 different SQL statments and it doesn't work right since the date gets added at the end of all the sensor information.

I would like the Sensor table with the fields
(time, sensor_Number, Sensor_Value) to be populated with the data from the currTime as I set up in VB and I would also like it to be populated with the records from a table called tblTemp that has the fields Sensor_Number and Sensor_Value.

I would like each sensor_number to be associated with a time since in my project each Sensor_Number is set up to represent an Office number.. So that Sensor Number 1 may be office # 101 ect..

and when i used this SQL statement it gave me an error

"INSERT INTO Sensors (Sen_Num, value) SELECT Sen_Num, value FROM tblTemp"

so I changed it to this and it worked

"INSERT INTO Sensors SELECT Sen_Num, value FROM tblTemp"

PHP Code:
 So in short I would like to know how to combine the following

"INSERT INTO Sensors  SELECT  Sen_Num, value FROM tblTemp"

"INSERT INTO Sensors (currTime)  VALUES ('" 
setTime "')"

so i would like to  know how to combine the two  
I
've tried quite a few different things such as...

"INSERT INTO Sensors  VALUES ('" & setTime & "', SELECT Sen_Num, value FROM tblTemp)" 


Thank you again for your help!

Reply With Quote
  #6  
Old May 11th, 2006, 12:45 AM
mail2radhikaraj mail2radhikaraj is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2006
Posts: 4 mail2radhikaraj User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 12 m 4 sec
Reputation Power: 0
Smile try this out

Quote:
Originally Posted by vyntage
ok, here is my problem now.

I can get the currTime to be entered into the Sensor Table. And I can get the Sensor Numbers and values to be loaded into the sensor table, but I have to perform 2 different SQL statments and it doesn't work right since the date gets added at the end of all the sensor information.

I would like the Sensor table with the fields
(time, sensor_Number, Sensor_Value) to be populated with the data from the currTime as I set up in VB and I would also like it to be populated with the records from a table called tblTemp that has the fields Sensor_Number and Sensor_Value.

I would like each sensor_number to be associated with a time since in my project each Sensor_Number is set up to represent an Office number.. So that Sensor Number 1 may be office # 101 ect..

and when i used this SQL statement it gave me an error

"INSERT INTO Sensors (Sen_Num, value) SELECT Sen_Num, value FROM tblTemp"

so I changed it to this and it worked

"INSERT INTO Sensors SELECT Sen_Num, value FROM tblTemp"

PHP Code:
 So in short I would like to know how to combine the following

"INSERT INTO Sensors  SELECT  Sen_Num, value FROM tblTemp"

"INSERT INTO Sensors (currTime)  VALUES ('" 
setTime "')"

so i would like to  know how to combine the two  
I
've tried quite a few different things such as...

"INSERT INTO Sensors  VALUES ('" & setTime & "', SELECT Sen_Num, value FROM tblTemp)" 


Thank you again for your help!






hey buddy , try this out..................

INSERT INTO Sensors
SELECT '" & setTime & "' ,Sen_Num, value FROM tblTemp"


i guess it shud work

lemme know,

regrds,

radz

Reply With Quote
  #7  
Old August 23rd, 2007, 09:09 AM
guitar_god_man guitar_god_man is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2007
Posts: 2 guitar_god_man User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 m 33 sec
Reputation Power: 0
Sql query update from Excel to ODBC driver

I am having some serious problems updating a record using excel visual basic to an odbc. I can run select queries with no problems but the below codes versions no will work



Version 1

adoConnection.Execute "UPDATE ARRAY_BAC_SCH_Schedule SET SCHEDULE_TIME = 8/16/2007 7:0:0 WHERE DEV_ID = 4500"


Version 2

all variables are dim as string

databaseQ = "ARRAY_BAC_SCH_Schedule"
tableQ = "SCHEDULE_TIME"
Valueupdate = "8/23/2007 7:0:0"
TableID = "DEV_ID"
TableDay = "DAY"
TableIDX = "IDX"
TableObjRef = "ObjRef"

TableIDQ = "4500"
TableDayQ = "1"
TableIDXQ = "2"
TableObjRefQ = "4500.sch1"


Updatequery = "UPDATE" & databaseQ & "SET" & tableQ & "=" & Valueupdate & "WHERE" & TableID & "=" & TableIDQ

adoConnection.Execute Updatequery


Version 3

sql statment looking like this


updatequery = "UPDATE" ARRAY_BAC_SCH_Schedule "SET" ARRAY_BAC_SCH_Schedule.SCHEDULE_TIME "=" #8/16/2007 7:0:0# "WHERE" (((ARRAY_BAC_SCH_Schedule.DEV_ID)=1104) "AND" ((ARRAY_BAC_SCH_Schedule.DAY)=5) "AND" ((ARRAY_BAC_SCH_Schedule.IDX)=2));


nothing appears to work.. I've even tried a select query and a record set update / edit which appears not to work.. I can edit the exact same record using access or excel's query tool but through visual basic it will not work properly. I have not problems connecting to the odbc to do any other funtions including select and insert ...but not matter what i try update will not work.. any help would save my day. I've spend 4 hours on this already.

thanks

Mike

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsProgrammingASP Development > Visual Basic / SQL help


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap