0

I have a table that looks like thisTable When I use this code it works fine

Private Sub CountData()
    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Try
        con.ConnectionString = "Data Source=LVAPP; Initial Catalog=CFLineTracker;Persist Security Info=True;User ID=CFLineAdmin;Password=aaonunit#1"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT COUNT(*) FROM ItemDetails where ItemMasterId = " & IMID & ""
        RD = cmd.ExecuteScalar
        Label5.Text = RD
    Catch ex As Exception
        MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
    Finally
        con.Close()
    End Try
End Sub

But when I try to run this code I get an exception

 Private Sub CountData()
    Dim con As New SqlConnection
    Dim cmd As New SqlCommand  
    con.ConnectionString = "Data Source=LVAPP; Initial Catalog=CFLineTracker;Persist Security Info=True;User ID=CFLineAdmin;Password=aaonunit#1"
    con.Open()
    cmd.Connection = con
    cmd.CommandText = "SELECT COUNT(*) FROM ItemDetails where Serial Number = " & SN & ""
    RDSN = cmd.ExecuteScalar
    Label5.Text = RDSN
End Sub

The SQL Exception is:

Additional information: An expression of non-boolean type specified in a context where a condition is expected, near 'Number'.

I'm using SQL SERVER 2008 R2 VB.net 2013

Abdellah OUMGHAR
  • 3,627
  • 1
  • 11
  • 16
Chuckie Chiller
  • 21
  • 5
  • 2
    If your column name has a space, you need to use [Serial Number] – James Z May 12 '16 at 14:24
  • JamesZ, I think you can add that as an answer. That will probably solve it. – Starceaker May 12 '16 at 14:28
  • @JamesZ yes i'm with JamesZ you can add that as an answer. – Abdellah OUMGHAR May 12 '16 at 14:31
  • 1
    Please *don't* use string concatenation to construct SQL statements. Someone may pass eg `1; DROP TABLE ItemDetails;--` instead of a valid serial number. Use parameterized queries – Panagiotis Kanavos May 12 '16 at 14:34
  • now I get Incorrect syntax near '=' SQL Server Error – Chuckie Chiller May 12 '16 at 14:39
  • Hard to tell why the incorrect syntax without seeing the newest query, but either way you really need to switch to a parameterized query: `cmd.CommandText = "SELECT COUNT(*) FROM ItemDetails where [Serial Number] = @sn"`, then `cmd.Parameters.AddWithValue("@sn", IMID)`, then on to your existing `RD = cmd.ExecuteScalar`. – Ed Gibbs May 12 '16 at 14:53
  • @Ed Gibbs Ok the new code : cmd.CommandText = "SELECT COUNT(*) FROM ItemDetails where [Serial Number] = @SN" cmd.Parameters.AddWithValue("@sn", SN) RDSN = cmd.ExecuteScalar i get this message now"The parameterized query '(@sn nvarchar(4000))SELECT COUNT(*) FROM ItemDetails where [Seri' expects the parameter '@sn', which was not supplied."} – Chuckie Chiller May 12 '16 at 15:07
  • Good catch - I confused your first code block with the second and used `IMID` instead of `SN`. Is it behaving for you? – Ed Gibbs May 12 '16 at 15:09
  • Sorry - our comments crossed. I think the parameter names are case-sensitive and you have uppercase `@SN` in your query and lowercase `@sn` in your `parameters.AddWithValue`. Try making them the same case. – Ed Gibbs May 12 '16 at 15:12
  • @EdGibbs no error message but it didn't count it should have counted 1 it it counted 0. – Chuckie Chiller May 12 '16 at 15:23
  • @ChuckieChiller - I hate to ask, but if you do the query in SSMS with the `Serial Number` in the WHERE clause do you get a count of zero? This is a really basic query and as far as I can tell the syntax is fine. – Ed Gibbs May 12 '16 at 15:30
  • @EdGibbs no error message but it didn't count it should have counted 1 it it counted 0. What I do is scan a serial number tag once that I place the scanned text into a variable called SN from there I want to add it to a table this all works but I want to make sure I don't get duplicates in my table so I do the count before I insert in the table.during the test this serial number already exist. – Chuckie Chiller May 12 '16 at 15:31
  • Hm, hard to tell without seeing more of the code because it may be a case of transaction isolation levels hiding the newly-inserted value, or whatever - and I'm afraid I can't pursue this because I have to get back to work. In general, if you want to avoid duplicates you should (1) add a unique index to `Serial Number`, (2) insert without checking if the value already exists, and (3) catch/handle the exception if there's a duplicate. See [here](http://stackoverflow.com/a/24740451/2091410). Note that the error code in the answer is for PK; for unique index use 2601. – Ed Gibbs May 12 '16 at 15:41
  • @EdGibbs as you probalycan tellI'm new to SQL pogramming. Thanks for your time the SSMS gives me this Must declare the scalar variable "@SN". – Chuckie Chiller May 12 '16 at 15:46
  • As you've got your answer already ([field name with space]), just a question: Are "ID=CFLineAdmin;Password=aaonunit#1" the actual user & pwd? :P –  May 13 '16 at 13:27
  • no they are fake and no it still counts to 0 unless I put the actual serial number 'abc123' in just like that where the @SN should be. – Chuckie Chiller May 13 '16 at 13:35

0 Answers0