Solved

How to insert multiple rows in one click

Posted on 2009-05-18
15
407 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
[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
  • 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
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!

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

632 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