|
I have a table called IMSUB with fields SUB, SUBV, & SUBD
pri key is SUB, SUBV
SUB = subassy number, not auto number
SUBV = subassy version, not autonumber
SUBD = new name of subassy
I want to insert a new subassy, therefore, I need to get the maximum value for SUB add 1 to it, set SUB = 1 and set the SUBD to the new name. How can I do this in SQL? MS Access! Thx to all!
|
|
|
|
|
SELECT SUM(SUB)+1 FROM IMSUB
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
thanks for the reply, just a question.
the statement SELECT SUM(SUB) + 1 from IMSUB, does this sum all the SUB numbers and add 1? I was trying the following statement:
INSERT INTO IMSUB SELECT MAX(SUB) + 1 AS sub1, 1 as SUBV1, "Name of new sub" as SUBD1;
DO you think this will work and is it atomic, so that multiple users will not collide?
|
|
|
|
|
Sorry, I'm obviously having a bad day - It should have been MAX(SUB)+1
mmcsherr wrote:
DO you think this will work and is it atomic, so that multiple users will not collide?
It is atomic, so multiple users won't collide - although I have to say Access is not very good for multiple users.
Create a test database and try it out - much faster than waiting for an answer from a guy who's already made one blindingly daft mistake today. 
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
No problem, thanks for the help anyway, you are right about Access, but thatis what I am stuck with....
|
|
|
|
|
hi all,
IN Sql Server:
i need to create trigger on table A as
when i insert new row in Table A new record will be inserted in Table B with the same primary key of Table A
please Help
thank you
|
|
|
|
|
To copy all the column values from A to B, use:
CREATE TRIGGER Insert_B_On_Insert_A
ON A
FOR INSERT
AS
INSERT INTO B
SELECT * FROM inserted The inserted pseudo-table inside a trigger definition contains all the rows that were inserted, or for a FOR/INSTEAD OF UPDATE trigger, the new values of the rows.
Why use INSERT/SELECT rather than INSERT/VALUES? The trigger is called once for each INSERT statement, not once for each row. If table A is loaded using INSERT/SELECT (where the SELECT returns more than one row) or a BULK INSERT more than one row will appear in the inserted table.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I have a dataset that has a table in it called say myTable.
There is a column in this table called field3
I have a textbox which displays the value of field3 using binding:
this.textbox1.DataBindings.Add("Text", myTable, "field3");
I have an event for when an Add button is pressed which Adds a new row to myTable:
DataRow newDR = myTable.NewRow();
newDR["field3"] = "Some Text";
myTable.Rows.Add(newDR);
The problem I have is that the texbox does not change to show the new value. It still shows the previous value.
So basically when I add a new row to the table I want to be able to point controls that have been binded to this new row. I've spent 2hrs trying to figure out how to do this and am at a loss 
-- modified at 20:59 Tuesday 11th October, 2005
|
|
|
|
|
Question:- is that only the field3 didn't change?how about others fields?
but as i experience before once i add a new row, in the event, i've to put last command --> Page.databind() to bind data again.
maybe you can try, Hope can help.
|
|
|
|
|
How do I Validate DateTime in storeproc for a particular format say 'ddmm'?
|
|
|
|
|
Look at the IsDate function.
<br />
select IsDate('11/15/05')<br />
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Thanks for the reply.
Actually IsDate is not available in SYBASE. So I had to write custom validation.
Thanks again.
|
|
|
|
|
I have a stored procedure that look slike this (it's much bigger, this example is for simplicity):
INSERT INTO Table1 ( Field1,Field2,Field3 )<br />
<br />
SELECT Field1,Field2,Field3 from Table2 where Field4 = @Var
It is grabbing all of the data from one table and importing it into another
here is my problem, i need the proc to be able to tell if the value in Field1 in Table2 already already exists in Table1. If it does, i need it to update table1, otherwise do an insert, as it is new data.
I have seen how to do this with an individual record, but i need to be able to do it with a mass set. the above code currently handles about 29 rows from Table2
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Break this down into a SELECT first:
SELECT Field1, Field2, Field3
FROM Table2
WHERE Field1 NOT IN (SELECT Field1 FROM Table1)
Then merge them together:
INSERT INTO Table1 (Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM Table2
WHERE Field1 NOT IN (SELECT Field1 FROM Table1)
The update is a little more problimatic:
UPDATE Table1
SET Field2 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field2),
SEt Field3 = (SELECT Field3 FROM Table2 WHERE Table1.Field1 = Table2.Field2)
WHERE Table1.Field1 IN (SELECT Field1 FROM Table2)
If you don't want redundant UPDATEs, do the UPDATE call first.
|
|
|
|
|
That is beautiful 
Here's a funny story. One of my coworkers came up with a solution for the problem i was trying to address with the above question. (As it turns out, this would not have worked for it).
However, the exact same stored procedure made changes to 2 other tables, and your code here was the perfect solution for them.
For the first time in almost a week, this damn app is actually working the way it was designed
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Hi, I have a question in regards to oracle database.. I have a oracle database installed on a server which is a linux system. And now I want to store data into the database which is chinese/jap/korean characters. I used INSERT INTO table VALUES ('X'), where X is a chinese character, but when I retrive the data back from the database it returns as a "?". So, I want to know is there any configuration or anything that I need to set in order to store multi byte char into the database?? I used (VARCHAR) in the database. Thanks
|
|
|
|
|
Hi there,
as I not tried with Oracle before yet. But In SQL, if u want to save unicode characters into DB, the query should be something like "values N('X')"
try with this.
<< >>
|
|
|
|
|
I am working with an access application as front end to a sql server database. One of the tables is a picture table with 2 fields, an Id field and an Image field.
J has problems with saving and retrieving bmp files using the stream object.
I would be happy if anyone has a solution.
The 2 sets of codes are:
Private Sub PickupPicture()
Dim db As ADODB.Connection
Set db = CurrentProject.Connection
Dim st As ADODB.Stream
Dim rs As New ADODB.Recordset
Dim SqlString As String
Dim MyPath As String
MyPath = CurrentProject.Path & "\Pictures\Temp.bmp"
SqlString = "SELECT Picture " _
& "FROM tbPicture " _
& "WHERE ((Id)=" & MyId & ");"
rs.Open SqlString, db, adOpenStatic, adLockReadOnly
If Dir(MyPath) <> "" Then
Kill MyPath
End If
Set st = New ADODB.Stream
st.Type = adTypeBinary
st.Open
st.Write rs.Fields("Picture").Value
st.SaveToFile (MyPath) 'full path for bmp file
st.Close
rs.Close
db.Close
UserForm1.Image1.Picture = LoadPicture("")
UserForm1.Image1.Picture = LoadPicture(MyPath)
End Sub
Private Sub SavePicture()
Dim db As ADODB.Connection
Set db = CurrentProject.Connection
Dim st As ADODB.Stream
Dim rs As New ADODB.Recordset
Dim SqlString As String
SqlString = "SELECT * FROM tbPicture;"
rs.Open SqlString, db, adOpenKeyset, adLockOptimistic
rs.AddNew
rs!ID = MinId
rs.Update
rs.Close
SqlString = "SELECT Picture " _
& "FROM tbPicture " _
& "WHERE ((Id)=" & MinId & ");"
rs.Open SqlString, db, adOpenKeyset, adLockOptimistic
Set st = New ADODB.Stream
st.Type = adTypeBinary
st.Open
st.LoadFromFile (Image1.Tag) 'full path for bmp file
rs.Fields("Picture").Value = st.Read
rs.Update
st.Close
rs.Close
db.Close
End Sub
Best regard
Erik Lund
|
|
|
|
|
Hello,
I am writing a client server application using VS2003 and MSDE 2000 Rel A. I've created a setup using a VS deployment project to handle both the installation of clients (GUI only) and server (MSDE + GUI) - the user selects an option using a custom dialog. I've also created a custom action to take care of the creation of the database and tables (if the user selected 'server' in the custom dialog). I would like to know whether this is a suitable place to install MSDE if it isn't already installed? I realise that I could use the MSDE Deployment Toolkit but this means maintaining a different setup project for both clients and servers.
Any advice would be much appreciated!
Many thanks,
Steve.
|
|
|
|
|
I have a table in my database called "country"
In it I have "country_id" and "country_name".
I want to write a query that outputs the countryname in order, but the first two would be out of order:
i.e.
UK
USA
Afghanistan
Albania
Algeria
American Somoa
Andora
...
Zambia
Zimbabwe
I know it's possible but stupidly just can't think how. I'd rather do this by SQL than on my ASP page as there will be a couple of places it will be called from.
Please help!
|
|
|
|
|
Add an extra column to the table for ordering. For each group have an order number, e.g. UK and USA could be order number 0, everything else could be order number 1. Then in your SQL you can do an ORDER BY OrderNumber, CountryName.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Aargh! You beat me by two minutes
I like the 0 and 1 idea. I was thinking 1, 2, 3, 4... but I thought that might take too long.
The man who smiles when things go wrong has thought of someone he can blame it on.
If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
|
|
|
|
|
Yeah hadn't thought about doing it that way.
I remember somewhile ago using NULL values in the sql, to be able to play with it, but I've been hammering my head and just can't remember!
Cheers
|
|
|
|
|
You could add a "OrderRank" column where you give your own order (which may take a while with countries) and order by that or you could select the two countries into a table variable and then select the rest of the values except for the UK and US. Then just select everything out of the table variable.
The man who smiles when things go wrong has thought of someone he can blame it on.
If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
-- modified at 7:14 Monday 10th October, 2005
|
|
|
|
|
Ok going to be really cheeky.. but what's the basic setup of code?
DECLARE @vCountryName varchar(30), @vCountryName2 varchar(30)
SET @vCountryName = "UK"
SET @vCountryName2 = "USA"
SELECT @vCountryName, @vCountryName2, CountryName
FROM Country
ORDER BY @vCountryName, @vCountryName2, CountryName
I am going to be needing to output it into a single result set.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
