|
Hi all,
i'm trying to build a SQL statement,
commStr = "insert into mytable values(default,?number,?text,?date) " +<br />
"select * from mytable " +<br />
"where not exists(select * from mytable where int_field = '" + <br />
System.Convert.ToInt32(TextBox1.Text) + "')";<br />
and I keep getting the following error
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> MySql.Data.MySqlClient.MySqlException: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from mytable where not exists(select * from mytable where int_field = '' at line 1<br />
at MySql.Data.MySqlClient.PacketReader.CheckForError()<br />
at MySql.Data.MySqlClient.PacketReader.ReadHeader()<br />
at MySql.Data.MySqlClient.PacketReader.OpenPacket()<br />
at MySql.Data.MySqlClient.NativeDriver.Prepare(String sql, String[] parmNames)<br />
at MySql.Data.MySqlClient.MySqlCommand.Prepare()<br />
at Service.InsertDB(String insStr, String connStr, Int32 inInt, String inText, String inDate)
the SQL string that is being passed looks like
insert into mytable values(default,?number,?text,?date) select * from mytable where not exists(select * from mytable where int_field = '20')<br />
What I'm trying to do is check that the entry is not already present in the database before i insert it.
Some help I'm going mad with this.
Thanks in advance.
|
|
|
|
|
From this page[^] it looks like you should simply say INSERT IGNORE INTO mytable .... I don't know if that applies to earlier versions of MySQL.
If you don't state which database you're using we tend to assume SQL Server 2000.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
The syntax is INSERT INTO table1 (field1,field2,field3) SELECT x,y,z FROM table2 WHERE fieldwhatever = 'whatever'
You need the same # of fields int he insert as what you select.
I don't know what the question marks in your sql are about, but my guess is that they are causing problems.
You shouldn't use the word values, but have the field names in the insert into.
Also, is it really your intention to insert duplicate rows into the table? Why do you have an exists clause, and you are selecting from the same table?
insert into mytable (default,number,text,date) select default,number,text,date from mytable where int_field = '20'
If you post your actual code...it would be easier to help you.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
i'm not trying to insert duplicate records i am 'trying' to do the opposite.
The '?' act sort of like tags so that the actual values can be put in later. The problem is definately not here as this works fine until i use the exist condition. I'm trying to use the exist to check for records as to not add duplicates.
I have solved the problem in the web page that I'm using but was just trying to solve the problem at the database level thus making it more reliable.
As the error message is quite obviously pointing to the sql statement, and the fact that a simple insert statement is working completely okay, then I am taking for granted that the problem occurs when i add the 'where not exists' condition. I was therefore just trying to get help with writing these kinds of sql statement. 
|
|
|
|
|
richiemac wrote: insert into mytable values(default,?number,?text,?date) select * from mytable where not exists(select * from mytable where int_field = '20')
You are inserting into mytable, and yet you are selecting from my table also. This is perfectly legal, however...it will insert duplicates into the table - if that is what you want.
Select * from mytable where not exists(select * from mytable where int_field = '20')
This will return NO records if there is a value of '20' in int_field for ANY record in mytable.
I don't fully grasp what you are trying to do, but look at this example of using exists in an insert into:
Three tables named (tblPeople,tblYoungPeople,tblDeadPeople)
All three have fields (PeopleID,LastName,FirstName,Age)
To fill the tblYoungPeople table with all people under 40 who are not dead, you would do this:
insert into tblYoungPeople (firstname,lastname,age)
select firstname,lastname,age from tblPeople p
where p.age < 40 AND not EXISTS(SELECT * from tbldeadpeople dp WHERE dp.peopleid = p.peopleid)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I thought a bit more about what you're trying to do. You want to insert a record into the table if, and only if, there are no records that have int_field = '20'.
Something you could do is like this:
insert into mytable (default,number,text,date)
select 'xxx' as default,123 as number,'yyy' as text,'2006/07/14' as date)
from mytable where not exists(select * from mytable where int_field = '20') LIMIT 1
The LIMIT 1 assures that only 1 record will be inserted (because you are selecting only 1 record). The values are aliased in the select statement, because you don't want to pull values from mytable.
Using my previous example, this worked:
INSERT INTO tblpeople (firstname,lastname,age)
SELECT 'John' as firsname, 'Williams2' as lastname, 20 as age
FROM tblPeople p
WHERE not EXISTS(SELECT * from tblpeople p2
WHERE p2.firstname = 'John' and p2.lastname='Williams2' and p2.age = 20) LIMIT 1
I hope this helps.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks for the help Eric.
I'm finally getting somewhere! Thanks again buddy!
|
|
|
|
|
Is there a way to change the datetime or smalldatime format without converting it to a string?
"All men by nature desire to know."
|
|
|
|
|
The internal format is always the same - 8 bytes for datetime, 4 bytes for smalldatetime. I'm not sure what you're asking here.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
let's say from yyyy/dd/mm to mm/dd/yyyy? Is that possible without using conversion?
"All men by nature desire to know."
|
|
|
|
|
Hi,
I want a SQL query which will return result set using exact match case.
e.g.
Query1:
SELECT * FROM <table_name> WHERE <column_1> = 'computer'
This query return result set with Column1 = 'computer'
Query2:
SELECT * FROM <table_name> WHERE <column_1> = 'COMPUTER'
This query return result set with Column1 = 'COMPUTER'
In above queries both return diff results.
Regards
Sumit
|
|
|
|
|
Did you mean you want to return results which contains both 'computer' and 'COMPUTER'?
In most SQLs the queries are case-insensitive which means both query 1 and 2 should return the same result.
If that is not the case, then just use WHERE UCASE(column1) = UCASE('Computer') so everything is search based on uppercase value.
If you want case-sensitivity instead, then just cast both values into varbinary, e.g. CAST('Computer' AS varbinary(8)).
For more information on SQL case sensitivity, you can read this link[^].
"You are special, but so is everybody else."
"Democracy is two wolves and a sheep voting on what to have for dinner" - Ross
Edbert
Sydney, Australia
|
|
|
|
|
As per your suggestion, I execute following query
SELECT * FROM TagMaster WHERE CAST('Metric' AS varbinary(6)) = CAST(TagName AS varbinary(6))
//Comment : There is a entry TagMaster where column TagName = 'Metric'
// But it return blank result set.
Then to cross check I execute following query
SELECT CAST('Metric' AS varbinary(6)), CAST(TagName AS varbinary(6))FROM tbl_UI_TagMaster
// Comment : Which gives two columns which are not matching at all.
// Like
-------------- --------------
0x4D6574726963 0x4D0065007400
0x4D6574726963 0x500072006F00
0x4D6574726963 0x410063007400
0x4D6574726963 0x500049004E00
0x4D6574726963 0x540069006D00
0x4D6574726963 0x45006D007000
0x4D6574726963 0x4C006F006300
0x4D6574726963 0x45006D006100
|
|
|
|
|
Read this[^]
Regards.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
I'm still pretty new to the Asp.Net world.
I have a website that a user will have to fill out some forms. Several of these forms I want the entries to be standardized into drop down boxes. (for intstance, ethnicity) I'd like to be able to pull values from an 'ethnicty' table and well several other tables for other drop down boxes.
How ever I have a concern, how many sqldatasource objects is to many? (Is it common practice to use as many as 10 different sql data sources on the same webpage?)
How well does the .net code optimize these data sources? For example, on each datasource does it open a connection, run the query then close connection for all 10 sources?
In general, if I ran the queries on page load and then set the values of the drop down boxes would/could that be more efficient?
Or are there a better places than page_load to do that??
Any info on these questions would be greatly appreciated, thanks
Drathmar
|
|
|
|
|
|
You should try looking at 'Factory' class which gives you an idea how to write database agnostic [generic] code.
|
|
|
|
|
I want to select categories with biggest id from the table below. For example, Diet should return 228 and excercise should return 225... I want distinct category with biggest id.
id Category
9 Diet
218 Diet
228 Diet
8 Exercise
215 Exercise
225 Exercise
1 Investing
224 Investing
234 Investing
5 Movie
219 Movie
229 Movie
Anybody can help me??
thanks in advance
-- modified at 22:36 Thursday 13th July, 2006
|
|
|
|
|
SELECT Category, MAX(id) AS MaximumID
FROM dbo.TableName
GROUP BY category
should do the trick. that is of course assuming you are using SQL Server, best in future to specify the actual database (and version) it removes any doubt and ensures the answer you get is accurate
|
|
|
|
|
thank you very much. This is exactly what i wanted.
|
|
|
|
|
No worries, MAX is one of SQL Server's "aggregation" functions. There are lots - SUM, MIN, AVG etc etc and Grouping is a very powerful feature - definitly worth the time to look into and understand.
|
|
|
|
|
//String* con = S"Provider=Microsoft.Jet.OLEDB.4.0;" + S" DataSource=C:\\AEM\\Database\\ProTunerDatabase.mdb";
OleDbConnection* ole = new OleDbConnection( S"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\AEM\\Database\\ProTunerDatabase.mdb" );
//OleDbDataAdapter* dadapter = new OleDbDataAdapter(S"select * from Item_List", ole);
OleDbDataAdapter* dadapter = new OleDbDataAdapter();
DataSet* dset = new DataSet();
ole->Open();
OleDbParameter* par;
dadapter->SelectCommand = new OleDbCommand(S"select * from test", ole);
dadapter->Fill( dset, S"test" );
dadapter->UpdateCommand = new OleDbCommand(S"update test set Item_name=@str where Item_id=@prev");
par = dadapter->UpdateCommand->Parameters->Add(new OleDbParameter ("@str", OleDbType::VarChar));
par->SourceColumn = "Item_name";
par->SourceVersion = DataRowVersion::Current;
par = dadapter->UpdateCommand->Parameters->Add(new OleDbParameter ("@prev", OleDbType::Integer));
par->SourceColumn = "Item_id";
par->SourceVersion = DataRowVersion::Original;
dadapter->UpdateCommand->Connection = ole;
dadapter->Update(dset,"test");
HOW DO I SPECIFY VALUES FOR @str and @prev ????
so that i can update my database.
I am using Visual C++.NET in Visual Studio 2005
Thanks,
Kedar Dave
|
|
|
|
|
dadapter->UpdateCommand->Parameters("@str")->Value = "test".
|
|
|
|
|
dadapter->UpdateCommand->Parameters("@str")->Value = "test";
I tried it....but it gave me the following errors:-
error C2064: term does not evaluate to a function taking 1 arguments
error C2227: left of '->Value' must point to class/struct/union/generic type
why do i get this?
setting par->Value = "test"; -- doesnt giv any errors but doesnt work either
Thanks,
Kedar Dave
|
|
|
|
|
My mistake. Parameres["@str"] - square brackets, not parentheses
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
