Problems in selecting record.

I have a table A which has the following structure:
Column Name(Type)
-- --
ID(int)
Repair Code(varchar)
Damage Code(varchar)
Location Code (varchar)
I also have a the master code table
Column Name(Type)
-- --
Code Type (int)
Code Name (varchar)
Code Description ( varchar)
and have a stored procedure called GetCodeDescription which input the Code
Type and Code Name , display the Code Description found in the code master
table.
I want to select the record from TabelA by inputting a ID , then select the
record which have the follwong structure
Column Name(Type)
--
ID (int)
Repair Code (varchar)
Repair Code Description (varchar)
Damage Code (varchar)
Damage Code Description (varchar)
Location Code (varchar)
Location Code Description (varchar)
How can I do it by using the GetCodeDescription stored procedure?
Thnak you very much !Trying to use the stored procedure is only going to make things much,
much more complicated than necessary. How about just doing a query?
SELECT A.ID,
A.RepairCode, B.COdeDescription as RepairDescription,
A.DamagerCode, C.COdeDescription as DamageDescription,
A.LocationCode, D.COdeDescription as LocationDescription
FROM TableA as A
JOIN MasterCodes as B
ON A.RepairCode = B.CodeName
AND B.CodeType = 1
JOIN MasterCodes as C
ON A.DamageCode = C.CodeName
AND C.CodeType = 2
JOIN MasterCodes as D
ON A.LocationrCode = D.CodeName
AND D.CodeType = 3
Roy
On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
<BallBall@.discussions.microsoft.com> wrote:

>I have a table A which has the following structure:
>Column Name(Type)
>-- --
>ID(int)
>Repair Code(varchar)
>Damage Code(varchar)
>Location Code (varchar)
>I also have a the master code table
>Column Name(Type)
>-- --
>Code Type (int)
>Code Name (varchar)
>Code Description ( varchar)
>and have a stored procedure called GetCodeDescription which input the Code
>Type and Code Name , display the Code Description found in the code maste
r
>table.
>I want to select the record from TabelA by inputting a ID , then select the
>record which have the follwong structure
>Column Name(Type)
>--
>ID (int)
>Repair Code (varchar)
>Repair Code Description (varchar)
>Damage Code (varchar)
>Damage Code Description (varchar)
>Location Code (varchar)
>Location Code Description (varchar)
>How can I do it by using the GetCodeDescription stored procedure?
>Thnak you very much !
>|||Thank for the answer , but because my code table container about 12000
records, if i join many times , i think the performance will be affected
"Roy Harvey" wrote:

