asked on

sql server update procedure

I dont know the syntax, I have this so far I think you can see what I want to do.

USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]
(
@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL
)
AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged,
SET StrShortTagged = @StrShortTagged 
WHERE ClientCode = @ClientCodeIn
END

Open in new window

Jim Horn

>I have this so far I think you can see what I want to do.
The syntax looks correct, and I hate to guess what users' requirements are, so why don't you tell us what you're trying to pull off here, and any error messages that are displayed when you execute the above code.

PeterBaileyUk

ASKER

as it was just to understand the syntax for an update procedure I didnt expand, I will pass the client code, and the other two variables in vb.net app and it update the word table.

The vb code is here but I havnt made the call to the procedure yet. so a bit of a mute point.

the code creates the two strings i want to send to the table words

   Public Sub CreateShort()
        Dim dtCodes = New DataTable
        Dim dtCodeWords = New DataTable
        Dim dtCodeWordsT = New DataTable
        Dim cmd As New SqlCommand
        Dim CmdWords As New SqlCommand
        Dim StrClientCode As String
        Dim StrProcName As String
        Dim StrShortString As String
        Dim StrShortTaggedString As String

        StrProcName = "usp_getUnTaggedCodeWords"
        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")

            cnSql.Open()
            cmd.Connection = cnSql
            cmd.CommandText = "SELECT TblWords.ClientCode FROM TblWords LEFT JOIN TblWordTags ON TblWords.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition WHERE (((TblWordTags.ClientCodeWordPosition) Is Not Null)) GROUP BY clientcode;"

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            dtCodes.Load(reader)

            For Each drRecord As DataRow In dtCodes.Rows

                StrClientCode = drRecord("ClientCode")
                If StrClientCode = "069558C" Then
                    MessageBox.Show("now")
                End If

                dtCodeWords = GetShortStringWordsData(StrClientCode, False)

                For Each drRecordW As DataRow In dtCodeWords.Rows
                    StrShortString = StrShortString & drRecordW("word") & Chr(32)
                    StrShortString = RTrim(StrShortString)
                    Debug.Print(StrShortString)
                Next

                dtCodeWordsT = GetShortStringWordsData(StrClientCode, True)

                For Each drRecordWT As DataRow In dtCodeWordsT.Rows
                    StrShortTaggedString = StrShortTaggedString & drRecordWT("word") & Chr(32)
                    StrShortTaggedString = RTrim(StrShortTaggedString)
                    Debug.Print(StrShortTaggedString)
                Next







            Next


        End Using
    End Sub

Open in new window

PeterBaileyUk

ASKER

ive attached the screenshot form sql server it has red underlines so the syntax must be wrong
ee.JPG

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

ok I worked it out myself;

USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]

@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL

AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged, StrShortTag = @StrShortTagged 
WHERE ClientCode = @ClientCodeIn
END

Open in new window

PeterBaileyUk

ASKER

Thank you I experimented before i saw your answer, its fine now thx