?
Solved

sql server/vb.net

Posted on 2016-09-20
15
Medium Priority
?
44 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 52

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 52

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 32

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 52

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 52

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 52

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

650 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