Solved

vbscript

Posted on 2008-10-14
31
899 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
[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
  • 15
  • 8
  • 4
  • +2
31 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22719972
Looks ok to me.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22720764
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
ID: 22722791
     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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22722845
Which line does it say object is required?
0
 

Author Comment

by:VBdotnet2005
ID: 22722960

this one

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

Author Comment

by:VBdotnet2005
ID: 22722977

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
ID: 22722983
You first need to dim it. like
Dim Conn
Dim RS
etc
0
 

Author Comment

by:VBdotnet2005
ID: 22723068
same error : "object required"
code 8000a01a8
0
 

Author Comment

by:VBdotnet2005
ID: 22723115
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
ID: 22723257
Are you running it in ASP?
0
 

Author Comment

by:VBdotnet2005
ID: 22723467
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
ID: 22724008
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
ID: 22724227
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
ID: 22724339
set conn=CreateObject("ADODB.Connection")          

doesn't work?
0
 

Author Comment

by:VBdotnet2005
ID: 22724361
No, it does not.

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22724378
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
ID: 22724419
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
ID: 22724440
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22724523
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
ID: 22724545

also, does the table have a primary key?

yes
0
 

Author Comment

by:VBdotnet2005
ID: 22724563
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
ID: 22724621
what is cn?
0
 
LVL 143

Expert Comment

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

Expert Comment

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

Author Comment

by:VBdotnet2005
ID: 22724761
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 143

Accepted Solution

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

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22725015
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
ID: 22725063
line 14
char 1
object variable not set
code 800a0058
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22725082
what IS line 14, now?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22725099
btw:
rs = nothing
should be:

set rs = nothing
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 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