Restore Database: type

Showing posts with label type. Show all posts

Thursday, March 29, 2012

Help with Cross-tab queries

How would you write the SQL statement to do a cross-tab queries.

My Table
DCNID-unique ID
AuditID-FK to the main table
DCN-int
Error Type (combo box with values tooth, date, provider, etc)
Line ID (combo box with values 1-40)
Adjustment Code-text

I would like my report to look like this.
DCN
LineID Tooth Date Provider
1 1 1
2 1
3
4
5
..
40

Thank you so much!

CristyYou could have done some research, this question has been asked and aswered thousands of times, here are some solutions from asktom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:7086279412131,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:419593546543,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:766825833740,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:925229353765,
:rolleyes:|||How would you write the SQL statement to do a cross-tab queries.that depends -- what database system are you using?

Tuesday, March 27, 2012

help with Check contraints

I need to add a check contraint to a table in SQL Server 2000 that would do
the following
if the value of [type] = 1 then [year] cannot be null
is this possible to do? because for the other values of [type] i want the
[year] to be null
thanks for any help!
benALTER TABLE your_table
ADD CONSTRAINT CK_your_table__type_year_match
CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
Jacco Schalkwijk
SQL Server MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>I need to add a check contraint to a table in SQL Server 2000 that would do
> the following
> if the value of [type] = 1 then [year] cannot be null
> is this possible to do? because for the other values of [type] i want the
> [year] to be null
> thanks for any help!
> ben|||Thank you very much. That was exactly what i was looking for
"Jacco Schalkwijk" wrote:

> ALTER TABLE your_table
> ADD CONSTRAINT CK_your_table__type_year_match
> CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>
>|||Sorry, one more question about check contraints.
is it possible to have a contraint that only allowed entries where for every
pair of columns [a] and [b] there is only 1 value in the [type] column? or
can this only be done through stored procedures and functions?
thanks again
"Jacco Schalkwijk" wrote:

> ALTER TABLE your_table
> ADD CONSTRAINT CK_your_table__type_year_match
> CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>
>|||Please explain "only 1 value in the [type] column".
ML|||well, ill have 3 possible valuse for type: 1,2,3. the other columns
category_ID, area_ID are used to specify a location in a grid like system.
i
want there to only be 1 value in the type column for every combination of
category_id and area_id. this will have to be similar to a query on the
entire table to ensure that the pair doesnt have an entry with type = 1 and
a
entry with type = 2 but multiple entries of type=1 is alowed.
i hope that helped a little
"ML" wrote:

> Please explain "only 1 value in the [type] column".
>
> ML|||On Fri, 28 Oct 2005 08:16:08 -0700, Ben <ben_1_ AT hotmail DOT com>
wrote:

>Sorry, one more question about check contraints.
>is it possible to have a contraint that only allowed entries where for ever
y
>pair of columns [a] and [b] there is only 1 value in the [type] column? or
>can this only be done through stored procedures and functions?
Hi Ben,
UNIQUE (a, b, type)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||that is a valid check contraint? i tried that and it was giving me errors.
sql server 2k. also, i dont think that will allow me to have multiple type=
1
values for each a,b pair. I need that to be allowed, but the type values
cannot be different.
"Hugo Kornelis" wrote:

> On Fri, 28 Oct 2005 08:16:08 -0700, Ben <ben_1_ AT hotmail DOT com>
> wrote:
>
> Hi Ben,
> UNIQUE (a, b, type)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||In that case you should have a separate table with (category_ID, area_ID) as
the Primary Key and Type as the other column. Your database is not properly
normalised, and this will cause all kinds of problems.
Feel free to post your table definitions and a description of your business
problem, and people will give you advise on how to improve your database.
In the mean time, if you don't have the scope or authority to make these
changes, you can apply a band aid with an indexed view (untested):
CREATE vw_chk_your_table
WITH SCHEMA_BINDING
AS
SELECT category_ID, area_ID, Type, COUNT_BIG(*) AS cnt
FROM your_table
GROUP BY category_ID, area_ID, Type
GO
CREATE UNIQUE CLUSTERED INDEX ixc_vw_chk_your_table
ON vw_chk_your_table (category_ID, area_ID)
Jacco Schalkwijk
SQL Server MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:45CE651A-D484-4FD1-8230-1D64BA2E34E8@.microsoft.com...
> well, ill have 3 possible valuse for type: 1,2,3. the other columns
> category_ID, area_ID are used to specify a location in a grid like system.
> i
> want there to only be 1 value in the type column for every combination of
> category_id and area_id. this will have to be similar to a query on the
> entire table to ensure that the pair doesnt have an entry with type = 1
> and a
> entry with type = 2 but multiple entries of type=1 is alowed.
> i hope that helped a little
> "ML" wrote:
>|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 52
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newshub.sd
su.edu!newsfeed.news2me.com!newsfeed2.easynews.com!newsfeed1.easynews.com!ea
synews.com!easynews!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-02!sn-post-01!supe
rnews.com!corp.supernews.co
m!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:562181
On Fri, 28 Oct 2005 23:44:03 -0700, Ben <ben_1_ AT hotmail DOT com>
wrote:
(cut topposting)
>"Hugo Kornelis" wrote:
>
(paste topposting)
>that is a valid check contraint? i tried that and it was giving me errors.
>sql server 2k.
Yes, it's valid. What were the errors you got? And what was the exact
text of the complete statement you used it in?

