?
Solved

Inserting into Oracle DB using WMI/VB

Posted on 2003-11-26
7
Medium Priority
?
868 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
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!

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Suggested Courses
Course of the Month11 days, 22 hours left to enroll

752 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