CASE statement in a JOIN

Hi,
I have two tables TABLE_A and TABLE_B

TABLE_A has rows like this:
PROJECT_ID TASK_ID TASK_NAME
1 100 One Hundred
1 110 One Hundred Ten
1 120 One Hundred Twenty
2 200 Two Hundred
3 300 Three Hundred
3 310 Three Hundred Ten

TABLE_B has rows like this:
PROJECT_ID TASK_ID AMOUNT
1 100 1000
1 110 1100
2 NULL 2000
3 300 3000

I want to inner join TABLE_A and TABLE_B such that if TASK_ID is available in TABLE_B, then join should happen on TASK_ID (on TABLE_A.TASK_ID=TABLE_B.TASK_ID), if TASK_ID is not available the join should happen on PROJECT_ID.

For example for PROJECT_ID=2, there is no TASK_ID in TABLE_B (in this situation the join should be ON PROJECT_ID)

How can we do a CASE like situation here?

Thanks in advance
qADoes this work?

ON ft1.field1 = Coalesce(t2.field1)
OR ft1.field2 = Coalesce(t2.field2)|||I have never seen coalesce used with 1 argument.|||Oops... I have forgotten where I was coming from with that now...

Remove the coalesces and it might work..?
(*confused*)|||You can do it the way that georgev mentioned (without the coalesces), but reqardless of how you do this, you are almost surely going to have duplication in your result set. Any place that you have more than one record in TABLE_A with the same PROJECT_ID and record(s) in TABLE_B with that PROJECT_ID and a null TASK_ID, you are going to have duplication of the values in TABLE_B.|||I agree. If I use OR clause here, it would give lots of duplicates. Any other suggestions??|||on (TABLE_A.TASK_ID=TABLE_B.TASK_ID or ( (TABLE_A.TASK_ID<>TABLE_B.TASK_ID or TABLE_B.TASK_ID is null) and
TABLE_A.PROJECT_ID=TABLE_B.PROJECT_ID)
)
?|||look ma, no dupes:select TABLE_A.PROJECT_ID
, TABLE_A.TASK_ID
, TABLE_A.TASK_NAME
, TABLE_B.PROJECT_ID
, TABLE_B.TASK_ID
, TABLE_B.AMOUNT
from TABLE_A
inner
join TABLE_B
on TABLE_B.TASK_ID = TABLE_A.TASK_ID
union
select TABLE_A.PROJECT_ID
, TABLE_A.TASK_ID
, TABLE_A.TASK_NAME
, TABLE_B.PROJECT_ID
, TABLE_B.TASK_ID
, TABLE_B.AMOUNT
from TABLE_A
inner
join TABLE_B
on TABLE_B.PROJECT_ID = TABLE_A.PROJECT_ID
where not exists
( select 937
from TABLE_B
where TASK_ID = TABLE_A.TASK_ID )|||select TABLE_A.PROJECT_ID
, TABLE_A.TASK_ID
, TABLE_A.TASK_NAME
, TABLE_B.PROJECT_ID
, TABLE_B.TASK_ID
, TABLE_B.AMOUNT
from TABLE_A
inner
join TABLE_B
on TABLE_B.TASK_ID = TABLE_A.TASK_ID
union
select TABLE_A.PROJECT_ID
, TABLE_A.TASK_ID
, TABLE_A.TASK_NAME
, TABLE_B.PROJECT_ID
, TABLE_B.TASK_ID
, TABLE_B.AMOUNT
from TABLE_A
inner
join TABLE_B
on TABLE_B.PROJECT_ID = TABLE_A.PROJECT_ID
where not exists
( select *
from TABLE_B
where TASK_ID = TABLE_A.TASK_ID )|||Is it me, or is that identical to r937's post, except you changed the 937 to an asterix..?|||i love it when my answers are secretly adopted by people :)

by the way, it's "asterisk" -- asterix (http://en.wikipedia.org/wiki/Asterix) was a french comic book character

:)|||That might be why I spell it that way then ;)
Would I be correct in assuming that

