Solved

Run Sql command in VB

Posted on 2003-10-21
17
583 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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