RUN SQL-SELECT FROM VB-APPLICATION

I would like to run a couple of SQL statements usually executed via Query Analyzer on a monthly bases, but I would like to create a small VB program so it can be run from the desktop.

I know I can create a Job in SQL and have it on the server, but I want to run via a VB apps.

The instruction I would like to put in VB is simple:

      UPDATE tablename
      SET column = expression
      WHERE expression

I don't want the VB program to ask to press a key, I would like to have statements executed when clicking the VB icon from the desktop.

I can connect VB to SQL instance successfully and even relate the table's DataField to VB Fields.

I'm using Visual Basic 6 and SQL 2000 (both with latest SP)
rayluvsAsked:
Who is Participating?
 
frankyteeCommented:
eg code below, since you want to run when your app opens then put this code in the open event of your opening form:
    Dim adoCon As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim sConnect As String
       
    Set adoCon = New ADODB.Connection
    Set adoCmd = New ADODB.Command
     '----------------------------------------------------------------------------------------
    'Open a connection using DSN and odbc
    '----------------------------------------------------------------------------------------
    sConnect = "Data Source='whatever DSN'; User ID ='" & "whateverUser"  & "';Password='" & "whateverPass" & "';"        
    adoCon.ConnectionString = sConnect
    adoCon.Open    
    adoCmd.ActiveConnection = adoCon
    With adoCmd
        '----------------------------------------------------------------------------
        'query on the fly
        .CommandText = " UPDATE tablename SET column = expression WHERE expression" 'whatever
        .CommandType = adCmdText
        .Execute
        '----------------------------------------------------------------------------
    End With  
    Set adoCon = Nothing
0
 
dqmqCommented:
Don't know why you need VB.  Seems you can do that with an SQLCMD shortcut.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rayluvsAuthor Commented:
I went thru the links and is in the level I'm in as to VB/ADO (Beginner)...Some of the download found where useful, but still I couldn't locate a way to run the VB without user-intervention...the explanations and the sample are all havening the user to interact with the application as to clicking butons to Next/Last/First/Save/Edit....etc.

I would like to know how to run the sql statement withing the VB app without user-intervention.  If you have some sample lines?

I understand I can use SQLCMD, but since I getting into VB I would like to do it thru there.


0
 
frankyteeCommented:
in VB, create an ADO command object, define your sql statement of that command object, then execute the command objects statement
0
 
rayluvsAuthor Commented:
Can you give more detail as to how to set these instruction in VB...

I have created the link to the SQL tables (Project->Components->Microsoft's ADO Data Control 6.0)...Also build the Connection string/Data Link Properties and link VB to the table (I did this for the two tables I need), and set the Recordset appropiately...created the Text-Boxes and related each to the tables columns (or fields) DataSource and DataField.

Like I said previously, I created a VB app that can Search with Next/Previous/Last/First also that can Add, Udate and Delet (all within buttons in the VB app).  So if press "First" command button, the VB app will bring to the screen the first record, so on with each command button.  This all works perfectly.

What I need how to execute SQL statements without having to actually press any button in the VB app. I want to have the VB app to run automatically when clicked on to be executed from the desktop.

How can this be done?








0
 
rayluvsAuthor Commented:
kool...let me try it
0
 
rayluvsAuthor Commented:
Question referring to the line,   sConnect = "Data Source='whatever DSN'; ...

I have this in the ADODC Properties "User Connection String":

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CTH;Data Source=XAUX4HEXDEX

What should I copy over to your line?

0
 
rayluvsAuthor Commented:
Ok...I created a ODBC and I place that there it seems to work....
0
 
rayluvsAuthor Commented:
Hey frankytee...worked perfectly...Thanx Lots !!!  

No I'm gonna play with it for while...

Two questions if you can:

   1. Can give some explanation on these lines:

    Set adoCon = New ADODB.Connection
    Set adoCmd = New ADODB.Command
    Set adoCon = Nothing

   2. The SQL statements I'll be running are various, not one as the example given.  In order to
      "execute" each SQL statement, I have to copy the lines "With adoCmd" thru "End With" per
      statement?

Thanx
0
 
frankyteeCommented:
1)
   'set object reference
    Set adoCon = New ADODB.Connection
    Set adoCmd = New ADODB.Command

    'release object from memory
    Set adoCon = Nothing

in fact (if you have time to play around with it) i think you can simplify it further by just using the adoCmd object and set the activeconnection and connection string directly to your string variables without the need to use the adoCon object. but i gave you generic code in case you want to also use an ADO recordset to populate fields etc in the same proc so you can just leave it as it is.

2) use a loop where you redefine your commandtext with whatever sql statements. your sql statements should be defined in an array. eg:
dim sql(1 to 10) as string 'whatever number of elements or you can redim it later
sql(1) = "......"
sql(2) = "......"
etc..

for i = 1 to x 'x = upper bound or number of elements in your array
  '.CommandText = " UPDATE tablename SET column = expression WHERE expression" 'whatever
   .CommandText = sql(i)
  .CommandType = adCmdText
  .Execute
next i
0
 
rayluvsAuthor Commented:
Thanx Lots !!
0
 
frankyteeCommented:
you're welcome, glad i could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.