IF EXISTS(SELECT 1 FROM MyTable)

Is more efficient than

IF EXISTS(SELECT * FROM MyTable)

Assuming that there is more than one column in the table?|||SELECT with EXISTS was optimized in SQL 2000 (I believe). It does not really pull back all of the columns as was the case I believe in SQL 7. I stopped eradicating it from code a while ago.|||Well a large portion of my work is still on 6.5 :p
I assumed that it would only ever need to return a single row - but since 6.5 doesn't have the TOP function, I assumed it returned the entire resultset.

Lots of assumptions, eh?|||I believe exists exits once it finds a nonqualifying row while NOT EXISTS will scan the whole result set.|||It seems so simple now! :p|||...while NOT EXISTS will scan the whole result set.actually, in this case it would likely be an index seek (or whatever it's called)

:)|||The reason that I stated that you will likely have duplicates regardless is because even in R937's code you could have duplicates in certain cases. While in your specific example you wouldn't have any duplicates with that code, you might have duplicates if there are any cases where you have any records on TABLE_B with a null TASK_ID and a PROJECT_ID that matches multiple records in TABLE_A or if you have multiple records in TABLE_B with a null TASK_ID. PROJECT_ID is evidently not a primary key in TABLE_A, whereas from your example, TASK_ID looks like it might be. For instance take the example below:

TABLE_A has rows like this:
PROJECT_ID TASK_ID TASK_NAME
1 100 One Hundred
1 110 One Hundred Ten
1 120 One Hundred Twenty
2 200 Two Hundred
3 300 Three Hundred
3 310 Three Hundred Ten
3 320 Three Hundred Twenty

TABLE_B has rows like this:
PROJECT_ID TASK_ID AMOUNT
1 100 1000
1 110 1100
2 NULL 2000
3 300 3000
3 NULL 4000
3 NULL 4100

Your result set will be:
PROJECT_ID TASK_ID TASK_NAME PROJECT_ID AMOUNT
1 100 One Hundred 100 1000
1 110 One Hundred Ten 110 1100
2 200 Two Hundred 200 2000
3 300 Three Hundred 300 3000
3 310 Three Hundred Ten NULL 4000
3 320 Three Hundred Twenty NULL 4000
3 310 Three Hundred Ten NULL 4100
3 320 Three Hundred Twenty NULL 4100

If you don't have any of that type of issue and are certain that you won't in the future, or you actually want that type of result in that type of instance, then no worries. Otherwise you might have a problem.

Case statement

Hi

I am having a few problem with a case statement.

I have a table with customer data in and I need to bring back all the rows
which have data missing from 2 different columns.

customer table
CustomerID Name SoftwareVersion
1001 TEST1 V5
1002 TEST2 V5
3 TEST3 V5
004 TEST4 V6
ect..

CustomerID Table
ID
1001
1002
1003
1004
ect...

I have a need customerid table and I need to list all the customers who are
not in this table or whos id does not match the new listings,
alos there is a softwareVersion Table list all the newest software so the same
will need to be done for this.

Thanks

Rich

You need to create the join batween the two table with CustomerID & ID, with not equal operator, yuor query may look like this

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

Gurpreet S. Gill

|||

Hi

I would like to use the case statement because where there is an error, I would like the cell to display

ERROR: 'then customerid'

Thanks

Rich

|||

I dont get you, what you want to do ? tell me in more detail.

|||

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

This won't work, you're just going to get every Customer joined to every customer except themselves.

You need to use an outer join something like this

SELECT A.CustomerID
FROM Customer A
LEFT JOIN CustomerID B ON A.CustomerID = B.[ID]
WHERE B.ID IS NULL

|||I assume you mean that the CustomerID table in your example above is really the result of a query you want to write. The problem is you haven't specified what "all the customers who are not in this table or whos id does not match the new listings" means. How do you know if a customer is not in that table or if they do not match the new listings, is that info in another table?|||

yes SnMSDN

he didnt make much clear what he wants, that why i did post that sample code. But he not responding, might he had solved the problem

sql

