Solved

How to insert multiple rows in one click

Posted on 2009-05-18
15
402 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Testing of the web services 1 80
Change to file doesn't show up 16 93
disadvantage of code behind appoarch in asp.net 3 49
Textbox autocomplete using jquery in asp.net 13 52
I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

710 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