Database Discussion Boards

help me pleeeease to update the avaliable quantity column [modified]

nellyvb.net10-Jun-06 3:32

10-Jun-06 3:32

I'm working on an Access DB and the OLEDB data adapter in a VB.net project,
I've a windows form in vb.net called Customers orders (partno,name,orderedquantity)
When I fill all the texts with it's data Pay attention to the txt of( the ordered quantity ) ,then I press the oreder button which save the entered data to it's table in the DB.
How can I make the (avaliablequantity) column in the other table (parts) updates it's selfe according to the ordered quantity.
I made an update query in the Access which update the table just when I click on it here it's( .
Please help me how can I make the update process work through the following code of the Customerorders .

<br />
Dim as strcon<br />
Try <br />
Strcon="provider=Microsoft.jet.oledb.4.0;data source=Sales.mdb" <br />
 <br />
Dim con as new oledbconnection(Strcon)<br />
Con.open<br />
Dim cmd as new oledb command<br />
cmd.commandtext=" insert into parts (orderno,partno,orderedquantity)values(" & textbox1.text & "," & textbox2.text & "," & textbox3.text & ")"<br />
cmd.executenonqeur<br />
 <br />
con.close<br />
catch exp as exception<br />
msgbox(exp.tostring)<br />
end try

with thanx

-- modified at 9:35 Saturday 10th June, 2006

Re: help me pleeeease to update the avaliable quantity column

mr_1234510-Jun-06 5:18

mr_12345

10-Jun-06 5:18

You will need to run another query to update that table

"update available_parts set quantity = quantity - " & textbox3.text & " where partno = " & textbox2.text

You should really look into using parameterized queries. Also rename textbox1, 2, 3 to something more meaningful.

Stop Error Message

Rakesh.Jha.011010-Jun-06 0:08

Rakesh.Jha.0110

10-Jun-06 0:08

How I can Stop Error Message When any exception occurred like where divide by zero exception occurred then error message came. But I don't want to display that message and come my message. Please give any Query example to stop error message. Confused | :confused:

Rakesh Jha

Re: Stop Error Message [modified]

albCode10-Jun-06 0:27

albCode

10-Jun-06 0:27

use loop

try<br />
{<br />
//Divide by zero<br />
}<br />
catch<br />
{<br />
//do not  write any code  line. If  u  divide by zero, gonna executed code  in catch section  but in  //catch do not write code  and then u not   gonna get  error message<br />
}

-- modified at 6:27 Saturday 10th June, 2006

Re: Stop Error Message

Colin Angus Mackay10-Jun-06 2:52

Colin Angus Mackay

10-Jun-06 2:52

albCode wrote:
use loop

I don't see a loop Confused | :confused:

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Slow FIll Database

md_refay9-Jun-06 23:51

md_refay

9-Jun-06 23:51

hi whan i try to connect to my database and fill the dataset it take about (2&3)minutes to fill dataset even if the table i fill from contain (2 or 3) rows i don't know why it take this is time to fill dataset and the application is hang_up until it's finishing fill dataset
and how i could increase data base performance and speed in fill and reterive data
i write that code but i it is doesn't effect the fill process it's still slow like it
***********************************The Code*******************************
Try
DataSet21.EnforceConstraints = False
DataSet21.Tables(0).BeginLoadData()
SqlDataAdapter1.Fill(DataSet21, "categories_dir_sp")
DataSet21.Tables(0).EndLoadData()
Catch ex As Exception
MsgBox(ex.Message)
End Try
**********************************************************************

ma_refay

Re: Slow FIll Database

Colin Angus Mackay10-Jun-06 2:54

Colin Angus Mackay

10-Jun-06 2:54

How long does the stored procedure take when running in Query Analyzer?

Disconnected DataBase

md_refay9-Jun-06 23:45

md_refay

9-Jun-06 23:45