CASE - How to show groups with count(*) = 0 (zero) ?

I would like to show even the rows where number of records are zero (in the Case statement ).

I couldn't find the solution either in the microsoft newsgroup, dbforums or Google. I fear I am overlooking something very very basic!
Thanks in advance.
:)

SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end,
Count(*) 'Number of Invoices'

FROM AP

GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end
ORDER BY Min(Amount)
----------
Suppose these are the Amounts (99,50,75,201,230)
The CURRENT OUTPUT is
<= 100 3
>200 2

The REQUIRED OUTPUT is
<= 100 3
<= 200 0
> 200 2select 'Unknown', count(*)
from AP
where Amount IS NULL
UNION ALL
select '<= 100 ', count(*)
from AP
where Amount <= 100
UNION ALL
select '<=200', count(*)
from AP
where Amount <= 100 AND Amount > 100
UNION ALL
select '>200', count(*)
from AP
where Amount > 200|||correction:
where Amount <= 200 AND Amount > 100|||Thanks HanafiH.

Using union will cause a pass through the table each time. That will generate a performance overhead which would increase as the number of divisions increase. I am generating this CASE statement using dynamic SQL. The application can provide any value to the number of divisions.

I want to take advantage of the performance benefit by using CASE.

Is there a solution using CASE itself?

Thanks again in advance.
:)|||declare @.GroupingTable table
(GroupingValue varchar(10),
SortOrder int)

insert into @.GroupingTable (GroupingValue, SortOrder) values('Unknown', 1)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 100 ', 2)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 118 ', 3)
insert into @.GroupingTable (GroupingValue, SortOrder) values('> 200', 4)

select GroupingTable.GroupingValue
from
@.GroupingTable GroupingTable
left outer join
(SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end GroupingValue
Count(*) 'Number of Invoices'
FROM AP
GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end) SummaryData
on GroupingTable.GroupingValue = SummaryData.GroupingValue
order by GroupingTable.SortOrder

blindman|||Originally posted by HornOkPlease
Thanks HanafiH.

Using union will cause a pass through the table each time. That will generate a performance overhead which would increase as the number of divisions increase. I am generating this CASE statement using dynamic SQL. The application can provide any value to the number of divisions.

I want to take advantage of the performance benefit by using CASE.

Is there a solution using CASE itself?

Thanks again in advance.
:)

The CASE may well be efficient, but the GROUP BY and ORDER BY necessary to make use of it is not. UNION *ALL* is very efficient. On my system your original case query has a 0.0604 total query cost, my query runs at 0.0192 total query cost, which is roughly three times faster than the CASE case (as it were).

I shudder to think about what insertion into a temp will cost.|||try this:select count(*) as TotalNumber
, sum(case when amount is null
then 1 else 0 end) as Unknown
, sum(case when amount <= 100
then 1 else 0 end) as "<= 100"
, sum(case when amount <= 200
then 1 else 0 end) as "<= 200"
, sum(case when amount > 200
then 1 else 0 end) as "> 200"
from ap
you guys with the cpu timers, please let me know how this compares to the other solutions

rudy
http://r937.com/|||r937, I think you hit the nail on the head.

blindman|||Hats off to you blindman!
Thanks, HanafiH, i've taken note of your message too.

Regards,
HornOkPlease|||Originally posted by r937
try this:select count(*) as TotalNumber
, sum(case when amount is null
then 1 else 0 end) as Unknown
, sum(case when amount <= 100
then 1 else 0 end) as "<= 100"
, sum(case when amount <= 200
then 1 else 0 end) as "<= 200"
, sum(case when amount > 200
then 1 else 0 end) as "> 200"
from ap
you guys with the cpu timers, please let me know how this compares to the other solutions

rudy
http://r937.com/

0.0376|||Originally posted by blindman
declare @.GroupingTable table
(GroupingValue varchar(10),
SortOrder int)

insert into @.GroupingTable (GroupingValue, SortOrder) values('Unknown', 1)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 100 ', 2)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 118 ', 3)
insert into @.GroupingTable (GroupingValue, SortOrder) values('> 200', 4)

