Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Inserting into Oracle DB using WMI/VB

Posted on 2003-11-26
7
Medium Priority
?
876 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 1200 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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Suggested Courses

604 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