Solved

Inserting into Oracle DB using WMI/VB

Posted on 2003-11-26
7
855 Views
Last Modified: 2013-12-25
I don't know if this is the best place to post this question...

Hello,

I am trying to insert a row into a Oracle 8.1.7 table using WMI.
The wierd thing is, that the same script will insert into the table from one computer, but I get the following error:

C:\tools\scripts\test.vbs(14, 1) Microsoft OLE DB Provider for ODBC Drivers: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

when I try to insert into the same table using the same script.

I created identical ODBC Datasources on both machines.  here is the script I'm testing with:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory; password=test"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM arch_id_change" , objConnection, _
    adOpenStatic, adLockOptimistic

objRecordset.AddNew
objRecordset("HW_inventory_id") = 2
objRecordset.Update

objRecordset.Close
objConnection.Close
-----------------------------------------------------

I have been able to retrieve data from this table from both computers...but i can't insert...

Thanks
0
Comment
Question by:haachee
[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
  • 3
7 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 9827523
Try to see if this works

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "DSN=Inventory; password=test"

objConnection.Execute "INSERT INTO arch_id_ch(HW_inventory_id) VALUES(2)

objConnection.Close

Leon
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9827536
BWT,  your recordset is Opened with Const adOpenStatic = 3, which means the reccorset is not updatable.  To update switch to Keyset or Dynamic.

Leon
0
 

Author Comment

by:haachee
ID: 9854895
Leon,

Thanks, it almost works...what I actually need to do is insert a value from a variable into the database.

For example one of the value being passed into the database will be generated by an argument being passed to the script:

c:\cscript test.vbs 2

code for test.vbs will have a line lik:

hw_id = wscript.arguments(0)

when I change the insert statement to:

objConnection.Execute "Insert Into arch_id_ch(HW_inventory_id) values (hw_id)"

I get the following error:
O:\tools\scripts\t_db.vbs(9, 1) Microsoft OLE DB Provider for ODBC Drivers: [Oracle][ODBC][Ora]ORA-00984: column not allowed here

So, I tried to change it to
...values (" & hw_id & ")" and I got the same error...

Any ideas?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:haachee
ID: 9854940
Here's the real script I want to implement...again..it works on my development server, but not my production server and both are running Win2K Server SP4

dim obj, objs, buf, RemotePC, RemoteMac
dim regEx, Match, Matches

RemotePC = wscript.arguments(0)
RemoteMac = wscript.arguments(1)

Set regEx = New RegExp
regEx.Pattern = "AccessF"
regEx.IgnoreCase = True
regEx.Global = True

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory; Password=test;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM eccd_load.access_data" , objConnection, _
    adOpenStatic, adLockOptimistic

Set objs = GetObject("WinMgmts://" & RemotePC & "/root/cimv2").InstancesOf("Win32_SoftwareFeature")
for each obj in objs
     if regEx.Test(obj.Name)     then
          objRecordset.AddNew
          objRecordset("MAC_ADDRESS") = RemoteMac
          objRecordset("File_Name") = obj.Name
          objRecordset("Last_Use") = obj.LastUse
          objRecordset("Product_Name") = obj.ProductName
          objRecordset("Install_Date") = obj.InstallDate
          objRecordset("Version") = obj.Version
          objRecordset.Update
     end if
next

objRecordset.Close
objConnection.Close

0
 
LVL 29

Accepted Solution

by:
leonstryker earned 300 total points
ID: 9855259
Try this:

objConnection.Execute "Insert Into arch_id_ch(HW_inventory_id) values ('" & hw_id & "')"

That is the datatype of the HW_inventory_id field?

I would try to do it this way:

Dim obj, objs, buf, RemotePC, RemoteMac
Dim regEx, Match, Matches
Dim strSQL
   
    RemotePC = wscript.arguments(0)
    RemoteMac = wscript.arguments(1)
   
    Set regEx = New RegExp
    regEx.Pattern = "AccessF"
    regEx.IgnoreCase = True
    regEx.Global = True
   
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open "DSN=Inventory; Password=test;"
   
    Set objs = GetObject("WinMgmts://" & RemotePC & "/root/cimv2").InstancesOf("Win32_SoftwareFeature")
    For Each obj In objs
         If regEx.test(obj.Name) Then
            strSQL = "INSERT INTO eccd_load.access_data VALUES('" & RemoteMac & "', '" & _
                        obj.Name & "', '" & obj.LastUse & "', '" & _
                        obj.ProductName & "', '" & obj.InstallDate & "', '" & _
                        obj.Version & "')"
            objConnection.Execute strSQL
         End If
    Next
    objConnection.Close

Leon
0
 

Author Comment

by:haachee
ID: 9855317
Great!  Thanks! That worked!

I'm new to vbs and WMI...I was just using the examples from Microsofts TechNet site...and sure enough i ran into problems using their code...they always gotta have bugs...
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9855325
The thing to remember are the single quotes for the char and varchar datatypes.

Thanks for the grade,

Leon
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
using web browser with BING 40 146
Access Object Property from VBA Module in Excel 2010 2 44
vb6 - Transfer from MSHFlexgrid1 to xls issue 8 63
Excel Automation VBA 19 115
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

738 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