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
Solved

Run Sql command in VB

Posted on 2003-10-21
17
584 Views
Last Modified: 2007-12-19
im needing to run a few sql (sybase) commands from my vb app but dont have a clue how.

i will be running something like this

select
count(1) from customer_notice where
print_confirm  = '10/20/2003' and cn_type_id = 1 and client_id = 137

how can i, in vb6, logon to the sql server, run this statement, and get the results saved to a variable.

thnx in adv.
0
Comment
Question by:Thermos
  • 9
  • 8
17 Comments
 

Author Comment

by:Thermos
ID: 9594118
trying to connect ive done this so far.  added reference to ms activex data objects library 2.7

then ran this...but just get an object not set error

Dim oConn As Connection
oConn.Open "Driver={INTERSOLV 3.10 32-BIT Sybase};" & _
           "Srvr=server;" & _
           "Uid=logon;" & _
           "Pwd=password"
0
 
LVL 1

Expert Comment

by:PuckJunkie
ID: 9594200
You need to instance the connection object variable in your example - add the word "New" to the declaration:

Dim oConn as New Connection

That will get rid of the Object Not Set error. Then use a Command object to execute your SQL code, returning the resultset into a Recordset object. If you need more specifics, let me know, but it sounds like you're already headed the right way -

Puck
0
 

Author Comment

by:Thermos
ID: 9594283
thnx man.  the new connection.  fixed the 1st problem.  but ya lost me on

"Then use a Command object to execute your SQL code, returning the resultset into a Recordset object."

can ya dumb that down for me please?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Expert Comment

by:PuckJunkie
ID: 9594507
LOL Sorry. This code should work on a SQL Server, given the correct login information. With your connection string and table names, etc., it should give you a recordset containing the result of your SQL statement:

Dim adoCON As ADODB.Connection
Dim adoRCS As ADODB.Recordset
Dim adoCMD As ADODB.Command


Set adoCON = New ADODB.Connection

adoCON.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=r2L4?i;Initial Catalog=pubs;Data Source=LOGIC_SQL"
adoCON.Open

Set adoCMD = New ADODB.Command
Set adoCMD.ActiveConnection = adoCON
   
adoCMD.CommandText = "SELECT * FROM dbo.authors"
adoCMD.CommandType = adCmdStoredProc
adoCMD.CommandTimeout = 15
       
Set adoRCS = adoCMD.Execute
0
 

Author Comment

by:Thermos
ID: 9594710
im having probs with that.  i think its cause i gave you the wrong sybase type above.  i was earlier able to connect w/ this....

oConn.Open "Driver={SYBASE System 11};Srvr=server;Uid=logon;Pwd=password"

is that the problem.  what can i change to do this?

thnx for the help btw :)
0
 
LVL 1

Expert Comment

by:PuckJunkie
ID: 9594742
Ooops - sorry again - I just noticed that the CommandType I set the Command Object to is incorrect - it should be adCmdText.

I also noticed you asked how to get the value out rather than just return a recordset. After the Execute method above, you can navigate through the recordset and return values. Example:

If adoRCS.BOF And adoRCS.EOF Then
    MsgBox "No records returned."
Else
    adoRCS.MoveFirst
    MsgBox adoRCS!au_lname
End If

The first part looks to see if the recordset is at both the end of the file and the beginning, which means the recordset is empty. If that's the case, then a MoveFirst method causes an error.

Assuming the recordset contains records, you can use the MoveFirst method to move to the first record in the database and examine any values using the recordset!fieldname syntax as above.

Other methods and properties you may find useful are listed on this MS website:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaobj01_20.asp


Puck
0
 
LVL 1

Expert Comment

by:PuckJunkie
ID: 9594749
What kind of problems are you having? If it's because I gave you the wrong CommandType, you'll get a "Syntax error or access denied" message when you execute the command.

Puck
0
 
LVL 1

Expert Comment

by:PuckJunkie
ID: 9594835
DOH!! For all those interested, yes, that was my actual sa password for the local instance of SQL Server. Well, I'd been meaning to change passwords anyway. Actually, I've been meaning to disable that account... no time like the present!

0
 

Author Comment

by:Thermos
ID: 9594844
***This is what im running***

Dim oConn As New Connection
Dim adoRCS As ADODB.Recordset
Dim adoCMD As ADODB.Command

