Re: How to add a column to an existing SQL database table - Database Discussion Boards

Re: How to combine UPDATE and COUNT in a query [modified]

Arun.Immanuel22-Apr-07 4:34

22-Apr-07 4:34

Hope this helps U.

Test this with some fake table before U proceed.

update product set
ckecked='ok' where id not in (select product_id from expirationdate)

Regards,
Arun Kumar.A

-- modified at 10:43 Sunday 22nd April, 2007

Re: How to combine UPDATE and COUNT in a query

kubben22-Apr-07 4:55

kubben

22-Apr-07 4:55

Try this:
update p set checked='ok'
from product p
where id in
( select id
from product p2
join expirationdate e on p2.id = e.product_id
group by id
having count(*) = 0)

I guess the question would be if there are records or not, it might be easier to do a left join if there are no records.
So that would be:
update p set checked='ok'
from product p
where id in
( select id
from product p2
left join expirationdate e on p2.id = e.product_id
where e.product_id is null)

Hope that helps.
Ben

Syntax Error in Join Operation

freefika22-Apr-07 2:51

freefika

22-Apr-07 2:51

Hello,
I'm using access 2007 as the database for a crystal reports project in vs.net 2005. When i try to preview or view the report, i get the error:

"Failed to open rowset"
Details: ADO Error Code:0x
Source: Microsoft Jet Database Engine
Description: Syntax error in JOIN Operation
SQL State: 3000
Native Error.

I tried to analyze the SQL Query...but i dunt understand wat hte problem is:

SELECT `tblDesc`.`North`, `tblDesc`.`South`, `tblDesc`.`East`, `tblDesc`.`West`, `tblDoc`.`Loan_Applicant`, `tblDoc`.`SubType`, `tblDoc`.`Plot_No`, `tblDoc`.`BR_Count`, `tblDoc`.`Family`, `tblDoc`.`Maid`, `tblDoc`.`Study`, `tblDoc`.`Plot_Area`, `tblDoc`.`Zoned`, `tblDoc`.`Tenure`, `tblDoc`.`BUA`, `tblDoc`.`Original_Price`, `tblDoc`.`Reg_Date`, `tblIntro`.`VRN`, `tblIntro`.`RRN`, `tblIntro`.`R_Date`, `tblIntro`.`I_Date`, `tblTitles`.`Title`, `tblValuation`.`Market_Value`, `tblValuation`.`MV_Words`, `tblValuation`.`Replacement_Value`, `tblValuation`.`RV_Words`, `tblValuation`.`Forced_Sale_Value`, `tblValuation`.`FSV_Words`, `tblValuation`.`Valuation_Date`, `tblValuator`.`Name`, `tblPropertyType`.`Property_Type`, `tblReports`.`ReportID`, `tblDesc`.`Topography`, `tblDesc`.`Location`, `tblDesc`.`Access`, `tblDoc`.`Address`, `tblExtra`.`Quality`, `tblExtra`.`Occupancy`, `tblExtra`.`Services`, `tblExtra`.`Comments`
FROM ((`ReportDB`.`dbo`.`tblIntro` `tblIntro` INNER JOIN `ReportDB`.`dbo`.`tblValuator` `tblValuator` ON `tblIntro`.`ValuatorID`=`tblValuator`.`ValuatorID`) INNER JOIN (((`ReportDB`.`dbo`.`tblReports` `tblReports` INNER JOIN `ReportDB`.`dbo`.`tblValuation` `tblValuation` ON `tblReports`.`ValuationID`=`tblValuation`.`ValuationID`) INNER JOIN `ReportDB`.`dbo`.`tblExtra` `tblExtra` ON `tblReports`.`ExtraID`=`tblExtra`.`ExtraID`) INNER JOIN ((`ReportDB`.`dbo`.`tblDoc` `tblDoc` INNER JOIN `ReportDB`.`dbo`.`tblPropertyType` `tblPropertyType` ON `tblDoc`.`PropertyID`=`tblPropertyType`.`PropertyID`) INNER JOIN `ReportDB`.`dbo`.`tblTitles` `tblTitles` ON `tblDoc`.`TitleID`=`tblTitles`.`TitleID`) ON `tblReports`.`DocID`=`tblDoc`.`DocID`) ON `tblIntro`.`IntroID`=`tblReports`.`IntroID`) INNER JOIN `ReportDB`.`dbo`.`tblDesc` `tblDesc` ON `tblReports`.`DescID`=`tblDesc`.`DescID`
WHERE `tblReports`.`ReportID`=1

