|
I had an error with % at end of query
<pre>select a.AdHeadline,a.AdText,a.DisplayUrl, a.TargetUrl from AdCampaign a inner join Adzone b
on a.sitecategory=b.category and a.language=b.language and a.keyword like %b.keyword%;</pre>
Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'b'.
|
|
|
|
|
make it as
'%'+b.keyword+'%'
|
|
|
|
|
USE pubs<br />
IF EXISTS (SELECT name FROM sysobjects <br />
WHERE name = 'au_info2' AND type = 'P')<br />
DROP PROCEDURE au_info2<br />
GO<br />
USE pubs<br />
GO<br />
CREATE PROCEDURE au_info2<br />
@lastname varchar(30) = 'D%',<br />
@firstname varchar(18) = '%'<br />
AS <br />
SELECT au_lname, au_fname, title, pub_name<br />
FROM authors a INNER JOIN titleauthor ta<br />
ON a.au_id = ta.au_id INNER JOIN titles t<br />
ON t.title_id = ta.title_id INNER JOIN publishers p<br />
ON t.pub_id = p.pub_id<br />
WHERE au_fname LIKE @firstname<br />
AND au_lname LIKE @lastname
this example was taken from msdn like examples
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
Hi,
I'm writing an application and I keep receiving an "Invalid Operation Exception" with this message: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
I created a DataSource using the "Add New Data Source" option from Visual Studio 2010 and dragged the controls onto the form. The dataset is set to detail instead of datagrid.
Showing at the bottom of the form I have a ktReunionDataset, reunionBindingSource, reunionTableAdapter, tableAdapterManager.
This table was originally created from a MS-Access database and then was cloned using Visual Studio. I assigned a primary key to the "ID" field through the Server Explorer via the Open Table Definition, then right clicking on the ID field and assigning it the Primary Key. The ID field shows a key icon indicating it's the Primary Key.
The code for the Save button off the navigator looks like this:
private void reunionBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
this.Validate();
this.reunionBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this.kTReunionDataSet);
}
Do I need to manually code an update for when the user presses the save button, and iterating through each entry? I have looked at other examples and it doesn't have an update attached to the Data Adapter only Fill and FillByxxx so I'm not sure I need to add something here? Any help is appreciated.
Thank you,
Glenn
|
|
|
|
|
have you tested your update SQL statement on the database? if so does that give an error?
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
Please don't remove your question after you have received an answer (you posted a message regarding this problem on the VB.NET forum). Others may have the same question, and can benefit from the answers.
If your adapter only has a fill command, then you may have configured it incorrectly. Try to configure it again, somewhere the wizard will ask you if you also want update, delete, and insert commands. I suspect that you left some checkbox unchecked the first time when you created the adapter.
My advice is free, and you may get what you paid for.
|
|
|
|
|
I got this to work, it appears that Visual Studio did not generate the Update methods. After going into the design view of the dataset and right clicking on the Fill,GetData of the TableAdapter and selecting "Configure" then clicking NEXT all the way through re-generates everything. After doing this everything works perfect.
Glenn
|
|
|
|
|
Ex:
Tables:
1) Account: ID,UserName,Pass
2) Campaign: ID,AccountID,Date,Balance
3) CampaignSpec: ID,CampaignID,Spec,Color
Note: Account.ID = Campaign.AccountID and Campaign.ID = CampaignSpec.CampaignID
|
|
|
|
|
delete a, b, c
from Account as a inner join Campaign as b
on a.[id] = b.[id] inner join CampaignSpec as c
on b.[id] = c.campaignid
and just include any where clause that you need else all will be deleted
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
I would suggest the more "proper" way to do this would be to set up proper foreign key relationships along with cascade options in the database. Then you don't have to worry about this at all.
|
|
|
|
|
Hi,
I have one table as,
id Items
1 col1
2 col2
3 col3
another table as
Sid col1 col2 col3 col4
1 A B NULL NULL
2 D E NULL F
now i have to compare Items of tbl1 with columns of tbl2.
here i need the output as true or false from select query.
for ex. col1 of tbl1 is compared with col1 of tbl2.if the col1 has value then it should return false. if all values are null then it should return as true.
here im expecting col1-false,col2-false,col3-true
Nothing is Impossible. Keep always Smiling... 
modified on Wednesday, May 5, 2010 5:39 AM
|
|
|
|
|
What have you tried so far ?
My advice is free, and you may get what you paid for.
|
|
|
|
|
I created a table in SqlServer with
CREATE TABLE Foo(
column1 bit not null default 0,
.....
)
this implicitly created a randomly named constraint which would set the default value.
Now when I try to run
ALTER TABLE Foo DROP COLUM column1
I got the error message DF_Foo_colum_2F34CDE is dependent on column1,
i.e. the randomly named constraint which sets the default value prevent the deletion of the column.
Now I could fix that on my database, but how could I write a script which will work on all databases?
A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station....
_________________________________________________________
My programs never have bugs, they just develop random features.
|
|
|
|
|
I found out how to find those bastards!
SELECT * FROM sys.sysobjects Where NAME LIKE 'DF__Foo%'
or
SELECT * FROM sys.objects type_desc='DEFAULT_CONSTRAINT'
But I also had a better work around: 
1. these nameless constraints being only on developers machines so far, I just dropped them manually
2. and give them a name in the create table with
CREATE TABLE FOO (
column1 bit not null CONSTRAINT DF_Foo_column1 DEFAULT 0
....
)
A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station....
_________________________________________________________
My programs never have bugs, they just develop random features.
|
|
|
|
|
Hello,
I am trying to read a DateTime value from an Oracle database (Oracle Version 11.2.0.1.0 running on a German Windows XP). The column is defined as "DATE", and the value is correctly shown as "2010-05-04 16:17:10.0" via the web browser when viewing all the data of the respective table.
But when I retrieve the data with the OracleClient (via OracleCommand and OracleDataReader) in a C# application (.NET 2), I receive a string (!) value of
"04.05.10 " , i.e. reader["LASTLOGIN"] returns a string with that value which I then convert to a DateTime using the Convert.ToDateTime() function.
By the way, a
int lastLoginCol = reader.GetOrdinal("LASTLOGIN");
LastLogin = reader.GetDateTime(lastLoginCol);
yields a "Specific cast is not valid" error message.
Please note the 6 blanks after the date part. Where has the time part gone to?
How can I retrieve the full date time value? Is the answer: "Not at all, it's Oracle"?
And what about a situation where the server is running with a different locale than the client? Is it really necessary to change to CHAR 14 (i.e. YYYYMMDDhhmmss - that does work with some tricks)?
Thanks a lot for your ideas!
Bernhard
|
|
|
|
|
The Oracle DATE type for the column does not have a corresponding equivalent within most languages you will use. As a result it must be cast to some other intrinsic type. Usually what will happen is that Oracle will convert it to a string using a session specific date format specification. Look up on NLS_DATE_FORMAT and if you don't know what that format is, the following might help you
SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
In .NET all you have to do is open an OracleDataReader from an OracleCommand and then call GetDateTime. It works for me. .NET should handle all the type conversion issues: the OracleDataReader "knows" how to convert SQL types to .NET types so that I don't have to. It's called encapsulation. Or maybe abstraction, I can never remember the difference between those two.
|
|
|
|
|
Thanks a lot for your input. The problem has dissolved somehow - I use to shutdown the server and the client in the evening and start them again next morning, and now the problem cannot be reproduced. Maybe there was some inconsistent state in the Oracle database, during development things can go wrong and Oracle likes to punish you horribly for the smallest misdeeds....
|
|
|
|
|
Hi All,
I am struggeling finding an effectient way to find a distance between a Point that intersects a polygon and the border of that polygon. I was able to use the STDistance comparing the point to every point that made up the polygon but that is taking a lot of time. Using SPatial indexe wasn't much helpful because the STDistance is not part of any constraint and even when I did put the constraint, the index didnt help much.
I appreciate any feedback.
Thanks
|
|
|
|
|
Looks like you might have to write some code. For the basics, google "distance point line". Once you have that, you just need to find the minimum of the distances to the edges of the polygon. Calculation should be O(n) for an n-gon.
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
Thats what I am doing but its freakin slow. After a lot of research I found an excelent blog describing an efficient way to do this:
Nearest neighbors[^]
|
|
|
|
|
hi iam using sql server 2000
my table is branch
BranchName nvarchar(50),
ContractNo nvarchar(50),
Email nvarchar(50),
StartDate datetime,
EndDate datetime,
ContractReminder int,(5days,10days,15days)
remindertype int(1month,3month,6month,annual)
i have to send the email to the user before enddate and check the ContractReminder if it is for 5 days then send before 5days ,if it is for 10days then before 10 days and also check the remindertype if it is for one then before one month before contractreminder ie 5 days or 10 days i have to send the mail like this for three months, 6 months , and for annually
how to write this querry can you give an idea which helps me
|
|
|
|
|
It looks like you need a job to run every day that by using two datediff criteria using an OR between them.
This could then get the list that is sent to.
Sorry I cannot be more specific as I am installing SQL 2008 R2 on my system, so I cannot try an example.
See BOL for Datediff and you will use days I would suggest using days for the month also as that would prevent the letter from being sent every day of the month.
SELECT * FROM branch
WHERE DATEDIFF('dd', GETDATE(), EndDate) = ContractReminder
OR DATEDIFF ('dd', GETDATE(), EndDate) = ReminderType * 30 NOT TESTED
Good Luck,
djj
|
|
|
|
|
hi iam using this querry
SELECT BranchName,ContractNo, Email,StartDate,EndDate, ContractReminder,
DATEADD(mm,ReminderType,StartDate)-ContractReminder as newd
it works fine for the first time only for 1 month, 3months,or 6months,
suppose the starttime is 2010-01-01 and enddate is 2010-12-31
the querry will works fine for only first time for 1st month, or 3rd month, or 6th month
but i want the querry to check upto enddate repeatedly ie for a year i want the querry to be executed 12 times, if it is for 3 months the querry should be executed for 4 times in a year
how can i get this querry please give suggesstion
|
|
|
|
|
A suggestion would be
SELECT BranchName, ContractNo, Email, StartDate, EndDate,
CASE
-- notice that the 101 is my favorite see BOL Convert for list of others
WHEN CONVERT(VARCHAR, DATEADD(day, ContractReminder, GETDATE()), 101) > EndDate
THEN 'Less than ' + CAST(ContractReminder as VARCHAR) + ' days left'
WHEN CONVERT(VARCHAR, DATEADD(month, remindertype, GETDATE()), 101) > EndDate
THEN 'Less than ' + remindertype + ' months left'
END AS MyFlag
INTO #RList
WHERE CONVERT(VARCHAR, DATEADD(day, ContractReminder, GETDATE()), 101) > EndDate
OR CONVERT(VARCHAR, DATEADD(month, remindertype, GETDATE()), 101) > EndDate
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
