July 12, 2011 at 12:17 pm
I would definitely suggest using a Stored Procedure. You can encapsulate all the business logic within it, and you can utilize things like output parameters, which are great for returning record ID values, and any error messages that occur.
Even if it is too far ahead for you now, keep it and study it, and you can use it later. Hope it helps.
BobL
Example:
CREATE PROCEDURE dbo.usp_Create_ApplicantNotes_Rec
@appidint,
@appnotedatedatetime,
@appnotecommentvarchar(400),
@privaterecbit,
@spstatintOUTPUT,
@errmsgvarchar(200)OUTPUT,
@recnintOUTPUT
AS
DECLARE @numrecs int
SET NOCOUNT ON
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO ApplicantNotes (AppID,AppNoteDate,AppNoteComment,PrivateRec)
VALUES (@appid,@appnotedate,@appnotecomment,@privaterec) --notice they are the input parameters above
SET @recn = SCOPE_IDENTITY()
SET @numrecs = @@rowcount
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record added'
SET @recn = 0
END
COMMIT TRANSACTION
RETURN @spstat
END TRY
BEGIN CATCH
DECLARE@ErrorNoint,
@Severityint,
@Stateint,
@LineNoint,
@errmessagevarchar(1000)
SELECT@ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@errmessage = ERROR_MESSAGE()
ROLLBACK TRAN
SET @errmsg = CONVERT(varchar(200), @errmessage)
SET @spstat = 0--error occured
END CATCH
GO
================================================================
Here is the code for the vb.net program. When you run it, the variable RecNo will contain either a 0 (for errro), or the actual record number for the integer-valued primary key.
'Code for SPROC dbo.usp_Create_ApplicantNotes_Rec
Dim msgToDisplay As String
Dim RecNo As Integer
Dim continueprocessing As Boolean
continueprocessing = True
Try
Dim myConnection2 as New SqlConnection(ConnString2)
myConnection2.Open()
Dim myCommand3 As New SqlClient.SqlCommand()
myCommand3.Connection = myConnection2
myCommand3.CommandText = "dbo.usp_Create_ApplicantNotes_Rec"
myCommand3.CommandType = CommandType.StoredProcedure
Dim par3 As New SQLClient.SQLParameter("@appid", SqlDbType.Int)
par3.Value = ApplicantNotes_AppID'variable holding a value
par3.Direction = ParameterDirection.Input
myCommand3.Parameters.Add(par3)
par3 = New SQLClient.SQLParameter("@appnotedate", SqlDbType.Datetime)
par3.Value = ApplicantNotes_AppNoteDate'variable holding a value
par3.Direction = ParameterDirection.Input
myCommand3.Parameters.Add(par3)
par3 = New SQLClient.SQLParameter("@appnotecomment", SqlDbType.Varchar, 400)
par3.Value = ApplicantNotes_AppNoteComment'variable holding a value
par3.Direction = ParameterDirection.Input
myCommand3.Parameters.Add(par3)
par3 = New SQLClient.SQLParameter("@privaterec", SqlDbType.Bit)
If ApplicantNotes_PrivateRec = True Then
par3.Value = True
Else
par3.Value = False
End If
par3.Direction = ParameterDirection.Input
myCommand3.Parameters.Add(par3)
par3 = New SQLClient.SQLParameter("@spstat", SqlDbType.Int)
par3.Direction = ParameterDirection.Output
myCommand3.Parameters.Add(par3)
par3 = New SQLClient.SQLParameter("@errmsg", SqlDbType.Varchar,200)
par3.Direction = ParameterDirection.Output
myCommand3.Parameters.Add(par3)
par3 = New SQLClient.SQLParameter("@recn", SqlDbType.Int)
par3.Direction = ParameterDirection.Output
myCommand3.Parameters.Add(par3)
myCommand3.ExecuteNonQuery()
myConnection2.Close()
myCommand3.Dispose()
par3 = Nothing
RecNo = 0
msgToDisplay = ""
Select Case Int32.Parse(myCommand3.Parameters("@spstat").Value.ToString)
Case 0
reply = MessageBox.Show("Error occured when creating database record using stored procedure dbo.usp_Create_ApplicantNotes_Rec. Error: " + myCommand3.Parameters("@errmsg").Value.ToString,"", MessageBoxButtons.OK, MessageBoxIcon.Error)
continueprocessing = False
RecNo = 0
Case 1
RecNo = Int32.Parse(myCommand3.Parameters("@recn").Value.ToString)
Case -1
'no record added.
End Select
Catch ex As Exception
reply = MessageBox.Show("Error occured when creating database record when attempting to use stored procedure dbo.usp_Create_ApplicantNotes_Rec. Error = " + ex.Message + vbCrLf, "", MessageBoxButtons.OK, MessageBoxIcon.Error)
continueProcessing = False
End Try
July 12, 2011 at 12:37 pm
Thanks rhlangley!
That was a really helpful post so I can see how the two technologies work together. I appreciate the effort you took to help me out. I am going to study these examples so I am not quite so overwhelmed with SQL Server. I had no idea, prior to visiting these forums, of how powerful SQL Server is and how much more you can do with it besides the simple 'insert', 'update' and 'delete' commands I had been used to.
July 12, 2011 at 1:11 pm
You are welcome. I wrote a program that creates all my SPROCs and vb.net code. The code created is what I gave you. I can create all the CRUD (Create, Read, Update, Delete) SPROCs (and others) and vb.net code for them in a couple of seconds. Also, it makes them all uniform in construct.
Study the metadata tables to find all the tables and columns in a database (invaluable to writing the program).
SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM information_schema.columns WHERE TABLE_SCHEMA = '" + n_SchemaName(b) + "' AND TABLE_NAME = '" + n_TableName(b) + "' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
(n_SchemaName() and n_TableName() are values from the prior select statement)
Between SQL Server and .net, there isn't much you can't do! Good luck in your studies!
BobL
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply