How to run SQL query from command button

[Imbriani]

I know this is a simple question, but I'm a newbie. What code do I use to run an SQL query from a command button on a VB 6.0 form? Thanks in advance for your tolerance. Kathy

 

[zemp]

The code that yoou would place in the click event would depend on your database and which tools you are using to connect to the database. Please provide more information.

Database?
Connection Objects?
Does the SQL return anything?

zemp

 

[zemp]

Also, have you checked the FAQs section of this forum (third tab at the top)? Take a look at the database section.

zemp

 

[MFritts253]

Kathy,
Do you have all your connections built? are you using stored procedures? what DB?

if you have your command objects built to run stored procedures all you will need to is call the command.

'Declare at the begining of the progect
Dim cmdMakeNCR As ADODB.Command
'In your form load event
Set cmdMakeNCR = New ADODB.Command
cmdMakeNCR.CommandType = adCmdStoredProc
cmdMakeNCR.CommandText = "QC_AddNCR"
cmdMakeNCR.Parameters.Append cmdMakeNCR.CreateParameter("InspectionID", adInteger, adParamInput)
cmdMakeNCR.Parameters.Append cmdMakeNCR.CreateParameter("UserID", adChar, adParamInput, 3)
cmdMakeNCR.Parameters.Append cmdMakeNCR.CreateParameter("NCR_ID", adInteger, adParamOutput)


'Command Button
Set cmdMakeNCR.ActiveConnection = conMain
'Let your procedure knows what its looking for
cmdMakeNCR.Parameters!InspectionID =
'then execut the query
cmdMakeNCR.Execute

hopefully i didn't confuse you, I'm not really sure what your working with but this is how i handle SQL sever and stored procedures in VB6

 

[JCruz063]

Imbriani,
If you're a newbie to programming, I would suggest you start off with Visual Basic .NET instead of VB6, as VB6 may become obsolete in a couple of years. Of course, this advice wouldn't apply to you if you have to work on an existing VB application.

JC

Friends are angels who lift us to our feet when our wings have trouble remembering how to fly...

 

[Imbriani]

Gosh, such wonderful response. I'm using an Access database and tables and trying to make a front end in VB. It's a very simple project, or it should be. These tables collect data from an online form. I download copies of them and this front end should allow me to view and search the data. I can't change the data in the tables because there's no direct connection between these downloaded tables and the real ones still collecting information . It's a nutty situation (I work for the government - there you have it) and I hope that this is the simplest solution. What I'd like to be able to do it simply search the various fields and produce query results. Let's say, for example, to pull up all the ships from Norfolk VA.
I sort of landed in the middle of this project. Heaven knows I'm not an expert and VB 6 is what is available right now. Can I write code that can be put in the "click" routine of a command button? Can parameters be used? I decided against using Access itself because downloading the tables produces a new DB each time and I have to keep changing the location of the tables in the split database if I use an Access front end. Does that clarify or muddy?

 

[zemp]

Yes, you can use the click event.
Yes, you can use parameters.

I would suggest using ADO as your connection objects. Take a look at faq222-3735 for a start.

Here is a link to some free online tutorials that might be of help.

http://www.vbexplorer.com/VBExplorer/databasetutorials.asp

zemp

 

[Imbriani]

zemp,

I took a look at your suggestions. I already have the connection set in code that's already written. I'll go through the tutorial and see if that helps, but on first glance, I didn't see anything that applied directly.

kathy

 

[Al42]

Kathy,

You want the user to define the search criteria? The easy (but sloppy) way is to give the user a few text boxes in which to type "ships", "Norfolk", etc. (Or list boxes from which to pick the criteria.) Then you build a string similar to:

strSQL = "SELECT * FROM tablename WHERE Item = '" & txtItems.Text & "' AND Location = '" & txtLocation.Text & "'"

Use strSQL (Dim'd as a string, of course) as your SQL string in the SQL command in the button's click event.

 

[Imbriani]

Al42,

That sounds like just what I want. I'll take sloppy. Now, can I get the results to display in a picture box, something like picResults.Print .......? I apologize for my ignorance.

Kathy