Having a date as the primary key in a database

I have a table in my database and the primary key is the date. I have to access that row of the table to get data. But when ever i execute the reader and then get the value i get the error, No data exists for the row/column. I have tried all different ways of having the sql command look for that string but with no joy. Is what i'm doing possible?

If you post the code you are having trouble with we may be able to help.

Code:

---

Public Sub addSale()
        conn.Open()
        sql = "SELECT sSold FROM Summary WHERE rDate =" & Date.Today
        Dim oc As New _
        System.Data.OleDb.OleDbCommand(sql, conn)
        Dim objRead As System.Data.OleDb.OleDbDataReader
        objRead = oc.ExecuteReader
        objRead.Read()
        intA = objRead.GetValue(0) + 1
        conn.Close()
        sql = "UPDATE Summary SET sSold = @sSold WHERE rDate =" & Date.Today
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(sql, conn)
        cmd.Parameters.Add("@sSold", OleDb.OleDbType.Integer).Value = intA
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub

---

I get the error at the underlined line

NEVER use string concatenation to build SQL statements. It is insecure and error-prone, as you've just proven. Use parameters EVERY time.

vb Code:

Dim oc As New OleDbCommand("SELECT sSold FROM Summary WHERE rDate = @rDate", conn)
 
oc.Parameters.AddWithValue("@rDate", Date.Today)

Also, if you're only getting a single value then you should be calling ExecuteScalar, not ExecuteReader.

Also, there's no reason for you to be executing a query at all. If you want to update a single row then just execute an UPDATE statement. This should work I think:

Code:

---

UPDATE Summary SET sSold = sSold + 1 WHERE rDate = @rDate

---

Code:

---

    Public Sub addSale()
        conn.Open()
        Dim oc As New _
        System.Data.OleDb.OleDbCommand("SELECT sSold FROM Summary WHERE rDate =@rDate", conn)
        oc.Parameters.AddWithValue("@rDate", Date.Today)
        Dim objRead As System.Data.OleDb.OleDbDataReader
        objRead = oc.ExecuteReader
        objRead.Read()
        intA = objRead.GetValue(0) + (DataGridView1.Rows.Count - 1)

        Dim cmd As New _
        System.Data.OleDb.OleDbCommand("UPDATE Summary SET sSold = @sSold WHERE rDate = @rDate", conn)
        cmd.Parameters.AddWithValue("@rDate", Date.Today)
        cmd.Parameters.Add("@sSold", OleDb.OleDbType.Integer).Value = intA
        cmd.ExecuteNonQuery()
        conn.Close()

    End Sub

---

I'm having trouble updating the table now, i don't get any errors but it doesn't update the table. I've tested the first section of getting the data from the table so i can take it add to it then update it. But it doesn't enter the value to the table. Can anyone see where i'm going wrong

What value does ExecuteNonQuery return?

Also, you should be using ExecuteScalar like I said, not ExecuteReader.

Finally, see this:

vb Code:

cmd.Parameters.AddWithValue("@rDate", Date.Today)

then see this:

vb Code:

cmd.Parameters.Add("@sSold", OleDb.OleDbType.Integer).Value = intA

then see this:

vb Code:

cmd.Parameters.AddWithValue("@sSold", intA)