select GroupingTable.GroupingValue
from
@.GroupingTable GroupingTable
left outer join
(SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end GroupingValue
Count(*) 'Number of Invoices'
FROM AP
GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end) SummaryData
on GroupingTable.GroupingValue = SummaryData.GroupingValue
order by GroupingTable.SortOrder

blindman

0.1158 and the slowest solution proposed.|||I think r937 was being modest. You don't need to time these solutions to see that his is better. It's the solution I was trying to get to, but I guess I was one or two cups of coffee short this morning.

blindman

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:
>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Capturing rows inserted from bulk insert

Is there any way to capture the count of rows processed by a bulk insert task?
A property perhaps?
I can run queries post import but would prefer it if there was a way to capture that number directly. It was something we had in the old DTS as the package ran. Anything we can do to discover it in SSIS?

Paul PisarekOne option is to enable the out-of-box logging to sysdtslog90 table - the components displays number of rows processed. It's in the 'message' text colum though, so you need to add a little bit of parsing to get your specific data.
KDog|||That's right. We're working on a sample that parses the string and creates a report from it, hopefully should be available to you soon. But, yes, parsing the log is the right approach.

Capturing Invalid Records for XML Inserts

Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-ak
Using SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/
|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/

Capturing Invalid Records for XML Inserts

Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-akUsing SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/

Capture System.IO rows from incoming file and insert into table

Ok, I'm not quite sure how to approach this one. This is a VB.NET console app in which I want to capture each row and throw it into a table. The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be.

1) What should I use to do this, dataset? I want to use stored procedures also, not inline SQL

Function here takes an incoming file, and splits it up into separate files. I want to insert each row that is succesfully split

Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String)

If sIncomingfile <> "" And sOutputDirectory <> "" Then

Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None)
f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read

Dim file As New IO.FileInfo(sIncomingfile)
Dim filefs As IO.FileStream = Nothing
If file.Exists Then
Try
filefs = New IO.FileStream(file.FullName, IO.FileMode.Open) 'Place: 1
Catch ex As Exception
SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1")
Application.Exit()
End Try
End If

Dim reader As New IO.StreamReader(filefs)
Dim counter As Integer = 0

Dim CurrentFS As IO.FileStream
Dim CurrentWriter As IO.StreamWriter
Dim extension As String = IO.Path.GetExtension(file.FullName)

If extension = ".mnt" Then
While Not reader.Peek < 0
Dim Line As String = reader.ReadLine
If IsNumeric(Line.Substring(0, 1)) Then
Dim Parts() As String = Line.Split(" "c) ' split row into parts
If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file
counter += 1
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)
CurrentWriter = New IO.StreamWriter(CurrentFS)
End If

If Not CurrentWriter Is Nothing Then
CurrentWriter.WriteLine(Line)
End If

End If
End While

If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()

MoveFilesFTP(sOutputDirectory, "mnt")

ElseIf extension = ".naf" Then
While Not reader.Peek < 0
Dim Line As String = reader.ReadLine
If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file
counter += 1
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)
CurrentWriter = New IO.StreamWriter(CurrentFS)
End If

If Not CurrentWriter Is Nothing Then
CurrentWriter.WriteLine(Line)
End If

End While

If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()

MoveFilesFTP(sOutputDirectory, "naf")
End If
Else
'input file not valid
SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1")
End If
End Sub

You don't need a console application to import the data into SQL Server if you are in SQL Server 2000 you need a DTS package and in SQL Server 2005 you need an Integration services package. The only important thing to note is SQL Server being a RDBMS(relational database management systems) sees a text file as having Null values so you import your data into a Temp table then do INSERT INTO your destination table. Try the link below for sample DTS and Integration services code. Hope this helps.

http://www.sqlis.com/

|||

Put this somewhere at the top:

Dim conn as new sqlconnection("Your connect string")
conn.open
Dim cmd1 as new sqlcommand("INSERT INTO ProcessedFiles(Filename) VALUES (@.Filename) SELECT SCOPE_@.IDENTITY()")

