PeterBaileyUk
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
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
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
ASKER
ive attached the screenshot form sql server it has red underlines so the syntax must be wrong
ee.JPG
ee.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Thank you I experimented before i saw your answer, its fine now thx
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.