Best way to insert records

  • 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

  • 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.

  • 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