User Name Password Register
DaniWeb IT Discussion Community
All
What is DaniWeb IT Discussion Community?
You're currently browsing the ASP.NET section within the Web Development category of DaniWeb, a massive community of 391,796 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,491 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our ASP.NET advertiser: Lunarpages ASP Web Hosting
Views: 25594 | Replies: 15
Reply
Join Date: Mar 2004
Location: Brisbane
Posts: 632
Reputation: Slade has a spectacular aura about Slade has a spectacular aura about 
Rep Power: 7
Solved Threads: 6
Slade's Avatar
Slade Slade is offline Offline
Practically a Master Poster

Working with SQL's Text data type

  #1  
Aug 31st, 2004
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
Formerly known as Slade.
AddThis Social Bookmark Button
Reply With Quote  
Join Date: Feb 2002
Location: New York
Posts: 862
Reputation: Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light 
Rep Power: 12
Solved Threads: 15
Moderator
Tekmaven's Avatar
Tekmaven Tekmaven is offline Offline
The C# Man, Myth, Legend

Re: Working with SQL's Text data type

  #2  
Aug 31st, 2004
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 .
-Ryan Hoffman

ASP.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
Reply With Quote  
Join Date: Feb 2002
Location: New York
Posts: 862
Reputation: Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light 
Rep Power: 12
Solved Threads: 15
Moderator
Tekmaven's Avatar
Tekmaven Tekmaven is offline Offline
The C# Man, Myth, Legend

Re: Working with SQL's Text data type

  #3  
Aug 31st, 2004
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")
-Ryan Hoffman

ASP.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
Reply With Quote  
Join Date: Feb 2002
Location: New York
Posts: 862
Reputation: Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light 
Rep Power: 12
Solved Threads: 15
Moderator
Tekmaven's Avatar
Tekmaven Tekmaven is offline Offline
The C# Man, Myth, Legend

Re: Working with SQL's Text data type

  #4  
Aug 31st, 2004
Wow, that was some fun VB.NET practice.. I haven't done anything in VB.NET for a while now
-Ryan Hoffman

ASP.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
Reply With Quote  
Join Date: Mar 2004
Location: Brisbane
Posts: 632
Reputation: Slade has a spectacular aura about Slade has a spectacular aura about 
Rep Power: 7
Solved Threads: 6
Slade's Avatar
Slade Slade is offline Offline
Practically a Master Poster

Re: Working with SQL's Text data type

  #5  
Aug 31st, 2004
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.
Formerly known as Slade.
Reply With Quote  
Join Date: Feb 2002
Location: New York
Posts: 862
Reputation: Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light 
Rep Power: 12
Solved Threads: 15
Moderator
Tekmaven's Avatar
Tekmaven Tekmaven is offline Offline
The C# Man, Myth, Legend

Re: Working with SQL's Text data type

  #6  
Sep 1st, 2004
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).
-Ryan Hoffman

ASP.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
Reply With Quote  
Join Date: Mar 2004
Location: Brisbane
Posts: 632
Reputation: Slade has a spectacular aura about Slade has a spectacular aura about 
Rep Power: 7
Solved Threads: 6
Slade's Avatar
Slade Slade is offline Offline
Practically a Master Poster

Re: Working with SQL's Text data type

  #7  
Sep 1st, 2004
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?
Formerly known as Slade.
Reply With Quote  
Join Date: Mar 2004
Location: Brisbane
Posts: 632
Reputation: Slade has a spectacular aura about Slade has a spectacular aura about 
Rep Power: 7
Solved Threads: 6
Slade's Avatar
Slade Slade is offline Offline
Practically a Master Poster

Re: Working with SQL's Text data type

  #8  
Sep 1st, 2004
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.
Formerly known as Slade.
Reply With Quote  
Join Date: Mar 2004
Location: Brisbane
Posts: 632
Reputation: Slade has a spectacular aura about Slade has a spectacular aura about 
Rep Power: 7
Solved Threads: 6
Slade's Avatar
Slade Slade is offline Offline
Practically a Master Poster

Re: Working with SQL's Text data type

  #9  
Sep 1st, 2004
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
Formerly known as Slade.
Reply With Quote  
Join Date: Feb 2002
Location: New York
Posts: 862
Reputation: Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light Tekmaven is a glorious beacon of light 
Rep Power: 12
Solved Threads: 15
Moderator
Tekmaven's Avatar
Tekmaven Tekmaven is offline Offline
The C# Man, Myth, Legend

Re: Working with SQL's Text data type

  #10  
Sep 1st, 2004
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 .
-Ryan Hoffman

ASP.NET Specialist / Webmaster, Extended64.com.
Please do not email or PM me with support questions. Please direct them to the forums instead.
Reply With Quote  
Reply

Only community members can participate in forum threads. You must register or log in to contribute.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

DaniWeb ASP.NET Marketplace
Thread Tools Display Modes

Similar Threads
Other Threads in the ASP.NET Forum

All times are GMT -4. The time now is 5:18 am.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC