Craig, A quick postscript to my earlier message. When I have my SQL working properly I comment out the Response.Write (strSQL) line. I never delete it. Then in the future if I have any other problem (it is amazing how they do appear) I just have to uncomment and I am straight into debugging.
regards Paul ----- Original Message ----- From: "Paul Robinson" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Wednesday, January 19, 2005 8:29 AM Subject: Re: [AspClassicAnyQuestionIsOk] Access SQL Query Tool > > Craig, > > My method of error tracking is to create the SQL as a string and then to Response.Write the string before I execute the command. This means that the SQL appears on the screen before the ASP goes into error mode. > For example - > > Dim strSQL, iCount, arrBU, arrProjNo, arrStatus, arrBuild, arrSite > > set objConn = server.CreateObject("ADODB.Connection") > strConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/EPSProjects.mdb") > > objConn.open strConnect > > strSQL = " SELECT rec_id, name FROM afm.gp_name WHERE (Group_Type <> 'Historic')ORDER BY name" > > Response.Write (strSQL) ' This will print your SQL string to the screen > > Set objRS = objConn.execute(strSQL) 'If there is an error in the SQL it will print to the screen now > > If objRS.EOF then > Response.Write "No records returned" > Else > arrBU = objRS.GetRows > End If > objConn.Close > > Copy the SQL from the screen > Open a new query in the Access database (in this instance above EPSProjects.mdb). Open the query in Design View. When the Show Table window appears, just click on the Close button without Adding a table. if you look at the query design toolbar, you should now see a View button labelled SQL with a drop down arrow next to it. Click on this button to go to the SQL window. You should now see 'SELECT;' highlighted.Press [Control] and V to paste your SQL string that you copied from the web page. > > The View button will now show the datasheet icon. When you click here, if there is an error in your SQL then a message will appear and you can start to debug. If your SQL is correct then the datasheet will appear with the results of your SQL statement. > > One point to note. If your SQL statement in ASP has a wildcard in the WHERE clause, the Jet engine will require a '%' character, but native Access uses the '*' character. You will need to make this change before error checking. > > For example in ASP for Jet > "SELECT FirstName, LastName, UserName FROM tblPersonnel WHERE LastName LIKE 'Robins%' > in Access > "SELECT FirstName, LastName, UserName FROM tblPersonnel WHERE LastName LIKE 'Robins*' > > If you have any problems debugging the SQL, please publish the SQL and error messages. > > Hope this all helps > > > ----- Original Message ----- > From: "Charles Carroll" <[EMAIL PROTECTED]> > To: <[email protected]> > Sent: Wednesday, January 19, 2005 5:03 AM > Subject: Re: [AspClassicAnyQuestionIsOk] Access SQL Query Tool > > > > > > And if somebody could step him trough going into access, creating a > > blank query and going to SQL view and pasting SQL that would be great. > > I could not walk him torugh it by chat because at my in-laws house all > > the MS office packages are Japanese and reading Kanji is tough. > > > > > > On Wed, 19 Jan 2005 04:35:41 -0000, Craig <[EMAIL PROTECTED]> wrote: > > > > > > I'm new to SQL and have run into a problem. There is an error in an > > > SQL statement on page I'm using. I would like to use the access data > > > base query tool to check my statements seeming that's what the db SQL > > > is querying. Can someone walk me through the process on how to do > > > this? I THINK I've found the query tool. I'm new to ASP and SQL so > > > everything is a learning experience and takes me about 3x as long as > > > everyone else so patience is a virtue right now. =) > > > > > > Thanks! > > > > > > > > > > > > > > > > > > > > > ________________________________ > > > Yahoo! Groups Links > > > > > > To visit your group on the web, go to: > > > http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/ > > > > > > To unsubscribe from this group, send an email to: > > > [EMAIL PROTECTED] > > > > > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > Yahoo! Groups Links > > > > > > > Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
