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.
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.
ASKER
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
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
ASKER
I forgot to mention that the field that i want to update in Customers is called Customers_field1
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.SqlC
Dim sqlConn As New SqlClient.SqlConnection("D
With scmdCounter
.Connection = sqlConn
.Connection.Open()
.CommandText = "Counters_Update"
.CommandType = CommandType.StoredProcedur
.Parameters.Add("@ID", MyId)
.ExecuteNonQuery()--------
.Connection.Close()
.Connection.Dispose()
.Dispose()
End With
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.SqlC
Dim sqlConn As New SqlClient.SqlConnection("D
With scmdCounter
.Connection = sqlConn
.Connection.Open()
.CommandText = "Counters_Update"
.CommandType = CommandType.StoredProcedur
.Parameters.Add("@ID", MyId)
.ExecuteNonQuery()--------
.Connection.Close()
.Connection.Dispose()
.Dispose()
End With
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.SqlC
With scmdCounter
.Connection = "your connection string here"
.Connection.Open
.CommandText = "Counters_Update"
.CommandType= CommandType.StoredProcedur
.Parameters.Add(@ID,MyIFVa
.ExecuteNonQuery
.Connection.Close
.Connection.Dispose
.Dispose
End With
Tim Cottee