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
Solved

How to insert multiple rows in one click

Posted on 2009-05-18
15
389 Views
Last Modified: 2012-05-07
Hi
I am using ASP.Net 2.0 code behind VB and SQL Server 2000. My problem is I want to insert multiple rows in one click.  When I am trying to insert it will give me error like The name CQ is not permitted in this context.... My code is 
        Dim CQ As String = Request("CommentsAfterQuestion")
        Dim CID As Integer = Request("drpDLforCentreName")
        Dim q12 As Integer = Request("q12")
        Dim q13 As Integer = Request("q13")
        Dim CD As String = Request("txtCallDate ")

Dim strCon As New SqlConnection("Data Source=d; Initial Catalog=NSQL; Integrated Security=True;")
        Dim strCMD As New SqlCommand
        strCon.Open()
        Dim strSQL As String = ""
  strSQL = "Insert into QuestionDetails (SurveyTypeID,    CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
                 "Values (1, CQ, CID, q12, CD, 6), " & _
                 "(1, CQ, CID, q13, CD, 7)"
        Try
            strCMD = New SqlCommand(strSQL, strCon)
            strCMD.ExecuteNonQuery()
        Catch ex As Exception
            Response.Write(ex.ToString)
        End Try
Please anyone help me how can I insert multiple rows.
Thanks in advance
0
Comment
Question by:arifpervez
  • 8
  • 7
15 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410216
You don't insert the values of your strings, but you insert "CQ" as string

strSQL = "Insert into QuestionDetails (SurveyTypeID,    CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
                 "Values (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6), " & _
                 "(1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
0
 

Author Comment

by:arifpervez
ID: 24410258
now i am getting error System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection......
0
 

Author Comment

by:arifpervez
ID: 24410278
Full error - System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Lexus.Frm_AddQuestionDetails.btnSubmit_Click(Object sender, EventArgs e) in C:\Documents and Settings\Test\My Documents\Visual Studio 2008\Projects\Test\Frm_AddQuestionDetails.aspx.vb:line 51
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 53

Expert Comment

by:Dhaest
ID: 24410295
Can you print the value of strSQL after you filled it (before your try)
0
 

Author Comment

by:arifpervez
ID: 24410337
strSQL value is -
1 -- this is SurveyTypeID
comments --- comments fiels
401 - centre id
12/05/2009 - date
8  -- this is q12
8 -- this is q13
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410358
I mean your complete sql-statement !
0
 

Author Comment

by:arifpervez
ID: 24410416
Dim CQ As String = Request("CommentsAfterQuestion")
        Dim CID As Integer = Request("drpDLforCentreName")
        Dim q12 As Integer = Request("q12")
        Dim q13 As Integer = Request("q13")
        Dim CD As String = Request("txtCallDate")

        Dim strCon As New SqlConnection("Data Source=Test; Initial Catalog=NSQL; Integrated Security=True;")
        Dim strCMD As New SqlCommand
        strCon.Open()
        Dim strSQL As String = ""
        strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
                    "Values (1, '" & CQ & "', " & CID & ", " & q12 & ", " & CD & ", 6) , " & _
                    "(1, '" & CQ & "', " & CID & "," & q13 & ", " & CD & ", 7)"

        Try

            strCMD = New SqlCommand(strSQL, strCon)        
            strCMD.ExecuteNonQuery()

        Catch ex As Exception
            Response.Write(ex.ToString)
        End Try

        strCMD.Dispose()
        strCon.Dispose()
        strCon.Close()
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410466
and what is you sql-statement !!!!!

 strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
                    "Values (1, '" & CQ & "', " & CID & ", " & q12 & ", " & CD & ", 6) , " & _
                    "(1, '" & CQ & "', " & CID & "," & q13 & ", " & CD & ", 7)"
Messagebox.Show strsql
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410476
I think you have forgotten single quotes for your string-values

strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
                    "Values (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6) , " & _
                    "(1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
0
 

Author Comment

by:arifpervez
ID: 24410523
still is getting error -

Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) Values (1, 'comm', 401, 8, '12/12/2009', 6) , (1, 'comm', 401,8, '12/12/2009', 7)

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410564
What version of sql are you using.
For sql2000/2005 try the following:
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"select (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6) , " & _
" union all select (1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"

For sql2008, try the following:
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"values (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6) , " & _
"values (1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"


http://www.dotnetspider.com/resources/19650-How-Insert-Multiple-Records-Using-Single-Insert-SQL-SERVER.aspx
0
 

Author Comment

by:arifpervez
ID: 24410580
I am using sql 2000. but still same problem -

Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) select (1, 'comments', 401, 8, '12/05/2009', 6) , union all select (1, 'comments', 401,8, '12/05/2009', 7)

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','. Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
0
 

Author Comment

by:arifpervez
ID: 24410615
I remove the , before union all. though same error
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 24410621
You'll need a small adjustment, because the following query will work:
Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat)
select 1, 'comments', 401, 8, '12/05/2009', 6
union all
 select 1, 'comments', 401,8, '12/05/2009', 7

In code:
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"select 1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6 " & _
" union all select 1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7"
0
 

Author Comment

by:arifpervez
ID: 24410631
Thanks for your help. Its working.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 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