'Connect to Pluto
oConn.Open "Driver={SYBASE System 11};Srvr=server;Uid=logon;Pwd=pass"

Set adoCMD = New ADODB.Command
Set adoCMD.ActiveConnection = oConn
   
adoCMD.CommandText = "select count(1) from customer_notice where print_confirm = '10/20/2003' and cn_type_id = 1 and client_id = 137"
adoCMD.CommandType = adCmdStoredProc
adoCMD.CommandTimeout = 15
       
Set adoRCS = adoCMD.Execute

***This is the error i get***
run-time error '-2147467259 (800004005)':
[INTERSOLV][ODBC SQL Server driver][SQL Server]Incorrect syntax near the keyword 'select'.

also where/how can i set what database to use?

0
 

Author Comment

by:Thermos
ID: 9594847
lol and i was wondering about ur password :)
0
 
LVL 1

Expert Comment

by:PuckJunkie
ID: 9594911
Hmmm. I was wondering about your SQL syntax. What exactly is count(1) supposed to mean? Do you have a fieldname in your table named "1"?

The syntax I usually use for counting records matching a query is:

SELECT Count(tablename.fieldname) AS CountField FROM tablename;

Brackets around tablenames and fieldnames if they have spaces or odd characters. "CountField" is what it will name the returned column:

adoCMD.CommandText = "SELECT Count([Table Name].[Field to count]) AS CountField FROM [Table Name];
adoCMD.CommandType = adCmdText
adoCMD.CommandTimeout = 15

Set adoRCS = adoCMD.Execute

If adoRCS.BOF And adoRCS.EOF Then
    MsgBox "No records returned."
Else
    adoRCS.MoveFirst
    MsgBox adoRCS!CountField
End If

As for the database selection, you select the database in the connection string. Or, at least, you do when using SQL: see my connection string, I've used "InitialCatalog=pubs". pubs is a database on my instance of SQL Server. On most everyone's, actually, it's a demo database that ships with SQL Server. I don't see where you've specified a database in your string, but I don't know jack about Sybase either.
0
 
LVL 1

Expert Comment

by:PuckJunkie
ID: 9594968
A little looking around showed me that some people using Sybase use a "database=" string in the connection string for Sybase System 11. I can't find official documentation on the driver itself, but you might try that if you want to attach to anything other than the default database - you should be able to set the default database in Sybase itself. This is what your current connection string will connect you to. Try:

"Driver={SYBASE System 11};Srvr=server;Uid=logon;Pwd=pass;database=dbname"

And see if it works. Also, there is a .DefaultDatabase property of the connection object. Once you connect using your connection string, you should be able to examine this property and see what the default database is for your server.

Puck
0
 

Author Comment

by:Thermos
ID: 9594971
ok i figured out the database thing.  and i still get the same error.  i know the sql syntax is fine.  i can run the exact same procedure in embacadero and it pulls back exactly what i need.  the problem should be somewhere else.
0
 

Author Comment

by:Thermos
ID: 9594990
i commented out this line.
'adoCMD.CommandType = adCmdStoredProc

and i dont get an error anymore.  but how can i see the results?  where does this store the number that should have been pulled from the script
0
 
LVL 1

Accepted Solution

by:
PuckJunkie earned 65 total points
ID: 9595038
Right, I mentioned above I gave you the wrong value for that parameter. Sorry again.

The returned recordset contains the number you're looking for. Navigate to the first (only) recordset in it and check the value in the first (only) field:

adoCMD.CommandText = "SELECT COUNT(1) FROM ...
adoCMD.CommandType = adCmdText
adoCMD.CommandTimeout = 15
       
Set adoRCS = adoCMD.Execute

If adoRCS.BOF And adoRCS.EOF Then
    MsgBox "No records returned."
Else
    adoRCS.MoveFirst
    MsgBox adoRCS.Fields(0).Value
End If
0
 

Author Comment

by:Thermos
ID: 9595111
well how aboutcha.  it worked.  thnx man.  youve been a wonderful help.  im gonna add on a few more points and then accept ya.
0
 
LVL 1

Expert Comment

by:PuckJunkie
ID: 9595133
Thanks to you. Good luck with your app -

Puck
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2013 combo box not working 3 51
How to read File Date Created using VB6 8 50
RUNRMTCMD from AS/400 12 68
which modules are active in VB6 project? 6 39
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
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…

791 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