| | |
Working with SQL's Text data type
Please support our ASP.NET advertiser: Intel Parallel Studio Home
![]() |
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
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
Formerly known as Slade.
Stored Procedure for Reading:
Stored Procedure for Updating:
Next post will include a class to update
.
ASP.NET Syntax (Toggle Plain Text)
CREATE PROCEDURE dbo.GetArt AS Select * FROM TableName
Stored Procedure for Updating:
ASP.NET Syntax (Toggle Plain Text)
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
. -Ryan Hoffman
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
Okay, here is a class that will interact with the stored procedures:
To grab the data using the class:
To update data using the class:
ASP.NET Syntax (Toggle Plain Text)
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:
ASP.NET Syntax (Toggle Plain Text)
Dim artData As New ArtDB("ConnectionStringWouldBePlacedHere") Dim datareader As System.Data.SqlClient.SqlDataReader = artData.GetArt()
To update data using the class:
ASP.NET Syntax (Toggle Plain Text)
Dim artData As New ArtDB("ConnectionStringWouldBePlacedHere") artData.UpdateArt(1, "Title", "Contents", 60, "HitText", "UserID")
-Ryan Hoffman
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
Wow, that was some fun VB.NET practice.. I haven't done anything in VB.NET for a while now
-Ryan Hoffman
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
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).
. 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).
-Ryan Hoffman
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
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
.
. 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
. -Ryan Hoffman
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
![]() |
Similar Threads
- Text data type troubles in T-SQL (Database Design)
Other Threads in the ASP.NET Forum
- Previous Thread: Html header of a page changed programmatically?
- Next Thread: socket creation
| Thread Tools | Search this Thread |
.net 2.0 3.5 activexcontrol advice ajax appliances asp asp.net bc30451 beginner bottomasp.net browser businesslogiclayer c# c#gridviewcolumn cac checkbox class compatible confirmationcodegeneration content contenttype countryselector courier css database datagrid datagridview datagridviewcheckbox datalist deadlock deployment development dgv dropdownmenu dynamic dynamically edit embeddingactivexcontrol fileuploader fill findcontrol flash flv forms gridview gudi homeedition iframe iis javascript jquery list menu mono mssql multistepregistration nameisnotdeclared novell objects opera order problem ratings redirect registration relationaldatabases rotatepage search security serializesmo.table sessionvariables silverlight smartcard smoobjects software sql ssl suse textbox tracking treeview unauthorized validatedate validation vb.net video virtualdirectory vista visual-studio visualstudio vs2008 web webapplications webarchitecture webdevelopemnt webdevelopment xml xsl





