?
Solved

stored procedure call

Posted on 2005-04-13
8
Medium Priority
?
147 Views
Last Modified: 2010-04-23
Hi you experts.

I have an sql database with the table Counters.
I want to create a stored procedure that gets as parameter the id of another table puts the value of a field in counters and then update the field in counters +1.

I would also like to tell me of how can i call that procedure from my vb.net code.

Thank you all in advance.  
0
Comment
Question by:adktd
  • 4
  • 3
7 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 13771418
Hi adktd,

Procedure could look like this:

Create Procedure Counters_Update(@ID int) As

If exists(Select [ID] From Counters Where [ID] = @ID)
Update Counters Set MyValue=MyValue + 1 Where [ID] = @ID
Else
Insert Into Counters ([ID],MyValue) Values(@ID,1)

Then to call it:

Dim scmdCounter As New System.Data.SqlClient.SqlCommand
With scmdCounter
  .Connection = "your connection string here"
  .Connection.Open
  .CommandText = "Counters_Update"
  .CommandType= CommandType.StoredProcedure
  .Parameters.Add(@ID,MyIFValue)
  .ExecuteNonQuery
  .Connection.Close
  .Connection.Dispose
  .Dispose
End With



Tim Cottee
0
 
LVL 1

Author Comment

by:adktd
ID: 13779302
Thank you very much Tim Cottee,but it's not exactly what i was looiking for.Here is a more detailed example of what i want to do.

Counters table has 3 fields :field1,field2,field3 and all are integers

Counters will have always 1 row, and there is no primary key on this table

I have another table Customers

What i want to do is pass somehow the id of a row in Customers to a stored procedure
And then the stored procedure will update the row in customers with the value of field1 of Counters,
then should update field1 from Counters to field1=field1+1
0
 
LVL 1

Author Comment

by:adktd
ID: 13779313
I forgot to mention that the field that i want to update in Customers is called Customers_field1
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 43

Expert Comment

by:TimCottee
ID: 13779552
adktd,

So the procedure would be something like:

Create Procedure Counters_Update(@ID int) As
Update Customers Set Customers_field1 = (Select field1 From Counters) Where Customers.[ID] = @ID
Update Counters Set field1 = field1+1




Tim
0
 
LVL 1

Author Comment

by:adktd
ID: 13780689
I have one problem. In .ExecuteNonQuery i get an error.
Although .connection.state=1.it says "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding." Why is that?
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 200 total points
ID: 13780766
adktd,

That is a strange one, presumably the stored procedure works fine if you run it from query analyser.

Does it actually do the updates if you call it from the application?

Can you see how long it takes in query analyser, this could be the problem, if you have no index on the customers table that can be used, this procedure may take too long to execute, you might be advised to add an index on the customer.[ID] field (or whatever it is actually called).

Tim.
0
 
LVL 1

Author Comment

by:adktd
ID: 13781680
i run the query manually from query analiser and works fine(i have to specify the id of course).
It doesn't do any updates when i call it from code. Here is all the code i use to call it :

Dim scmdCounter As New System.Data.SqlClient.SqlCommand
Dim sqlConn As New SqlClient.SqlConnection("Data Source=" & MyServ & ";Initial Catalog=" & dbName & ";User Id=" & MyUserName & ";Password=" & MyPass)

With scmdCounter
                .Connection = sqlConn
                .Connection.Open()
                .CommandText = "Counters_Update"
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@ID", MyId)
                .ExecuteNonQuery()-------------->After this line i get the timeout
                .Connection.Close()
                .Connection.Dispose()
                .Dispose()
            End With
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question