>also, i dont think that will allow me to have multiple type=1
>values for each a,b pair. I need that to be allowed, but the type values
>cannot be different.
Re-reading what you write, I now see I misinterpreted your question. The
asnwer to your original question should have been:
UNIQUE (a, b)
That would allow only 1 value for type for every pair of values for a
and b, as you originally requested. As such, it would NOT allow multiple
type=1 for a a,b pair, since multiple type=1 is incompatible with "only
1 value in the [type] column".
At this point, I have sincere doubts if these columns really should be
combined in the same table at all. But I must also admit that I'm no
longer sure if I actuallly understand your requirement. It might help if
you posted a few concrete examples of rows of data that can or can not
be in the table at the same time. It would also be a tremendous help if
you could explain the actual business problem that you're trying to
solve.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with case when

I need help.
This is the code I currently have and need to change:
p.code as "Code",
p.detail_type as "Detail Type",

p.code shows diagnostic code numbers and billing procedure numbers
while p.detail_type indicate which is which. Diagnostic code numbers
are designated as '-2' (minus 2) in the 'detail_type' and procedure
codes are designated as '-4' (minus 4) in the 'detail_type.' The query
I am using now (see below) gives me duplicate appt dates to show both
the diagnostic code and procedure code. I need to clean this up a bit.

What I want this to do is find a statement that will separate the codes
into diagnosis numbers and procedure numbers and place these numbers in
different columns in the ad hoc report that is generated.

I would like something to the effect of:
If p.detail_type = -2 then place the code number in a column known as
"Code"
If p.detail_type - -4 then place the code number in a column known as
"Procedure"

Any ideas on how to write this?

Select
/* Individual Client Task List */

a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
p.code as "Code",
p.detail_type as "Detail Type",
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

Quote:

Originally Posted by

>From Appointments a, Patientmedicalrecords p

Thanks for your help. dwerden.dwerden (dwerden@.purdue.edu) writes:

Quote:

Originally Posted by

I need help.
This is the code I currently have and need to change:
p.code as "Code",
p.detail_type as "Detail Type",
>
p.code shows diagnostic code numbers and billing procedure numbers
while p.detail_type indicate which is which. Diagnostic code numbers
are designated as '-2' (minus 2) in the 'detail_type' and procedure
codes are designated as '-4' (minus 4) in the 'detail_type.' The query
I am using now (see below) gives me duplicate appt dates to show both
the diagnostic code and procedure code. I need to clean this up a bit.
>
>
What I want this to do is find a statement that will separate the codes
into diagnosis numbers and procedure numbers and place these numbers in
different columns in the ad hoc report that is generated.
>
I would like something to the effect of:
If p.detail_type = -2 then place the code number in a column known as
"Code"
If p.detail_type - -4 then place the code number in a column known as
"Procedure"
>
Any ideas on how to write this?

CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, Erland. Your code separated them like I wanted but I continue
to get 2 entries for each actual appointment (as shown below).

Provider--Session Date--Session Type--Code--Proceure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--(empty)--Done--bb
Bossick--7/6/2006--1 hr Session--(empty)--99212--Done--bb

Is there a way to force these to combine into only one entry like this?

Provider--Session Date--Session Type--Code--Procedure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--99212--Done--bb

Erland Sommarskog wrote:

Quote:

Originally Posted by

CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Select
/* Individual Client Task List */

a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

Quote:

Originally Posted by

>From Appointments a, Patientmedicalrecords p

Where a.uniquenumber = p.appt_uniquenumber
and a.Division = 3
and a.Inactive = 0
and a.Personal = 0
and a.Ingroup = 0
and a.Appttype not like 'TELE'
and a.Apptdate between '1-Jul-2006' and sysdate - 1
and a.Appttype not in ('AEP2','AEP4','AOD','TOCE2','TOCE4','ETOH2
Class','AEP4 Class','AOD1 Class')
and (substr(a.Complaint1,1,2) = 'TS'
or a.Complaint1 like 'Secretary Signed'
or a.Complaint1 is null
or a.Signoffinits is null)
/* Next line excludes Pyle, Kyler, Aunt Bee, Rowdy */
and a.Patientnumber not in ('57629','82362','125163','139842')
Order by
a.Provider,
a.Apptdate,
a.Lastname|||Assuming there is at most one row in Patientmedicalrecords with detail_type
= -2 and at most one with detail_type = -4 for each matching row in
Appointments, then
Change:
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,

To:
Coalesce(p1.Code,'') As Code,
Coalesce(p2.Code,'') As Procedure,

And change:
From Appointments a, Patientmedicalrecords p
Where a.uniquenumber = p.appt_uniquenumber
and a.Division = 3
and a.Inactive = 0
<rest of where conditions>

to:

From Appointments a
Left Outer Join Patientmedicalrecords p1 On a.uniquenumber =
p1.appt_uniquenumber
And p1.detail_type = -2
Left Outer Join Patientmedicalrecords p1 On a.uniquenumber =
p12.appt_uniquenumber
And p1.detail_type = -4
Where a.Division = 3
and a.Inactive = 0
<rest of where conditions>

Tom

"dwerden" <dwerden@.purdue.eduwrote in message
news:1154457844.644843.182100@.m73g2000cwd.googlegr oups.com...

Quote:

Originally Posted by

Thanks, Erland. Your code separated them like I wanted but I continue
to get 2 entries for each actual appointment (as shown below).
>
Provider--Session Date--Session Type--Code--Proceure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--(empty)--Done--bb
Bossick--7/6/2006--1 hr Session--(empty)--99212--Done--bb
>
Is there a way to force these to combine into only one entry like this?
>
Provider--Session Date--Session Type--Code--Procedure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--99212--Done--bb
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

> CASE p.detail_type WHEN -2 THEN p.code END AS Code,
> CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
Select
/* Individual Client Task List */
>
a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

Quote:

Originally Posted by

>>From Appointments a, Patientmedicalrecords p

|||>p.code shows diagnostic code numbers and billing procedure numbers while p.detail_type indicate which is which. <<

Stop writing code like this. You never cram two or more attributes
into one column. Hey, why not have a column for "squids and
automobiles", too?

What you have done is re-invent the variant record from COBOL, FORTRAN,
Pascal, etc. and other procedural languages.|||That's a great idea!

I think I remember seeing a squid looking automobile thingy in some animated
film recently.

Joe, Did you create the database used by the film company?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1154462822.641976.57700@.m73g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>p.code shows diagnostic code numbers and billing procedure numbers while
>>p.detail_type indicate which is which. <<

>
Stop writing code like this. You never cram two or more attributes
into one column. Hey, why not have a column for "squids and
automobiles", too?
>
What you have done is re-invent the variant record from COBOL, FORTRAN,
Pascal, etc. and other procedural languages.
>

Help with Case

I have the following data with following conditions:

Old Type New Type

Number: 01 in table1 with Connection : 'J' in table2 'GE'

Number: 01 in table1 with Connection : 'K' in table2 'GPE'

Number: 01 in table1 with Connection: 'L' In table2 'GPA'

Number: 02 in table1 with Connection: 'I' in table2 'I02'

I used queries like:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

ELSE CASE WHEN t2.Connection = 'K' THEN 'GPE'

ELSE CASE WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END END END END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID).

