SPONSORS:






User Tag List

Results 1 to 7 of 7
  1. #1
    Guest

    Using DB Tester and Excel

    When I run this script in Silk 5.0, I get Syntax error for the INSERT and "Operation must use an updatable query" error for the UPDATE. Any idea on what's wrong with these statements? I have my ODBC drivers configered correctly. In fact, a similar testcase with SELECT statement works.

    Thanks in advance

    [-] testcase SQLTest () appstate none
    [ ]
    [ ] // DSN and database details
    [ ] STRING sDBData = "DSN=Silk;DBQ=d:\silk\scripts\data\login.xls"
    [ ]
    [ ] // Connect to database and keep a reference to it
    [ ] HANDLE hDBHandle = DB_Connect (sDBData)
    [ ]
    [ ] //I am trying both these SQL statements. For the INSERT
    [ ] //I get syntax error
    [ ] //STRING sSQL = "INSERT INTO `result$``result$` (`result$`.id, `result$`.status) VALUES (1, 2)"
    [ ]
    [ ] //For this UPDATE, I get error message "Operation must use an updatable query"
    [ ] STRING sSQL = "UPDATE `result$``result$` SET `result$`.status=1 WHERE `result$`.id='one"'
    [ ]
    [ ] HANDLE hSQL = DB_ExecuteSql (hDBHandle, sSQL)
    [ ] DB_FinishSQL (hSQL)
    [ ] DB_Disconnect (hDBHandle)


  2. #2
    Junior Member
    Join Date
    Dec 1999
    Location
    Lexington, MA. USA
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Using DB Tester and Excel

    James Soderborg, Director of Consulting for Segue, wrote an article in March's segue.com (Segue's online customer newsletter) about accessing info from Excel using DBTester. I don't know if it's applicable to your specific issue, but it's worth checking out. (NOTE:You will need your username (first full word of your company name) and customer id# (the number you use when you contact Tech Support) to access the newsletter. There is an archive link on the far right of the screen to access back issues. Check out March. Hope it helps!

  3. #3
    Senior Member
    Join Date
    Jul 2001
    Location
    Elizabeth, CO, USA
    Posts
    653
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Using DB Tester and Excel

    Since the error is coming from the ODBC driver and not really from SilkTest, the best place to look for an answer is Microsoft's knowledge base.

    And from such I quote:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>
    An ODBC connection to Excel is read-only by default. ... You must set ReadOnly to False in your connection string or your DSN configuration if you want to edit your data. Otherwise, you receive the following error message:

    Operation must use an updateable query.
    <HR></BLOCKQUOTE>
    So, add ";ReadOnly=False" into your connection string.

    If you then get the error of "Spreadsheet is full," you'll have to modify the cells selected in your spreadsheet to not include the entire spreadsheet but just the data that's there.

    - Dave


    ------------------
    Amelior Technology
    dreed@ameliortech.com

    [This message has been edited by dcreed (edited 09-19-2001).]
    WaltzingRhino.com
    A concerned Borland customer, a fly in the ointment, a wrench in the works.

  4. #4
    Member
    Join Date
    Feb 2001
    Location
    New York, NY
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Using DB Tester and Excel

    Thanks,
    I set the DSN to be Readonly=False, but I still get the same error:
    [ ] INSERT INTO `Sheet2$``Sheet2$` (`Sheet2$`.In) VALUES ( 'pass' )
    [ ] *** Error: (42000) [Microsoft][ODBC Excel Driver] Syntax error in INSERT INTO statement.
    I went to Microsoft Knowledge Base and the syntax is the same as what i used:
    strSQL = "insert into tmp_MEMO1234 (data1) values ('String')"

    ------------------
    -Lev Aks
    lev_aks@in-nyc.com

    [This message has been edited by levaks (edited 09-19-2001).]

  5. #5
    Member
    Join Date
    Feb 2001
    Location
    New York, NY
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Using DB Tester and Excel

    Does anybody know proper syntax for INSERT statement that actually works for Excel?

    Here is my code that does not work:

    [ ] string sConnectExcel = "DSN=E911;DBQ=C:\Silk_Scripts\Results.xls"


    [ ] HDATABASE hdbc1 = DB_Connect (sConnectExcel)


    [ ] Integer i

    [ ] sSQL = "INSERT INTO `Sheet2$` `Sheet2$` (`Sheet2$`.In) VALUES ( '{i}' )"

    [ ] hstmnt1 = DB_ExecuteSql (hdbc1, sSQL)

    ------------------
    -Lev Aks
    lev_aks@in-nyc.com

    [This message has been edited by levaks (edited 09-19-2001).]

    [This message has been edited by levaks (edited 09-19-2001).]

  6. #6
    Member
    Join Date
    May 2001
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Using DB Tester and Excel

    Hi Levaks,
    I have been using Excel spreadsheet successfully.

    Here it's the INSERT syntax that works for me.

    handle hDB = DB_Connect ("dsn=Excel Files;DBQ=C:\segue\test.xls")
    String sSQL = "INSERT INTO `Results$` (Query,Records) VALUES ('test',20)"
    handle hSql = DB_ExecuteSql (hDB, sSQL)
    DB_FinishSQL(hSql)

    Here it's the UPDATE statment

    handle hDB = DB_Connect ("dsn=Excel Files;DBQ=C:\segue\test.xls")
    String sSQL1 = "Update `Results$` set Records=100 where Query = 'test'"
    handle hSql = DB_ExecuteSql (hDB, sSQL1)
    DB_FinishSQL(hSql)


    ------------------


    [This message has been edited by mansri (edited 09-20-2001).]

  7. #7
    Senior Member
    Join Date
    Aug 2000
    Location
    NY,NY,USA
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Using DB Tester and Excel

    Santosh is that you?
    send me a note at russell.romei@db.com

    ------------------

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 09:06 AM.

Copyright BetaSoft Inc.