?
Solved

sql server/vb.net

Posted on 2016-09-20
15
Medium Priority
?
41 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 51

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 53

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 51

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 29

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 51

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 51

Accepted Solution

by:
Vitor Montalvão earned 2000 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 51

Expert Comment

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

764 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