Allow only one entry per day?

Question

Hello,

I am using sql for this, I would like to know How I can allow only one entry (in the database) per day for each user ID

I have a table with Users IDs and the date (day/month/year). Every time a new row is inserted, the ID and the current date is inserted. The ID is the PK. However, I want to allow a new entry for the same user the next day, and so on.

Thanks in advance!

Answer 1

Hi, i think you need to have a multiple key in that table (key is userid and date)

By code you need to ensure that the user and date does not exists in the db, if not exists you could insert, else dont insert and show that to the user....

---

Si la respuesta te ha sido util Marcala como Respuesta o Votala.
Mi Blog: Jtorrecilla
Enlace a Faq de Winforms en Ingles Muy bueno

Answer 2

Same thing can be achieved through the stored procedure. Write your validation code in stored procedure, like checking for row existing in database and inserting or not inserting the record.
Other way is to use the trigger and fire it before row insert event and generate the error if  another entry exists.

---

http://www.ishan-solution.blogspot.com

Answer 3

I agree with jtorrecilla. You will have to create composite key (UserId + Date) in your table. And before inserting into the database you can use following query.

IF (SELECT COUNT(*) FROM TableName WHERE UserID = @UserID AND CurrentDate = @CurrentDate) =  0

     --INSERT QUERY

In the above query if the count is 0 then you can insert into the table.

---

Gaurav Khanna Visual Basic MVP

Answer 4

umm.. Could you please be more specific, I am really confused. How do I create a composite key?

Also, I tried using the query you posted and I am getting an "Expression expected" error on SELECT

I also tried doing it like this:

sqlselect = "SELECT COUNT(*) FROM MyTable WHERE UserID = @UserID AND Date = @Date"
            If sqlselect = 0 Then
                MsgBox("I can insert")
            Else
                MsgBox("Cannot Insert")
            End If

not working either :s

Answer 5

Please could you put your complete code here??

To review correctly, and give a better answer

---

Si la respuesta te ha sido util Marcala como Respuesta o Votala.
Mi Blog: Jtorrecilla
Enlace a Faq de Winforms en Ingles Muy bueno

Answer 6

 Private Sub btnNewUserEntry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewUserEntry.Click
        Dim sqlinsert As String
        Dim sqlselect As String
        Dim currentDate As Date = DateAndTime.Now
        Dim culture As New CultureInfo("es-ES")
       currentDate = thisDate.ToString("d", culture)
       
        Try
            If tboUserID.Text = "" Then
                MsgBox("Please Insert Valid ID")
                Exit Try
            End If

            sqlselect = "SELECT COUNT(*) FROM entries WHERE UserId = @UserId AND Date = @Date"
            If sqlselect = "0" Then
                MsgBox("I can insert")
            Else
                MsgBox("Cannot Insert")
            End If

           '  If (SELECT COUNT(*) FROM entries WHERE UserId = @UserId AND Date = @Date) =  0 then
            End If

            sqlinsert = "INSERT INTO entries(UserID, Date)" & _
           "VALUES(@UserID, @Date)"
            Dim cmd As New SqlCommand(sqlinsert, con1)

          
            cmd.Parameters.Add(New SqlParameter("@UserID", tboUserID.Text))
                       cmd.Parameters.Add(New SqlParameter("@Date", CurrentDate))
.
            If con1.State = ConnectionState.Closed Then
                con1.Open()
            End If
            cmd.ExecuteNonQuery()
            If con1.State = ConnectionState.Open Then
                con1.Close()
            End If
          
            ClearTextBox(Me)
            RefreshDGV2()

        Catch se As SqlException
            If se.Message.StartsWith("Violation of PRIMARY KEY constraint") Then
                MsgBox("Max Entry Reached for Today", MsgBoxStyle.OkOnly, "Attention")
            Else
                MsgBox("Error: " & se.Source & ": " & se.Message, MsgBoxStyle.OkOnly, "Connection Error")
            End If

        End Try
        End Sub

Answer 7