cmd1.parameters.add("@.Filename",sqldbtype.varchar)

dim cmd2 as new sqlcommand("INSERT INTO ProcessedLines(FileID,LineNum,LineData) VALUES (@.FileID,@.LineNum,@.LineData)",conn)

cmd2.parameters.add("@.FileID",sqldbtype.int32)

cmd2.parameters.add("@.LineNum",sqldbtype.int32)

cmd2.parameters.add("@.LineData",sqldbtype.varchar)

Then after you get the filename in your code:

cmd1.parameters("@.Filename").value={Your filename variable}

cmd2.parameters("@.FileID").value = cmd1.executescaler

Then after you read a line of data from the file:

cmd2.parameters.add("@.LineNum").value=counter

cmd2.parameters.add("@.LineData").value=line

cmd2.executenonquery

And at the end of your program:

conn.close

of course, this assumes you have a table named processedfiles that has a Filename column, as well as an identity field. I would put a ProcessedDate field in there too, that defaults to GetUTCDate(). And a table named ProcessedLines that has three columns (FileID,LineNum,LineData).

Is that what you were looking for?

Capture Result of Update?

I have an Update statement in a stored procedure, and I want to capture
the number of rows affected for returning to the caller. If this was a
simple Insert, I'd use Scope_Identity() to get the Identity. But in
this case I want the count of all updated rows.
Thanks.
You want to use either @.@.RowCount (or RowCount_Big() if there is any
possibility that more than 2 billion rows may be affected by the Update
statement.
Something like:
Declare @.UpdateCount int
Update Mytable Set ... Where ...
Select @.UpdateCount = @.@.RowCount
Tom
Then @.UpdateCount will contain the number of rows which matched the Where
clause in your Update statement.
<bradwiseathome@.hotmail.com> wrote in message
news:1143750464.490863.149600@.v46g2000cwv.googlegr oups.com...
>I have an Update statement in a stored procedure, and I want to capture
> the number of rows affected for returning to the caller. If this was a
> simple Insert, I'd use Scope_Identity() to get the Identity. But in
> this case I want the count of all updated rows.
> Thanks.
>

capture line of flat file [Error]

Hi,

I have a flat file with several rows of entire type in one of the rows a string comes and when it goes away to guard in the BD it falls, since I can know in that this row of the flat file the string?

I'd like to help but I am not sure I followed your question. Can you calrify?

Rafael Salas

|||

PastillaReturn wrote:

Hi,

I have a flat file with several rows of entire type in one of the rows a string comes and when it goes away to guard in the BD it falls, since I can know in that this row of the flat file the string?

Sorry, this is getting lost in translation. Do you mean that you require to know which row(s) of the flat file failed?

-Jamie

|||

yes

|||

This isn't currently possible. If you want to change that, go here, vote, and add a comment:

Row numbers added to pipeline
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335)

-Jamie

Cant Update, Insert, or Delete rows

I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help.

I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types.

I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed.

I may upload a copy of the exact exception page, if someone thinks that it may help.

Here is the update command that was generated:

UPDATE [Record Information] SET [Speed] = @.Speed, [Recording Company] = @.Recording_Company, [Year] = @.Year, [Artist] = @.Artist, [Side 1 Track Title] = @.Side_1_Track_Title, [Side 1 Track Duration] = @.Side_1_Track_Duration, [Side 2 Track Title] = @.Side_2_Track_Title, [Side 2 Track Duration] = @.Side_2_Track_Duration, [Sleeve Description] = @.Sleeve_Description WHERE [Record Database ID] = @.original_Record_Database_ID

Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.

What I recommend is that you click the Learn link at the top of the page and go through some of the videos or quickstart tutorials to familiarise yourself with how the SqlDataSource works. It doesn't, for example, generate stored procedures under any circumstances - which is why you can't find any. You also need to understand what Optimistic Concurrency is, how to manage it and when to use it. Finally, you should understand what datatypes are the most appropriate for your data. Having nothing but ints and text datatypes is not very likely to be appropriate.