> Trying to use the stored procedure is only going to make things much,
> much more complicated than necessary. How about just doing a query?
> SELECT A.ID,
> A.RepairCode, B.COdeDescription as RepairDescription,
> A.DamagerCode, C.COdeDescription as DamageDescription,
> A.LocationCode, D.COdeDescription as LocationDescription
> FROM TableA as A
> JOIN MasterCodes as B
> ON A.RepairCode = B.CodeName
> AND B.CodeType = 1
> JOIN MasterCodes as C
> ON A.DamageCode = C.CodeName
> AND C.CodeType = 2
> JOIN MasterCodes as D
> ON A.LocationrCode = D.CodeName
> AND D.CodeType = 3
> Roy
>
> On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
> <BallBall@.discussions.microsoft.com> wrote:
>
>|||Try the join first. You will probably find the performance is just fine,
and simpler to manage.
"BallBall" <BallBall@.discussions.microsoft.com> wrote in message
news:36B2D0B0-6201-42BE-933B-079D4FCE44DE@.microsoft.com...
> Thank for the answer , but because my code table container about 12000
> records, if i join many times , i think the performance will be affected
> "Roy Harvey" wrote:
>
Code
master
the|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Your personal narrative and pseudo-code are useless.
That would be a horrible design error! Go back to the foundations; a
table is made up of one and only one kind of entity. There is no such
thing as "master code table" in a valid schema.
Next, a data element can be a type or a code but not a "type_code";
read ISO-11179. Next you confuse rows with records and columns with
fields and do not bother to give us table names.
Finally, how do you get enough codes into a VARCHAR(1) column? In a
good design, codes are fixed length and have a validation rule. There
is no such thing as a magical universal "id" in RDBMS; you are not
actually using IDENTITY in an RDBMS, are you'
I have to make a wild guess about the key
CREATE TABLE RepairRequests
(repair_nbr INTEGER NOT NULL,
repair_code INTEGER NOT NULL
REFERENCES Repairs (repair_code)
ON UPDATE CASCADE,
PRIMARY KEY (repair_nbr, repair_code),
damage_code INTEGER NOT NULL
REFERENCES Damages(damage_code)
ON UPDATE CASCADE,
location_code INTEGER NOT NULL
REFERENCES Locations (location_code)
ON UPDATE CASCADE);
CREATE TABLE Repairs
(repair_code INTEGER NOT NULL PRIMARY KEY,
repair_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Damages
(damage_code INTEGER NOT NULL PRIMARY KEY,
damage_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Locations
(location_code INTEGER NOT NULL PRIMARY KEY,
location_description VARCHAR(20) NOT NULL,
.) ;
I have no idea; where is the code for this stored procedure?
I see that you also do not understand what a repeated group is and how
to program in a tiered architecture. This should be done as a simple
query. But assuming that you really do hate RDBMS, how do you handle
a repair request with more or less than two damages on it?|||>Thank for the answer , but because my code table container about 12000
>records, if i join many times , i think the performance will be affected
If performance is bad the table is not indexed correctly. Also it is
not necessary to guess what performance will be, all you have to do is
run the queries in Query Analyzer to see what it is. Easy, quick, and
you can learn a lot.
Twelve thousand rows is really all that large, by the way. With
proper indexing performance should be fine with ten or a hundred times
as many rows.
Roy

problems in editing a record... why? help pls....

can someone help me why it produces an error...

error is: ERROR 22001 Microsoft ODBC SQL Server Driver SQL Server STRING or data of BINARY was cut short.
ERROR 01000 Microsoft ODBC SQL Server Driver SQL Server statement was ended.

This is my code here for editing a record...

Protected Sub Button_save2_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_save2.Click
'||||| Create string connection
Dim StrConn As String = "Dsn=MS_PKG01;UID=emiline;APP=Microsoft? Visual Studio? 2005;WSID=MSHNP200603;DATABASE=MS_PKG01;Trusted_Connection=Yes"
'||||| Create connection object
Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn)

'||||| Open connection
MyConn.Open()

'|||| Create odbcCommand object
Dim Update_record As New Odbc.OdbcCommand("UPDATE TM0001 SET TM0001.syain_name = ?, TM0001.syain_pass = ?, TM0001.office_id = ?, TM0001.birth_date = ?, TM0001.empl_date = ?, TM0001.user_iden = ? ", MyConn)

Dim hireYear As String
Dim hireMonth As String
Dim hireDay As String
Dim date_hire As String

hireYear = DropDownList_hire_yr.Text
hireMonth = DropDownList_hire_mo.Text
hireDay = DropDownList_hire_day.Text

date_hire = hireYear + "/" + hireMonth + "/" + hireDay

'|||| Add command parameters
Update_record.Parameters.Add("@.P1", OdbcType.Char, 8).Value = TextBox_id.Text
Update_record.Parameters.Add("@.P2", OdbcType.Char, 20).Value = TextBox_name.Text
Update_record.Parameters.Add("@.P3", OdbcType.Char, 20).Value = TextBox_pswd.Text
Update_record.Parameters.Add("@.P3", OdbcType.Char, 40).Value = DropDownList_office.SelectedValue
Update_record.Parameters.Add("@.P3", OdbcType.Char, 2).Value = date_hire
Update_record.Parameters.Add("@.P3", OdbcType.Char, 10).Value = TextBox_bday.Text
Update_record.Parameters.Add("@.P3", OdbcType.Char, 1).Value = DropDownList_iden.SelectedValue

'|||| Execute command
Update_record.ExecuteNonQuery()

'|||| Close connection
MyConn.Close()
End Sub
End Class

I modify some codes already but still same error...

my code:

'||||| Create connection object
Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn)

'|||| Create odbcCommand object
Dim Update_record As New Odbc.OdbcCommand("UPDATE TM0001 SET TM0001.syain_name = ?, TM0001.syain_pass = ?, TM0001.office_id = ?, TM0001.birth_date = ?, TM0001.empl_date = ?, TM0001.user_iden = ? where syain_id = ? ", MyConn)

