Database

Stored Procedure and ADO.NET

11-Nov-04 9:07

Do something like this

select * from
(
select case blah then 1 else 0 end as ContactIsRead from blah blah blah
) mail
where mail.ContactIsRead = 0

You create an alias for the entire query, and then filter on it. The alternative is this:

select case blah then 1 else 0 end as ContactIsRead from blah where (insert same query here that you used in the case statement ).

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Re: CASE function

Michael Potter11-Nov-04 10:06

Michael Potter

11-Nov-04 10:06

This is a problem better suited for the WHERE clause. A CASE statement would just complicate the matter.

UPDATE 
     EmailTable
SET
     ContactIsRead = 1,
     ContactDateRead = DATE()
WHERE
     EmailId = 1234 AND
     ContactIsRead = 0

In the case where the email had already been read, the UPDATE would result in 0 rows altered.

mysorian10-Nov-04 11:22

mysorian

10-Nov-04 11:22

I have a Stored Procedure with one input parameter and one
output parameter.
I create a SQLCommand for a Stored Procedure
I add parameter1 with direction input
I add parameter2 with direction output
I add parameter 1 and paramter2 to the Cmd's parameter Collection
I assign an input value to parameter1
I invoke the command's ExecuteReader() and try to
output the result(Parameter2) via response.write
I get a blank?
ps:
1. Procedure works on Query Analyzer
2. At the very end of code I verify two parameter are in
the Paramters collection.

Christian Graus10-Nov-04 11:30

10-Nov-04 11:30

Try assigning a value to the output parameter and see if it changes. Make it in/out and change the SP to store the incoming value somewhere so you can see if it's getting there OK. Check your spelling. Smile | :)

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

mysorian10-Nov-04 12:07

mysorian

10-Nov-04 12:07

Christian:

Thanks for the quick reply. Although I coded as an output while in Query Analyzer, it actually stores it as input/ouput, since while retrieving the output you have to read it into a variable.My spelling must be OK, since no exceptions are raised. I will diagnose some more, but I will get there. I just do not want to reinvent the wheel.

Is 'Command.ExecuteReader' the correct way?

The above is invokation is OK for a stored procedure with one input returning a number of rows of result.

mysorian

Christian Graus10-Nov-04 12:12

10-Nov-04 12:12

Here's a snippet of my code that does what you're trying to do:

			SqlCommand cmd = new SqlCommand("SetTrackThumbNail", Connection);<br />
			cmd.CommandType = CommandType.StoredProcedure;<br />
			cmd.Parameters.Add("@thumbNailPath", thumbNailPath);<br />
			cmd.Parameters.Add("@artistName", artistName);<br />
			cmd.Parameters.Add("@trackID", trackID);<br />
			cmd.Parameters.Add("@thumbID", -1);<br />
			cmd.Parameters["@thumbID"].Direction = ParameterDirection.Output;<br />
		<br />
			cmd.ExecuteNonQuery();<br />
<br />
			return (int) cmd.Parameters["@thumbID"].Value;

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Bizzare SQL Query Problem

mysorian10-Nov-04 12:46

mysorian

10-Nov-04 12:46

Christian:

Thanks a lot. Actually, I finally made it work with ExecuteReader()method of the command. What happens actually, is that there is no streaming data and you cannot use the DataReader's Read(). There is nothing to read. However, you may just read out the Output parameter's value. I will try if the ExecuteNonQuery() works as well, as nothing is returned, but everything is contained.

Thanks

jay

Tristan Rhodes10-Nov-04 10:49

Tristan Rhodes

10-Nov-04 10:49

I have a bizzare problem with an SQL query I am trying to build. The problem is, I have a query with a conditional WHERE EXISTS statement that modifies every value in my table, but the SELECT part of the statement returns only the correct, single instance.

This is done in access, in VB, and any help would be greatly appreciated, the queries are listed below.

This select statement returns 1 instance:

SELECT SubCategory.SubCategoryDesc
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old)

But this update command turns every entry in my table into str_New:

UPDATE SubCategory
SET SubCategory.SubCategoryDesc = str_New
WHERE EXISTS (SELECT SubCategory.SubCategoryDesc
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old))

Any ideas why this occurs? It's driving me NUTS!

Cheers

Cata

Re: Bizzare SQL Query Problem

Christian Graus10-Nov-04 11:28

Re: Bizzare SQL Query Problem

10-Nov-04 11:28

Well, the exists clause will return true all the time, so every row is updated.

I don't know enough about the Access subvariant of SQL to provide a definitive solution, but I'd have thought that this would work:

UPDATE SubCategory
SET SubCategory.SubCategoryDesc = str_New
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old)

otherwise, this may work

UPDATE SubCategory
SET SubCategory.SubCategoryDesc = str_New
WHERE SubCategory.CategoryKey IN
(
SELECT SubCategory.CategoryKey
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old)
)

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Tristan Rhodes11-Nov-04 6:06

Tristan Rhodes

11-Nov-04 6:06

Had another crack with those two, but still getting errors.

Type Mismatch on JOIN expression:

str_SQL = "UPDATE SubCategory SET SubCategory.SubCategoryDesc = '" & str_New & "' " _
& "WHERE SubCategory.CategoryKey IN " _
& "(SELECT SubCategory.SubCategoryDesc FROM SubCategory " _
& "INNER JOIN Category ON Category.CategoryKey = SubCategory.CategoryKey " _
& "WHERE ((Category.CategoryDescription)= '" & GetCurrCategory & "') " _
& "AND ((SubCategory.SubCategoryDesc)= '" & str_Old & "'))"

