Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Conclusion
Click here to download a complete ad hoc query tool written in Visual Basic .NET. The program lets you select an Access database, and enter a series of SQL statements, separated by semi-colons. When you click the Execute button, the program breaks the statements apart and executes them.
The program examines each statement to determine what kind of SQL statement it is. If the statement begins with SELECT, the program assumes that the statement will return data, so it uses the ExecuteQuery function to execute the query. The program also uses ExecuteQuery if the statement begins with SP_ or XP_ because SQL Server databases contain a lot of data-returning stored procedures that have names beginning with SP_ and XP_. If you convert the program to use SQL Server or MSDE, this lets those procedures return their data normally.
If a statement doesn't start with SELECT, SP_, or XP_, the program executes it with function ExecuteNonQuery.
Take a look at the code, and modify it to suit your needs. You may want to take parts and integrate them into your full-featured database applications. You may also want to modify the code to perform other checks on the SQL statement. For example, you may want to prohibit dangerous statements such as DROP TABLE. With a few small changes such as these, this example can let you add an extremely powerful ad hoc query capability to your application with very little effort.
For more information on using databases with VB.NET, see my book Visual Basic .NET Database Programming (Que, 2002, http://www.vb-helper.com/vbdb.htm).