I'll take an example:

An ID can have multiple connections attached to it. Let Say:

Table1 ID : 0001

Number: 01

Table2 ID: 0001

Connection: J, K, S, O, P.

Now, when i do the above query, i would get a result of 3 rows new case types for ID 0001.

I would like to just get one, and the first priority will be put on the condition of (GE).

everytime I see a Number '01' and Connection 'J', I would ignore the other connection. If i do not see Number = '01' and Connection: 'J'. i would go for the rest of the conditions.

Hope you guys see what problem i am having.

Thanks,

Jul.

Try this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

WHEN t2.Connection = 'K' THEN 'GPE'

WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

Since I do not like repeating the condition, I would do this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

|||Still, i get multiple results.....Thanks though.|||So you want only one row returned?

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN (

select id, min(Connection) [Connection]

from Table2

group by id) AS t2

ON (t1.ID = t2.ID)

|||It works....Thanks sooo much.....

Wednesday, March 21, 2012

Help with a simple Query

I am trying to make a single display page for an author's books.

the books page only displays books of a type "type" (novels, non-fiction, etc)

I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this:

<asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader%>" SelectCommand="SELECT * FROM [Books] ( If @.Type <> "" then WHERE ([Type] = @.Type)) ORDER BY [SortDate] DESC">

But it doesn't seem to want to work. I get a "server tag is not well formed" error.

Try this:

SELECT*FROM [Books]WHERE (@.TypeISNULLOR [Type]=@.Type)ORDERBY [SortDate]DESC

|||

Or, for text variables:

SELECT*FROM [Books]WHERE [Type] like IsNull(@.Type,'%')ORDERBY [SortDate]DESC

That is not, by the way, an exact equivalent of the previous poster's query.

This version allows a wildcard search, whereas the other query requires an exact match.

For example, if you were wanting to search for both Novels and Novellas, a value for @.Type of 'Novel%' would get you both types of book in one query.

Numbers and dates don't work this way. :(

Monday, March 12, 2012

help with @@IDENTITY

hello,

I've got a problem reading the @.@.identity in vb.net
I tried it the way below and get the error: Public member 'EOF' on type 'Integer' not found.
(--> means with rsLastIdent)

comm_user = "SET NOCOUNT ON; INSERT INTO user (firstname, lastname, company, emailAddress) VALUES ...); SELECT @.@.IDENTITY AS Ident;"
comm = new SqlCommand(comm_user, dbConnection)
dbConnection.Open()
Try
 rsLastIdent = comm.ExecuteNonQuery()
 Catch ex As Exception
 Response.Write("Exception:")
 Response.Write(ex.ToString)
 End Try
if NOT rsLastIdent.EOF then
feed_userID = rsLastIdent.Fields.Item("Ident").Value
end if

The sql-statement is correct - I tried it on SQL Server and got the correct result, so something is wrong with my vb.net code...

Please can anybody help me and tell me how to declare my rsLastIdent or another way to code it in vb.net to get the @.@.identity?

Thanks a lot!

timWhat does ExecuteNonQuery return? It is not what you are trying to get at.

Try .ExecuteScalar() and cast the returned value to an int.

Dim rsLastIdent as integer
rsLastIdent=Integer.Parse(comm.executeScalar().ToString())

There are more elegant ways to do this, I expect.|||Douglas,

Thanks for your help!

executeScalar() was the function I needed! It works perfect now!

Regards,
Tim

Help with "Error converting data type varchar to float"

Error converting data type varchar to float.
I am getting this error. Any one there to guide meYou have come to the right place. We are all mind readers here. We scan your brains alpha waves and decode it into the source code you are looking at that generated this error.|||Unfortunately, I am not receiving any signal from my scan. *shrug* I am also fighting off a cold though, so it could be a blockage on my side.

My best guess given the information you have provided so far is that you are attempting in your code to convert a varchar-format variable or constant containing a date into a float datatype.