Missing Operator in Query "FROM Category ..... = Subcategory.CategoryKey":

str_SQL = "UPDATE SubCategory " _
& "SET SubCategory.SubCategoryDesc = '" & str_New & "' " _
& "FROM Category " _
& "INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey " _
& "WHERE ((Category.CategoryDescription) = '" & GetCurrCategory & "') " _
& "AND ((SubCategory.SubCategoryDesc)= '" & str_Old & "')"

I've played around with it, but still can't get it to work. Any more ideas?

Cheers

Cata

Never Mind - Solved It

Tristan Rhodes11-Nov-04 8:30

Tristan Rhodes

11-Nov-04 8:30

Never mind Smile | :)

Thanks for the help, it was pretty much spot on.
Needed to be using SubCategoryKey rather than the CategoryKey. One was specific, the other one was general.

Cheers again

Cata

Re: Never Mind - Solved It

Christian Graus11-Nov-04 9:03

Re: Bizzare SQL Query Problem

11-Nov-04 9:03

No worries, glad to help Smile | :)

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Michael Potter11-Nov-04 10:16

Michael Potter

11-Nov-04 10:16

There is no correlation between the queries in the update statement. No matter which row the SQL Server tests, the EXISTS statement will always resolve to true and therefore, update the row.

I don't think the EXISTS statement is a good solution for this problem (although you could make it work). Try the IN statement:

UPDATE 
     SubCategory 
SET 
     SubCategory.SubCategoryDesc = str_New
WHERE 
     SubCategory.SubCategoryDesc IN
          (SELECT 
               SubCategory.SubCategoryDesc 
           FROM 
               Category 
           INNER JOIN 
               SubCategory 
               ON Category.CategoryKey = SubCategory.CategoryKey
           WHERE 
               ((Category.CategoryDescription)= GetCurrCategor()) AND
               ((SubCategory.SubCategoryDesc)= str_Old))

Excel data type problem

@LX10-Nov-04 4:42

@LX

10-Nov-04 4:42

Hi,
In an excel sheet, I have a column (text format), but if I try open this using ODBC driver, this column is open as double data type. Exist any way to change the data type for each column?

I don't want to use a TEXT function from excel, this resolve my problem, but I want another way to chnage it.

Thank you.

ALX

build a cbo box

skicrud10-Nov-04 3:53

skicrud

10-Nov-04 3:53

What am I doing wrong? I do not get any output

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
OleDbConnection1.Open()

OleDbDataAdapter1.SelectCommand.CommandText = "SELECT Location, HotelID FROM Hotel"
OleDbDataAdapter1.SelectCommand.ExecuteNonQuery()
Do

ComboBox1.Items.Add(OleDbDataAdapter1.SelectCommand.CommandText)

Loop
OleDbConnection1.Close()
End Sub

Re: build a cbo box

Mekong River10-Nov-04 4:00

Mekong River

10-Nov-04 4:00

skicrud wrote:
What am I doing wrong? I do not get any output

Can you tell use, what do you want to do?

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: build a cbo box

Gavin Jeffrey10-Nov-04 5:08

Gavin Jeffrey

10-Nov-04 5:08

Dont exactly know what you are trying to do here but shouldn't you instead of putting executeNonQuery() put in executeReader()??
I dont know what the do loop is for either??
Try filling a datatable or dataset with you adapter i.e oledbDataAdapter1.fill(myDatatable)
then use your datatable to fill your comboBox ie.
comboBox1.datasource = myDataTable
comboBox1.valueMember = "hotelID"
comboBox1.displayMember = "Location"
ComboBox1.DataBind()

Something like that anyways.

Urgent about connection to MSDE 2000

wk_vigorous10-Nov-04 3:26

wk_vigorous

10-Nov-04 3:26

Hi, guys.

This is an urgent problem that I can only conncet to MSDE 2000(release A, not sp3 or sp3a) from local computer but can not connect to the one running on remote PC. Every time such information is prompted:"SQL Server doesn't exist or access denied."

By the way, the SQL Server service manager running on my PC can start or stop MSDE on both local and remote PC.

Help please. Thanks.

vigorous

Mekong River10-Nov-04 4:06

Mekong River

10-Nov-04 4:06

wk_vigorous wrote:
This is an urgent problem that I can only conncet to MSDE 2000(release A, not sp3 or sp3a) from local computer but can not connect to the one running on remote PC

What is the version of your windows operating system? Does your windows install any service pack?

wk_vigorous wrote:
SQL Server doesn't exist or access denied

Try to check the information from here[^] and google.

wk_vigorous wrote:
This is an urgent problem that I can only conncet to MSDE 2000(release A, not sp3 or sp3a) from local computer but can not connect to the one running on remote PC

Try to check the connection library.[^]

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

wk_vigorous10-Nov-04 5:53

wk_vigorous

10-Nov-04 5:53

The operating system is xp sp2. I thought maybe the firewall block relevant port but even i close the firewall i still can't connet to MSDE.

vigorous

Richard Deeming10-Nov-04 7:42

Richard Deeming

10-Nov-04 7:42

This is probably an SP2 issue. Have a look at MS KB 841249[^] - How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server.

Another problem I have seen is if all protocols are disabled. Run the server network utility (svrnetcn.exe), and make sure you have at least one protocol in the "Enabled protocols" list.
http://www.trinet.co.uk/kb.asp?kbid=25[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer