question for Robert Bradley (and ofcourse also others) ,odbc and sql

[pomawo]

Hello,<br><br>I have three questions about odbc (Robert Bradley, maybe you can help me).<br>First some information : I have made an odbc link between an visual fox pro relation sales system and an accesss<br>Invoice system. The odbc link works fine.<br><br>My first question is (see *) <br>How can I make this statement correct, so that the value of ln_test wil be passed in the statement <br><br>My second question is<br>How can I put an array or cursor in an sql-insert statement. If i want to insert more attributes of an record.<br>I wanrt to point here to array klant_odbc<br><br>My third question<br>How can I search with an sql statement to a value and how can I filter ( such as set filter to in Visual Fox Pro) the records of the access database (so that it is possible to add,delete and modify waht is not possible by a view query)<br><br><br>Thanks to everyone who helps me<br><br>&&& koppeling met access databas<br>ln_klantid = thisform.relatie.value<br>ln_test = &quot;MAART7&quot;<br>SELE klanten<br>SELECT klantid,naambedrij,voornaamco from klanten where klantid = thisform.relatie.value&nbsp;&nbsp;into array klant_odbc<br><br>lnhandle = sqlconnect('factuur')<br>IF lnhandle &lt;= 0<br>MESSAGEBOX('Kan geen verbinding maken', 16, 'SQL Connect Error')<br>ELSE<br>MESSAGEBOX('Verbinding gemaakt', 48, 'SQL Verbinding met <br>facturerings systeem')<br><br>* sqlexec(lnhandle,&quot;insert into klanten (klantid) values &quot; + (ln_test))<br><br>SQLDISCONNECT(lnhandle)<br>ENDIF<br>

 

[foxdev]

<b>1)</b> You were close with <br><FONT FACE=monospace>sqlexec(lnhandle,&quot;insert into klanten (klantid) values &quot; + (ln_test))</font> <br>but as you probably know, the fact that ln_test is numeric (I would assume) causes a data type mismatch error when you try to run the above code.&nbsp;&nbsp;Change it to this: <br><br><FONT FACE=monospace>sqlexec(lnhandle,&quot;insert into klanten (klantid) values &quot; + ;<br>str(ln_test))</font><br><br><b>2)</b>I don't know of any simple way to pass all the elements of an array to a single call to an SQL pass-through statement, but from the wording of your question, I don't think you need to (it <i>sounds</i> like you just want to insert all the values for a single record).&nbsp;&nbsp;To do that, you would modify your statement to be:<br><br><FONT FACE=monospace>sqlexec(lnhandle, ;<br>&quot;insert into klanten (klantid, field2, field3) values &quot; +;<br>str(ln_test), myvalue2, myvalue3) </font><br><br><b>3)</b>You can search using SQL pass-through in a similar way, such as:<br><br><FONT FACE=monospace>sqlexec(lnhandle, ;<br>&quot;select * from klanten where klantid=&quot; + str(ln_test))</font><br><br>Or you can provide a wider search criteria (such as all clients within a certain city) to effectively give you a filter.<br><br>You can also create a remote view, which will look and smell like a Fox table, but in fact allow you to modify the base data (in this case, an Access database).&nbsp;&nbsp;If you've never done this, then the View Designer is the easiest way to do it (views must be stored in a DBC, so if you don't have one, you'll have to create one to store these views).<br><br>I admit I haven't worked much with Access, so I'm sure there are others here who have much more experience than I and that can add some additional information. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>