id_user = TextBox_id.Text
name = TextBox_name.Text
pass = TextBox_pswd.Text
office_id = DropDownList_office.SelectedValue
bday = TextBox_bday.Text
iden = DropDownList_iden.SelectedValue

hireYear = DropDownList_hire_yr.Text
hireMonth = DropDownList_hire_mo.Text
hireDay = DropDownList_hire_day.Text

date_hire = hireYear + "/" + hireMonth + "/" + hireDay

'|||| Add command parameters

Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = name
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = pass
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = office_id
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = date_hire
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = bday
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = iden
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = id_user

'||||| Open connection
MyConn.Open()

'|||| Execute command
Update_record.ExecuteNonQuery()

'|||| Close connection
MyConn.Close()

|||

Hi,

i think a textfield has more data as permitted, so the update command crashed. You can inspect the prepared SQLCommand inside the Update_record object and try to execute this command direct in the database (Query Analyzer or Database Command Prompt Interface).

Regards
Marc Andre

|||

hmmm i already check it and there is nothing wrong...

and whenever i input a new record it always satisfy the condition in the data type...

but still i dont why when i edit it generates error in my code...

hmmm another thing that i observe in my code is that when

i click the save button when editing it doesnt change anything...but the first record or the initial record always shows...

i tried putting is postback but nothing happens...

everytime i press the save button to saved my changes in the data the initial entry shows...

coz in my pageload the initial entry or the first record in the dabse is shown...

hmmm what do u think?

problem with xml parsing in enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But while retrive records from queues it shows the following err

XML parsing: line 0, character 0, unrecognized input signature

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: XML parsing: line 0, character 0, unrecognized input signature

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

when we use ths following query we r getting records properly

(select * from sys.transmission_queue )

For solving this we have used following commands

USE master ;

GO

ALTER DATABASE database name SET ENABLE_BROKER ;

alter ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname'

use dbname
select * from sys.transmission_queue order by enqueue_time desc

create master key encryption by password = 'pass@.word1';

alter AUTHORIZATION ON DATABASE::[dbname] TO [sa];

but unable to solve it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

The error you show is a client side error (ADO.Net error), not a server error. It seems that you're trying to read a column that is not XML type through an SqlXml datatype. In adition it seems that you're using a Web Service (HTTP endpoint?) to access the server. Is this true?

BTW, messages should be dequeue using RECEIVE from the target service's queue, not select from sys.transmission_queue

|||

Thanx for giving right solution.

Ofcourse its problem with column size.

i resolved it.

Thanx..

Ali

problem with xml parsing in enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But while retrive records from queues it shows the following err

XML parsing: line 0, character 0, unrecognized input signature

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: XML parsing: line 0, character 0, unrecognized input signature

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

when we use ths following query we r getting records properly

(select * from sys.transmission_queue )

For solving this we have used following commands

USE master ;

GO

ALTER DATABASE database name SET ENABLE_BROKER ;

alter ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname'

use dbname
select * from sys.transmission_queue order by enqueue_time desc

create master key encryption by password = 'pass@.word1';

alter AUTHORIZATION ON DATABASE::[dbname] TO [sa];

but unable to solve it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

The error you show is a client side error (ADO.Net error), not a server error. It seems that you're trying to read a column that is not XML type through an SqlXml datatype. In adition it seems that you're using a Web Service (HTTP endpoint?) to access the server. Is this true?

BTW, messages should be dequeue using RECEIVE from the target service's queue, not select from sys.transmission_queue

|||

Thanx for giving right solution.

Ofcourse its problem with column size.

i resolved it.

Thanx..

Ali

problem with xml parsing in enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But while retrive records from queues it shows the following err

XML parsing: line 0, character 0, unrecognized input signature

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: XML parsing: line 0, character 0, unrecognized input signature

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

when we use ths following query we r getting records properly

(select * from sys.transmission_queue )

For solving this we have used following commands

USE master ;

GO

ALTER DATABASE database name SET ENABLE_BROKER ;

alter ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname'

use dbname
select * from sys.transmission_queue order by enqueue_time desc

create master key encryption by password = 'pass@.word1';

alter AUTHORIZATION ON DATABASE::[dbname] TO [sa];

but unable to solve it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

