Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Put a constant value into my insert statement (Read 5608 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Put a constant value into my insert statement
21.02.11 at 14:20:33
 
Put a constant value into my insert statement
 
Quote:
I need to put a constant value into my insert statement.
Can you tell me how to arrange the “DataRange” parameter in the “SQLXL.InsertRecordset Table” statement please.
Can you just send me a code example of how you do that.

 
Interesting question.
 
The easiest would be if you would have the constant value in your spreadsheet as well and just offer SQL*XL the whole range as data range. You may actually hide the column. SQL*XL retrieves the values from Excel directly without looking whether the column is visible or not.
 
The next easiest solution is to replicate the data on another part of the spreadsheet. It may be that you don't want to present this constant value to the user. However suppose you have a second tab on the spreadsheet where you just reference the values from the first sheet and where you add this constant too. Example:
Suppose your data is on Sheet1!B1:D10. You could make another sheet (hidden or not) named Sheet2 where Sheet2!B1:D10 contains simpel formulas to reference the values from Sheet1.  
B1: =Sheet1!B1, B2: =Sheet1!B2, etc. Now you can add the constants in column A: Sheet2!A1:A10. If you reference this range as the data range it would work fine.
 
There are other solutions as well. You could create a trigger on the table that fires on each insert statement to populate this default field.
Back to top
 
« Last Edit: 21.02.11 at 14:20:41 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Bleftonee
Ex Member



Put a constant value into my insert statement
Reply #1 - 17.06.11 at 09:53:22
 
Dear Friend,
 
I have struck at a place where I need to read the value from BadBoy variable into database SELECT statement.
E.g.
select STATUSID from ORDER_STATEMENT t where t.Orderid= SBADBOY_VARIABLE
 
Can anybody help me out to read this value
 
Thanks
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Put a constant value into my insert statement
Reply #2 - 17.06.11 at 12:19:17
 
Hi,
 
There are several ways to do this:
 
Run the following statement so it prompts you to enter the value:
select STATUSID from ORDER_STATEMENT t where t.Orderid= :Your_VARIABLE  
 
You can also put your variable into your spreadsheet. When you store it in A1 you can use:
select STATUSID from ORDER_STATEMENT t where t.Orderid= :A1
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1