There is lost code in your code... Please try qwith this:

 Private Sub btnNewUserEntry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewUserEntry.Click
        Dim sqlinsert As String
        Dim sqlselect As String
        Dim currentDate As Date = DateAndTime.Now
        Dim culture As New CultureInfo("es-ES")
       currentDate = thisDate.ToString("d", culture)
       
        Try
            If tboUserID.Text = "" Then
                MsgBox("Please Insert Valid ID")
                Exit Try
            End If

sqlselect = "SELECT COUNT(*) FROM entries WHERE UserId = @UserId AND Date = @Date"
If con1.State = ConnectionState.Closed Then
                con1.Open()
            End If
 Dim cmd As New SqlCommand(sqlselect , con1)
 cmd.Parameters.Add(New SqlParameter("@UserID", tboUserID.Text))

                       cmd.Parameters.Add(New SqlParameter("@Date", CurrentDate))

            If  cmd.ExecuteNonQuery() = 0 Then
   sqlinsert = "INSERT INTO entries(UserID, Date)" & _
           "VALUES(@UserID, @Date)"
           if cmd.ExecuteNonQuery() = 0 then
Msgbox("Data saved!")
           else
Msgbox("No rows affected")
            end if
            Else
                MsgBox("Cannot Insert")
            End If
            
            If con1.State = ConnectionState.Open Then
                con1.Close()
            End If
          
            ClearTextBox(Me)
            RefreshDGV2()

        Catch se As SqlException
            If se.Message.StartsWith("Violation of PRIMARY KEY constraint") Then
                MsgBox("Max Entry Reached for Today", MsgBoxStyle.OkOnly, "Attention")
            Else
                MsgBox("Error: " & se.Source & ": " & se.Message, MsgBoxStyle.OkOnly, "Connection Error")
            End If

        End Try
        End Sub

---

Si la respuesta te ha sido util Marcala como Respuesta o Votala.
Mi Blog: Jtorrecilla
Enlace a Faq de Winforms en Ingles Muy bueno

Answer 8

umm I always get the "Cannot Insert" message Box. However, there is no previous data in the database

Answer 9

Sorry please review this:

 Private Sub btnNewUserEntry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewUserEntry.Click
        Dim sqlinsert As String
        Dim sqlselect As String
        Dim currentDate As Date = DateAndTime.Now
        Dim culture As New CultureInfo("es-ES")
       currentDate = thisDate.ToString("d", culture)
       
        Try
            If tboUserID.Text = "" Then
                MsgBox("Please Insert Valid ID")
                Exit Try
            End If

sqlselect = "SELECT COUNT(*) FROM entries WHERE UserId = @UserId AND Date = @Date"
If con1.State = ConnectionState.Closed Then
                con1.Open()
            End If
 Dim cmd As New SqlCommand(sqlselect , con1)
 cmd.Parameters.Add(New SqlParameter("@UserID", tboUserID.Text))

                       cmd.Parameters.Add(New SqlParameter("@Date", CurrentDate))

            If  cmd.executescalar() = 0 Then
   sqlinsert = "INSERT INTO entries(UserID, Date)" & _
           "VALUES(@UserID, @Date)"
           if cmd.ExecuteNonQuery() = 0 then
Msgbox("Data saved!")
           else
Msgbox("No rows affected")
            end if
            Else
                MsgBox("Cannot Insert")
            End If
            
            If con1.State = ConnectionState.Open Then
                con1.Close()
            End If
          
            ClearTextBox(Me)
            RefreshDGV2()

        Catch se As SqlException
            If se.Message.StartsWith("Violation of PRIMARY KEY constraint") Then
                MsgBox("Max Entry Reached for Today", MsgBoxStyle.OkOnly, "Attention")
            Else
                MsgBox("Error: " & se.Source & ": " & se.Message, MsgBoxStyle.OkOnly, "Connection Error")
            End If

        End Try
        End Sub

---

Si la respuesta te ha sido util Marcala como Respuesta o Votala.
Mi Blog: Jtorrecilla
Enlace a Faq de Winforms en Ingles Muy bueno

Answer 10

You only changed the first cmd.ExecuteNonQuery() to cmd.executescalar() right? It's giving me the same thing :(

Answer 11