hi iam work in project and we use disconnected db in our project
what is the scenario?
1- we create adapter that contain 4 stored procedures (insert_update_delete_insert)
2-we generate dataset
4-the adapter create sqlconnection
4- in the add button (that we use to add new product to our database i write the code down but it dosn't work no new rows are add to database )
i need to know where the wrong
****************the code******************************************
Try
SqlInsertCommand1.Parameters("@category_name").Value = TextBox2.Text
SqlInsertCommand1.Parameters("@category_description").Value = TextBox3.Text
DataSet21.AcceptChanges()
SqlDataAdapter1.Update(DataSet21, "categories_dir_sp")
Catch ex As Exception
MsgBox(ex.Message)
End Try
***********************************************************************
no rows are add to database what is the wrong in my code

ma_refay

Advantages of SQL data types like SqlString vs String

Talal Sultan9-Jun-06 10:31

Talal Sultan

9-Jun-06 10:31

Hi,

I would like to know what is the advantage of using Sql types in C# like SqlString, SqlInt32,etc... versus the normal type string, int etc...

The scenario: Having a class representing each table of the database. Each class has Insert, Update, Select, Delete methods (plus possible others) to perform those operations on the specific table represented by the class. Will there be a significant improvement of performance if my class variables are SQL types or not really?

Thanks for the insight.
Talal

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Re: Advantages of SQL data types like SqlString vs String

Rob Graham9-Jun-06 12:06

Rob Graham

9-Jun-06 12:06

Depending on how you use those classes, it could even hurt performance. Type conversion is going to occur every time you need to display or update a class instance variable that is SqlString, for example. On the other hand the conversion would normally only happen once per database read or write if the variables were normal String types.
Also, it's normally better to model your classes after business objects, rather than the database structures, that makes it easier to co-ordinate multi-table updates.

Re: Advantages of SQL data types like SqlString vs String

Talal Sultan9-Jun-06 22:03

Talal Sultan

9-Jun-06 22:03

Thank you for the advice. You're right, I do perform a lot of type conversion everytime I want to display or update data from the database. I guess I will revert back to normal types Smile | :)

Talal

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Re: Advantages of SQL data types like SqlString vs String

r.stropek13-Jun-06 4:21

r.stropek

13-Jun-06 4:21

Hi!

Don't forget that there is another big difference between Sqlxxx types and the normal types: Sqlxxx types can be null (Property Null). If your fields support null-values you should use Sqlxxx types.

Regards,
Rainer.

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

Re: Advantages of SQL data types like SqlString vs String

Talal Sultan13-Jun-06 4:54

Talal Sultan

13-Jun-06 4:54

Hi,

Yes exactly, I do have some fields that are allowed to be null, in fact I decided to use Sqlxxx types because of that null thing, but I guess I can get around this by putting empty strings for string values and some special integer value (like -1) for int values.

Thanks for pointing this out.
Talal

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Counters in SQL Server ?

kumar.bs9-Jun-06 2:07

kumar.bs

9-Jun-06 2:07

Hi,

What are counters in SQL Server. And its significance?

bsk

Re: Counters in SQL Server ?

Frank Kerrigan9-Jun-06 2:31

Frank Kerrigan

9-Jun-06 2:31

Counts allow you monitor the performance of SQL server and tune your server and schema for better performance.

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: Counters in SQL Server ?

kumar.bs9-Jun-06 3:45

kumar.bs

9-Jun-06 3:45

What is the significance of Counters in Replication (SQL Server)?

bsk

Re: Counters in SQL Server ?

Kevin Horgan18-Jun-06 8:59

Kevin Horgan

18-Jun-06 8:59

Hi Kumar,

SQL Server has about 150 different counters which can be enabled to measure system (database) performance. You can get lots of information regarding performancing monitoring and tuning including using counters at : www.sql-server-performance.com for example.

You set counters to measure things like page faults per second or user connections etc etc

Cheers,

Kevin

syntax in interger and string in SQL

angelagke9-Jun-06 1:36

angelagke

9-Jun-06 1:36

INSERT titles (title, cost)
VALUES ('testing', '35')

why SQL statement above can work? since the 35 is interger and not a string, so shount not be include '' for interger value.... Confused | :confused:

angela

Re: syntax in interger and string in SQL

albCode9-Jun-06 2:17

albCode

9-Jun-06 2:17

Sure it works
u can add values as integer too (for integer filed)

ur example works as this:

INSERT titles (title, cost)<br />
VALUES ('testing', 35)<br />

Re: syntax in interger and string in SQL

angelagke10-Jun-06 7:56

angelagke

10-Jun-06 7:56

i know, because i test before..
but i don't know why i can work.... Hmmm | :|

angela

Re: syntax in interger and string in SQL

Eric Dahlvang9-Jun-06 3:24

Eric Dahlvang

9-Jun-06 3:24

Automatic data type conversion.

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: syntax in interger and string in SQL

angelagke10-Jun-06 7:58

angelagke

10-Jun-06 7:58

oic, no wonder it works lah;)

thanks ya.. Smile | :)

angela

change the predicate to use "CASE"

angelagke9-Jun-06 1:31

angelagke

9-Jun-06 1:31

how to change SQL statement below to use CASE instant of "AND" "OR" after condition where :-

ALTER PROCEDURE dbo.authorInfor
@au_lname varchar(40)

AS

select title, price, Name = substring (au_lname, 1,40), type
from authors a, titles t, titleauthor ta

where a.au_id = ta.au_id AND t.title_id = ta.title_id AND(au_lname = @au_lname OR au_lname = '' OR au_lname is Null)

Thanks.

Angela

Re: change the predicate to use "CASE"

Frank Kerrigan9-Jun-06 2:33

Frank Kerrigan

9-Jun-06 2:33

If AND OR works use it; its must more efficient than using case.

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: change the predicate to use "CASE"

angelagke10-Jun-06 7:42

angelagke

10-Jun-06 7:42

really can works and get the result that what i want with my previous SQL statement...
the matter is my boss want me to change the predicate to use "Case" D'Oh! | :doh:

anyway thanks for your suggestion;)

angela

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