Having a date as the primary key in a database

[RufusLDK]

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?

[ProphetBeal]

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

[RufusLDK]

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

[jmcilhinney]

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

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:

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

[RufusLDK]

    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

[jmcilhinney]

What value does ExecuteNonQuery return?

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

Finally, see this:

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

then see this:

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

then see this:

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