|
Am sure this would solve the problem. But, moving all this to a stored procedure would be a good thing future enhancements. For example, if the record exists, what if we have to delete the existing record and then insert a new record. or what if we just have to update. Moving all the DB related activities would simply enhancements or modifications in future. Would suggest to use SP's wherever possible.
Bhaskara
|
|
|
|
|
I have a problem that's an extension of one I was helped with before -hope someone can help!
A table (wm7_raw_spl_data)
Fields;
propertyID
date_2016_all (date field)
day_reading
night_reading
This table contains loads of readings for all properties. Each day/night reading is a separate record. The only way to separate records for a property is by the date the readings were calculated on.
So.. I want to choose the latest rundata for all properties. Properties are run off and rerun ad hoc, i.e. NOT all at the same time.
I did this like so:
SELECT *
FROM dbo.wm7_raw_spl_data INNER JOIN
(SELECT propertyID AS property_id2, MAX(date_2016_all) AS assessmentdate
FROM dbo.wm7_raw_spl_data
GROUP BY propertyID) wm7_raw_spl_data_1 ON dbo.wm7_raw_spl_data.date_2016_all = wm7_raw_spl_data_1.assessmentdate AND
dbo.wm7_raw_spl_data.propertyID = wm7_raw_spl_data_1.property_id2
OK! I now have a view I can use this to get the latest readings for all properties.
Now I want create a view to choose the 2nd latest run data for all properties...
Any help would VERY gratefully received. I'm having to do a big comparison of data and this is a basic requirement for the process.....
Patrick
Everything & Nothing
http://www.lostsheep.com
|
|
|
|
|
I think I would add an Identity to wm7_raw_spl_data to ease the query development but, given the original query try:
SELECT
*
FROM
dbo.wm7_raw_spl_data
INNER JOIN
(SELECT
propertyID AS property_id2,
MAX(date_2016_all) AS assessmentdate
FROM
dbo.wm7_raw_spl_data
WHERE
date_2016_all < (SELECT
MAX(date_2016_all)
FROM
wm7_raw_spl_data
WHERE propertyID = property_id2)
GROUP BY
propertyID) wm7_raw_spl_data_1
ON
(dbo.wm7_raw_spl_data.date_2016_all = wm7_raw_spl_data_1.assessmentdate AND
dbo.wm7_raw_spl_data.propertyID = wm7_raw_spl_data_1.property_id2)
|
|
|
|
|
I have an Access MDB file with a password on it, but I cannot access it via ADO.NET. I know its my connection string, but I've searched the web far and wide and I cannot get the correct connection string.
static public DataTable MDBPasswordQuery(string MDBPath,string Query,string Table,string Password)
{
try
{
DataTable dTable = new DataTable(Table);
string ConnText;
ConnText="Provider=Microsoft.Jet.OLEDB.4.0;";
ConnText+="Data Source="+MDBPath+";";
ConnText+="Jet OLEDB:Database Password="+Password+";";
System.Data.OleDb.OleDbDataAdapter HL7db = new OleDbDataAdapter(Query,ConnText);
HL7db.Fill(dTable);
return dTable;
}
catch(Exception Err)
{
throw new Exception("MDBQuery: "+Err.Message);
}
}
I get the following error: No value given for one or more required parameters.
Can anyone help ?
|
|
|
|
|
Hi there. Which line is triggering the exception? is it on the creation of the OleDbDataAdapter, or is it on the .Fill(dTable) statement?
|
|
|
|
|
|
Unbelieveable, it was my Query statement the whole time. Thanks for making me take a step back from the problem.
|
|
|
|
|
I'm glad you got it figured out.
|
|
|
|
|
I am connecting to an MSACCESS DB. I would like to loop through the database and return which records are different. I cannot get the statement to work. When I hardcode the vars the statement works, when I reference the loop counter var it does not -HELP PLZ!!!
FOR j = 1 TO 10
k = j + 1
SQL_query = "SELECT ["&k&"].id, ["&k&"].x, ["&k&"].y FROM "&k&" LEFT JOIN "&j&" ON ["&k&"].x = ["&j&"].x WHERE (((["&j&"].x) Is Null));"
|
|
|
|
|
Hello,
I have two tables in a DataSet (Customers and Orders) and they are displayed in a DataGrid. A relation is set up between the two tables.
Now, I have set up all of the necessary commands to update the tables. The user edits the data grid however he/she wishes and then hits a button to update. In the button click code I have
<br />
daCust.Update(ds,"Customers");<br />
daOrders.Update(ds,"Orders");<br />
If the user inserts a new Customer and some new Orders for that customer and then hits update, everything is fine. However, if he/she then tries to delete that new customer, I get a DELETE statement conflicted with COLUMN REFERENCE constraint error because it deletes the Customer and then tries to delete the Orders which reference that customer.
Can anyone suggest a simple solution where I can display two tables in a data grid and have the user edit them in any possible way or order and be able to update the database without getting a constraint error? The way I have it done it doesnt work unless you follow a certain order.
Thanx for the help,
-Flack
|
|
|
|
|
hi all
im not sure if you can do this or not, but i want to pass into a stored procedure
the name of the table as a string and do a query on that
i tried doing it below, but that way doesnt work
any ideas ?????
si
declare @table_name varchar(20)
set @table_name = 'table1'
select *
from @table_name
|
|
|
|
|
try exec ('select * from ' + @table_name)
It will be slower than just executing the SQL directly, and it bypasses all of the advantages of stored procedures (security, strongly typed parameters).
|
|
|
|
|
You can certainly do that.
But, instead of using "SELECT" as a T-SQL, construct it dynamically. Here is how that can be done within your SP.
DELCARE @sql_stmt varchar2(100)
SET @sql_stmt = "SELECT * FROM " + @table_name
EXEC ( @sql_stmt )
Bhaskara
|
|
|
|
|
I want to know the exact difference between a data provider(e.g. oledb provider) and driver(e.g. odbc driver). Is there any difference between them??
How r they related??
|
|
|
|
|
ODBC is Open Data Base Connectivity, which is a connection method to data sources and other things. It requires that you set up a data source, or what's called a DSN using an SQL driver or other driver if connecting to other database types. Most database systems support ODBC.
OLE is Object Linking and Embedding. OLEDB is partly distinguished from OLE itself, now called "automation".
OLEDB is the successor to ODBC, a set of software components that allow a "front end" such as GUI based on VB, C++, Access or whatever to connect with a back end such as SQL Server, Oracle, DB2, mySQL etal.
OLEDB is a different type of data provider that came about with MS's Universal Data Access in 1996 and does not require that you set up a DSN. It is commonly used when building VB apps and is closely tied to ADO. It works with COM, and DCOM as of SQL 7.0.
|
|
|
|
|
OLEDB is a specification for a driver, as is ODBC. So you have OLEDB drivers and ODBC drivers. There are also native drivers, which each have their own specification.
Providers are merely .NET interfaces to the drivers. If you did not have providers, then you would have to code directly against the OLEDB or ODBC api yourself, or use the old (non .NET) ADO.
Since OLEDB is more recent than ODBC, it is more full featured, and often faster. Given a choice, you should use it. Try and stay away from native drivers unless you want to be tied to a particular database. Performance gains from native drivers are usually marginal.
|
|
|
|
|
Hi,
pls help me to find a way.
1.i am able to store a jpg file in SQL server using C#
2.when displaying that image ,that data in binary format.pls help me o display
note : i am using this code in webparts to display in sharepoint.
pls give me the procedure to diaply image data from SQL server to Webpart or browser.
thx
sree
|
|
|
|
|
|
Hello. I need to store custom objects in a SQL Server 2000 table and then having them available to make some queries. I don't need to convert them into objects again. Objects are written in Visual C#
What is the easieset way to achieve it? Do I have to write a method to map each attribute into a table field?
Regards,
Diego F.
|
|
|
|
|
Diego F. wrote:
Do I have to write a method to map each attribute into a table field?
this is one way.
also you can persist it as XML and save it as string.
or wait till Yukon (MS SQL 2005) comes .. It can store .NET objects as any data type
|
|
|
|
|
If I decide to use XML, can then later make SQL queries directly to the data base? I don't need to convert the fields back to objets anymore.
What do you think is easier: mapping or using XML?
I can't wait to Yukon
|
|
|
|
|
Diego F. wrote:
What do you think is easier: mapping or using XML?
depends on the object model you have ..
if simple (array of objects that hold value members) it's better to store them as fields not XML.
If you store them as XML..you'll have to make some parsing after retrieving them from DB (which is bad IMO)
|
|
|
|
|
OK, I decided to map the objects.
The first problem I have encountered is how to design the table that represents a collection. How can I do that?
|
|
|
|
|
Hi
can you send the object model you try to save ?
for example :
if you try to save an array of Quadrilaterals.. each one has 4 points so you can make something like this :
tblQuad:
ID,name,Fillcolor
tblPoint
ID,X,Y
tblQuadPoint
QuadID,PointID,order
and so on.
|
|
|
|
|
I'll try to explain my application. I'm making an application that
models a restaurant. I manage three types of objects:
- Dish: the basic object that has an id, name, description and price. It
represents all available dishes in the restaurant.
- DishCollection: is a collection of Dish objects that represents the dishes
from a single order (all dishes from all customers in a table)
- Order: this object has simple types - date, table, totalPrice- and a
DishCollection. This one represent an order, with the details of number of
table, date, the total price and the dishes that the customer have asked for
(the DishCollection)
I want to store the Order objects in a data base and later make some queries
to know the money collected, the most popular dish,... So I have to create
the tables.
Can you help me with the tables design?
|
|
|
|