any help will be greatly appreciated...

Re: Syntax Error in Join Operation

Krish - KP22-Apr-07 17:43

Krish - KP

22-Apr-07 17:43

for ex. after INNER JOIN `ReportDB`.`dbo`.`tblReports` `tblReports`, again another INNER JOIN is starting.

Hope this is the problem

inner join usage is
INNER JOIN ON <condition>
where as in some of the inner joins of your query the "ON <condition>" is missing after <tablename>

Regards
KP

Casts from DataReader

Brady Kelly22-Apr-07 0:52

Brady Kelly

22-Apr-07 0:52

I don't normally do much data access work, so I've never got the better of this little demon. I'm getting an exception trying to cast a field (TaxRate) to a double, because it's an integer value. However, it is declared as 'float' in the DB, but just because the value for all records at this time is the integer 14, I can't cast it to a float. How daft is that?

Re: Casts from DataReader

xibeifeijian22-Apr-07 15:22

xibeifeijian

22-Apr-07 15:22

Not sure about your question.Can you try Convert class?

▒▒〓▒〓▒▒

▒〓〓〓〓〓▒

▒▒〓▒〓▒▒

▒〓〓〓〓〓▒

▒▒〓▒〓▒▒

How to add a column to an existing SQL database table

furjaw21-Apr-07 20:29

furjaw

21-Apr-07 20:29

Visual Basic 2005
I used DataSet Designer to add a column called "Case#" to an existing SQL table.
The table is used by a DataGridView in a VB 2005 program.
I got it working on my development computer in debug mode.
Then, when I installed it, I got an exception "Case# is an invalid column" and none of my data records appeared.

Adding the following statement did not help:
PatientDataSet.Patient.Columns.Add("Case#")

Re: How to add a column to an existing SQL database table

kubben22-Apr-07 1:51

kubben

22-Apr-07 1:51

I don't know anyway that you could add a column to a sql table just by adding it to your dataset designer. YOu need to have rights on your sql server to alter the table you want to add the column to. Then you would need to use a sql query to add the column. Assuming Case# is an integer your query statement might look like this:

ALTER TABLE yourtablenamehere ADD Case# int NULL

NOTE I can't remember if # is allowed in a column name or not. I guess running that query would tell you that.

Hope that helps.
Ben

Re: How to add a column to an existing SQL database table

Jerry Hammond22-Apr-07 4:26

Jerry Hammond

22-Apr-07 4:26

I know the pound is allowed if you're naming your columns within SQL2005. Not sure if you're doing so through VB.NET.

Learning is not a spectator sport. - D. Blocher

Re: How to add a column to an existing SQL database table

furjaw22-Apr-07 17:27

furjaw

22-Apr-07 17:27

I have a number of users running this application in remote locations.
Of course, they cannot lose their existing data.
It was requested that I include a column called "CaseNbr" in the table.
The file that I need to add the column to is an SQL table that is being updated via a DataGridView.
I think that it was a mistake to add the column via DataSet Designer, so, I restored my project from a backup eliminating that step.
So, now the column does not exist in the database and I am back to square one.
I would like to add the column to the SQL tables via my app so that my various users can have it added to their databases. Every user has his own database.
There is only one program that accesses the table and it is very simple.
The user selects (or adds) a row (Patient record) then the program simply passes that Patient's info to the main form of the application.
I know nothing about SQL. There are no SQL statements in my app. Everything is done by TableAdapter and BindingSource statements like:
Me.PatientTableAdapter.Fill(Me.PatientDataSet.Patient)
PatientBindingSource.Position = Form1.DefaultPatient
That's why I don't know how to add a column to the SQL table.

Re: How to add a column to an existing SQL database table

furjaw22-Apr-07 18:48

furjaw

22-Apr-07 18:48

I added the following statement to my program:

Me.PatientDataSet.Patient.Columns.Add("CaseNbr", Type.GetType("System.String"))

But it did not do anything.

I added the statement as the first command in the Form Load routine:

Public Class Patient
Public Sub Patient_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.PatientDataSet.Patient.Columns.Add("CaseNbr", Type.GetType("System.String"))
Me.PatientTableAdapter.Fill(Me.PatientDataSet.Patient)
PatientBindingSource.Position = Form1.DefaultPatient
End Sub

How to query this?

