|
Oh yeah, there's that, I never use it so I didn't think of it.
A console app could be written to do it.
|
|
|
|
|
I have a VB.net app on about 10pc's that access an Access 2007 database on a server to control stock. The stock quantities are getting all messed up, and I cant find out why. I tried setting record locking from the Office button > Advanced Options > Record Locking (Default Record locking = Edited Record) but this seems to be more of a generic kind of setting, and not specific to a particular database - I.e. you can set this without opening a database. How do I ensure that a record is locked when executing a query?
|
|
|
|
|
Could you post a code snipet of how you are updating the table ?
For example, a statement like the following would be kind of hard to corrupt.
update Inventory
set qty_on_hand = qty_on_hand - qty_sold
where item_code = "ABC123"
Is it possible that you have two operators who say, "Oh yeah, we have 3 on hand"
and each one tries to confirm an order for 2 items? This would leave you with a
qty_on_hand = -1.
The way around this would be to build a transaction that makes a request for 2 items to be sold and if it can't then rollback the request. (or create a back order).
Something like:
begin transaction
update Inventory
set qty_on_hand = qty_on_hand - qty_sold
where item_code = "ABC123"
if (select qty_on_hand from Inventory where item_code = "ABC123") < 0 then
rollback
Message to user: "Unable to complete transaction. Not enough qty on hand."
else
commit
End if
|
|
|
|
|
Hi David
What you describe above is pretty much exactly the situation, except...
A Sales Order typically has about 20 lines (I display the lines in a datagrid, they fill in the qty despatched for each item, and click confirm despatch. Two users can definitely try to despatch the same item at the same time.
Then it gets worse. After they confirm the despatch...
a) I update the Sales Order Header table - Status changes from Awaiting Despatch to Despatched
b) For each row in the datagrid:
i) See how much stock there is of the item in the stock table(Select query)
ii) Update the Stock Table with new Qty (Update Query)
ii) Write the qty despatched to the Sales Order Detail Table (Update Query)
iii) Write a transaction to a stock History Table (Insert Query)
iv) Write batch information to a Batch table
Both your suggestions may help (i.e. instead of first running a select query to get the stock qty, calculating the new qty in the app, then doing a second query to update the table with the new value, to rather do the calculation in the query)
This is a bit of a problem because of the way I store the stock qty - they have about 1000 stock items, that can exist in any of 10 warehouses. I only keep items that have stock in a warehouse in this table - kind of hard to explain here - perhaps i need to load every stock item in every warehouse in this table.
Second I like the idea of a transaction rollback, but I need to rollback all the other table writes and updates as well? Is this possible.
I can post code, but its long and would be much more than a snippet - can try to shorten it if this would help?
|
|
|
|
|
Or
Change your stock table to have an allocated field and increment/decrement that as the request is processed or cancelled/completed. It will depend on your business process, if the sales process takes minutes or a longer period this may suit.
You have to deal with crash edge cases where you have orphaned allocated values.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have an XML document with parent and 3 different chile elements.
Does anyone know how to get this into SQL please?
Might be a silly question but it's the first time for me. 
|
|
|
|
|
Probably depends on what you really mean.
But some possible solutions.
1. MS SQL Server (TSQL)can process XML. So write some TSQL to do it.
2. Write some C# (or Java, C++, perl, etc) which parses the XML and outputs in to import files. Then use the inport feature of the database to import it.
Myself I would generally always chose 2 because I am more familar with doing it with something besides TSQL and because then I don't need to worry about impacting database performance while it runs.
|
|
|
|
|
If the XML is something like:
<records>
<record>
<id>1</id>
<name>foo</name>
</record>
<record>
<id>2</id>
<name>bar</name>
</record>
</records>
and I don't yet a table for it, then my preference is to read the XML into an XmlDocument, iterate the records, iterate the children, cobble up appropriate CREATE TABLE and INSERT statements, execute the CREATE TABLE, then iterate the document again executing the INSERT for each record.
I have a console application to do this.
|
|
|
|
|
The XML data will be put on my ftp server and I need to read the data.
I have created the tables in SQL but now need to write a stored procedure to read the XML. All examples I found only use the 1 table so I still haven't managed to get the data into multiple tables.
Also in the child elements there is a field that include the foreign key [/int] added to it. I suppose I could do a substring once I read the data but for now I'm still stuck.
|
|
|
|
|
Hi,
I have listen that in code project there is ready code of asp.net. Please guide me how to find that.
|
|
|
|
|
ASP.NET forum[^]
ASP.NET Articles[^]
and on the home page you will lists of the latest articles, tips and questions which you can filter to asp.net
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Hello all
I have this query
DECLARE @a nvarchar(50)
SET @a = '1,3'
select *
from tableName
where CAST(ColumnId as nvarchar(50)) in (@a)
which doesn't work.
i would like it to work like the following query
select *
from tableName
where ColumnId in (1,3)
please advice
Thank's
|
|
|
|
|
Hi,
Try with two diffrenet integer parameters if your columnID is integer. its working fine 4 me..
Happy Coding... 
|
|
|
|
|
You are trying to compare a string '1,3' with an integer field that is not going to work.
Either do as Wipin suggested and use 2 variables or change the entire query to dynamic sql
DECLARE @a nvarchar(50)
SET @a = 'select * from tableName where ColumnId in (1,3)'
exec (@a)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You can try this:
DECLARE @a nvarchar(50)
SET @a = '1,3'
declare @tsql as nvarchar(max)
set @tsql = 'select *
from tableName
where CAST(ColumnId as nvarchar(50)) in ('+@a+')'
exec (@tsql)
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I'd advice to not use the "in" operator with a user-defined varchar, but to name the values individually.
SELECT *
FROM tableName
WHERE ColumnId = 1 OR ColumnId = 2
You can simply concatenate "OR ColumnId = @value" to the query and set the parameters.
Bastard Programmer from Hell 
|
|
|
|
|
I have this tables:
illness (cod_i,illness)
symptoms (cod_s,symptom)
and
illness_symptoms (cod_i,Cod_s)
How can i do to find out a illness that has for example 2/3 symptoms
-> cod_i | cod_s
i_1 s_1
i_1 s_2
i_1 s_3
that is select the illness that has the s_1 and s_2 symptoms. Please Help me
|
|
|
|
|
You need to select from the many-to-many table joined to itself, as in (not tested):
1. assuming s_1 and s_2 are known:
SELECT * FROM illness_symptoms AS is1
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
WHERE is1.cod_s=s_1 AND is2.cod_s=s_2
2.
Assuming symptom1 and symptom2 are known:
SELECT * FROM illness_symptoms AS is1
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
INNER JOIN symptoms AS sy1 ON is1.cod_s=sy1.cod_s
INNER JOIN symptoms AS sy2 ON is2.cod_s=sy2.cod_s
WHERE sy1.symptom=symptom1 AND sy2.symptom=symptom2
In both cases, then probably apply one more JOIN to obtain the details of the illnesses found.
|
|
|
|
|
THANKS A LOT MAN
i just tried the firs option with 2 and four symptoms and it really works. I doing a program to predict based on four choosen symptoms(combobox) if there are a illness, is like the idea of helping some kind of doctor in the task of diagnose illness. REALLY THANKS first time i ask in this very informative website![Java | [Coffee]](https://www.codeproject.com/script/Forums/Images/coffee.gif)
Powerful Germany...
|
|
|
|
|
You're welcome.
PS: you can always vote on a message to express to what extent it has helped you.
|
|
|
|
|
my Vote +5 for good example and nice explaination...
Happy Coding...
|
|
|
|
|
Dear All,
We are planning to have a complete solution for SQL Server disaster situation.
We will have two servers, one is locally and the other is in another country where we have VPN connections between the two offices.
I planned to build a transactional replication with fail over options to redirect our applications to the subscriber server.
My question is: Is this option (Transactional Replication) the most useful and best solution for mu purpose?
And if it is the best solution, how can our applications redirect to the subscriber server if the primary server is down???
Is there any thing to use that may check the primary server availability and redirect the applications to the subscriber server automatically???
Your help is very very very appreciated
Best Regards
Kind Regards
OBarahmeh
|
|
|
|
|
obarahmeh wrote: Is this option (Transactional Replication) the most useful and best solution for mu purpose?
Actually there are several ways of doing this, but if your way works for you then it is the best way. Sorry I do not remember how to fail-over. Currently we use log shipping but not the High Availability of it as we manually switch (not my idea).
Good luck, and do not forget to practice the fail-over every so often.
|
|
|
|
|
Hi, I'm fairly new to SQL and am using Oracle SQL Developer. I have a table with a field of data type DATE. (It is the 3rd field along). I need to insert a date and time into the field. I have tried the following:
INSERT INTO rentals VALUES (1, 7782, 02/02/2010 09:45:00,'PQ07JHG');
INSERT INTO rentals VALUES (1, 7782, TO_DATE(02/02/2010 09:45:00, 'dd-mm-yyyy' 'hh24:mm:ss'), 'PQ07JHG');
INSERT INTO rentals VALUES (1, 7782, TO_DATE(02/02/2010 09:45:00), 'PQ07JHG');
I keep on getting the same error: Bind variable "45" is NOT DECLARED.
Thanks in advance for any help
|
|
|
|
|
What tool are you using to insert the data (application you are building or a management tool) it looks like a management tool so try:
INSERT INTO rentals VALUES (1, 7782, TO_DATE('02/02/2010 09:45:00'), 'PQ07JHG');
I do believe todate takes a string literal as the first parameter.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
