stored procedure call

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.  
LVL 1
adktdAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
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
 
TimCotteeHead of Software ServicesCommented:
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
 
adktdAuthor Commented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
adktdAuthor Commented:
I forgot to mention that the field that i want to update in Customers is called Customers_field1
0
 
TimCotteeHead of Software ServicesCommented:
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
 
adktdAuthor Commented:
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
 
adktdAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.