|
Hello
I have an application writing Japanese characters to the database. When I use MS SQL 2000, it works fine - the complete string is stored, but when I use MSDE, the strings seems to be truncated
Any ideas? Is there any setting in MSDE that I'm overlooking?
Thanks for any help
Chandra
|
|
|
|
|
Hi Guru's,
What's the trim command on SQL?
Thanks
Dom;)
|
|
|
|
|
Hi,
Using trim in SQL 2000 - ltrim() and rtrim() as given below: removes trailing and leading blanks. Hope this helps you.
For Example:
------------
use pubs
go
select ltrim(rtrim(fname)) from
employee
go
- Harini
|
|
|
|
|
Thanks Harini
|
|
|
|
|
I have a table that stores two types of update records. At the moment I'm using a bit field to determine the update type, e.g. IsStatusUpdate. This value is 0 for comment updates and 1 for status updates. It has been bothering me recently that this could be made a char(1) field, with the more readable 'S' for status updates and 'C' for comment updates.
My only misgiving here though is the use of a literal value that is seemingly arbitrary to the user. With 'IsStatuUpdate bit, not null', I know I must store 1 for a status update. With 'UpdateType char(1), not null', I don't know that I must store 'S' for a status update. Any thoughts on this?
|
|
|
|
|
Bit fields will required less storage.
Also better when using with code (C# for example) because you'll need to deal with true,false. not magic values (S,C).
In the GUI, you can use a check box.
I think it's always better to separate how you display data from how you store it.
|
|
|
|
|
If I have a DataRow and a foreign key constraint (1:N), is there a way to get a DataTable with all of the childs of the relationship?
e.g. if I have
DepartmentDataTable dep = ...
EmployeeDataTable emp = ...
ForeignKeyConstraint fk = new ForeignKeyConstraint(dep.IDColumn, emp.DepartmentIDColum);
DepartmentRow hq = dep.FindByID(666);
EmployeeDataTable allHQEmployees = magic</>(hq, fk);
?
Developers, Developers, Developers, Developers, Developers, Developers, Velopers, Develprs, Developers! We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP Linkify!|Fold With Us!
|
|
|
|
|
Have you tried using the DataRow.GetChildRows() method? If you define a DataRelation instead of a ForeignKeyConstraint (which will be added along with the DataRelation) you can use DataRow.GetChildRows() to get a DataRow[] reference. Once you have DataRow[] you could instantiate a new EmployeeDataTable and copy DataRow[] into the new data table using the DataTable.ImportRow() method.
You could alternatively do something using a DataView, sort by DepartmentID and call DataView.FindRows(), then perform the same copy operation I mentioned above.
But other than creating a new table object and copying rows from one table to the next there is no way to return a new data table based on a "filter" of an existing table.
|
|
|
|
|
If I call GetData() on an System.Data.OleDb.OleDbAdapter , does that actually read all data from the database?
Or does it just open the rowset, and the data is read only when I access the actual rows?
Developers, Developers, Developers, Developers, Developers, Developers, Velopers, Develprs, Developers! We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP Linkify!|Fold With Us!
|
|
|
|
|
System.Data.OleDb.OleDbAdapter doesn't exist, but System.Data.OleDb.OleDbDataAdapter does. But it doesn't have a GetData() method, it has a fill method which loads a disconnected dataset or datatable will all the results of the query and then disconnects (if the connection was closed before Fill() was called.
|
|
|
|
|
Is it possible to have a constraint that depends on a column value? For instance, I would like my DocUpdate table to require NewDocStatus is not null when IsStatusUpdate = 1, but allow NewDocStatus to be null in other records.
|
|
|
|
|
Using a check constraint you can do the following:
<br />
CREATE TABLE myChkTable (IsStatusUpdate BIT, NewDocStatus VARCHAR(25))<br />
go<br />
<br />
ALTER TABLE myChkTable ADD CONSTRAINT chkTblConstraint CHECK (<br />
(IsStatusUpdate = 1 AND NewDocStatus IS NOT NULL) OR IsStatusUpdate = 0<br />
)<br />
go<br />
<br />
--the first three will succeed<br />
INSERT INTO myChkTable VALUES (0, null)<br />
INSERT INTO myChkTable VALUES (1, 'status')<br />
INSERT INTO myChkTable VALUES (0, 'not update')<br />
<br />
--this will fail<br />
INSERT INTO myChkTable VALUES (1, null)<br />
<br />
|
|
|
|
|
I don't mean to sound stupid.
But what does the term "MRD" stands for in SQL Server?
Does anybody know?
Thanks.
R
|
|
|
|
|
Hi,
For the life of me, I haven't been able to figure the following out and I hoping someone out there can offer some good advice.
I have an ASP page that lists a lot of manuals and their associated new features for that particular release.
For example:
Manual title New Feature
manual 1 new feature1
manual 1 new feature2
manual 1 new feature3
manual 2 new feature1
manual 3 new feature1
manual 3 new feature2
Rather than have the same manual listed consecutively with a different new feature association, I would like to list the results as such:
Manual title New Feature
manual 1 new feature1
new feature2
new feature3
manual 2 new feature1
manual 3 new feature1
new feature2
Is this possible using ASP and SQL?
Here's the stored procedure:
<code>CREATE PROCEDURE [docadmin1].[sp_view_nfprodrel]
@product nvarchar (250)
AS
SELECT distinct top 100 Percent doc_id, title, relnum, substring(dn,3,14) as DN, product, New_Feature FROM nf_prodrel_man WHERE product = @product order by Title
GO</code>
Here's the view being called from the stored procedure:
<code>CREATE VIEW dbo.nf_prodrel_man
AS
SELECT TOP 100 PERCENT dbo.Books.Title + ' ' + dbo.Books.Rel_Num AS title, dbo.PROD_REL.Product, dbo.DOC_RELATIONSHIP.Doc_ID,
dbo.New_Feature.NF_ID, SUBSTRING(dbo.New_Feature.New_Feature, PATINDEX('% %', dbo.New_Feature.New_Feature),
LEN(dbo.New_Feature.New_Feature)) AS New_Feature, dbo.Books.DN, dbo.New_Feature.Description, RIGHT(dbo.PROD_REL.Product, 3)
AS RELNUM
FROM dbo.PROD_REL INNER JOIN
dbo.NF_PROD_REL ON dbo.PROD_REL.Prod_Rel_ID = dbo.NF_PROD_REL.Prod_REL_ID INNER JOIN
dbo.New_Feature ON dbo.NF_PROD_REL.NF_ID = dbo.New_Feature.NF_ID INNER JOIN
dbo.DOC_RELATIONSHIP ON dbo.NF_PROD_REL.REL_ID = dbo.DOC_RELATIONSHIP.REL_ID INNER JOIN
dbo.Books ON dbo.DOC_RELATIONSHIP.Doc_ID = dbo.Books.Doc_ID
ORDER BY dbo.Books.Title + ' ' + dbo.Books.Rel_Num, dbo.PROD_REL.Product</code>
Thanks in advance!
Regards,
Jenn
|
|
|
|
|
It is possible to do this in SQL, but it is much easier to acheive in the ASP page (especially as it is a presentation thing). The structure would look a bit like:
lngOldBookId = -1
For Each objRec In arrBookList
Response.Write("<tr><td>")
If lngOldBookId <> objRec.BookId Then
Response.Write(objRec.Title)
lngOldBookId = objRec.BookId
Else
Response.Write(" ")
End If
'write other bits of result table.
Next So you only print a book title when it is different from the previous record.
Hope that helps.
Andy
|
|
|
|
|
Thanks Andy. I will give a try and let you know how I fare.
Have a great day,
Jenn
|
|
|
|
|
I normalized a table in a database project I'm working on so that instead of having something like this:
Table: entities
-----------------
ID NAME RELATED_ITEM_ID_1 RELATED_ITEM_ID_2 ..... RELATED_ITEM_ID_40
1 Entity 1 1 24 67
2 Entity 3 14 NULL 45
I now have something like these two tables:
Table: entities
-----------------
ID NAME
1 Entity 1
2 Entity 3
Table: entity_items
-----------------
ENTITY_ID RELATED_ITEM_ID
1 1
1 24
1 67
2 14
2 45
I want to return a table that has a summary of the entities table returning the top three entity_items so that it will come back looking like the original (non-normalized) entities table.
I am using a MySQL database, but any help with the basic concepts and SQL here would be appreciated. I'm sure this is a common problem people deal with, but I didn't even know the terminology to use to find an answer on the web.
Thanks.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
|
Thank you. That was helpful.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hello!
I can't execue the following sql string because I am getting an error message. How can I increment the specific field in the table by one?
SQLSTR = "UPDATE setup SET TotalUser=TotalUser + 1 WHERE RecordNumber = 1"
Thanks
Journey
|
|
|
|
|
B Journey wrote: I can't execue the following sql string because I am getting an error message.
What is the error message?
|
|
|
|
|
SQLSTR = "UPDATE setup SET TotalUser=TotalUser + 1 WHERE RecordNumber = 1"
Call Initialize_Connection()
Call Execute_Statement(SQLSTR) ' the error occurs in this procedure
the procedure content is here:
Sub Execute_Statement(GetSQL)
Set ObjectRecord = Server.CreateObject ("ADODB.Recordset")
ObjectRecord.CursorLocation = adUseServer
ObjectRecord.CursorType = adOpenkeyset
ObjectRecord.LockType = adLockOptimistic
ObjectRecord.Open GetSQL, ObjectConnection,,, adCmdText 'error points this line
End Sub
Thanks
|
|
|
|
|
Okay - so at least we now know WHERE the error is. You still have not told us WHAT the error is.
What is the error message?
|
|
|
|
|
Sorry! Here is the error message. I have double checked all fields in the table nothing looks wrong. It is working on my local server but when I transfer it to server in Internet, I am getting this error message.
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/emlak/include/scripts.inc, line 138
I have included the procedure in my previous message where the error is pointing the line 138.
Thanks
|
|
|
|
|
That's a typical error when you have misspelled one of the column names (e.g. TotalUsers or RecordNumber)
Developers, Developers, Developers, Developers, Developers, Developers, Velopers, Develprs, Developers! We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP Linkify!|Fold With Us!
|
|
|
|