This is a no-no in most technical (and social, for that matter) circles.|||Actually u should first tell that where this problem is coming in ur source code or database script|||I am getting this error. Any one there to guide meI knew a girl like that once...

On a more serious note, can you give us something more to work with to help you solve your problem? Please post the SQL code that you were running when you got this error. Look here (http://www.dbforums.com/showthread.php?t=1196943) for more detailed help.

-PatP

Wednesday, March 7, 2012

Help Troubleshoot "Could not find Type in assembly"

I am attempting to create a CLR Procedure. I was able to create the assembly, but I am unable to create a procedure on the assembly. This is the error I receive:

Msg 6505, Level 16, State 1, Procedure DINEServiceProc, Line 2

Could not find Type 'DINEServiceProc' in assembly 'DINEService'

Here is the VB code to create the class:

<code>

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class DINEServiceProc

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub ServiceProc(ByVal iMsg As String, ByVal iMsgType As String)

Dim conn As SqlConnection

'Create an in-process connection to the instance of SQL Server

conn = New SqlConnection("Context Connection=True")

Dim DINEService As New DINEService

Try

conn.Open()

DINEService.ProcessStartRequest(iMsg, iMsgType)

Catch sqe As SqlException

'Console.WriteLine(sqe.Message)

Return

Finally

conn.Close()

End Try

End Sub

End Class

</code>

And here is the code to create the assembly and the procedure:

<code>

USE [ServiceBrokerTest]

/****** Object: SqlAssembly [DINEService] Script Date: 01/03/2006 10:38:00 ******/

CREATE ASSEMBLY [DINEServiceProc]

AUTHORIZATION [dbo]

FROM 'D:\EHIT\ServiceBroker\DINEService\DINEService\bin\Debug\DINEService.dll'

WITH PERMISSION_SET = SAFE

CREATE PROCEDURE dbo.DINEServiceProc

(

@.msg nvarchar(MAX),

@.msgType nvarchar(MAX)

)

AS EXTERNAL NAME DINEServiceProc.DINEServiceProc.ServiceProc;

</code>

What am I doing wrong here?

As this is a vb assembly I'd say you're falling foul of VB creating a default namespace around your classes. Have a look in ildasm or reflector at your assembly and see if you don't have a namespace in there. In which case the EXTERNAL NAME in your CREATE PROCEDURE statement should look like so: AS EXTERNAL NAME DINEServiceProc.[NamespaceName.DINEServiceProc].ServiceProc.

Niels
|||

As nielsb said, you're running into the VB default namespace issue.

CREATE PROCEDURE ....

AS EXTERNAL NAME [DINEServiceProc].[DINEServiceProc.DINEServiceProc].[ServiceProc]

should work for you.

|||Yes, that was it. Thanks to both of you.|||

I just wanted to thank you folks for this one (i.e. The NAMESPACE ).

I have been battling with this morning. The problem that I found was.. that in the example that I was following, the author has manually compiled the .dll in a directory with a similar name to that of the namespace and warns you in the creation of the assembly to use the fully qualified path to the directory . This is true and worked out fine.

Where I came unstuck was in the creation of the Stored Procedure by passing the same fully quailified path (minus the c:\ ) INSTEAD of the namespace.

BTW I was using C#

regards Steve

|||I had the same issue, and this was the fix. Thanks for postings!!!|||I had similar problems. I found I was using the wrong case for the namespace. I found the "MSIL Disassembler" helped me solve my problems.|||

I just had the same problem. It seems C# also does the same thing with the 'hidden' namespace. The documentation and error message should be updated to include this condition.

-Lukasz

|||Thanks, for your reply!

I also use C# and the problem occurred for me as well, you've saved me a lot of time.

Regards,
Peter Larsson!

Help Troubleshoot "Could not find Type in assembly"

I am attempting to create a CLR Procedure. I was able to create the assembly, but I am unable to create a procedure on the assembly. This is the error I receive:

Msg 6505, Level 16, State 1, Procedure DINEServiceProc, Line 2

Could not find Type 'DINEServiceProc' in assembly 'DINEService'

Here is the VB code to create the class:

<code>

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class DINEServiceProc

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub ServiceProc(ByVal iMsg As String, ByVal iMsgType As String)

Dim conn As SqlConnection

'Create an in-process connection to the instance of SQL Server

conn = New SqlConnection("Context Connection=True")

Dim DINEService As New DINEService

Try

conn.Open()

DINEService.ProcessStartRequest(iMsg, iMsgType)

Catch sqe As SqlException

'Console.WriteLine(sqe.Message)

Return

Finally

conn.Close()

End Try

End Sub

End Class

</code>

And here is the code to create the assembly and the procedure:

<code>

USE [ServiceBrokerTest]

/****** Object: SqlAssembly [DINEService] Script Date: 01/03/2006 10:38:00 ******/

CREATE ASSEMBLY [DINEServiceProc]

AUTHORIZATION [dbo]

FROM 'D:\EHIT\ServiceBroker\DINEService\DINEService\bin\Debug\DINEService.dll'

WITH PERMISSION_SET = SAFE

CREATE PROCEDURE dbo.DINEServiceProc

(

@.msg nvarchar(MAX),

@.msgType nvarchar(MAX)

)

AS EXTERNAL NAME DINEServiceProc.DINEServiceProc.ServiceProc;

</code>

What am I doing wrong here?

Niels
|||

As nielsb said, you're running into the VB default namespace issue.

CREATE PROCEDURE ....

AS EXTERNAL NAME [DINEServiceProc].[DINEServiceProc.DINEServiceProc].[ServiceProc]

should work for you.

|||Yes, that was it. Thanks to both of you.|||

I just wanted to thank you folks for this one (i.e. The NAMESPACE ).

Where I came unstuck was in the creation of the Stored Procedure by passing the same fully quailified path (minus the c:\ ) INSTEAD of the namespace.

BTW I was using C#

regards Steve

I just had the same problem. It seems C# also does the same thing with the 'hidden' namespace. The documentation and error message should be updated to include this condition.

-Lukasz

|||Thanks, for your reply!

I also use C# and the problem occurred for me as well, you've saved me a lot of time.

Regards,
Peter Larsson!

Help Troubleshoot "Could not find Type in assembly"

I am attempting to create a CLR Procedure. I was able to create the assembly, but I am unable to create a procedure on the assembly. This is the error I receive:

Msg 6505, Level 16, State 1, Procedure DINEServiceProc, Line 2

Could not find Type 'DINEServiceProc' in assembly 'DINEService'

Here is the VB code to create the class:

<code>

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class DINEServiceProc

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub ServiceProc(ByVal iMsg As String, ByVal iMsgType As String)

Dim conn As SqlConnection

'Create an in-process connection to the instance of SQL Server

conn = New SqlConnection("Context Connection=True")

Dim DINEService As New DINEService

Try

conn.Open()

DINEService.ProcessStartRequest(iMsg, iMsgType)

Catch sqe As SqlException

'Console.WriteLine(sqe.Message)

Return

Finally

conn.Close()

End Try

End Sub

End Class

</code>

And here is the code to create the assembly and the procedure:

<code>

USE [ServiceBrokerTest]

/****** Object: SqlAssembly [DINEService] Script Date: 01/03/2006 10:38:00 ******/

CREATE ASSEMBLY [DINEServiceProc]

AUTHORIZATION [dbo]

FROM 'D:\EHIT\ServiceBroker\DINEService\DINEService\bin\Debug\DINEService.dll'

WITH PERMISSION_SET = SAFE

CREATE PROCEDURE dbo.DINEServiceProc

(

@.msg nvarchar(MAX),

@.msgType nvarchar(MAX)

)

AS EXTERNAL NAME DINEServiceProc.DINEServiceProc.ServiceProc;

</code>

What am I doing wrong here?

Niels
|||

As nielsb said, you're running into the VB default namespace issue.

CREATE PROCEDURE ....

AS EXTERNAL NAME [DINEServiceProc].[DINEServiceProc.DINEServiceProc].[ServiceProc]

should work for you.

|||Yes, that was it. Thanks to both of you.|||

I just wanted to thank you folks for this one (i.e. The NAMESPACE ).

Where I came unstuck was in the creation of the Stored Procedure by passing the same fully quailified path (minus the c:\ ) INSTEAD of the namespace.

BTW I was using C#

regards Steve

I just had the same problem. It seems C# also does the same thing with the 'hidden' namespace. The documentation and error message should be updated to include this condition.

-Lukasz

|||Thanks, for your reply!

I also use C# and the problem occurred for me as well, you've saved me a lot of time.

Regards,
Peter Larsson!

Help Troubleshoot "Could not find Type in assembly"

I am attempting to create a CLR Procedure. I was able to create the assembly, but I am unable to create a procedure on the assembly. This is the error I receive:

Msg 6505, Level 16, State 1, Procedure DINEServiceProc, Line 2

Could not find Type 'DINEServiceProc' in assembly 'DINEService'

Here is the VB code to create the class:

<code>

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class DINEServiceProc

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub ServiceProc(ByVal iMsg As String, ByVal iMsgType As String)

Dim conn As SqlConnection

'Create an in-process connection to the instance of SQL Server

conn = New SqlConnection("Context Connection=True")

Dim DINEService As New DINEService

Try

conn.Open()

DINEService.ProcessStartRequest(iMsg, iMsgType)

Catch sqe As SqlException

'Console.WriteLine(sqe.Message)

Return

Finally

conn.Close()

End Try

End Sub

End Class

</code>

And here is the code to create the assembly and the procedure:

<code>

USE [ServiceBrokerTest]

/****** Object: SqlAssembly [DINEService] Script Date: 01/03/2006 10:38:00 ******/

CREATE ASSEMBLY [DINEServiceProc]

AUTHORIZATION [dbo]

FROM 'D:\EHIT\ServiceBroker\DINEService\DINEService\bin\Debug\DINEService.dll'

WITH PERMISSION_SET = SAFE

CREATE PROCEDURE dbo.DINEServiceProc

(

@.msg nvarchar(MAX),

@.msgType nvarchar(MAX)

)

AS EXTERNAL NAME DINEServiceProc.DINEServiceProc.ServiceProc;

</code>

What am I doing wrong here?

Niels
|||

As nielsb said, you're running into the VB default namespace issue.

CREATE PROCEDURE ....

AS EXTERNAL NAME [DINEServiceProc].[DINEServiceProc.DINEServiceProc].[ServiceProc]

should work for you.

|||Yes, that was it. Thanks to both of you.|||

I just wanted to thank you folks for this one (i.e. The NAMESPACE ).

Where I came unstuck was in the creation of the Stored Procedure by passing the same fully quailified path (minus the c:\ ) INSTEAD of the namespace.

BTW I was using C#

regards Steve

I just had the same problem. It seems C# also does the same thing with the 'hidden' namespace. The documentation and error message should be updated to include this condition.

-Lukasz

|||Thanks, for your reply!

I also use C# and the problem occurred for me as well, you've saved me a lot of time.

Regards,
Peter Larsson!

Help Troubleshoot "Could not find Type in assembly"

I am attempting to create a CLR Procedure. I was able to create the assembly, but I am unable to create a procedure on the assembly. This is the error I receive:

Msg 6505, Level 16, State 1, Procedure DINEServiceProc, Line 2

Could not find Type 'DINEServiceProc' in assembly 'DINEService'

Here is the VB code to create the class:

<code>

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class DINEServiceProc

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub ServiceProc(ByVal iMsg As String, ByVal iMsgType As String)

Dim conn As SqlConnection

'Create an in-process connection to the instance of SQL Server

conn = New SqlConnection("Context Connection=True")

Dim DINEService As New DINEService

Try

conn.Open()

DINEService.ProcessStartRequest(iMsg, iMsgType)

Catch sqe As SqlException

'Console.WriteLine(sqe.Message)

Return

Finally

conn.Close()

End Try

End Sub

End Class

</code>

And here is the code to create the assembly and the procedure:

<code>

USE [ServiceBrokerTest]

/****** Object: SqlAssembly [DINEService] Script Date: 01/03/2006 10:38:00 ******/

CREATE ASSEMBLY [DINEServiceProc]

AUTHORIZATION [dbo]

FROM 'D:\EHIT\ServiceBroker\DINEService\DINEService\bin\Debug\DINEService.dll'

WITH PERMISSION_SET = SAFE

CREATE PROCEDURE dbo.DINEServiceProc

(

@.msg nvarchar(MAX),

@.msgType nvarchar(MAX)

)

AS EXTERNAL NAME DINEServiceProc.DINEServiceProc.ServiceProc;

</code>

What am I doing wrong here?

Niels
|||

As nielsb said, you're running into the VB default namespace issue.

CREATE PROCEDURE ....

AS EXTERNAL NAME [DINEServiceProc].[DINEServiceProc.DINEServiceProc].[ServiceProc]

should work for you.

|||Yes, that was it. Thanks to both of you.|||

I just wanted to thank you folks for this one (i.e. The NAMESPACE ).

Where I came unstuck was in the creation of the Stored Procedure by passing the same fully quailified path (minus the c:\ ) INSTEAD of the namespace.

BTW I was using C#

regards Steve

I just had the same problem. It seems C# also does the same thing with the 'hidden' namespace. The documentation and error message should be updated to include this condition.

-Lukasz

|||Thanks, for your reply!

I also use C# and the problem occurred for me as well, you've saved me a lot of time.

Regards,
Peter Larsson!

Help to write a query to kamal

hello,
I have a Question Paper table
Create Table QPaper (QPID, QPName,SubjectSLNo,Type, NoOfQues)
Data is like
Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10)
Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5)
Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2)
Insert Into QPaper Values (4, 'UNIT2', 1,'Objective', 15)
My Output should be
1, 'UNIT1', 1,'Objective', 1, 1
1, 'UNIT1', 1,'Objective', 1, 2
1, 'UNIT1', 1,'Objective', 1, 3
1, 'UNIT1', 1,'Objective', 1, 4
--
--
--
1, 'UNIT1', 1,'Objective', 1, 10
2, 'UNIT1', 1,'Descriptive', 5,1
2, 'UNIT1', 1,'Descriptive', 5,2
--
--
--
2, 'UNIT1', 1,'Descriptive', 5,5
3, 'UNIT2', 1,'Objective', 2,1
3, 'UNIT2', 1,'Objective', 2,2
4, 'UNIT2', 1,'Objective', 15,1
4, 'UNIT2', 1,'Objective', 15,2
4, 'UNIT2', 1,'Objective', 15,3
--
--
--
--
--
4, 'UNIT2', 1,'Objective', 15,15
thanks on advance
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Try,
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
Create Table QPaper (QPID int, QPName varchar(25) ,SubjectSLNo int,Type
varchar(25), NoOfQues int)
go
Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10)
Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5)
Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2)
Insert Into QPaper Values (4, 'UNIT2', 1,'Objective', 15)
go
select
a.QPID,
a.QPName,
a.SubjectSLNo,
a.Type,
a.NoOfQues,
n.number
from
QPaper as a
inner join
number as n
on n.number <= a.NoOfQues
order by
a.QPID,
n.number
go
drop Table QPaper, number
go
AMB
"kamal hussain" wrote:

