Solved

Inserting into Oracle DB using WMI/VB

Posted on 2003-11-26
7
822 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now