|
Hi,
I am facing problems with the Sub Query parameter injecting problem. Let me give u the code so tat u can understand the prob
String strSQL = “select d.deptno,e.empname from dept d, (select empname from emp where hdate = : inputDate and jdate= : inputDate) e”;
OralcConnection con = new OracleConnection(conString);
OracleCommand cmd = new OracleCommand(strSQL,con);
OracleDataReader dr;
cmd.CommandType = CommandType.Text;
cmd.Parameter.Add(“:inputDate”,”01/01/2006”);
dr = cmd.ExecuteReader();
but when ever i run this code its saying tat ORA-01008: not all variables bound.
If I add other parameter to the CommandObject with same name and value its working fine. But I need to add only one parameter to this CommandObject.
Please help out
sri
sri
|
|
|
|
|
Try adding the parameter without the colon ...
cmd.Parameter.Add(“inputDate”,”01/01/2006”);
Also, I don't know if it is a formatting issue with your post, but in your query, you appear to have a space between the colon and the variable name.
|
|
|
|
|
Hi Jim,
thanks for your response, i traied that process also but still i its giving the same error. is there any thing i am missing in my code. please help me out.
sri
|
|
|
|
|
Hi,
I got the solution we have to just mention the binding type
dbCmd.BindByName = true;
then it will take only two parameters
sri
|
|
|
|
|
I am working on an application that requires the identification
of items that are selected from a list box on a form so that it
could pass the parameters to a report to be generated on the fly.
The application is in development in MS Access. I have created the
form with a list box that is populated from a table. There is a
command button on the form that submits the form to the report.
The report criteria in the WHERE clause is supposed to have a
parameter that passes the results to the report before it is
generated.
I have successfully been able to retrieve the selected items
from the listbox, but these results are in the command button
on the form. How do I pass these parameters from the control
to the report before the report is generated.
The code in the click even of the command button on the form
with the list box is as follows:
Private Sub Preview_Click()
If IsNull([DueDate]) Then
MsgBox "You must enter a specified Due Date."
DoCmd.GoToControl "DueDate"
Else
Dim frm As Form, ctrl As Control, varItm As Variant
Dim strFormName As String
Set frm = Forms![Report for Dates Enhanced]
strFormName = frm.Name
Set ctrl = frm!lstExcludeViaCodes
For Each varItm In ctrl.ItemsSelected
Debug.Print ctrl.ItemData(varItm)
Next varItm
Me.Visible = False
End If
End Sub
I would like to be able to retrieve the value on the report.
I have tried to use the following code in the criteria for
the WHERE clause in the underlying query that feeds the report:
In ([Forms]![Report for Dates Enhanced]![lstExcludeViaCodes].[ColumnHidden])
The SQL generated then is:
SELECT [Sales-PO Query].[Vendor Name],,,,, [Sales-PO Query].[PO Number]
FROM [Sales-PO Query]
WHERE (([Sales-PO Query].[Sale Deliver Via]) In ([Forms]![Report for Dates Enhanced]![lstExcludeViaCodes].[ColumnHidden]))
ORDER BY [Sales-PO Query].[Profit Center], [Sales-PO Query].[Vendor Name], [Sales-PO Query].[Sale Delivery Date];
The idea is to generate the result for the criteria in the WHERE clause as follows:
WHERE (([Sales-PO Query].[Sale Deliver Via]) In ('SaleDeliverVia1', 'SaleDeliverVia2', 'SaleDeliverVia3')
A really good way to retrieve the selected items from the
listbox on the form might be something like:
Forms![Report for Dates Enhanced]![lstExcludeViaCodes].SelectedItems
... but this property is not available. The only property that is
remotely close is:
Forms![Report for Dates Enhanced]![lstExcludeViaCodes].InSelection
Could somebody who is knowledgeable about MS Access reports
and forms that pass parameters kindly provide some additional
input as to how I could get the listbox selection results
to pass to the query that feeds the report?
To quickly see a sample database as a sample basepoint,
click the menu item > File > New and then select from the
Templates task panel > "On My Computer", and then click the
"Databases" tab. Finally, let Microsoft's wizard create
a database such as "Inventory Control".
Any assistance would be greatly appreciated.
|
|
|
|
|
Hi,
Im not 100% if this will work for you, but when ever I send parameters to Access reports I use report.filter ""
Hope this will work for you
|
|
|
|
|
how to use logical expressions like 'AND' or 'OR' in mdx query? this is
basicly what I´m trying to do:
SELECT CustomerName, ManagerName, ProjectNumber
FROM Cube
WHERE ProjectManager = 'xyz' OR AccountManager = 'xyz' OR Division =
'xxx' OR Division = 'yyy'
can anyone help me with this? I was trying to use the filter condition,
but I don´t really know how to use it correctly, are there any
examples or tutorials about this topic?
cellardoor
|
|
|
|
|
Dear CPians,
I am designing an application that is data centric and will require alot of data migration daily.
In detail, I have a table in SQL Server 2005 that will have to be emptied and then populated with 500 million records twice a day!
Note that the data is provided in TEXT files every 12 hours.
My question is, when should I start considering table clustering? are there any techniques that should I be aware of?
Thanking you in advance.
|
|
|
|
|
Hi,
My problem is that, due to large data in SQL server, my Inventry software is getting to slow while loading data from the server, is there any solution from the database side to increase the performance, or i have to change the entire code of the software, which is going to be the worst thing to do, as i have written the code in VB, I have used distributed Database Access Layer to access the data from the database
|
|
|
|
|
|
Sunil Lanke wrote: my Inventry software is getting to slow while loading data from the server
The first thing to do is check the indexes on your tables.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
By large data do you mean "many records" or "large binary blobs"?
The "many records" issue can often be helped by better indexing, and query reorganization.
|
|
|
|
|
Hi
How can i names of all the tables from a Microsoft Access Database? Is there any query to do it?
We Believe in Excellence
www.aqueelmirza.cjb.net
|
|
|
|
|
you can get it like this:
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U'
Best Regards,
Apurva Kaushal
|
|
|
|
|
SELECT name <br />
FROM MSysObjects<br />
where type = 1
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi,I'm implementing mass information procedures that is stored in a SQL Database.
What methods and actions i need to take in order for the process to be faster in a case like this (its more then 1000000 rows).
I'm trying also to improve the memory usage since i use the DataTable in C# and I'm looking for a better way to process the retrieved data. is there a better class or method thats improving the speed and preventing any memory leaks ?
please advise....
Thanks for any help,
Lior S 
|
|
|
|
|
hi friends
i'm creating a new stored procedure that will describe a tables' structure.
the procedure i'm writing is
CREATE PROCEDURE Describe (@table_name VarChar) AS
SET NOCOUNT OFF
DESC @table_name
GO
this gives me a Syntax error that incorrect syntax near the keyword DESC
i have tried using the keyword DESCRIBE too, but it still gives me the same error.
help me plz.
thankyou and best regrads
|
|
|
|
|
Saira Tanwir wrote: CREATE PROCEDURE Describe (@table_name VarChar) AS
SET NOCOUNT OFF
DESC @table_name
GO
it would be like this:
CREATE PROCEDURE Describe (@table_name VarChar(1000)) AS
SET NOCOUNT OFF
select name from syscolumns where id =object_id (@table_name)
GO
Best Regards,
Apurva Kaushal
|
|
|
|
|
I receive currency notes in bulk sorted by serial numbers. If i make individual entry of each currency note in database table, it will grow very big, so i am storing the start range and end range of serial numbers of the notes i receive in single instance.
Index StartRange EndRange TotalNotes
1 21 30 10
2 11 17 7
3 35 45 10
4 31 35 5
Now i want to determine whether the currency note of serial number 16 is present in the database. I may also want to determie whether the serial numbers of range 29 to 33 present or not.
Please tell me how to do so ?
Imtiaz
|
|
|
|
|
Something like:
SELECT 1 FROM TABLE
WHERE @SerialNumber between StartRange and EndRange
Imtiaz Murtaza wrote: I receive currency notes in bulk sorted by serial numbers.
Really. What's your address...?
|
|
|
|
|
Imtiaz Murtaza wrote: i want to determine whether the currency note of serial number 16 is present in the database.
See Paddy Boyd's answer.
Imtiaz Murtaza wrote: I may also want to determie whether the serial numbers of range 29 to 33 present or not.
select * from TableName where startrange <=29 and endrange >=33
However, you will need a more complex query if you want to check for the case where you have one range of 25 to 30 and another of 31 to 35. This would mean that your 29 to 33 range is present, just not in one record. Maybe that isn't an issue for you.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi I have two servers one runnning sql 2000 and another running sql 2005.
i am using BI from visual studio.net 2005 and running a sql report.
server with sql 2005 has a server linked to server with sql 2000.
all query works fine but when it comes ot stored procedure it gives me below error.
TITLE: Microsoft Report Designer
------------------------------
An error occurred while retrieving the parameters in the query.
Could not find stored procedure 'SURVEY.sys.sp_procedure_params_managed'.
------------------------------
ADDITIONAL INFORMATION:
Could not find stored procedure 'sys.sp_procedure_params_managed'. (Microsoft SQL Server, Error: 2812)
I check the RPC and made them allow in both servers. i thought it could be due to compatibility issues so i changed th compatibility issue to 70 from 80 but i am still getting this error. dont know what am i doing wrong. every where i am giving an appropriate names like
servername.dbname.user.table does anyone know how to fix this problem or any one ever come across this problem
please help
|
|
|
|
|
Please tell me the easiest way to make the file ute.asp require a user name and password.
Thanks
|
|
|
|
|
Hi all , I have designed my Database in SQL Express 2005 and I want it host on SQL 2000 supported hoster (brinkster.com) ... Please advice how can i Downgrade my Database ... Generating scripts ??? Creating .BAK files ???... Urgent reply awaits ... thanks
Kaliem
|
|
|
|
|
Hi
I run what I thought was the most basic example of a table that allows a cascading delete:
CREATE TABLE TEST
(
ID tinyint NOT NULL,
PARENT_ID tinyint NULL,
CONSTRAINT PK PRIMARY KEY (ID),
CONSTRAINT FK FOREIGN KEY (PARENT_ID) REFERENCES TEST(ID) ON DELETE CASCADE
)
go
I get the error:
Introducing FOREIGN KEY constraint 'FK' on table 'TEST' may cause
cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO
ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Server PEER170, Line 5
What am I doing wrong???
cheers,
Neil
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
