sql 2008 and VB 2010

[neoman]

Hi,

I am using the following select statement to inject data into my table

"INSERT INTO TblVersion([ComputerName],[Version],[Date]) VALUES ('" & compname & "','" & myversion & "','" & mytime & "' )"

This works fine.

I need some advice:
If the computer name is already in the 'computerName' field I just want to update the 'myversion' and 'mytime' fields, rather than have another entry for the same computer.

how do i perform this check?

Thabks in advance


VB2010 and SQL 2008

[szlamany]

Can you use a STORED PROCEDURE instead of this single sql statement?

If so you would do something like this.

Create Procedure awc_PartMonthly_Edit_Save
			 @Part varchar(100)
			,@NoteKey varchar(100)
			,@Note varchar(5000)
			,@Flag1 varchar(100)
			,@yn varchar(1)
As
Set NoCount On

Update PartMonthly_T
   Set  Note=@Note
          ,TDate=GetDate()
   Where PartId=@Part
       and Convert(varchar(10),NoteDate,101)+'-'+NoteGen+'-'+Cast(NoteType as varchar(10))=@NoteKey

If @@RowCount=0
    Insert into PartMonthly_T values (@Part,Left(@NoteKey,10),Substring(@NoteKey,12,1)
                   ,Right(@NoteKey,Len(@NoteKey)-13),@Note,GetDate())

[neoman]

Hi,

I am quite new to Vb and SQL. Is the stored procedure created on the SQL server or in my vb application?

[szlamany]

It is created on the server - it's basically a "package" of several queries that run as "one".

The script I showed you is run from a query window in SQL SERVER MANAGEMENT STUDIO - and it creates the SPROC. You do this only once - SPROC is retained in the DB for user after that. You can drop/re-create it or ALTER it...

They are called from the client using code something like this...

Try
    Using dcn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("LocalSQLServerAWC").ToString)
        Using cmd As New SqlCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "dbo.awc_SetFormula"
            cmd.Connection = dcn
            cmd.CommandTimeout = 0
            DetermineParameters(dcn, cmd)
            cmd.Parameters("@CtrlVal1").Value = ctrlval1
            cmd.Parameters("@CtrlVal2").Value = ctrlval2
            dcn.Open()
            strMessage = cmd.ExecuteScalar().ToString
            .NewObject("formula", "true")
            .Seperate()
        End Using
    End Using
Catch ex As Exception
    strSuccess = ""
    strMessage = ex.Message
End Try

They have many advantages - but some of the obvious ones are CODE RE-USE (you can call it from many places in the client side) and the ability to change them in the DB without changing your client code (unless of course you add a parameter - but there are even ways to eliminate that requirement).

I only use SPROCS - never write INLINE SQL statements.

[neoman]

Hi,
Stored prices do make sense. Could you help me create one for my situation as i am finding it a little confusing to say the least. I can work out the vb side of things.

Thanks

[szlamany]

I am making assumptions on the datatype and size of your fields - you will need to adjust that to make them real.

Create Procedure TblVersionPut_P
     @ComputerName varchar(100)
    ,@Version varchar(100)
    ,@Date datetime
As
Set NoCount On

Update TblVersion Set [Version]=@Version
                              ,[Date]=@Date
             Where [ComputerName]=@ComputerName

If @@RowCount=0
  Insert into TblVersion values (@ComputerName, @Version, @Date)

Put that into a SSMS Query Window - and click Execute

Go to a new query window and type this to test it

Begin Tran
Exec TblVersionPut_P 'TestComp','123','2012-01-01'
Select * From TblVersion
Exec TblVersionPut_P 'TestComp2','999','2012-02-01'
Exec TblVersionPut_P 'TestComp','456','2012-02-02'
Select * From TblVersion
Rollback

The Begin Tran/Rollback makes all these test inserts go away - but you should see the results as a new row for TESTCOMP - then a new row for TESTCOMP2 and a change to TESTCOMP.

I always do all my testing in SSMS - before trying to get the VB side to talk to the sprocs - makes for easy creation of SQL code that's fully vetted on the server before you waste your time making it work from VB.

[neoman]

Fantastic!
Thanks for your support. I can understand how this works and how valueable SP can be. I will attempt to call this SP from VB tomorrow.