Help needed for SQL statement in VB 6.0...?

Question

My database is in Access.Consider the following code from my program:
i = InputBox("Enter registration number:", "Search Box")
record_set.Open "select * from stock where prod_id = 'i' ", conn, adOpenDynamic, adLockOptimistic

When its running its giving error Number 3021. Can anyone help me abt this plz....Thxxx

Answer 1

Try "select * from stock where prod_id = '" & i & "'".

i is a variable; "i" is a literal constant that always evaluates to "i".

Actually, try "select * from stock where prod_id = '" & replace(i, "'", "''") & "'". (Sorry about all the "''" mess.) This changes any single apostrophes to double apostrophes so the SQL will be valid if there are any apostrophes in the input.

Answer 2

First of cdmillstx, is point you the right way.



For debugging purposes, you might want to declare a string variable to hold your SQL statement, then use that as the parameter. That makes it easier to see what you've got in the string when you make the call.



Another trick you can use with an Access DB, is to let Access write your SQL string for you. Then on the toolbar, click the little arrow by the first button. That will drop down a menu where you can choose Design View, Datasheet View, or SQL View.

Click SQL view to see the SQL generated by your query, then cut and paste it into your program. If you have a complex query you might want to simplify it by removing the extra parenthesis that the parser throws in.

Answer 3

What sq. database do you have? sq. is in basic terms the question language. you like a database that is conscious the sq. language to be waiting to apply it, case in point, MySQL, Oracle Database, Microsoft sq. Server, etc. sq. is the language that facilitates you to speak on your database to characteristic information, replace or delete information, etc. After setting up and installation your sq. database, you are able to then use sq. to first create your database and then artwork with the concepts. There are 3 time-honored information on a thank you to pass into sq. statements: a million. Command line. you are able to the two enter out of your computing gadget's command/terminal window, or a custom command window put in by using the database. it is the main direct and least confusing thank you to speak to the database, ie, entering into sq. statements. 2. by using some GUI interface. for people who discover the command line daunting, there are GUI purposes (with abode windows, menus, buttons, etc.) that permit you enter sq. instructions and do different purposes with the database. case in point, the MySQL GUI equipment has the MySQL question Browser. 3. Code sq. statements into your application. you do not positioned sq. statements suitable on the HTML internet web site, considering the undeniable fact that could desire to be a huge secure practices flaw. you do not want human beings to be waiting to get entry to your database and thieve or wreck your information. with the intention to get entry to your database, you place the sq. statements into your backend application server and while a internet site customer clicks on a hyperlink to improve information out of your database, that hyperlink will call you application which connects on your database and retrieces the concepts and writes the html web site and sends that to the customer to apply. trouble-free server part scripting for internet web content often use Hypertext Preprocessor. vast employer corporation purposes use Java.