|
I have a big problem with MS.Access. Now, I have two databases (DB). The problem is that I want to add a record to the first DB and the result I expect that that record is exist in the second DB automatically. In addition, two DBs are on the two computers. Thanks...
|
|
|
|
|
Create a third db (database) with the tables for the two dbs in a single located server point. Use linked tables on client dbs versions to the link central version of the tables. You can now run multiple clients off the same back end db.
|
|
|
|
|
Maybe my question is not clearly. The problem is that I want to control (add, remove, delete, or update a record of a table) the remote database of MS.Access on another computer when I'm from a database of Access on my computer.
|
|
|
|
|
hi there,
vtalau wrote:
I have a big problem with MS.Access
=>what probs u r facing?
vtalau wrote:
I want to add a record to the first DB and the result I expect that that record is exist in the second DB automatically
=> whenever u insert a record to the first DB, also insert this to the second one.
<< >>
|
|
|
|
|
Try to do little home work on Distributed Transaction if you want to update into different database.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
I think "Distributed Transaction" is not exist in MS.Access. How I control (add,remove,delete,or update a record of a table) the remote database of Access when I'm from a database of Access. Two databases are also on two computers.
|
|
|
|
|
Sorry that was a mistake.
If it is access you have to ensure that both database table got Inserted/Deleted/Updated.
Otherwise you have to upgrade your access to Sql Server or other.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
Hi Guyz!!
pls help,
how can one measure the size of connection pool.
confused:
|
|
|
|
|
|
what I mean is that:
-how can one find out the number of connections to the pool
-is there any limits to the connection pooling,if so how can I trace if that limit is reached.
hope I 've clarify my question now "enjoycrack"
|
|
|
|
|
|
I am trying to dubug some of stored procedure which is located in my local server and as well as database which is located in network.
I followed all steps which found in Sql Server 2000 help. But the moment i start debug, the debugger is not allowing me to step into/throu.
Why this ? What will be the issue.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
You can copy the body of your sp to Query Analyzer and debug it there. Replacing input variables foe DECLARES and use SET @Variable to set intial conditions.
|
|
|
|
|
That is what i am doing. But the moment i try to start debug, the Queary analyzer won't allow me to do setu throu/down. It completes the Sp execution automatically.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
Friends,
Is it possible for me to store entire MS-Word document in the SQL server database. If yes, then please tell me how can we do so ? What will be the column's datatype and how to store & retrieve the word file ?
Thanks.
Ahsan
|
|
|
|
|
Yes, you can store an entire MS Word document in Sql Server.
The column in which the document is stored should probably be of type 'image', and you can use the ADO.Net clients to write to the column (image columns accept a byte array - byte[]).
You can read the document into a stream, and use the stream's buffer (byte array) as input for the image column.
Reading the document back from the database works in a similar fashion - read the byte array from the image column into a stream.
Look at FileStream for more info.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
hi there,
play with binary large objects (BLOBs) data type.
<< >>
|
|
|
|
|
I just checked the SQL server data types. There is no BLOB data type. The only matching datatype is "binary". By BLOB do you mean "binary" ?
Ahsan
|
|
|
|
|
The best practise for storing large files such as Word documents is to store the file path location and name in the database. And have the actual file stored in the file system rather than in the SQL server database.
You could use the varbinary[^] column type however there is a limit of 8000 bytes on SQL Server 2000.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
Hello frends!
i have this table:
+-------+----------+-----------------+----------+
| ID | ParentID | ChildIDs | ModuleID |
+-------+----------+-----------------+----------+
| 9 | [NULL] | 91 | 20001010 |
| 90 | [NULL] | 901 | 20001018 |
| 901 | 90 | 9011,9012 | [NULL] |
| 9011 | 901 | [NULL] | [NULL] |
| 9012 | 901 | [NULL] | [NULL] |
| 91 | 9 | 911,912,913,915 | [NULL] |
| 911 | 91 | [NULL] | [NULL] |
| 912 | 91 | [NULL] | [NULL] |
| 913 | 91 | [NULL] | [NULL] |
| 914 | 91 | [NULL] | [NULL] |
| 915 | 91 | [NULL] | [NULL] |
+-------+----------+-----------------+----------+
now what im supose to do is display rows based on the moduleIDs. Now when ModuleID is displayed along with the other IDs, its supose to display all the IDs included in each row's childIDs. for example i search for ModuleID=20001010 the row with ID=9 will be displayed along with row with ID=91 (because its ID is included in ID=9 's childID and because its ParentID is ID=9 ). So will the row with ID=911 will be displayed (because its ID is included in ID=9 's childID) and so on.
I've been busting my head open bout how i could do this. i can only make the 1st tier show (ID=91 ) but the next set of rows i'm quite clueless about. Any Ideas how I could make this work? Thanx so much for all your helps...believe me il be very much greatful to you all!
Aim small, miss small...
|
|
|
|
|
A very interesting problem.
Most likely you can solve this by creating a User-Defined function (or Stored Procedure) that keeps calling itself to union all the result from the ChildIDs.
How about something like below (example of usage: "SELECT ID, ModuleID FROM dbo.GetModule('91')").
Note: I haven't tested the UNION yet. You might need to modify it a bit to make the union work.
CREATE FUNCTION dbo.GetModule
{
@ListID nvarchar(2000)
}
<code>--Concatenate all ChildIDs of all IDs in @ListID. Basically COALESCE is used to replace NULL with '' (empty string)</code>
DECLARE @ChildIDs varchar(100);
SELECT @ChildIDs = COALESCE (@ChildIDs + ', ', '') + ChildIDs
FROM tblModule WHERE ID IN (SELECT value FROM dbo.Split(@ListID)))
<code>-- Selects the Parent's ID and ModuleID, then UNION with ChildIDs' result if there are ChildIDs</code>
IF @ChildIDs = null
SELECT ID, ModuleID --This is to return only parent result if there are no ChildIDs
FROM tblModule
WHERE ID IN (SELECT value FROM dbo.Split(@ListID)
ELSE
SELECT ID, ModuleID
FROM tblModule
WHERE ID IN (SELECT value FROM dbo.Split(@ListID)
UNION --This is the recursive code to union with result of ChildIDs
SELECT ID, ModuleID
FROM dbo.GetModule(@ChildIDs)
RETURN
Of course, you also need the Split UDF to split your ChildIDs into array of IDs that is acceptable in SQL:
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (value)
SELECT
Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)))
Set @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
END
INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))
RETURN
END
Signature
The only valid use of the BLINK tag: Schrodinger's cat is <blink>not dead .
-- modified at 1:38 Tuesday 20th September, 2005
|
|
|
|
|
Hi edbert!
Now that u've mentioned it, it does look like an interesting problem. i had an inital impression that it was pretty much exhausting.
well im glad u understand my problem...i was strugling on how i could explain that as clear as posible im not even sure it was but thanx for ur reply.
I got the concept on what ur telling me. I've to create a function that separates my childIDs and loops until every single one is displayed. impressive thinking! unfortunately i didnt quite understand the code you posted. My bad i forgot to tell you im using MySQL database and vb.Net as my backup code i just figured this is a query problem, why not post it in SQL thread . and to top thing of im still a beginner programer . I wasnt even sure if...then statements were available in SQL commands. If its not too much trouble and if u've the spare time could you pls explain briefly the code you posted. but if u cant i'd understand completly.
thanx so much for your help edbert! i realy appreciate it!
Aim small, miss small...
|
|
|
|
|
Well, I'll try to explain as much as I can.
Basically there are 2 functions there (GetModule and Split).
The Split functions is used to convert from text (e.g. '1,2,3,4') into a table with 2 columns (Id, Value).
So, splitting the value of '1,2,3,4' will result in:
Id | Value
1 1
2 2
3 3
4 4
This is because SQL statement CAN accept e.g. SELECT * FROM [TABLE] WHERE ID IN '1,2,3,4' but it CAN'T ACCEPT SELECT @ FROM [TABLE] WHERE ID IN @ListOfIDs .
Notice that the only difference is @ListOfIDs which contains the value '1,2,3,4' as varchar/string.
Therefore, @ListOfIDs need to be split into several rows using the Split UDF before it can be used in an SQL WHERE condition.
You don't need to dwell too deep in Split function right now, but if you need further explanation let me know.
The second function, GetModule, is used to get the Id and Module of the parent row (the first ID you specified), and then tries to concatenate the children ids into the result.
CREATE FUNCTION dbo.GetModule
{
@ListID nvarchar(2000)
}
The code above basically says we want to create a User Defined Function called GetModule belonging to dbo user. It accepts a parameter of nvarchar (or unicode string) with length of 2000.
DECLARE @ChildIDs varchar(100);
The above declares a variable called @ChildIDs which can have a varchar up to 100 in size.
--Concatenate all ChildIDs of all IDs in @ListID. Basically COALESCE is used to replace NULL with '' (empty string)
SELECT @ChildIDs = COALESCE (@ChildIDs + ', ', '') + ChildIDs
FROM tblModule WHERE ID IN (SELECT value FROM dbo.Split(@ListID)))
The above tries to select all the ChildIDs columns from the tblModule and combines them, so for example if your ID (in the @ListID variable) is 91, then it will result in '911,912,913,915'.
But if your @ListID contains '90, 91' it will return the ChildIDs as a combined string which is '901' + '911,912,913,915' ending up in '901,911,912,913,915'.
COALESCE is basically a function that checks if @ChildIDs is null then return '' instead of returning null. You can replace it with IsNull function.
After we get all the child ids of our rows, we then check whether there are child ids or not.
-- Selects the Parent's ID and ModuleID, then UNION with ChildIDs' result if there are ChildIDs
IF @ChildIDs = null
SELECT ID, ModuleID --This is to return only parent result if there are no ChildIDs
FROM tblModule
WHERE ID IN (SELECT value FROM dbo.Split(@ListID)
The function above says if there are no child ids, then just return the parents' ID and ModuleID (e.g. if the parent ID is 91, it will return '91', null).
ELSE
SELECT ID, ModuleID
FROM tblModule
WHERE ID IN (SELECT value FROM dbo.Split(@ListID)
UNION --This is the recursive code to union with result of ChildIDs
SELECT ID, ModuleID
FROM dbo.GetModule(@ChildIDs)
In the code above we check if there are child ids, then we should combine (UNION) both the parent result and the children results. The children results will be taken by calling the GetModule and passing the children's ids of the parent's id.
AFAIK, IF and ELSE also exists in MySQL as they are standard SQL syntax. Coalesce might not, but you can replace it with IsNull or a similar function that checks if a value is null then return something else.
That's my explanation so far.
Try each part of the code one by one to have a better understanding of them (e.g. the split function, the function to return child ids).
I hope that helps.
Ed
|
|
|
|
|
wat can i say...have u ever think of authoring an article about certain tutorials? crystal ed!
Its amazing how one can learn tons of stuff online...and they say computers were a waste of time. But with explanations like this how can one argue with the results
thanx ed! the way i see it, this program is just beginning...
Aim small, miss small...
|
|
|
|
|
No worries. I might actually write a tutorial on SQL tricks because I see that some people might need it.
Just one word of note, try learning update with parameters in ADO.Net, creating stored procedures and user defined functions.
You'll need them as a programmer.
Ed
|
|
|
|
|