Solved

sql server/vb.net

Posted on 2016-09-20
15
32 Views
Last Modified: 2016-09-20
I have some code to send some data to insert into a table but having trouble with the USP with it showing syntax error

The USP:
USE [Dictionary]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_InsertLinkWords]
@StrShort varchar(50),
@WordCommon varchar(50)
AS
BEGIN
INSERT INTO [dbo].[TblShortDescLink] ([StrShort], WordCommon )

SELECT [dbo].[TblWords].[StrShort], @WordCommon as WordCommon
FROM [dbo].[TblWords] 
WHERE  [dbo].[TblWords].StrShort=@StrShort
GROUP BY StrShort
END

Open in new window


VB.net
    Private Sub PanelLinkShort_Click(sender As Object, e As EventArgs) Handles PanelLinkShort.Click
        Dim StrProcName As String

        Dim selectedRowCount As Integer =
           DataGridViewStringsBulk.Rows.GetRowCount(DataGridViewElementStates.Selected)
        Dim x As String
        If selectedRowCount > 0 Then

            Dim connectionString As String = ConfigurationManager.ConnectionStrings("Dictionary").ConnectionString
            StrProcName = "usp_InsertLinkWords"
            Using conn As New SqlConnection(connectionString)
                Using cmd As New SqlCommand(StrProcName, conn)
                    cmd.CommandTimeout = 0
                    cmd.CommandType = CommandType.StoredProcedure
                    conn.Open()

                    Dim StrCommon As String
                    StrCommon = ""

                    Dim i As Integer
                    For i = 0 To selectedRowCount - 1

                        'get rows

                        cmd.Parameters.AddWithValue("@StrShort", DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value)
                        If i = 0 Then
                            StrCommon = DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value
                        Else

                        End If
                        cmd.Parameters.AddWithValue("@WordCommon", StrCommon)

                    Next i

                End Using
            End Using
            MessageBox.Show("Finished")

        End If
    End Sub

Open in new window

ex
0
Comment
Question by:PeterBaileyUk
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41806000
Did you run the code or you didn't because of the marked red lines?
0
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41806003
what's the error message you get here?
0
 

Author Comment

by:PeterBaileyUk
ID: 41806008
i didnt run the code.  I think its because wordcommon is not in the tblwords. can i create a usp to insert directly with the select component its only in tblshortdesclink i nee to append and i have that data already in the vb
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41806010
Please run the code first.
Personally I never take SSMS seriously in these kind of situations.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41806012
In that case use 4 part name...

ServerName.DBName.SchemaName,tableName
0
 

Author Comment

by:PeterBaileyUk
ID: 41806063
I dont understand what I do to the usp in this case I understand the naming convention but not what I should do in the usp ID: 41806012
0
 

Author Comment

by:PeterBaileyUk
ID: 41806068
I found another piece of code i had made in this format with the insert in the vb

so can this be replicated in a usp

cmdInsert.CommandText = "INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & "','" & StrClientName & "','" & StrWord & "'," & intWordLen & ",'" & StrFull & "'," & IntWordPosition & "," & IntNoOfWords & " )"
                            cmdInsert.ExecuteNonQuery()

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41806069
like this or is this a bad way

cmdInsert.CommandText = "INSERT INTO TblShortDescLink (StrShort, StrCommon) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & " )"

Open in new window

0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41806071
I can't see in your vb code the execution of the USP. Isn't a cmd.ExecuteNonQuery() missing?
0
 

Author Comment

by:PeterBaileyUk
ID: 41806073
a correction in the vb.net with this

cmdInsert.CommandText = "INSERT INTO TblShortDescLink (StrShort, StrCommon) VALUES ('" & DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value & "','" & StrCommon & " )"
cmdInsert.ExecuteNonQuery()

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41806075
ID: 41806071  I wasnt sure which is the best way to do it i think this answers the question, i only just wrote this bit

ID: 41806073
0
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41806078
If you want to get rid of the USP and run the INSERT directly from the vb.net then don't forget to remove the line:
cmd.CommandType = CommandType.StoredProcedure
0
 

Author Comment

by:PeterBaileyUk
ID: 41806081
Ive changed it to this now:

    Private Sub PanelLinkShort_Click(sender As Object, e As EventArgs) Handles PanelLinkShort.Click
        Dim StrProcName As String

        Dim selectedRowCount As Integer =
           DataGridViewStringsBulk.Rows.GetRowCount(DataGridViewElementStates.Selected)
        Dim x As String
        If selectedRowCount > 0 Then


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

                    cmdInsert.CommandTimeout = 0


                    Dim StrCommon As String
                    StrCommon = ""

                    Dim i As Integer
                    For i = 0 To selectedRowCount - 1

                        'get rows

                        'cmd.Parameters.AddWithValue("@StrShort", DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value)

                        If i = 0 Then
                            StrCommon = DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value
                        Else

                        End If


                        'cmd.Parameters.AddWithValue("@WordCommon", StrCommon)
                        cmdInsert.CommandText = "INSERT INTO TblShortDescLink (StrShort, StrCommon) VALUES ('" & DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value & "','" & StrCommon & " )"
                        cmdInsert.ExecuteNonQuery()

                    Next i

                End Using
            End Using
            MessageBox.Show("Finished")

        End If
    End Sub

Open in new window

0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41806084
Any error?
0
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41806115
>>cmdInsert.CommandText = "INSERT INTO TblShortDescLink (StrShort, StrCommon) VALUES ('" & DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value & "','" & StrCommon & " )"
                        cmdInsert.ExecuteNonQuery()

I don't really recommend to hard code the "insert statement" in the codes. your original issue was not resolved, this would consider as a quick fix for me.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question