5 Replies - 468 Views - Last Post: 23 April 2012 - 05:31 PM Rate Topic: -----

#1 ThatsSoShaneka  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 13-April 12

Inserting data if a row is not already there

Posted 22 April 2012 - 04:30 PM

Basically I am trying to insert some data about a user into a database for a test that they complete. If it's their first time completing the test then I want their results to be automatically stored. If not then I want their results to only be overwritten if they agree to it.

Here's my code so far:


    Dim sqlx As OleDbCommand = New OleDbCommand
    Dim con As New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = Decisionmaths.mdb")
    Dim rdr As OleDbDataReader

    Private Sub frmassessmark_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim todaysdate As DateTime = Today

        txtmark.ReadOnly = True
        txtpercent.ReadOnly = True
        txtgrade.ReadOnly = True

        sqlx.Connection = con
        con.Open()

        Select Case frmyourassess.comboxassess.SelectedIndex
            Case 0
                sqlx.CommandText = ("SELECT * FROM Results WHERE StudentNumber = " & frmlogin.txtID.Text & " AND AssessmentID = '" & "KRUSK1" & "'")
                rdr = sqlx.ExecuteReader()

                If Not rdr.HasRows Then
                    sqlx.CommandText = ("INSERT INTO Results VALUES(" & CInt(frmlogin.txtID.Text) & ",'" & "KRUSK1" & "','" & todaysdate & "','" & CInt(txtmark.Text) & "','" & CInt(txtpercent.Text) & "','" & txtgrade.Text & "')")

                ElseIf rdr.HasRows Then
                    Dim newmarks As Integer

                    newmarks = MsgBox("Are you sure you want to overrive your current score?", vbYesNo + vbExclamation)

                    If newmarks = 6 Then
                        Me.Show()
                        sqlx.CommandText = "UPDATE Results SET AssessmentDate = '" & todaysdate & "', Mark = '" & CInt(txtmark.Text) & "', Percentage = '" & CInt(txtpercent.Text) & "', Grade = '" & txtgrade.Text & "' WHERE StudentNumber = " & CInt(frmlogin.txtID.Text) & " AND AssessmentID = " & "KRUSK1" & ""
                        Me.Show()
                    End If
                End If

            Case 1
                sqlx.CommandText = ("SELECT * FROM Results WHERE StudentNumber = " & frmlogin.txtID.Text & "AND AssessmentID = '" & "KRUSK2" & "'")
                rdr = sqlx.ExecuteReader()

                If Not rdr.HasRows Then
                    sqlx.CommandText = ("INSERT INTO Results VALUES(" & CInt(frmlogin.txtID.Text) & ",'" & "KRUSK1" & "','" & todaysdate & "','" & CInt(txtmark.Text) & "','" & CInt(txtpercent.Text) & "','" & txtgrade.Text & "')")

                ElseIf rdr.HasRows Then
                    Dim newmarks As Integer

                    newmarks = MsgBox("Are you sure you want to overrive your current score?", vbYesNo + vbExclamation)

                    If newmarks = 6 Then
                        Me.Show()
                        sqlx.CommandText = "UPDATE Results SET AssessmentDate = '" & todaysdate & "', Mark = '" & CInt(txtmark.Text) & "', Percentage = '" & CInt(txtpercent.Text) & "', Grade = '" & txtgrade.Text & "' WHERE StudentNumber = " & CInt(frmlogin.txtID.Text) & " AND AssessmentID = " & "KRUSK1" & ""
                        Me.Show()
                    End If
                End If

        End Select

    End Sub


The problem that I'm experiencing is that the results won't store after the users complete the test, so it would be great if anyone could help me to figure out where I'm going wrong.

Is This A Good Question/Topic? 0
  • +

Replies To: Inserting data if a row is not already there

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Inserting data if a row is not already there

Posted 23 April 2012 - 08:35 AM

Not sure what could be happening as this type of thing requires some stepping through the code and debugging by checking your values.

One thing that might help is you msgbox.

Let's make a few changes using MessageBox.Show instead of MsgBox.

Create a variable to hold a dialogresult

Dim newmarks as DialogResult

newmarks = MessageBox.Show("Are you sure you want to overrive your current score?","Overwrite",Messageboxbuttons.YesNo, MessageboxIcon.Exclamation)

If newmarks = dialogresult.yes then


This should replace
Dim newmarks As Integer

 newmarks = MsgBox("Are you sure you want to overrive your current score?", vbYesNo + vbExclamation)
 If newmarks = 6 Then


If it's still not working, step through your code and see what it is doing with each step of the process.

EDIT:
Oh and you're also missing some ' in your insert statement.
shouldn't this
AND AssessmentID = " & "KRUSK1" & ""
be
AND AssessmentID = " & "'KRUSK1'" ' note, you don't need to append "" as the statement ends properly.
?

This post has been edited by CharlieMay: 23 April 2012 - 08:42 AM

Was This Post Helpful? 0
  • +
  • -

#3 ThatsSoShaneka  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 13-April 12

Re: Inserting data if a row is not already there

Posted 23 April 2012 - 02:54 PM

Thank you for the help! I made the changes like you suggested but I still can't seem to get the code to insert the data.
Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Inserting data if a row is not already there

Posted 23 April 2012 - 04:13 PM

Any errors occurring?
Can I see the code with the updated changes?
Was This Post Helpful? 0
  • +
  • -

#5 ThatsSoShaneka  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 13-April 12

Re: Inserting data if a row is not already there

Posted 23 April 2012 - 04:52 PM

Nope I don't get any errors it just doesn't change in the database. And ermmm that's all the code to update the system because a mark, percentage and grade is shown on this form in text boxes and the idea was for whatever was shown in these text boxes would be stored into the database.
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Inserting data if a row is not already there

Posted 23 April 2012 - 05:31 PM

So have you stepped through the code to see if it's hitting all the areas and holding the values you expect?
You've got some different conditions you're checking for and need to make sure those conditions are being met and the enclosed code is being executed.

Another thing to try is to print the result of your statments with their concatenated values and run them against your database to see if they work as expected.

What I meant by re-posting was to ensure that you didn't make some other error when making the changes I've suggested.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1