>
> hello,
> I have a Question Paper table
> Create Table QPaper (QPID, QPName,SubjectSLNo,Type, NoOfQues)
> Data is like
> Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10)
> Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5)
> Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2)
> Insert Into QPaper Values (4, 'UNIT2', 1,'Objective', 15)
> My Output should be
> 1, 'UNIT1', 1,'Objective', 1, 1
> 1, 'UNIT1', 1,'Objective', 1, 2
> 1, 'UNIT1', 1,'Objective', 1, 3
> 1, 'UNIT1', 1,'Objective', 1, 4
> --
> --
> --
> 1, 'UNIT1', 1,'Objective', 1, 10
> 2, 'UNIT1', 1,'Descriptive', 5,1
> 2, 'UNIT1', 1,'Descriptive', 5,2
> --
> --
> --
> 2, 'UNIT1', 1,'Descriptive', 5,5
> 3, 'UNIT2', 1,'Objective', 2,1
> 3, 'UNIT2', 1,'Objective', 2,2
> 4, 'UNIT2', 1,'Objective', 15,1
> 4, 'UNIT2', 1,'Objective', 15,2
> 4, 'UNIT2', 1,'Objective', 15,3
> --
> --
> --
> --
> --
> 4, 'UNIT2', 1,'Objective', 15,15
>
> thanks on advance
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

