Solved

sql server/vb.net

Posted on 2016-09-20
15
39 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 50

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 52

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 50

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 50

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 50

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 50

Expert Comment

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

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

691 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