The error you show is a client side error (ADO.Net error), not a server error. It seems that you're trying to read a column that is not XML type through an SqlXml datatype. In adition it seems that you're using a Web Service (HTTP endpoint?) to access the server. Is this true?

BTW, messages should be dequeue using RECEIVE from the target service's queue, not select from sys.transmission_queue

|||

Thanx for giving right solution.

Ofcourse its problem with column size.

i resolved it.

Thanx..

Ali

problem with updating record using xml

hi,
i have the following tables
client
clientID IDENTITY
FirstName
LastName
AddressId
Contact
AddressID
address1
address2
city
state
country
i have two system S1 and S2 , both will be in a different places.
both s1 and s2 can add client data. remember clientId is identity field.
once in a interval S2 will generate a xml file and upload into a server
(third system
S3) now S1 will get the XML file from S3 i.e. server.
now S1 has to update all those records from XML which is sent by S2.
the problem is both S1 and S2 would have clientID with same id but with
different
informations.
if i update records in S1 using ClientID from S2, then records which have same
ID will get affected.
in this scanario how can i update records in S1...
after some days again S2 will upload xml file , and that file should also
be updated again without affecting records entered in S1.
can anyone help in this regard
Hi Thiru,
It really depends on how you've implemented the process. Please provide more
details of what technology you are using to achieve this.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Thiru.S" <Thiru.S@.discussions.microsoft.com> wrote in message
news:F5AE4559-8377-4E0D-8C1A-CD7B5820ED67@.microsoft.com...
> hi,
> i have the following tables
> client
> --
> clientID IDENTITY
> FirstName
> LastName
> AddressId
> Contact
> --
> AddressID
> address1
> address2
> city
> state
> country
>
> i have two system S1 and S2 , both will be in a different places.
> both s1 and s2 can add client data. remember clientId is identity field.
> once in a interval S2 will generate a xml file and upload into a server
> (third system
> S3) now S1 will get the XML file from S3 i.e. server.
> now S1 has to update all those records from XML which is sent by S2.
> the problem is both S1 and S2 would have clientID with same id but with
> different
> informations.
> if i update records in S1 using ClientID from S2, then records which have
> same
> ID will get affected.
> in this scanario how can i update records in S1...
> after some days again S2 will upload xml file , and that file should also
> be updated again without affecting records entered in S1.
> can anyone help in this regard
>
>
>

problem with updating record using xml

hi,
i have the following tables
client
--
clientID IDENTITY
FirstName
LastName
AddressId
Contact
--
AddressID
address1
address2
city
state
country
i have two system S1 and S2 , both will be in a different places.
both s1 and s2 can add client data. remember clientId is identity field.
once in a interval S2 will generate a xml file and upload into a server
(third system
S3) now S1 will get the XML file from S3 i.e. server.
now S1 has to update all those records from XML which is sent by S2.
the problem is both S1 and S2 would have clientID with same id but with
different
informations.
if i update records in S1 using ClientID from S2, then records which have sa
me
ID will get affected.
in this scanario how can i update records in S1...
after some days again S2 will upload xml file , and that file should also
be updated again without affecting records entered in S1.
can anyone help in this regardHi Thiru,
It really depends on how you've implemented the process. Please provide more
details of what technology you are using to achieve this.
thanks
Chandra
---
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Thiru.S" <Thiru.S@.discussions.microsoft.com> wrote in message
news:F5AE4559-8377-4E0D-8C1A-CD7B5820ED67@.microsoft.com...
> hi,
> i have the following tables
> client
> --
> clientID IDENTITY
> FirstName
> LastName
> AddressId
> Contact
> --
> AddressID
> address1
> address2
> city
> state
> country
>
> i have two system S1 and S2 , both will be in a different places.
> both s1 and s2 can add client data. remember clientId is identity field.
> once in a interval S2 will generate a xml file and upload into a server
> (third system
> S3) now S1 will get the XML file from S3 i.e. server.
> now S1 has to update all those records from XML which is sent by S2.
> the problem is both S1 and S2 would have clientID with same id but with
> different
> informations.
> if i update records in S1 using ClientID from S2, then records which have
> same
> ID will get affected.
> in this scanario how can i update records in S1...
> after some days again S2 will upload xml file , and that file should also
> be updated again without affecting records entered in S1.
> can anyone help in this regard
>
>
>