CODEWITHSUNDEEP
sqlsql-servervb.netvisual-studio-2013
entrelac
entrelac

Reputation: 113

'System.Data.SqlClient.SqlException'

I'm writing a code that does either an interpolation or a curve fitting of a certain number of experimental points stored in a database. Part of this application allows the user to add new points to the data.

This is that part of the code:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim connection As SqlConnection = New SqlConnection()
    connection.ConnectionString = "Data Source=.;Initial Catalog = Newtcf;Integrated Security=True"
    Dim sqlq As String = "Insert into Points (x-axis,y-axis,weight) Values (@x-axis,@y-axis,@weight)"
    Dim cmd As New SqlCommand(sqlq, connection)

    cmd.Parameters.Add("@x-axis", SqlDbType.Float).Value = TextBox1.Text
    cmd.Parameters.Add("@y-axis", SqlDbType.Float).Value = TextBox2.Text
    cmd.Parameters.Add("@weight", SqlDbType.Float).Value = TextBox3.Text

    connection.Open()
    cmd.ExecuteNonQuery()
    cmd.Parameters.Clear()
    connection.Close()
End Sub

I'm getting the following error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near '-'.

Incorrect syntax near '-'.

And the line:

cmd.ExecuteNonQuery()

is highlighted in green. I honestly have no clue what the issue is, or what the "exception" means. And help from our instructors is not readily available. I'd really appreciate some clarification.

Upvotes: 1

Views: 201

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

It is because special character '-'. You shoud delimite column names. Change to:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim connection As SqlConnection = New SqlConnection()
    connection.ConnectionString = "Data Source=.;Initial Catalog = Newtcf;Integrated Security=True"
    Dim sqlq As String = "Insert into Points ([x-axis],[y-axis],[weight]) Values (@x,@y,@w)"
    Dim cmd As New SqlCommand(sqlq, connection)

    cmd.Parameters.Add("@x", SqlDbType.Float).Value = TextBox1.Text
    cmd.Parameters.Add("@y", SqlDbType.Float).Value = TextBox2.Text
    cmd.Parameters.Add("@w", SqlDbType.Float).Value = TextBox3.Text
    connection.Open()
    cmd.ExecuteNonQuery()
    cmd.Parameters.Clear()
    connection.Close()
End Sub

Look for details here https://technet.microsoft.com/en-us/library/ms176027%28v=sql.105%29.aspx

Upvotes: 3

Simon Darlow
Simon Darlow

Reputation: 550

If you have hyphens in your column name the column must be wrapped in square brackets i.e. x-axis becomes [x-axis].

If you run query 

SELECT x-axis 
FROM BLAH

it would actually be trying to subtract column axis from column x.

I would also avoid using non-alphanumeric characters in your @ variables unless it's an underscore.

Try this:

Dim sqlq As String = "Insert into Points ([x-axis],[y-axis],[weight]) Values (@x_axis,@y_axis,@weight)"

Upvotes: 0

Related Questions