Private Sub btnNewUserEntry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewUserEntry.Click
        Dim sqlinsert As String
        Dim sqlselect As String
        Dim currentDate As Date = DateAndTime.Now
        Dim culture As New CultureInfo("es-ES")
       currentDate = thisDate.ToString("d", culture)
       
        Try
            If tboUserID.Text = "" Then
                MsgBox("Please Insert Valid ID")
                Exit Try
            End If

sqlselect = "SELECT COUNT(*) FROM entries WHERE UserId = @UserId AND Date = @Date"
If con1.State = ConnectionState.Closed Then
                con1.Open()
            End If
 Dim cmd As New SqlCommand(sqlselect , con1)
 cmd.Parameters.Add(New SqlParameter("@UserID", tboUserID.Text))

                       cmd.Parameters.Add(New SqlParameter("@Date", CurrentDate))

'if returns 1 or null... 
            If  cmd.executescalar() <> 0 Then
   
                MsgBox("Cannot Insert")
else
sqlinsert = "INSERT INTO entries(UserID, Date)" & _
           "VALUES(@UserID, @Date)"
           if cmd.ExecuteNonQuery() = 0 then
Msgbox("Data saved!")
           else
Msgbox("No rows affected")
            end if
 
            End If
            
            If con1.State = ConnectionState.Open Then
                con1.Close()
            End If
          
            ClearTextBox(Me)
            RefreshDGV2()

        Catch se As SqlException
            If se.Message.StartsWith("Violation of PRIMARY KEY constraint") Then
                MsgBox("Max Entry Reached for Today", MsgBoxStyle.OkOnly, "Attention")
            Else
                MsgBox("Error: " & se.Source & ": " & se.Message, MsgBoxStyle.OkOnly, "Connection Error")
            End If

        End Try
        End Sub

Lets try something:

---

Si la respuesta te ha sido util Marcala como Respuesta o Votala.
Mi Blog: Jtorrecilla
Enlace a Faq de Winforms en Ingles Muy bueno

Answer 12

Now Jtorrecilla code should work....

you can also do it

 If  cmd.executescalar() <> 0 Then------------remove it 

              MsgBox("Cannot Insert")
else

leave it----------------------

sqlinsert = "INSERT INTO entries(UserID, Date)" & _
           "VALUES(@UserID, @Date)"
           if cmd.ExecuteNonQuery() = 0 then
Msgbox("Data saved!")
           else
Msgbox("No rows affected")
            end if


                MsgBox("Cannot Insert")

If con1.State = ConnectionState.Open Then
                con1.Close()
            End If
         
            ClearTextBox(Me)
            RefreshDGV2()

        Catch se As SqlException
            If se.Message.StartsWith("Violation of PRIMARY KEY constraint") Then
                MsgBox("Max Entry Reached for Today", MsgBoxStyle.OkOnly, "Attention")
            Else
                MsgBox("Error: " & se.Source & ": " & se.Message, MsgBoxStyle.OkOnly, "Connection Error")
            End If

        End Try

---

Just Be Humble Malange!

Answer 13

A lot of persons help you with code, and show this and you are replying in these short sentences, should you not show your code as well, probably simply to answer with that

---

Success
Cor

Answer 14

hi all

i would've done it this way;

create a primary key date & userid in the dataset.

Answer 15

create a primary key date & userid in the dataset.

Answer 16

Hi Ibrahimakos thanks for the reply, but please dont put twice... we´re still waiting for the OP to check if any of the given answer were successfull...

---

Si la respuesta te ha sido util Marcala como Respuesta o Votala.
Mi Blog: Jtorrecilla
Enlace a Faq de Winforms en Ingles Muy bueno

Answer 17

thank you guys for all your replies. I must be doing something wrong however. Still same result. I will recheck all the code I have and post back. If there is any other way of doing this without taking into account the code I have, please post it.

Answer 18

Gitxy, please could you put your complete code, or upload your project to Skydrive to take a look?

---

Si la respuesta te ha sido util Marcala como Respuesta o Votala.
Mi Blog: Jtorrecilla
Enlace a Faq de Winforms en Ingles Muy bueno