Arun.Immanuel21-Apr-07 18:55

Arun.Immanuel

21-Apr-07 18:55

Hi everyone!.

I have 2 columns in a table say col1 and col2.

Sample Data:
===========
col1   col2
60     1000
20     0
30     0
40     1500
20     0
70     0
80     0


I need a query that will display the following output:
col1   col2    col3
===================
60     1000    1000
20     0       980 
30     0       950 
40     1500    1500
20     0       1480 
70     0       1410 
80     0       1330 


Description:
============
if col2 not equal to ZERO then 
    col3=col2.
else
    col3=previous col3 - current col1

I don't need cursors.

Thanks in advance.

Regards,
Arun Kumar.A

Re: How to query this?

Colin Angus Mackay21-Apr-07 23:42

Colin Angus Mackay

21-Apr-07 23:42

Arun.Immanuel wrote:
I don't need cursors.

You mean you don't "want" cursors. "Need" and "want" are different concepts.

The only way I can see to do this without cursors is in the client side code. i.e. in the application that calls the SQL.

Now, two importance concepts about databases here:

1. The data is essentially unordered. The implementation of the database may appear to give the data an order, but that is just incidental. You should not rely on that order. If you want guaranteed order then you have to define something in the table that you can ORDER BY

2. When retrieving data, an individual row has no concept of the rows around it - or even if there are any rows around it - so it will not be able to subtract anything from a column on the "previous" row. (There is no order, remember, so the concept of "previous" or "next" does not apply)

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: How to query this?

Arun.Immanuel21-Apr-07 23:58

Arun.Immanuel

21-Apr-07 23:58

Thank U very much.

I have done that with the .NET code.

I just want to know , if there exists any query.

Regards,
Arun Kumar.A

Re: How to query this? [modified]

Arun.Immanuel22-Apr-07 0:25

Arun.Immanuel

22-Apr-07 0:25

Hi,

Thank U very much for Ur reply.
Sorry for not explaining the entire scenario.

I know that the rows in SQL are not stored in order.

But, this is what I am doing:

I am querying the above 2 columns using "ORDER BY Date".
Here "Date" is another column based on which I am retrieving the records.
So, the output will definitely be in order.

Thus we can refer the value of row(n) from row(n+1) since I am having
the column Date with all the dates(i.e.no date missing).

And the query would be:

select A.col1,A.col2,B.col2
from tblName A,
( select Date-1 "Dte",col2 from tblName )B
where A.Date=B.Dte

Here B.col2 will be shifted 1 level with respect to A.col1.
I have not included the order by clause here.

So, I will be able to refer previous row from the current row.

But, I do not know how to implement the required output as mentioned above.

Any help would be appreciated.

Regards,
Arun Kumar.A

-- modified at 6:41 Sunday 22nd April, 2007

Re: How to query this?

DQNOK24-Apr-07 12:03

DQNOK

24-Apr-07 12:03

Have you been successful yet? I'd like to see your solution if you have.

I toyed with this for a couple of minutes, but quickly realized that your algorithm can best be stated in pseudo-code like this:

<code>getCol3( rownum )
{
   if( 0 = col2(rownum) ) Then
      return getCol3(rownum-1) - col1(rownum);
   else
      return col2(rownum);
}</code>

which is a *recursive* procedure. Some DBMs support recursive queries (Oracle and DB2 for instance), but I'm not sure even they would work here as they tend to recurse on *existing* field values, not on generated values like you want here. Check out the CONNECT BY clause if you're interested.

I'm inclined to think Colin is correct here; I SERIOUSLY doubt this can be done without procedures. I have similar requirements in my tables, but have resigned myself to putting the table updates into loops within code.

Let us know if you do figure out a clever way.

Good luck.

David

How to work with manage code in the dll

Romiks21-Apr-07 8:55

Romiks

21-Apr-07 8:55

Hi All,
I have another problem with calling manage code from dll.
I do the following:
1. Create dll project in the VS2005, add /clr option under project property->C++->general
2. Add header file to the project with interface:

<br />
 #define Base_API __declspec(dllexport)<br />
<br />
 class BaseI_SqlDbConnection;<br />
 class Base_API Base_SqlDbConnection<br />
 {<br />
  public:<br />
   Base_SqlDbConnection(); <br />
   BaseI_SqlDbConnection *m_pi;<br />
 };<br />

3. Add define of class to the .cpp file. It is the simple wrapper on the SqlClient.

