Solved

Inserting into Oracle DB using WMI/VB

Posted on 2003-11-26
7
848 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
  • 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
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.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

839 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