locked
getting error "sqlexception was unhandled" RRS feed

  • Question

  • Hi everyone,

    I tried to create an insert sql query using a vb.net script and for some reason im getting an error message "sqlexception was unhandled"

    Here is the screenshot

    and here is my entire vb.net script

    Private Sub RegUser(ByVal tmpGSMNo As String, ByVal tmpText As String, ByVal tmpID As Long)
            Dim SQLServer As String = My.Settings.SQLServer
            Dim conn As System.Data.SqlClient.SqlConnection
            Dim InsertUserText As System.String
            Dim InsertUserCommand As System.Data.SqlClient.SqlCommand
    
            conn = New System.Data.SqlClient.SqlConnection(SQLServer)
            conn.Open()
    
            Dim nsms As New SMSWS.TDSSMSService
            Dim tempError As String = ""
    
            Dim melding As String = "Du har blitt registrert"
    
            Dim str As String = tmpText
            Dim str2 As System.String = str.Replace("TDS REGSR", "")
            Dim strArr() As System.String = str2.Split(" "c)
    
            InsertUserText = System.String.Format("INSERT INTO SMSService([GSMNo], [Navn], [Firma], [Sentraler]) VALUES ({0}, {1}, {2}, {3})", tmpGSMNo, strArr(1), strArr(2), strArr(3))
            InsertUserCommand = New System.Data.SqlClient.SqlCommand(InsertUserText, conn)
    
            InsertUserCommand.ExecuteNonQuery()
            conn.Close()
            conn.Dispose()
    
    End Sub
    

    Have a great day.


    Thanks, Ed
    Monday, January 16, 2012 8:51 AM

Answers

  • thanks for you help uri and manish.. :) i was able to figure it out.. i juz need to put '' on it.. :D

    FROM

    VALUES ({0}, {1}, {2}, {3})", tmpGSMNo, strArr(1), strArr(2), strArr(3))

    TO
    VALUES ('{0}', '{1}', '{2}', '{3}')", tmpGSMNo, strArr(1), strArr(2), strArr(3))


    its working now.. :)


    U guys rock.: :D I appreciate it.. :D


    Thanks, Ed
    • Proposed as answer by Asim Bagwan Monday, January 16, 2012 10:29 AM
    • Marked as answer by amber zhangEditor Wednesday, January 25, 2012 5:18 AM
    Monday, January 16, 2012 9:45 AM

All replies

  • Ed

    What is tmpGSMNo, strArr(1), strArr(2), strArr(3)????

    INSERT INTO [Table Name] (field1,field2,field3) VALUES ('" textbox1.text & "','" & textbox2.text & "','" & textbox3.text & "')"


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 16, 2012 9:08 AM
  • I used a string format and named as InsertUserText. The value of InsertUserText are purely string which will be converted later on InsertUserCommand. 

     

    if you noticed strArr(), its a string of arrays being split. Thats y I used  strArr(1), strArr(2), strArr(3).. Hope it make sense to you..

     


    Thanks, Ed
    Monday, January 16, 2012 9:17 AM
  • Ed,

    This is the general syntax to insert rows in table.

    Insert into mytable (col1, col2, col3) values ('mycol3','mycol3','mycol3')
    

    But in your case its as follows

     

     

    INSERT INTO SMSService([GSMNo], [Navn], [Firma], [Sentraler]) VALUES ({0}, {1}, {2}, {3})", tmpGSMNo, strArr(1), strArr(2), strArr(3))
    
    
    

    Please remove this "{0}, {1}, {2}, {3})" and then try as Uri mentioned above.

     


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    Monday, January 16, 2012 9:25 AM
  • thanks for you help uri and manish.. :) i was able to figure it out.. i juz need to put '' on it.. :D

    FROM

    VALUES ({0}, {1}, {2}, {3})", tmpGSMNo, strArr(1), strArr(2), strArr(3))

    TO
    VALUES ('{0}', '{1}', '{2}', '{3}')", tmpGSMNo, strArr(1), strArr(2), strArr(3))


    its working now.. :)


    U guys rock.: :D I appreciate it.. :D


    Thanks, Ed
    • Proposed as answer by Asim Bagwan Monday, January 16, 2012 10:29 AM
    • Marked as answer by amber zhangEditor Wednesday, January 25, 2012 5:18 AM
    Monday, January 16, 2012 9:45 AM