|
The table might have been imported to a different schema, try prefixing the table name with the schema name in the query. Or as Chris Meech suggested, check for permissions.
|
|
|
|
|
Thanks for the help. Chris' solution worked.
|
|
|
|
|
I would like to thank everyone in advance for their answers as I am extremely stuck. Also apologies if I put this question on wrong as it is my first attempt at this.
I am currently developing a C# application for a project at work, which uses a SQL 2008 server as a back end database. I know it’s an older way but I have been using windows forms and ADO.Net and have managed through this site and various books to get quit far but now have hit a problem that I cannot find answered on here. Some tables are going to be very large and hence I want to implement paging and have done this through a stored procedure. The application works out what page it requires and passes the start row index and number of rows to return to the procedure. This works perfectly (though I have read various posts to do with performance, and different ways of achieving paging in SQL).
My problem has arisen now that I won’t to introduce searching into the mix. Most posts/articles are geared towards ASP.net application which a user specifies the search criteria and the results for that search are returned in a page form. But I want to copy the functionality of a system we currently run at work called Syspro. Syspro will return you to the page relevant to the data you have just edited, added, or searched for. For example it opens the form with a data grid view on page one, I add new data which will be 100 pages in, after adding the data and closing the add form it finds the page that result is on and shoots the data grid view page to that page and high lights the new row. This works in the same way when you search and edit as you can skip through data on the add/mod form as well.
Is there a way of replicating this behavior in SQL or is this something that is handled in the C# application. I need a way of determining what page the data I require is on, but I understand that SQL only assigns row numbers after a query is run against that table. I have thought of using the primary key to save me the trouble, but some tables will allow deletes.
|
|
|
|
|
Lance Parker wrote: Is there a way of replicating this behavior in SQL o
Your question about what "behavior" you want to replicate is confusing but yes you can implement paging in SQL.
Basic steps.
1. Create your SQL with appropriate search parameters. You should still use parametrized values.
...a. If you have just a couple of items you can hard code the SQL.
...b. With more than a couple items create the SQL dynamically.
2. Apply appropriate ordering to the SQL. This can include creating it dynamically.
3. Apply paging (dynamic SQL again) See the following link
SQL Server 2005 Paging Results[^]
At the business level you might also want to consider what happens to paging if the data being paged changes underneath. Very common answers are
1. It won't, so there is no problem.
2. It won't in relation to the user because the work flow and usage insures that.
Be VERY sure that you analyze this at the business level before deciding that this could be a problem.
Also you should require sufficient search constraints to insure that no single result has lot of pages. Three pages is ok, 100 isn't. If someone claims otherwise then note that users do not randomly seek data. They know what they are looking for. So make them tell you it. Paging is a convenience so they don't have to tell you everything in detail but ignoring it can inhibit rather than help workflow.
|
|
|
|
|
Thank you jschell.
But I must apologise as after returning to work today and looking at the programme in which I am trying to copy some functionality from, I have realised I haven’t explained what I am after properly, and fear I have caused some confusion. But as for what you have told me in the above it validates most of the code I have written, thank you for that.
With that I’ll try again to explain myself, I caused confusion by saying there was a search involved "Apologies". In a nut shell what I am after is basically "Selecting and displaying a page based on a records location". Now I know that until a query is run against the table there is no theoretical position of a record. But what the programme does is open the pageForm on page one of the data in that table. Although there is a ID column on the table (as I have poked around in the programme files) the page is organised by the first column which is stock-Code. Hence you can page through all the results from a stock code beginning with "A" right through to "Z" which I think is around probably 1000 pages at the minute, not that we go through them all. Then a user opens a new form to edit or add a new record, when the form is closed and the data returned to the database the pageform will display the page relevant to the stock code added, and you can page backwards and forwards from this page. The same applies to the search, which is conducted in a separate searchForm, which displays the possible results for that criteria, which the user selects one, and returns to the pageForm which now displays the page with that record on.
Do you jschell or any one have any idea of how this is accomplished?
|
|
|
|
|
It is ordered by the stock code.
Everything I said at the SQL level applies.
You would write code in a db layer to do that.
Then the GUI level would use the db layer.
|
|
|
|
|
how we find next week day's
date of next day using current calender .
suppose we put sunday in variable then show date 6 jan 2013.we want find next week all days date one by one.
|
|
|
|
|
Do some reading around the datetime data type, there are various attributes that will help you. DayOfWeek (I think) returns 1-7 which can be used to get the info you need.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
dear all
i try to learn ado.net so i start with an article how to build database using ado+access and vb.net and think this article is compatible with visual studio 2005 not 2010 which i have so some error i face like
Me.BindingContext(DataSet1, "table1").Position = 0
i tray to read the first record from access data base
any one can guide me to learn new version of ado.net with ms access
best regards
|
|
|
|
|
I will make 2 recommendations
1. Change to SQL Server database it will be much more useful to train on.
2. Learn to do some research this is an excellent tool[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hello all, i want to ask how syntax cast real to numeric using scale in sql server 2005, i am already try but still error in datagrid, i am using jeasyui framework, can you help me.please...?! 
|
|
|
|
|
What error? What did it say? Can you post you Sql-statement?
|
|
|
|
|
insert into rk_process (code,name,label) values ('01','lee001','aa' )
insert into rk_process (code,name,label) values ('01','lee002','aa' )
insert into rk_process (code,name,label) values ('01','lee003','aa' )
insert into rk_process (code,name,label) values ('01','leek001','aa' )
insert into rk_process (code,name,label) values ('01','leek001','aa' )
want to filter only (lee) but it showing (leek) also
select name from rk_process where name like '%lee%')
thanks in advance.
Result
lee001
lee002
lee003
leek001
leek001
but i need only
lee001
lee002
lee003
|
|
|
|
|
You specified in your like clause that you want any words with "lee" so that is what you get. Try "lee0" so it will not get those containing the 'k'.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Not tested, but off-hand...
'%lee[0-9]%'
or
'%lee[^k]%'
P.S. You didn't specify which database system you are using. And you probably don't need the leading percent sign (%)
modified 28-Dec-12 12:55pm.
|
|
|
|
|
Tested:
select name from rk_process where name like '%lee[0-9]%'
Yields:
lee001
lee002
lee003
|
|
|
|
|
thank you so much PIEBALDconsult
|
|
|
|
|
by using this query you can achive you require output
select * from rk_process where name like '%lee0%'
--raj
|
|
|
|
|
I'm not designing a purchase order system per se, but it is the closest thing to what I'm actually doing so that's why my question/concern involve it. In a "minimal" PO system, you have the following tables:
Customers
Orders
Order Details
Products
When an order is first created (e.g., #123), product descriptions and prices are pulled from the Products table. That order is marked as paid. A few months go by and some of the products in the Products table get updated with new prices. Now what happens when I look at order #123 and its details? The price of that order will no longer match the sum of its individual products. Is that common? Am I looking at this from the wrong angle?
Thanks.
- DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
|
|
|
|
|
Instead of having one price record per item, have many records with history so you can see what the price was when the order was placed.
Something like:
Item: ItemId,Name,Description,Supplier,etc.
1,Widget,A standard widget,Widget Co,...
ItemPrice: PriceId,ItemId,FromDate,ToDate,Price
1,1,2012-01-01,2012-07-01,1.00<br />
2,1,2012-07-01,null,1.50
When a new price is added, the FromDate of the new record and the ToDate of the previous price are set as Now.
You find the current price by WHERE ToDate IS NULL
To find a price for an order you can use WHERE orderdate BETWEEN FromDate and ISNULL(ToDate,Now)
You may add NextPrice and PreviousPrice fields if you need to do a lot of lookups.
(Specific syntax depends on the particular database in use.)
|
|
|
|
|
Another alternative is to have the details table also record the price.
That is basically a requirement if one allows a manager to do a price override.
|
|
|
|
|
Good point. Yet shouldn't there be some sort of an adjustment record that can store that rather than leave an auditor wondering why the price was different?
Still, you need both the price history and a way to override the price (with a paper trail).
|
|
|
|
|
PIEBALDconsult wrote: Yet shouldn't there be some sort of an adjustment record th
An Audit record.
The audit records would also keep track of things like voided transactions, reversed items, etc.
|
|
|
|
|
jschell has the right of it, put all the details required to calc the value on the detail record. This may also include any discount/adjustment values. Your invoice report should only pull the descriptors from the product and customer tables
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am learning Asp.net with C# and Sql
server. I came across with a huge fixed width text file of 1780 columns. I have to import this file in sql server table. I will appreciate if you can help in this.
Thanks
Ibrahim Sharief
isharief291@gmail.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
