Hey guys. I just want to know how would I write a stored procedure in SQL server 2000 to read, and another to update a Text data type field. I also need to know how I am able to execute this stored Procedure in asp .net using vb .net.

My table is something like this:

ArtID int
Title varchar 200
Contents Text
Hits int
HitText nvarchar 200
UserID nvarchar 20

Any help is appreciated!

Cheers,

Slade

Stored Procedure for Reading:

CREATE PROCEDURE dbo.GetArt
 AS
 Select
 	 *
 FROM
 	 TableName

Stored Procedure for Updating:

CREATE PROCEDURE dbo.UpdateArt
 {
 	 @ArtID int,
 	 @Title varchar(200),
 	 @Contents text,
 	 @Hits int,
 	 @HitText nvarchar(200),
 	 @UserID nvarchar(20)
 }
 AS
 
 UPDATE
 	 TableName
 
 SET
 	 Title = @Title,
 	 Contents = @Contents,
 	 Hits = @Hits,
 	 HitText = @HitText,
 	 UserID = @UserID
 
 WHERE
 	 ArtID = @ArtID

Next post will include a class to update :cool:.

Okay, here is a class that will interact with the stored procedures:

Imports System.Data
 Imports System.Data.SqlClient
 
 Public Class ArtDB
 
 	Private _connectionString As String
 
 	Sub New(ByVal ConnectionString As String)
 		_connectionString = ConnectionString
 	End Sub
 
 
 	Public Function GetArt() As SqlDataReader
 
 		' Create Instance of Connection and Command Object
 		Dim myConnection As New SqlConnection(ConnectionString)
 		Dim myCommand As New SqlCommand("GetAds", myConnection)
 
 		' Mark the Command as a SPROC
 		myCommand.CommandType = CommandType.StoredProcedure
 
 		' Execute the command
 		myConnection.Open()
 		Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
 
 		' Return the datareader 
 		Return result
 
 	End Function
 
 
 	Public Sub UpdateArt(ByVal ArtID As Integer, ByVal Title As String, ByVal Contents As String, ByVal Hits As Integer, ByVal HitText As String, ByVal UserID As String)
 
 		' Create Instance of Connection and Command Object
 		Dim myConnection As New SqlConnection(ConnectionString)
 		Dim myCommand As New SqlCommand("UpdateArt", myConnection)
 
 		' Mark the Command as a SPROC
 		myCommand.CommandType = CommandType.StoredProcedure
 
 		' Add Parameters to SPROC
 		Dim parameterArtID As New SqlParameter("@ArtID", SqlDbType.Int)
 		parameterArtID.Value = ArtID
 		myCommand.Parameters.Add(parameterArtID)
 
 		Dim parameterTitle As New SqlParameter("@Title", SqlDbType.VarChar, 200)
 		parameterTitle.Value = Title
 		myCommand.Parameters.Add(parameterTitle)
 
 		Dim parameterContents As New SqlParameter("@Contents", SqlDbType.Text)
 		parameterContents.Value = Contents
 		myCommand.Parameters.Add(parameterContents)
 
 		Dim parameterHits As New SqlParameter("@Hits", SqlDbType.Int)
 		parameterHits.Value = Hits
 		myCommand.Parameters.Add(parameterHits)
 
 		Dim parameterHitText As New SqlParameter("@HitText", SqlDbType.VarChar, 200)
 		parameterHitText.Value = HitText
 		myCommand.Parameters.Add(parameterHitText)
 
 		Dim parameterUserID As New SqlParameter("@UserID", SqlDbType.VarChar, 20)
 		parameterUserID.Value = UserID
 		myCommand.Parameters.Add(parameterUserID)
 
 		' Execute the command
 		myConnection.Open()
 		myCommand.ExecuteNonQuery()
 		myConnection.Close()
 
 		' Dispose the resource expensive objects
 		myCommand.Dispose()
 		myConnection.Dispose()
 	End Sub
 
 	Public Property ConnectionString() As String
 		Get
 			Return _connectionString
 		End Get
 		Set(ByVal Value As String)
 			_connectionString = Value
 		End Set
 	End Property
 
 End Class

To grab the data using the class:

Dim artData As New ArtDB("ConnectionStringWouldBePlacedHere")
 Dim datareader As System.Data.SqlClient.SqlDataReader = artData.GetArt()

To update data using the class:

Dim artData As New ArtDB("ConnectionStringWouldBePlacedHere")
 artData.UpdateArt(1, "Title", "Contents", 60, "HitText", "UserID")

LOL that is basically the standard format I use now, to think it was so simple... and I was just doing stupid things! oh well :).


Thanks heaps tekmaven you rule.

LOL! Thanks for the praise ;). Anyways, always try to use n[datatypes]. They might hold 'slightly' less; but they are unicode datatypes (they hold WAY more types of chars).

Also, wherever you're using SQL Queries, try to impliment Stored Procedures. They are faster, and they provide MUCH more security (against nasty SQL injection attacks).

hehe I always use stored procedures. But people shouldn't be able to inject if it's just a reading query right? I'm not too sure. Also, what types of characters WONT text hold? Can you give examples?

How do I databind the content of the "Content" field to a label? And are you suggesting I use ntext? I don't mind about the size, I was forced to use it because nvarchar was too small since most of it will be for storing html.

LMAO! I don't know whether to be angry or happy. Turns out I was databinding it correctly but I had the label set to invisible lol.

Cheers Tekmaven, I'll buy you a beer if I ever go to America,

Slade

ROFL, I'm underage ;) . I don't think text can store this character: 廙

(FYI, thats a Final Pe in hebrew :))

//Edit: I guess MySQL can't store it either ;) .

What exactly does the dispose method do? If I am accessing the connection pretty frequently is it still ok to dispose of it?

When an object impliments IDisposable, it means it has a Dispose() method, which cleans up any objects that the object has created for its own use. It should be called right before the object goes out of scope.

If you don't call it, those resources will be released *eventually* do to the .NET Garbage Collector. But, why wait? Call .Dispose() as frequently as possible ;-).

is it ok to use this method for datalists and grids etc? I noticed you didn't use it in the Public Function GetArt() As SqlDataReader...

Yeah, you can adapt it to use a DataSet. But, from a performance standpoint, you're better off using SqlDataReader; there is much less overhead. You can bind a SqlDataReader to DataGrids and most other controls that databind.

Yup, dispose of them once your done. We are talking about the same thing... the dispose method isn't just another method of that class, it was inherited (and probably overridden in that class) because the object impliments the IDisposable interface ;).

This article has been dead for over six months. Start a new discussion instead.