|
Hi,
This is really bugging me, I have a product table with a quantity column, When a user buys an item I want the quantity column to change to "SOLD" or 0.
The thing is, how do I do this if a customer buys more than one item at a time?
I need to be able to pass the number of product ID's to the database and then let a stored procedure UPDATE the selected ProductID's Quantity column to "SOLD" or 0.
Somebody help or put me in the right direction! I have tried all i can think of but my SQL knowledge is limited!
Thanks in advance
JetSet
|
|
|
|
|
Hope this will help, when the user buy 3 item with the product ID 2, execute the stored procedure passing the quantity and the ID
update TableName set Qty=Qty-@Qty where ID=@ID
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
thanks, I worked it out in the end, and it only took me the whole day!!!
UPDATE Products
SET ProdQuantity = 0
WHERE ProductID =(SELECT ProductID FROM ShoppingCart WHERE ProductID = Products.ProductID and ShoppingCart.CartID=@CartID)
Their you go
Thanks again
|
|
|
|
|
Hi all~
How do I write a Sql Statement that joining 2 tables which in 2 different database files respectively?
Thanks
|
|
|
|
|
In SQL Server to access table in different database use database_name.dbo.table_name
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Oh
Iam sorry.
I miss to mention.
I would like to write a sql statement that can join 2 tables and 1 is assoicated by one connection and 1 is assoicated by another connection.
Is it suitable to use "database_name.dbo.table_name" with SQL Ce Server 2000?
Also what is the database name and dbo?
Can I get some examples?
Thanks
|
|
|
|
|
I don`t know if it's suitable with SQL Ce Server.
If your database name is db1 and your table name is table1, to access the field field1 in the table use:
db1.dbo.table1.field1
I hope this helps.
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
I'm in the midst of developing a module which adds/remove contact list from Microsoft Outlook. I have two problems currently:
1. When my module perform the insert, I get this security popup: "A program is trying to access email addressed stored in Outlook. Do you want to Allow this?"
Now, there's a software that automatically click "YES" when this popup appears, but is there a better solution that this? Thanks a bunch!
Download the software I mentioned ("Context Magic")[^]
2. I wish to insert contact item into (a) a database, and (b) outlook address book. And I wish to wrap this INSERT inside a transaction. Now, usually, we just pass a IDbTransaction into our Create(..) method:
Create(ct As ContactItem, tran As IDbTransaction)
Now if I want the transaction to cover my insert into Microsoft Outlook address book also?
Thanks in advance!
References:
a. Article From Code Project[^]
b. Article from MSDN[^]
c. Download Binary if you don't have Microsoft Outlook 2003 (and therefore the object library required) [^]
Norman Fung
|
|
|
|
|
I try to use ODBC to update an item in MySQL table "my_status", but it doesn't work. Seems the data can be loaded to the dataset, but how to write it back to the data sourse? Is there anybody can give me some ideas or samples on how to do this?
Here is my code:
/////connect to the database with DataSet
String* strSql = "SELECT * FROM my_status";
OdbcDataAdapter* da = new OdbcDataAdapter(strSql,MYSQLCONN);
OdbcCommandBuilder* custCB = new OdbcCommandBuilder(da);
OdbcCommand* cmd;
cmd = new OdbcCommand("Select * from my_status", MyConn);
da->SelectCommand = cmd;
DataSet* wrbtDS = new DataSet();
da->Fill(wrbtDS, "my_status");
DataRow* cRow = wrbtDS->Tables->Item["my_status"]->Rows->Item[0];
//// load the image into the dataset
String * FILE_NAME = "Solid.jpg"; // for inserting a image file
FileStream* fs = new FileStream(FILE_NAME, FileMode::Open, FileAccess::Read);
Int16 imglen = (int)fs->Length;
Byte buffer[] = new Byte[imglen];
fs->Read(buffer,0,buffer->Length);
cRow->Item["image"] = buffer;
/* until here the item["image"] is changed and shows in DataSet, but how to update the data source with this updated DataSet??????? The following code doesn't work and how to figure it out?*/
wrbtDS->Tables->Item["my_status"]->Rows->Item[0]->AcceptChanges();
da->Update(wrbtDS, "my_status");
Thanks a lot!
Kevin
|
|
|
|
|
Hi there,
Just a small question: Does anyone know how to display the menu bar on the top of a table screen in SQL Server (2000) Enterprise Manager? There must be a setting somewhere that I could not find out. I really hate to use the menu in the main form everytime. Thanks a lot!
Frank
|
|
|
|
|
Hello All!
I'm working on a Store Procedure. This works fine...
SELECT MAX(TableID) AS returnvalue
FROM TblID
WHERE (Inuse = 0)
UPDATE T1
SET Inuse = 1
FROM TblID T1 INNER JOIN
(SELECT MAX(TableID) AS MaxT
FROM TblID
WHERE (Inuse = 0) AND (TableID <= 5)) T2 ON T1.TableID = T2.MaxT
But now I need to make it so if the VALUE of "InUse" is not 0, it will give an error message or something. I will use this SP in my VB.net program. Unless it is easier to catch the error on the client side within my program. But I thought this would be the better way to go. And if I do have a message built in the SQL code, how can I display that in my code for my program.
This is what I have now for the SP code....
IF (SELECT MAX(TableID) AS returnvalue
FROM TblID
WHERE Inuse = 0) BEGIN
UPDATE T1
SET Inuse = 1
FROM TblID T1 INNER JOIN
(SELECT MAX(TableID) AS MaxT
FROM TblID
WHERE (Inuse = 0) AND (TableID <= 5)) T2 ON T1.TableID = T2.MaxT END ELSE BEGIN PRINT ' Wrong'
And if your curious, this is my code to call the SP in vb.net....
conLog = New SqlConnection("Server=localhost;uid=**;pwd=**;database=footbet")
cmdLog = New SqlCommand("loginStore", conLog)
cmdLog.CommandType = CommandType.StoredProcedure
cmdLog.Parameters.IndexOf("@InUse")
conLog.Open()
cmdLog.ExecuteScalar()
myMainfrm.Show()
conLog.Close()
Thank you for your help!
Rudy
|
|
|
|
|
How could I get random a number of rows in SQL Server database
Example i've got 70 rows data in table "Question" which has two fields including "QuestionID"(int) and "QuestionContent"(nvarchar(1000)), now i want to take random 10 rows from it
Thank you so much.
|
|
|
|
|
Yes, wouldn't a SELECT RANDOM * FROM... function be useful in SQL? As a supplementary to your question (and a pre-cursor to a workaround to it), ponder this: You would think that
SELECT RAND( DATEPART( ms, GETDATE() ) )
GO
(using the current millisecond as a seed for the rand function) would return a pretty good random number between 0 and 1.... but I always get (and I notice that in the SQLServer Books Online in their example they also get) a number beginning 0.7... very occassioally a 0.9... pops up, but only occcassionally...
what is going on?
If anyone knows the answer to this, or has a working random SQL function then a workaround to your question is to inlcude an extra numeric field in your table and fill that with random numbers before each call, which you then order by that field... ie
ALTER TABLE QUESTION ADD RandomNumber FLOAT NOT NULL DEFAULT 0
GO
Then every time you want a random set of questions:
UPDATE QUESTION SET RandomNumber = RAND( DATEPART( ms, GETDATE() ) )
GO
SELECT TOP 10 * FROM QUESTION ORDER BY RandomNumber
GO
A bit clumsy, I know, but I'm guessing your SQLServer isn't going to be overloaded by this....
..and of course, it requires a random function that actually works....
cheers
Phil
|
|
|
|
|
Actually i'm making a web quiz and i stored all my questions in "Question" table, my idea is that i want to take random about 5 questions each time to show to users
I'm starting learning SQL SERVER 2000 so i really don't know how to do this, if there is another way to do this, please show me
Thank you
|
|
|
|
|
Have you seen this article on this site?
http://www.codeproject.com/useritems/RandomSelect.asp[^]
So there is a simple solution after all!
I have also discovered that calling the SQL RAND function without any seed number gives a far better rnage of random numbers - though whether it gives the same sequence each time a form is called or the server rebooted is another matter - need to test that.
cheers
Phil
|
|
|
|
|
I have made an application to read information from SQL Server for some work, when my App is working, I do not want any body can change information on the database I am using, means serialize that database, how to do this? I use SQLDMO.
Thankz!
|
|
|
|
|
All of the books, examples and help files I've been through assume the existance of the database, which you can then "Add a Connection" for in Server Explorer. This means that I have to use a database program, such as MS Access, to create the tables, fields, etc. at least before using it in the VS IDE. This also means that I have to use Access to add tables or fields, etc. I have grown to like the XML Schema Designer and have been trying to figure a way to use it to create the databases I need for the applications we're developing, and so have the application create the corresponding MDB files. Can this be done, or am I missing something incredibly simple (again )?
Thanks!
Mitchell
|
|
|
|
|
Hi Mitchell. You can use COM Interop to create .mdb databases. Here's an MSDN document[^] on the topic.
|
|
|
|
|
|
Excellent help, Mike! That's exactly what I needed and having just tried it find it works great. Thanks!
Mitchell
|
|
|
|
|
VB.Net
ADO.Net
How do I populate textbox(s) from column(s) from the same data row from a Combobox?
These items are of course generically named for demonstation purposes only.
I have…
DataSet1
Table1
Column1
Column11
Textbox1 which is bound to DataSet1, Table1, Column1
Textbox2 which is bound to DataSet1, Table1, Column11
DataSet2
Table2
Column2
Column22
All columns in Table2 are Doubles.
ComboBox3
The form that is displayed is populated from DataSet1.
ComboBox3 source and value member is set to DataSet2, Table2, Column2.
I only want to display one column in the ComboBox.
To populate Textbox1 on SelectedValueChanged of ComboBox3 I have…
Me.Textbox1.Text = Val (Me.ComboBox3.Text)
This works fine.
But I want to populate Textbox2 from Column22, with the corresponding value that is in the same row as the value selected to populate Textbox1 at the same time.
DataSet2
Table2
Column2
1
2
3
4
5
Column22
11
22
33
44
55
So if Textbox1 was populated with “4”, I want Textbox2 to be populated with “44” at the same time, (they are on the same DataRow).
Eventually I would like to populate other textboxes as well, or just simply expand on this. In Access this was easy to do, but I am moving to and using VB.Net and ADO.Net.
How do I do this?
Thanks
WindSailor
|
|
|
|
|
I'm a little lost between the DataSet1 and DataSet2, but it seems that the combobox is bound to DataSet2 and the info from that DataSet is what you want in the other textboxes, so:
It is very easy to do this using BindingManagerBase. I had to look through five books on ADO.NET before finding one that explained how to use it. I'm not sure why the other books ignore it because it makes things a lot simpler. (Murach is the the publisher)
Bind your controls
In your code:
1) Dim bmbName As BindingManagerBase 'before the Form Designer generated code
2) bmbName = Me.BindingContext(DsName1, "TableName") 'after the dataset's been filled
3) Private Sub cboName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboName.SelectedIndexChanged
bmbName.Position = cboName.SelectedIndex
End Sub
Now the other controls will change as you change the combobox.
|
|
|
|
|
Thanks!
DataSet2 was a lookup table for DataSet1 or for the form I was working on, I actually have 17 different lookup tables integrated into this form. Busy, yes.
I included your suggestions and made changes on some of the items...
I needed to change the DataBindings to retrieve the value of the LookUp Column and then save that value to the original Table and Column that the form was built on.
In your code:
1) Dim bmbName As BindingManagerBase 'before the Form Designer generated code
2) bmbName = Me.BindingContext(DsName1, "TableName") 'after the dataset's been filled
2a) NOTE: I actually changed it to:
bmbName = Me.BindingContext(YourLookUpDataSet,"YourLookUpTableName")
'after the DataSet's been filled
3) Private Sub cboName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboName.SelectedIndexChanged
Dim ThisVariable1 As Double
Me.TextBox2.DataBindings.Clear()'Databinding was originally done on the _
'property settings of TextBox2 so I could scroll through records.
bmbName.Position = cboName.SelectedIndex
Me.TextBox2.DataBindings.Add(New Binding _
("Text",YourLookUpDataSet, "YourLookUpTableName.YourLookUpColumnName"))
ThisVariable1 = Me.TextBox2.Text
Me.TextBox2.DataBindings.Clear()
Me.TextBox2.DataBindings.Add(New Binding _
("Text", YourOriginalDataSet, "OriginalTable.OriginalColumn"))
'reset to your original DataBinding.
Me.TextBox2.Text = ThisVariable1
ThisVariable1 = 1 'Setting default value for that column
Me.BindingContext(YourDataSetName, "YourTable").EndCurrentEdit()
YourDataAdapter.Update(YourDataSetName, "YourTable")
End Sub
This does work for me, I hope this is what you were talking about... if not please expand on it.
Thanks again,
Windsailor
|
|
|
|
|
I'm glad to hear it worked for you. My example was just to get you started. Your requirements are more complicated than anything I have tried so far.
|
|
|
|
|
I read this article:
http://www.codeproject.com/aspnet/PagingLarge.asp[^]
I'm developing a multi-user community site which will host many hundreds of thousands of users. The paging will be implemented in search pages (user search), mailbox, etc etc.
What are your thoughts on this article? I was told that using temp table would actually be more scalable, but the author of the article seems to rule it out completely.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
