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
TableNameStored 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 = @ArtIDNext 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 ClassTo 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")Wow, that was some fun VB.NET practice.. I haven't done anything in VB.NET for a while now :)
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.
Are we talking about the same thing here? I mean the dispose method :). I just read an article saying you should always dispose. So wherever I use a command or a connection I should dispose of them once I'm done am I correct?
http://www.lhotka.net/WeBlog/PermaLink.aspx?guid=67fcc062-465e-49f6-b48d-f9b4ecf4582f
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 ;).
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.