Solved

vbscript

Posted on 2008-10-14
31
889 Views
Last Modified: 2013-12-25
Is this a correct way to add data to MQ-SQL 2000 in VBscript? I know to to do it in .net, wondering how it is done in vbscript.


set conn=Server.CreateObject("ADODB.Connection")  
conn.Open "myconstring"
set RS = server.createObject("ADODB.recordset")
rs.Open("select * from test", cn)  
 

   RS.addnew
   rs.Fields("timex").Value = now
   rs.Fields("userx").Value = objEventObject.TargetInstance.Name
   rs.Fields("loginx").Value = Started
      
   RS.update  
0
Comment
Question by:VBdotnet2005
  • 15
  • 8
  • 4
  • +2
31 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Looks ok to me.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
there is 1 important change:

rs.Open("select * from test", cn)  

should be:

rs.Open("select * from test where 1=0 ", cn)  

otherwise, you might get the whole table data fetched to the client, just to add 1 row...
0
 

Author Comment

by:VBdotnet2005
Comment Utility
     This is a VBscript I have to run on Windows XP. It keeps saying line # "object is required"
            

            set conn=Server.CreateObject("ADODB.Connection")  
            conn.Open " Data Source=leia;Initial Catalog=myDB;Persist Security Info=True;User ID=myusername;Password=mypassword"
            set RS = server.createObject("ADODB.recordset")
            rs.Open "select * from test where 1=0 ", cn  
 
               RS.addnew
               rs.Fields("date_time").Value = now
               rs.Fields("comp_name").Value = "mycomputername"
               rs.Fields("Log_test").Value = "Started"
            rs.Fields("user_test").Value = "myusername"
            
      
               RS.update  
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Which line does it say object is required?
0
 

Author Comment

by:VBdotnet2005
Comment Utility

this one

 set conn=Server.CreateObject("ADODB.Connection")  
0
 

Author Comment

by:VBdotnet2005
Comment Utility

could also be ?

 conn.Open " Data Source=test1;Initial Catalog=myDB;Persist Security Info=True;User ID=myusername;Password=mypassword"

I can only open it in notepad
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
You first need to dim it. like
Dim Conn
Dim RS
etc
0
 

Author Comment

by:VBdotnet2005
Comment Utility
same error : "object required"
code 8000a01a8
0
 

Author Comment

by:VBdotnet2005
Comment Utility
dim conn
dim rs
set conn=Server.CreateObject("ADODB.Connection")             
conn.Open "Data Source=myserver;Initial Catalog=mydb;Persist Security Info=True;User ID=myusername;Password=mypassword"
set RS = server.createObject("ADODB.recordset")
rs.Open "select * from mytable where 1=0 ", cn  
 
RS.addnew
rs.Fields("date_time").Value = now
rs.Fields("comp_name").Value = "mycomputername"
rs.Fields("Log_on").Value = "Started"
rs.Fields("user_name").Value = "myusername"
RS.update  
rs = nothing







Here is the error


line      3
char      1
error     object required :'Sever'
code      800a01A8
source Microsoft VBscript runtime error
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Are you running it in ASP?
0
 

Author Comment

by:VBdotnet2005
Comment Utility
Are you running it in ASP?

No, I have this vbscript on my Desktop.  test.vbs. When I doubled click it, I got the error above.
0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
VbScript doesn't user Server.CreateObject() but only CreateObject()
Check this tutorial that maybe can help you
http://www.w3schools.com/vbscript/func_createobject.asp
0
 

Author Comment

by:VBdotnet2005
Comment Utility
ok. I removed "Sever"

error : Microsoft ODBC Driver Manager Data source name not found and not default driver specified
code 80004005
0
 
LVL 67

Expert Comment

by:sirbounty
Comment Utility
set conn=CreateObject("ADODB.Connection")          

doesn't work?
0
 

Author Comment

by:VBdotnet2005
Comment Utility
No, it does not.

error : Microsoft ODBC Driver Manager Data source name not found and not default driver specified
code 80004005
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
the connection string is incomplete:
conn.Open "Provider=SqlOledb;Server=myserver;Initial Catalog=mydb;Persist Security Info=True;User ID=myusername;Password=mypassword"

Open in new window

0
 

Author Comment

by:VBdotnet2005
Comment Utility
error : arguments are of the wrong type, are out of acceptable range, or are in conflict with one another
source  ADODB.recordset
0
 

Author Comment

by:VBdotnet2005
Comment Utility
dim conn
dim rs
set conn=Server.CreateObject("ADODB.Connection")            
conn.Open "Provider=SqlOledb;Server=myserver;Initial Catalog=mydb;Persist Security Info=True;User ID=myusername;Password=mypassword"
set RS = server.createObject("ADODB.recordset")
rs.Open "select * from mytable where 1=0 ", cn  
 
RS.addnew
rs.Fields("date_time").Value = now
rs.Fields("comp_name").Value = "mycomputername"
rs.Fields("Log_on").Value = "Started"
rs.Fields("user_name").Value = "myusername"
RS.update  
rs = nothing
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
what about this:

also, does the table have a primary key?
rs.Open "select * from mytable where 1=0 ", cn , 1

Open in new window

0
 

Author Comment

by:VBdotnet2005
Comment Utility

also, does the table have a primary key?

yes
0
 

Author Comment

by:VBdotnet2005
Comment Utility
error : arguments are of the wrong type, are out of acceptable range, or are in conflict with one another
source  ADODB.recordset
0
 

Author Comment

by:VBdotnet2005
Comment Utility
what is cn?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
good catch. it should actually be conn (connection object)
0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
>> No, it does not
It works fine for me!
 
0
 

Author Comment

by:VBdotnet2005
Comment Utility
ok. I changed cn to conn, but

error: current recordset does not support updating. This may be a limitation of the provider, or the selected locktype.
code   800A0CB3
SOURCE : ADODB.recordset
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
we are coming closer:
rs.Open "select * from mytable where 1=0 ", conn , 1, 3

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
note that if you wanted to use the adodb constants (adOpenkeyset, adLockOptimistic) instead, you would either include the definitions:
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=122

or better, define this METADATA setting in the html body:
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=202
0
 

Author Comment

by:VBdotnet2005
Comment Utility
line 14
char 1
object variable not set
code 800a0058
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
what IS line 14, now?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
btw:
rs = nothing
should be:

set rs = nothing
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

16 Experts available now in Live!

Get 1:1 Help Now