Solved

sql server/vb.net

Posted on 2016-09-20
15
35 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 48

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 51

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 48

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 48

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 48

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 48

Expert Comment

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

821 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