<br />
 using namespace System;<br />
 using namespace Data;<br />
 using namespace SqlClient;<br />
<br />
 #ifdef _MANAGED<br />
 #pragma managed(push, off)<br />
 #endif<br />
 BOOL APIENTRY DllMain( HMODULE hModule,<br />
  DWORD ul_reason_for_call,<br />
  LPVOID lpReserved<br />
 )<br />
 {<br />
  return TRUE;<br />
 }<br />
 #ifdef _MANAGED<br />
 #pragma managed(pop)<br />
 #endif<br />
 class BaseI_SqlDbConnection<br />
 {<br />
  public:<br />
   BaseI_SqlDbConnection()<br />
   : m_SqlDbConnection(gcnew SqlConnection){}<br />
<br />
   gcroot<SqlConnection ^> m_SqlDbConnection;<br />
 };<br />
 Base_SqlDbConnection::Base_SqlDbConnection() : m_pi(new BaseI_SqlDbConnection)<br />
 {<br />
 }<br />

4. Call the class from MFC application looking like this:
Base_SqlDbConnection test;

With help of debugger, I found that the BaseI_SqlDbConnection()
constructor doesn't want to call and object m_SqlDbConnection could not be created.

I guess that the problem is in the managed code, but I am newbie for this. Please help
me to resolve the problem or point me to the article about this.

SQL Server enum datatype problem

tanvir.faraj20-Apr-07 22:31

tanvir.faraj

20-Apr-07 22:31

I want to declare a datatype like this:

<br />
create table abc<br />
(<br />
days enum('sat','sun','mon')<br />
)<br />

How can i do that? I know that mysql, oracle supports this type of database but what about sql server 2005? Also can i use user defined datatype for this kind of problem? if so then please tell me how.

Thanks.
TFR

Re: SQL Server enum datatype problem

Sascha Andres22-Apr-07 3:22

Sascha Andres

22-Apr-07 3:22

Hi,

there is no enum datatype in MS SQL. You may use a check
constraint. Something like that:

CREATE TABLE tblTest (<br />
  ID INT PRIMARY KEY IDENTITY,<br />
  ANormalColumn VARCHAR(10) DEFAULT ('') NOT NULL,<br />
  RestrictedColumn INT<br />
  CHECK (RestrictedColumn IN (1,2,3)<br />
)

The first of the following inserts is OK, while the latter
throws an error:

INSERT INTO tblTest (ANormalColumn, RestrictedColumn) VALUES ('A', 1)<br />
INSERT INTO tblTest (ANormalColumn, RestrictedColumn) VALUES ('B', 4)

-sa

--
http://www.livingit.de
http://www.not2long.net

query of select max number

Mohammad Daba'an20-Apr-07 19:48

Mohammad Daba'an

20-Apr-07 19:48

Hi, i have a DB table which has just only one column, i have an integer column field in it, i want to know the query for retriving not the highes number (i mean as select max of the column) i want the select the number comes before the max number, Wink | ;)

Thanks alot
Hamody

Re: query of select max number

japel20-Apr-07 20:03

japel

20-Apr-07 20:03

Here you go...

SELECT Max(myIDColumn) FROM myTable

This will return the highest number in a column from a table.

When people make you see red, be thankful your not colour blind.

Re: query of select max number

Mohammad Daba'an20-Apr-07 20:06

Mohammad Daba'an

20-Apr-07 20:06

hello, thanks i know that
SELECT Max(myIDColumn) FROM myTable
will return the highest or max number, i want to get the number comes before the max number not the max itself Cry | :((

Thanks alot
Hamody

Re: query of select max number

japel20-Apr-07 20:24

japel

20-Apr-07 20:24

Sorry didn't read you post very well.

SELECT Max(myIDColumn - 1) FROM myTable

When people make you see red, be thankful your not colour blind.

Re: query of select max number

Mohammad Daba'an20-Apr-07 20:34

Mohammad Daba'an

20-Apr-07 20:34

thanks a lot there, merci beacoup Rose | [Rose]

Thanks alot
Hamody

Re: query of select max number

Arun.Immanuel21-Apr-07 3:59

Arun.Immanuel

21-Apr-07 3:59

I think this will work if the number u require is always
1 less than the max number.

Here is the command for the column which have random values.

select max(colName) From tblName where
colName not in( select Max(colName)From tblName)

Regards,
Arun Kumar.A

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.