|
Morning guys (GMT guys that is)
I am having issues deciding on exactly how to perform a very large chunk of programming. I need to query the database im working with to build up a table of statistics from the various tables within the database. Each product must be selected and then 10 individual statistics, many involving averaging and percentaging, must be generated per product. Therefore there will be 10 queries x the number of products found run each time.
The thing im not really sure about is quite where to put the programming itself. Should it be in a seperately coded class as part of my ASP.net project or should it be completely coded in sql in a stored procedure.
I need a speed, ease of achievability and also a managability perspective on any proposed technique. Thanks
|
|
|
|
|
Hi Sam
I would use a stored procedure based on the following SQL:
SELECT ProductCode,
(SELECT ###Statistics sub-select### FROM MyTable1 A
WHERE A.ProductCode = Product.ProductCode) AS Statistic1,
(SELECT ###Statistics sub-select### FROM MyTable2 A
WHERE A.ProductCode = Product.ProductCode) AS Statistic2,
###etc
FROM Product
ORDER By ProductCode You need to replace the "###" bits with SQL that matches your statistical requirements.
Hope that helps.
Andy
|
|
|
|
|
hi everybody!
I am a beginner in PL/SQL.I am currently doing a project based on PL/SQL with java as front end.
Basically I am developing an editor for PL/SQL.
I want to know how could we retrieve the resulting statements of PL/SQL program that is executed in Oracle.
for eg:
how to retrieve the result of a query say,
'Query completed successfully' which is displayed in Oracle editor to the front end form.
Does anybody know how to proceed with this?
Kindly bear with me as am new to Code Project.
If anybody of you know the answer, can you be so kind and
respond me!
Regards,
Shalini
|
|
|
|
|
|
Hi everybody,
I wrote a script in Installshield that changes the MaxLocksPerFile value in
Registry for Jet 3.x and Jet 4.0
While installing the application through Installshield, the custom value for
MaxLocksPerFile is being overwritten to 9500 in Registry for Jet
4.0(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0) but not
for Jet 3.x(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 3.x).
The value is being overwritten to 9500 before the control reaches at the
script code.
I am wondering from where this 9500 value is being retrieved and set in
Registry.
Is there some file where these default values are mentioned.
If so then why 9500 value is not being set for Jet 3.x and is being set only
for Jet 4.0.
i am using MS Office 2002. Drivers for ODBC MS Access is above 4.0
and Installshield Version is 5.5.
Please help me to resolve this Issue.
-Regards
Harpreet
|
|
|
|
|
Hi,
Actually the what I am trying to implement is the following three requirements:
1) If the registry value Jet MaxLockPerFile is set to 30000 or lower the kit installation should set the value to 30000.
2) If the registry value Jet MaxLockPerFile doesn't exist the kit installation should set the value to 30000.
3) If the registry value Jet MaxLockPerFile is higher than 30000 the kit installation should not change this value in the registry.
To implement the above 3 requirements, I wrote the following script in Installshield:
szRegPath = "SOFTWARE\\Microsoft\\Jet\\4.0\\Engines\\Jet 3.x";
if(RegDBKeyExist(szRegPath) = 1) then
if(RegDBGetKeyValueEx(szRegPath, "MaxLocksPerFile", nzDummy,
szTmp, nzDummy) = 0) then
if (StrToNum (nVar, szTmp) = 0) then
if(nVar < 30000) then
nVar = 30000;
if(NumToStr (szTmp1, nVar) = 0) then
RegDBSetKeyValueEx(szRegPath, "MaxLocksPerFile", REGDB_NUMBER,
szTmp1, -1);
endif;
endif;
endif;
else
RegDBSetKeyValueEx(szRegPath, "MaxLocksPerFile", REGDB_NUMBER,
"30000", -1);
endif;
endif;
szRegPath = "SOFTWARE\\Microsoft\\Jet\\4.0\\Engines\\Jet 4.0";
if(RegDBKeyExist(szRegPath) = 1) then
if(RegDBGetKeyValueEx(szRegPath, "MaxLocksPerFile", nzDummy,
szTmp, nzDummy) = 0) then
if (StrToNum (nVar, szTmp) = 0) then
if(nVar < 30000) then
nVar = 30000;
if(NumToStr (szTmp1, nVar) = 0) then
RegDBSetKeyValueEx(szRegPath, "MaxLocksPerFile", REGDB_NUMBER,
szTmp1, -1);
endif;
endif;
endif;
else
RegDBSetKeyValueEx(szRegPath, "MaxLocksPerFile", REGDB_NUMBER,
"30000", -1);
endif;
endif;
Now for Jet 3.x it is working fine but when i check for fulfilment of the 3rd requirement in Regisrty(for Jet 4.0), the value changes to 30000, if previously it was greater than 30000.
This is because, the RegDBGetKeyValueEx function in script for(above) for Jet 4.0 retrieves the value 9500 instead of the value that we set greater than 30000( say 35000).
This is exactly what is happening. I will be highly obliged if anybody can help me to resolve this issue.
|
|
|
|
|
Hi all,
I am developing an employee search appl.I am using a textbox for name where the user can just enter the initials and entire name is searched.I am using stored procedures and passing the entered value as a parameter to stored proc.The problem is i haved framed the query as
select * from employee where fname like '@fname%';
this is not working and returns 0 rows
help!!!
|
|
|
|
|
in your case it is searching for the string starting with "@fname" as no data found starting with this string returns zero rows.
to my understanding @fname, in the query is variable.
if so, the same to be used as ...
select * from employee where fname like '%' + @fname + '%'
|
|
|
|
|
|
Hi,
Is it possible to use the parameter as an alias name for a column or any method I could use?
e.g.
@Conference - accepts either West or East, this is so that I could make use of the SP for Listing teams under East Conference and West Conference.
Create procedure [dbo].[sp_SelectConference]
(
@Conference varchar(20) = null
)
as
begin
select
TeamCity + ' ' + TeamNickName as @Conference + 'Confrence',
NumWins as 'Wins',
NumLoses as 'Loses'
from
teams
where
Conference = @Conference
end
Please advice
Dom;)
|
|
|
|
|
To my knowledge it is not possible.
however i'ld like to know the advantage in using alias of this
|
|
|
|
|
Hi Krish,
The query result will be binded to two data grid, west conference and east conference, and usually aliases appears as header text on the grid.
Dom
|
|
|
|
|
Hi
This is a good question. You need to assign your select statment in a temporary variable. And use that variable to execute your query 'exec (@sql)'. Please note here the bracket for executing statements. Hope this helps.
For example:
use pubs
go
declare @sql varchar(2000)
declare @AliasName varchar(10)
set @AliasName = 'Eest'
select @sql = 'select fname as ' + @AliasName + ' from employee'
print @sql
exec (@sql)
-Harini
|
|
|
|
|
iwant to store a decimale value like (10.05) in a decimal column in a database SQL 2000 but it converts this value to zero.
what data type should i choose to store these values
Thanks
Dad
|
|
|
|
|
Need to specify both Precision & Scale for Decimal datatype.
for example ...
create table TblA(c1 decimal(5, 2), c2 varchar(10))
insert into TblA values(10.05, 'abc')
|
|
|
|
|
Thanks For help
but I want to know what does (Precision & Scale)means and how can i use them to meet my requirements.
Dad
|
|
|
|
|
for example to have a number with 3 digits on int portion and 2 of decimal
i.e. precision - 5 and scale - 2
means total digits are 5 (precision) out of that 2 are decimal places (scale)
|
|
|
|
|
Great Thanks
But what is the diffrance between decimal and float data type
and how can I store a value like this(123/34)in my database.
Dad
|
|
|
|
|
Numeric data types with
fixed precision and scale are
decimal[(p[, s])]
numeric[(p[, s])]
floating point data
float(n)
for a detailed explanation regarding decimal, numeric, float & real you can refer to online help on "Data Types"
commin for storing the value (123/34) is that you want to store result of this expression, then you can use either decimal/numeric data type with specific number of decimal places or float data type.
|
|
|
|
|
I need to know the steps to connect to the Access database from my C#.Net code written using VS 2005.
|
|
|
|
|
Sanjeewani wrote: I need to know the steps to connect to the Access database from my C#.Net code written using VS 2005.
I typed "C# Connection Access Database" into a search engine and this[^] was on the first page of hits.
|
|
|
|
|
Hello Frnds,
I'm making a Store procedure in which i'm passing a parameter of string type have hotelid like this('1,3'). i want to compare these hotelid from hotel table which have hotelid in int in different records.
like this
-----------------------
hotelid Hotelme
1 Xyz
2 ABC
3 KHL
4 PQR
5 RST
I have hotelid like this (3,5).
I want to get the Hotel Name by store procedure. how can i compare hotelid from string to int while i have hotel id in hotel table is of type INT.
please give me some suggestion.
thanx and regards
Dotnet
|
|
|
|
|
for converting string to int, in SQL Server there are two function.
CAST(<value> AS <datatype>)
CONVERT(<datatype>, <value>)
in your case you can use ...
cast(hotelid AS int)
or
convert(int, hotelid)
|
|
|
|
|
Hello, I'm new to attributes. Attributes seem to solve a problem I have with my data representation. However, how are attributes stored in a database?
Specifically, I'm using VS'03 to build a typed data schema, wherein I create and populate some elements. I can use the SqlDataAdapter to load/retrieve from the dB, no problem. Now, I add attributes to the schema, how do I get them into the dB and what is the representation?
Thanks.
NYC Traveler
|
|
|
|
|
I'm trying to use the SqlDataAdapter to map 2 of 5 columns into a DataTable object and then update changes made to the local table back to the table in the database. Every time the Update command is called, I get DBConcurrency errors. I know this is not because of an actual concurrency issue because I am the only one accessing this database and I've tried this code in a separate program to isolate it from anything else I might be doing. I've read in a few places that this can be caused by an invalid update string, but I can't figure out what's wrong with mine. The code I'm using is below.
string sc = "";
string uc = "";
// The following chooses the proper select and update
// commands based on the value of MethodNumber. This
// is where I choose the column in the original table
// that I want to work with and call it OrderPoint in
// the local table. SSID is the PK.
if(MethodNumber == 1)
{
sc += "Select SSID, Method1OrderPoint As OrderPoint From OrderPoints";
uc += "Update OrderPoints Set Method1OrderPoint = @OP Where SSID = @SSID";
}
else
{
sc += "Select SSID, Method2OrderPoint As OrderPoint From OrderPoints";
uc += "Update OrderPoints Set Method2OrderPoint = @OP Where SSID = @SSID";
}
// Create a new DataTable and SqlDataAdapter, fairly standard
DataTable TableOp = new DataTable("TableOp");
SqlDataAdapter AdapterOp = new SqlDataAdapter(sc, Connection);
// Add parameters to the Update command. I've checked the
// SqlRowUpdatingEventHandler and these are being passed properly
// at run time
SqlCommand UpdateCommand = new SqlCommand(uc, Connection);
UpdateCommand.Parameters.Add("@SSID", SqlDbType.NChar, 14, "SSID");
UpdateCommand.Parameters.Add("@OP", SqlDbType.Int, 1, "OrderPoint");
AdapterOp.UpdateCommand = UpdateCommand;
// Add the custom table/column mappings. I've tried every conceivable
// permutation for every possible value I can think of to put in these,
// but no luck so far.
DataTableMapping MappingOp = AdapterOp.TableMappings.Add("OrderPoints", "TableOp");
MappingOp.ColumnMappings.Add("SSID", "SSID");
MappingOp.ColumnMappings.Add("Method1OrderPoint", "OrderPoint");
MappingOp.ColumnMappings.Add("Method2OrderPoint", "OrderPoint");
// Fill the schema and the table. These work fine. The TableOp
// looks and behaves exactly as I would expect.
AdapterOp.FillSchema(TableOp, SchemaType.Mapped);
AdapterOp.Fill(TableOp);
// Change TableOp.
foreach(DataRow CurrentRow in TableOp.Rows)
{
CurrentRow["OrderPoint"] = 1;
}
// Here's the update command.
AdapterOp.Update(TableOp);
This throws the following exception:
System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 records.
I've also tried using a DataSet object instead of the single DataTable with no luck.
I'm using Sql Server Express 2005 with Visual Studio 2003. I've also tried using their visual interface for building an SqlDataAdapter and it fails to generate update and delete commands for a mapped table.
Is it even possible to do this, and if not, what exactly is the point of table/column mapping?
Thanks
-Matt
|
|
|
|
|