|
I have the following query statement in RichTextBox control
While executing i am reading the text from the Richtextbox and passing it to
SqlDataAdapter object. But it is telling me that incorrect syntax near 'GO'
statement
MyQuery:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_createAddEditscript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_createAddEditscript]
GO
MyCode:
try
{
SqlConnection con = new SqlConnection(conStr);
SqlDataAdapter da = new SqlDataAdapter(this.richTextBox1.Text.ToString(), con);
DataSet ds = new DataSet();
da.Fill(ds);
//this.dataGrid1.DataSource = ds.Tables[0];
}
catch (SqlException e1)
{
MessageBox.Show(e1.Message);
}
Please help me
Thanks in Advance,
Pothirajan C
|
|
|
|
|
Hi
In SqlDataAdapter da = new SqlDataAdapter(this.richTextBox1.Text.ToString(), con);
The first parameter in the Select Command and DA. Use this command to fill the dataset. As you are written you drop a procedure and does not select anything !
To run such command against Sql Server use SQlCommand class.Like this :
SqlCommand cmd = new SqlCommand("<sqlcommand>",<sqlconnection>);
cmd.ExecuteNonQuery();
|
|
|
|
|
The reason is that "GO" is not part of SQL. It is a command for the Query Analyser to tell it to run the next set of statements in a different batch.
Also, as your query is not returning a result set you should not be trying to Fill a DataSet. You should create a SqlCommand object then ExecuteNonQuery() on it. For example:
SqlCommand cmd = new SqlCommand("if exists (select * from dbo.sysobjects where id ="+
"object_id(N'[dbo].[sp_createAddEditscript]') and OBJECTPROPERTY(id, N'IsProcedure')"+
" = 1) drop procedure [dbo].[sp_createAddEditscript]", myConnection);
myConnection.Open();
cmd.ExecuteNonQuery();
myConnection.Close();
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
hi all check this out, it returns this error
ERROR [HYT00] Subquery returns more than 1 row
the update query is as follows
UPDATE Products p, shoppingcart s
SET p.Prodquantity = (p.ProdQuantity - s.Quantity)
WHERE p.ProductID =(SELECT ProductID FROM ShoppingCart
WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "'
Why is this and how do I get round it???
Thanks in advance
Jetset
|
|
|
|
|
jetset32 wrote:
SELECT ProductID FROM ShoppingCart
WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "'
This is returning more than one row. You need to make sure each cart can only contain each product once, or change your SQL, or do this:
UPDATE Products p, shoppingcart s
SET p.Prodquantity = (p.ProdQuantity - s.Quantity)
WHERE p.ProductID =(SELECT TOP 1 ProductID FROM ShoppingCart
WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "'
The TOP 1 thing is a hack, there is obviously a problem in the data in your database.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
thanks,
havnt had time to try this yet, but will let u know the outcome.
|
|
|
|
|
the TOP 1 thing didnt work, and I have tried every way i know to get this to work! the shopping cart will only contain the productID once, so am still a little clueless on this one!
Any one have any ideas?
Cheers
|
|
|
|
|
jetset32 wrote:
the TOP 1 thing didnt work, and I have tried every way i know to get this to work!
OK, is there more to the SQL than we're seeing ? ( it ends in a + ).
jetset32 wrote:
the shopping cart will only contain the productID once, so am still a little clueless on this one!
Have you made sure this is the case ? It seems the problem is elsewhere, or the TOP 1 thing would have worked, but that's still the only place you have a subquery that you assume will return one item, and apparently it's not. Have you run the SQL in query analyser, a bit at a time ( like do the select for the product ID first, then if that works, add the rest, etc ) to see what you get ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi,
The sub query will return more than one product id , this is because the customer has bought more than one item.
the query works if only one item is purchased but if more than one item is bought then mysql returns the error more than 1 row is returned, that is correct.
I want it to update all the product id's with the quantity that the customer has bought.
Can this be done in MySQL, I dont know.
Thanks in advance
Stuart
|
|
|
|
|
OK, then you're going about it completely wrong
UPDATE Products p, shoppingcart s
SET p.Prodquantity = (p.ProdQuantity - s.Quantity)
WHERE p.ProductID =(SELECT ProductID FROM ShoppingCart
WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "'
Why 'WHERE ProductID = ProductID' ? That's the same as WHERE 1=1.
UPDATE Products p, shoppingcart s
SET p.Prodquantity = (p.ProdQuantity - s.Quantity)
WHERE p.ProductID = s.ProductID
AND s.CartID = '" + Convert.ToString(cartID) + "'
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Cheers, your right, I knew this site was a god send.
Thanks for all your help with this
Stuart
|
|
|
|
|
Is it possible to get the record which is updated when an update trigger executed? Thank you.
<italic>Work hard, Work effectively.
|
|
|
|
|
Hi everybody!
I 'm going to generate a report which I need the summery of some times for example I 've three recorde with these values :
ID Desc Time
2 Something 03:15:45
6 Something else 12:48:50
12 Another Thing 20:15:36
in my report I need something like this:
Sum of time : 36:20:11
any body can help me to do this. may be a stored procedure ,may be a way?????
Masoud_TB
|
|
|
|
|
Hi,
I am using the GetSchemaTable() method of the C# .NET OleDbDataReader class to read the schema of an Access table. The following columns always have the same wrong values:
AllowDBNull = True
IsUnique = False
IsKey = False
IsAutoIncrement = False
Why do these schema columns show the opposite values of what they should be?
Any help appreciated,
Royce
|
|
|
|
|
Can someone tell me whether or not the below SQL will do what I want it too...
I am trying to pull a listing of categories with the number of articles under each in a single query instead of using two queries and a loop. Does the below SQL seem coorect for doing this? Is this evenb possible?
Also it's important to note, i'm not interested in returning anything from the article list table, just the details from the categorey are required, one from each categorey NOT one for each categorey for each article returned as well... 
$sql = '<code>SELECT</code> cat_list.pkid,
cat_list.parentid,
cat_list.name,
cat_list.img_url,
cat_list.content,
<code>COUNT</code>(class_articles.pkid) <code>AS </code>article_cnt
<code>FROM </code>class_articles, cat_list
<code>WHERE </code>class_articles.parentid=cat_list.pkid
<code>AND</code> cat_list.pkid='.$parentid;
Regards,
Alex
It's frustrating being a genius and living the life of a moron!!!
|
|
|
|
|
First, you don't need that join because it is redundand. You could quite easily put WHERE class_articles.parentid=...
Second, COUNT is an aggregator and requires that the other columns be placed in a GROUP BY clause at the end of the statement so that it knows what to count. i.e. GROUP BY pkid, parentid, name, img_url, content
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
hey
when i try to search an "noise word" it drop me an ERROR
what i have done :
- stop full text service
- remove the words i want ,from noise.enu , noise.eng , noise.dat (@@language = u.s...) the files is in system32 and in program files\...\config .
- start full text service
-and rebuild and after that re populate the catalog
and it still drop me an ERROR !!! how can i resolve this prob ???
tnx tnx .
|
|
|
|
|
removing ignored words with full-text catalog
|
|
|
|
|
hey
when i try to search an "noise word" it drop me an ERROR
what i have done :
- stop full text service
- remove the words i want ,from noise.enu , noise.eng , noise.dat ('@@language' = u.s...) the files is in system32 and in program files\...\config .
- start full text service
-and rebuild and after that re populate the catalog
and it still drop me an ERROR !!! how can i resolve this prob ???
tnx tnx .
|
|
|
|
|
Hi all
I have an empty database in access. I want to create the tables using SQL statements as that easier for me.
How can I do that with MS Access? remember tha db is empty
Thanks
|
|
|
|
|
You know, this is really easy to find in Microsoft Access' online help.
Type "How do I create table in SQL" in to the answer wizard (That's the annoying paper clip thing)
From the selection select "Create an SQL-specific query" (it was the first choice for me). From the page that is shown, expand the section marked "Create a data-definition query to create, delete, or alter tables, or to create indexes in a database". That should answer your question.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
My project is in initial phase.i m doing project on SPATIAL MESSAGING. The system is for rescue purposes mainly.Each user will b equiped with a PDA ( which v ppl havnt arranged yet) n can send msg to a main server via GPRS.Users will b connected throu website(our design is like this...u can suggest somthing else)ideally this system is to b designed wid PDA users, but no PDA here...cant afford...so we ourselves are just attaching GPS n GPRS modules with LCD-developed a microcontroller based Hardware.)That device(v’ll develop) shud only b able to send n receive some text msges.Some database will store msgs with time n coordinates.Do we need full os for this?Shuld we embeded linux or ne embedded window therer....wt to use...how....umm...a website(wap site) is to designed n a database...with dynamic entries....wt i hav studied is web services 'll be used...website is connected to a database in SQL. Every time a user gets connected to website...is entered automatically in database...database will check status of user after some time n if user no longer exists...remove entry automatically....As far as i hav studied i 'll need vb.net n web services for website n sql n ado.net for database....m i right?
Will u plz guide me k how it wid b done...n wat shuld i do n study n in wat order
v doing work in a group n i m assigned the job of server, databse, webservices....i m almost new to all this n now a days studying on these.Ur advice n help ll b welcomed by me n i wud b much thankful to u...n if possible 4 u, kindly refer some other person if available n if possible who can help me out n is willing to do so
Regards
|
|
|
|
|
virgo* wrote:
me n i wud b much thankful to u...n if possible 4 u,
Man, this crap is annoying to read. how old are you, 8 ?
virgo* wrote:
....As far as i hav studied i 'll need vb.net n web services for website
No. You can use vb.NET or C# ( it sounds like VB.NET is more your style tho ) to write a website, which requires ASP.NET. It sounds like you don't necessarily want a website ( something you can connect to via a browser ), and for the PDA connection, you will need a webservice ( again, C# or VB.NET, and so long as we're naming technologies, XML ).
virgo* wrote:
sql n ado.net for database....
First, you need a database, probably SQL Server 2000. Then you will use SQL to write the database, and ADO.NET isjust a library in VB.NET or C#, nothing special you need to source. If you look at data access examples in either language, you'll be looking at ADO.NET.
virgo* wrote:
Some database will store msgs with time n coordinates.Do we need full os for this?
Yes, no database will run without an OS. And if you use Linux, expect hell getting .NET to connect to the DB. At least, I would have thought so.
virgo* wrote:
i m assigned the job of server, databse, webservices....
What experience do you have in these areas ? Or is the whole thing bound to go belly up ecause you guys have an idea, and no experience at all ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
|
Sorry, I cannot understand your post, all the abbreviations were giving me a headache.
Look at what you wrote:
b ==> be
v ==> ? I have no idea what this means
ppl ==> people
n ==> and
msg ==> message
thou ==> through
u ==> you
wid ==> with (also as abbreviation for "would")
v'll ==> we'll
shud/shuld ==> should
msges/msgs ==> messages (why the two different spellings?)
ne ==> any?
therer ==? ???
wt/wat ==> what
i ==> I (is it really that hard to hit the shift key?)
hav ==> have
m ==> am
plz ==> please
Ur ==> your
ll ==> will
4 ==> for
¡Por Dios!
Your post is unstructured - which is possibly a reflection on your thinking. If you are to get anywhere you need to communicate effectively. I've read your post through twice now and I don't know what your problem is, all I know is that you have a problem and you are stressed by it.
I'll try and answer the recognisable stuff.
virgo* wrote:
Every time a user gets connected to website...is entered automatically in database...database will check status of user after some time n if user no longer exists...remove entry automatically....
Do you mean that each time they connect to the website there is some sort of audit trail created? Or that they just get authenticated onto the website based on the credentials supplied being verified against details in the database?
If it is the former then you can create a simly INSERT query to be run when they access a page. This can be triggered off in, say, the Page Load event handler of an ASPX page.
If it is the latter then you can google site:msdn.microsoft.com Forms Authentication SQL Server and you should find a solution that you can use in the first few hits.
virgo* wrote:
As far as i hav studied i 'll need vb.net n web services for website n sql n ado.net for database....m i right?
Yes. VB.NET as the language, using ASP.NET to use web services, SQL Server for the backend database and ADO.NET to access it in your application (that's the web service you'll be writing)
virgo* wrote:
Will u plz guide me k how it wid b done...n wat shuld i do n study n in wat order
There are many articles on CodeProject and MSDN that will help you - Those are the two main sources of free information that I use on a daily basis. You should learn how to use Google effectively and ensuring that your terminology is correct will help. That is why I have tried to straighten out the terms above as precision helps to find things faster.
I hope this guides you in the right direction.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
