Click here to Skip to main content
15,887,083 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
this code is in the button click , i get each data out using spilt
but i encounter error at "cmd.CommandType = CommandType.Text"


VB
Dim conn As New SqlConnection(GetConnectionString())
                Dim sb As New StringBuilder(String.Empty)
        
                Dim splitItems As String() = Nothing
                For Each item As String In sc
        
                    Const sqlStatement As String = "INSERT INTO Date (dateID,date) VALUES"
                    If item.Contains(",") Then
                        splitItems = item.Split(",".ToCharArray())
                        sb.AppendFormat("{0}('{1}'); ", sqlStatement, splitItems(0))
        
                    End If
                Next
        
                Try
                    conn.Open()
                    Dim cmd As New SqlCommand(sb.ToString(), conn)
                   
                    cmd.CommandType = CommandType.Text
                    cmd.ExecuteNonQuery()
        
        
                    Page.ClientScript.RegisterClientScriptBlock(GetType(Page), "Script", "alert('Records Successfuly Saved!');", True)
                Catch ex As System.Data.SqlClient.SqlException
                    Dim msg As String = "Insert Error:"
                    msg += ex.Message
        
                    Throw New Exception(msg)
                Finally
                    conn.Close()
                End Try

the same code , the below work
VB
Dim conn As New SqlConnection(GetConnectionString())
        Dim sb As New StringBuilder(String.Empty)

        Dim splitItems As String() = Nothing
        For Each item As String In sc

            Const sqlStatement As String = "INSERT INTO GuestList (groupID,guest,contact,eEmail,relationship,info,customerID) VALUES"
            If item.Contains(",") Then
                splitItems = item.Split(",".ToCharArray())
                sb.AppendFormat("{0}('{1}','{2}','{3}','{4}','{5}','{6}','{7}'); ", sqlStatement, splitItems(0), splitItems(1), splitItems(2), splitItems(3), splitItems(4), splitItems(5), Session("customerID"))

            End If
        Next

        Try
            conn.Open()
            Dim cmd As New SqlCommand(sb.ToString(), conn)
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()


            Page.ClientScript.RegisterClientScriptBlock(GetType(Page), "Script", "alert('Records Successfuly Saved!');", True)
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String = "Insert Error:"
            msg += ex.Message

            Throw New Exception(msg)
        Finally
            conn.Close()
        End Try
Posted

[Answering a follow-up question by OP and clarification]

About SQL injection, please see http://en.wikipedia.org/wiki/SQL_injection[^].

I agree with Dave Kreskowiak: you should use parametrized query. But your exception was thrown by a simple reason: you probably did not write any query text at all, parametrized or not.

I searched your code and did not find any "CommandText" string. You need to assign a value to the property System.Data.SqlClient.SqlCommand.CommandText, http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtext.aspx[^]

—SA
 
Share this answer
 
Comments
Espen Harlinn 31-Jan-12 15:28pm    
Good point :)
Sergey Alexandrovich Kryukov 31-Jan-12 16:05pm    
Thank you, Espen.
--SA
Whoops. My bad. I missed the SqlCommand object in the first snippet.

In either case, this is a very BAD way to build an SQL query. You should be using parameterized queries instead of string concatenation. They make your code much easier to read and debug, and more robust, even to the point of protecting against certain SQL injection attacks, whether intentional or accidental.
 
Share this answer
 
v2
Comments
cutexxbaby 31-Jan-12 13:25pm    
can show me how in the second one? beside that you say the first one i did, but where can indicate to me?
cutexxbaby 31-Jan-12 13:27pm    
so what should do?
Dave Kreskowiak 31-Jan-12 13:45pm    
First, you can start by Googling for ".NET parameterized query" for a ton of examples.

Next, you can then Google for "SQL Injection Attacks" to find out why what you're doing is so bad.
Sergey Alexandrovich Kryukov 31-Jan-12 15:24pm    
I added my answer to answer OP's question on injection and added my clarification, credited this answer as I agree with it -- please see.
--SA
Ronald Vilmé 31-Jan-12 13:30pm    
Hello!
My name is Ronald. I am practicing in Vb.net. I want to save the data of listbox in database sql server 2008. Please some one can help me?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900