Solved

Run Sql command in VB

Posted on 2003-10-21
17
581 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 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

20 Experts available now in Live!

Get 1:1 Help Now