Sunday, February 26, 2012

help SP Code

I type Code in SP

Exec ('INSERT INTO XXX (A,B,C)

Select A,'Y8',C From YYY

GROUP BY A,B');

But it has error, Value 'ABC' not correct

Why, Thanks

William

Exce SP Code have error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Y8'.

|||

Try this:

EXEC ('INSERT INTO XXX (A,B,C) Select A,''Y8'',C From YYY GROUP BY A,B');

Chris

|||

If you want to use the single quote (‘) with in the sql string you have to use the escape sequence.

Many languages support \ as escape sequential char. But in sql server the same char need to be repeated (twice).

Example:

Select @.a = 'Sql Server''s'

So you have to change your query as follow as,

Exec (

'INSERT INTO XXX (A,B,C)

Select A,''Y8'',C From YYY

GROUP BY A,B'

);

Friday, February 24, 2012

Help select data type

Hi Everyone,

I have a table in my database that stores info to process a 'Parts Rush' request, so a user can get there part expedited. I want to have a field in my table named 'Status'.

The four status' are:

Pending

Approved

Declined

Coachback

The question is should I use integers to represent the status (ie. 1 = Pending) or should I set that field to varchar and store the actual word?

I'm just trying to develop my SQL skills and what to exercise good practices. I am using SQL Server 2005 Express.

Thank You

-Sam

I recommend setting the status field to an int, and having a separate table linking the ints to the status strings - sure you'll have to join, but I believe it's a better practice than flat-out throwing a string in the main table.|||

I agree with making it an integer type over a varchar. In addition, you might want to consider making it either a TINYINT or a SMALLINT to save space if you know that you will never have 255 different specific statuses for TINYINT or 32767 different status for SMALLINT. Use of either of these datatypes will save you some space and over the long pull might slightly improve IO performance.

However, you also need to weigh some compatibility factors. The INTEGER datatype is certainly more universally available in other DBMS implementations. You may not want to use TINYINT or SMALLINT to avoid a few cross-platform issues. I think the more universal type would be the INT type.

Sunday, February 19, 2012

help required in writing a query

hi all,

consider the following scenario:

I have 2 tables ModelSalesUnitCode and SalesUnitCode with following data:

ModelSalesUnit
Kis Type Salu_cd
PC 6 2AA01
WA 3 2AA01
Wa 3 3AA01

SalesUnitCode
Salu_cd Kis Type Salu_desc
2AA01 null null generic description
2AA01 WA 3 WA description
3AA01 WA 3 generic description

Now I write this query to get the salu_cd and their respective descriptions for the model with (kis = WA and Type = 3)

Select
a.kis,
a.type,
a.salu_cd,
b.salu_desc
From
modelsalesunitcode a, salesunitcode b
Where
b.salu_cd = a.salu_cd
And
a.kis = 'WA'
And
a.type = '3'

This gives me
kis type salu_cd salu_desc
WA 3 2AA01 generic description
WA 3 2AA01 WA description
WA 3 3AA01 generic description

is it possible to modify the above query in such a way that it returns

kis type salu_cd salu_desc
WA 3 2AA01 WA description
WA 3 3AA01 generic description

meaning where description exists for (Kis = WA and Type = 3) pick it up (WA Description in above case) if it does not exist then pick the generic description.

Thanx in advance
Omer

/Add clause in WHERE:

Select
a.kis,
a.type,
a.salu_cd,
b.salu_desc
From
modelsalesunitcode a, salesunitcode b
Where
b.salu_cd = a.salu_cd
And
a.kis = 'WA'
And
a.type = '3'
--------
AND b.kis IS NOT NULL
--------

Help Reqd Urgently (SQL Query)

Pls consider the following table:--

Table Name: ChemoAdmin

Fields:

Field Name: Field Type: Typical Value:

patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg

I require the following output using one query:--

DATE 09/17/2004 09/21/2004

Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg

Pls help.You mean you'd like a specify a daterange and have it shown above the other results returned by the same select-statement (seperated by an empty line)?

Is there any reason why you need this in a single sql-statement?|||It is better if this can be done using one select statement. Because, I would like to create a view that would give the output as shown.|||I don't see a way of doing that in a view. You might want to create a stored procedure instead that produces both the daterange as well as the select output. However, I feel that your aim is off.|||this requires a full outer join

however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this case

i always write a full outer join as a left outer join unioned with a right outer join where there's no matching row (and then i always flip the right outer join over into a left)
select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
left outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t2.stationDate = '2004-09-21'
where t1.stationDate = '2004-09-17'
union all
select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t2
left outer
join ChemoAdmin as t1
on t2.drugName
= t1.drugName
and t1.stationDate = '2004-09-17'
where t2.stationDate = '2004-09-21'
and t1.drugName is null|||this requires a full outer join

however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this caseOk, that just begs the question: Why do you avoid FULL OUTER JOIN when it does exactly what is needed? While I use FULL and CROSS joins rather sparingly, when they do exactly what I want I'll cheerfully use the little beggars.

-PatP|||because not every database supports FULL OUTER (yes, i know that microsoft sql server does, but it's easier remembering the workaround than trying to remember which database supports it)

but more importantly, in this instance the join condition include conditions on only one table or the other

i'm not certain that this will produce the same results:select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
full outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t1.stationDate = '2004-09-17'
and t2.stationDate = '2004-09-21'
feel like testing it for us? :)|||Because your code tests both tables, it effectively reduces the join to a conventional INNER join. Any result set rows that "miss" (either right or left) will fail due to the comparison with NULL. The only difference between the two (FULL versus unions of left and right) would be if you used UNION instead of UNION ALL.

-PatP|||so you're saying that the FULL OUTER example that i posted will not work?

so what would you do to make it work in this particular example?

don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see it|||Can any drug in your list have more than 2 dosages ? if so joining the table just twice wont work. Use the following query to find the maximum number of doses for any drug in that table.

select max(counter) max_number from
(select drugName, count(*) counter from ChemoAdmin
group by ChemoAdmin) derived

if the max_number is very high and likely to be a variable then the best way is to use a stored procedure.

if it is just 2 or 3 then, the you can use the self left outer joins not a full outer join

for instance if the max_number is 2 then

select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
left outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t1.stationDate >= '2004-09-17'
and t1.stationDate < '2004-09-21'
and t2.stationDate >= '2004-09-17'
and t2.stationDate < '2004-09-21'
and t1.dose <> t2.dose
and t1.doseUnit <> t2.doseUnit

if the max_number is 3 then join the result again with the table and so on, but do not join too many times though because it would cause performance problems.|||so you're saying that the FULL OUTER example that i posted will not work?

so what would you do to make it work in this particular example?

don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see itI didn't pooh on anything. In my first post I just asked why you avoided a FULL join when I use them occaisionally.

The point that I raised in my second post is that if you look at things from the set based perspective, if either set it empty (returning NULL in SQL) then the WHERE clause comparison has to fail, reducing the result set to the intersection (an INNER join in SQL) or less.

Sorry if you got the impression that I was denigrating your work around for a full join. I didn't mean to do that at all.

I'm still not at all convinced that we understand what the original poster wanted. They showed us a third of the equation (the results they want), without giving us either the input data or the rules they used to determine the output. Before we debate the relative merits of solutions, we need to understand what the rules are!

-PatP|||I'm still not at all convinced that we understand what the original poster wanted. so true

but then, the most interesting threads are where the original poster asks something innocuous (to them) and we just run with it in all sorts of directions

:rolleyes:

Help Reqd Urgently (SQL Query)

help regarding this search

//and i m getting this error

Syntax error converting the varchar value 'NPO04/136 ' to a column of data type int.

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: Syntax error converting the varchar value 'NPO04/136 ' to a column of data type int.

====================================

can anyone help me in this i m doing a search in a form ( for ex. u search for clientID n it'll come up in search result n once u select that it'll fill in the form) if anyone knw how to do this plz help me. thank u

Protected

Sub cmdSelect2_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles cmdSelect2.ClickDim sIDAsString

sID = lstResults.SelectedValue

Dim iIDAsInteger' iID = Int32.Parse(sID)'' If iID > 0 ThenDim selectSQLAsString

selectSQL =

"SELECT * FROM contactinfo "

selectSQL &=

"WHERE ClientID=@.ClientID"Dim cmdAsNew SqlCommand(selectSQL, conSR)

cmd.Parameters.AddWithValue(

"@.ClientID", iID)Dim readerAs SqlDataReader

Try

conSR.Open()

reader = cmd.ExecuteReader()

reader.Read()

' Fill the controls.'txtCaseid.Text = reader("Caseid").ToString()

txtClientID.Text = reader(

"ClientID").ToString()

txtFirstname.Text = reader(

"Client_Fname").ToString()

txtLastname.Text = reader(

"Client_Lname").ToString()

txtRace.Text = reader(

"Race").ToString()

txtCounty.Text = reader(

"County_of_origin").ToString()

txtGender.Text = reader(

"Gender").ToString()

txtState.Text = reader(

"State").ToString()

txtReligion.Text = reader(

"Religion").ToString()

txtContactID.Text = reader(

"ContactID").ToString()'reader.Close()'' enable_fields()

lblResults.Text =

""Catch exAs Exception'lblResults.Text = "Error inserting record"

lblResults.Text =

"Error inserting record"

lblResults.Text = ex.Message

Finally

conSR.Close()

EndTry

lblID.Text = sID

phID.Visible =

True

txtID.Text = sID

txtAct.Text =

"edit"

cmdSubmit.Text =

"Update"

lblCurrentAct.Text =

"Update Existing Record"'Else'If iID = 0 Then' lblResults.Text = "Client not found in Contact Information."'Else' lblResults.Text = "Please select a contact from the list."''' End IfEndSub

Hello my friend,

The field you wish to search by has non-numeric data in it so use a string to pass as the parameter instead of an int. You pass in iID so it crashes. Use a string variable instead and you should be fine.

Kind regards

Scotty

Thursday, March 29, 2012

Help with Cross-tab queries

Tuesday, March 27, 2012

help with Check contraints

Help with case when

Help with Case

Wednesday, March 21, 2012

Help with a simple Query

Monday, March 12, 2012

help with @@IDENTITY

Help with "Error converting data type varchar to float"

Wednesday, March 7, 2012

Help Troubleshoot "Could not find Type in assembly"

Help Troubleshoot "Could not find Type in assembly"

Help Troubleshoot "Could not find Type in assembly"

Help Troubleshoot "Could not find Type in assembly"

Help Troubleshoot "Could not find Type in assembly"

Help to write a query to kamal

Sunday, February 26, 2012

help SP Code

Friday, February 24, 2012

Help select data type

Sunday, February 19, 2012

help required in writing a query

Help Reqd Urgently (SQL Query)

Help Reqd Urgently (SQL Query)

help regarding this search

Syntax error converting the varchar value 'NPO04/136 ' to a column of data type int.

Restore Database

Blog Archive

About Me