Inserting data into one record of a database from different forms

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 1543 Views - Last Post: 08 April 2014 - 09:53 AM Rate Topic: -----

#1 Khutchutchu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-March 14

Inserting data into one record of a database from different forms

Posted 07 April 2014 - 01:34 AM

My application have four forms, each collects and store data into an access database. Now I have a problem, the data collected in the second form is stored into the next record instead of the same record as form1, probably different fields. After searching the net, I got this but it is not working in my case. Please help, thanks.
Private Sub txtStudent_Number_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtStudent_Number.TextChanged

        Dim stringConn As String
        Dim OleDbConn As OleDbConnection
        Dim sqlQuery As String
        stringConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\StudentProfile.accdb"
        OleDbConn = New OleDbConnection(stringConn)
        
        Try

            
            OleDbConn.Open()

            sqlQuery = "SELECT MAX([student number]) FROM Table1 "
            Dim cmd As OleDbCommand = New OleDbCommand(sqlQuery, OleDbConn)
            cmd.ExecuteNonQuery()
            Dim stringCmd As String = cmd.ExecuteScalar

            If stringCmd Is DBNull.Value Then

                txtStudent_Number.Text = "txtStudent_Number"

            Else

                txtStudent_Number.Text = (Convert.ToInt32(stringCmd) + 1).ToString

            End If
        Catch myException As Exception

            MsgBox("Think again!")
        Finally
            Dim DoCmd As Object

            sqlQuery = "INSERT INTO Table1 ([student number]) VALUES ([@student number]) "
            DoCmd.OpenForm ("Form2"), WHERE ID:= "ID=" & Me!ID
            Dim cmd As OleDbCommand = New OleDbCommand(sqlQuery, OleDbConn)
            cmd.Parameters.AddWithValue("@student number", txtStudent_Number.Text)
            cmd.ExecuteNonQuery()

        End Try
        OleDbConn.Close()
    End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Inserting data into one record of a database from different forms

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1726
  • View blog
  • Posts: 5,704
  • Joined: 25-September 09

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 05:20 AM

There is no WHERE clause in an INSERT statement. An INSERT will ALWAYS create a new row, to alter a row, you need to set up an UPDATE statement which has a WHERE clause.

UPDATE Table1 SET [student number] = @student WHERE ID = @id
'Create and fill parameter for @student
'Create and fill parameter for @id
'Execute UPDATE statement.

Was This Post Helpful? 0
  • +
  • -

#3 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 190
  • View blog
  • Posts: 692
  • Joined: 03-February 10

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 06:06 AM

This is VBA - for Access to open a form DoCmd.OpenForm and can't be used in a VB.Net project
Was This Post Helpful? 0
  • +
  • -

#4 Khutchutchu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-March 14

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 07:59 AM

@demausdauth
Thanks for highlighting that, though I wish you could explain why can't it be used with VB.Net project. Sure my code is better off without it.
Was This Post Helpful? 0
  • +
  • -

#5 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 190
  • View blog
  • Posts: 692
  • Joined: 03-February 10

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 08:27 AM

Because VBA and VB.Net are different platforms. The code that you are looking for to open a form.
Here is the best answer to the difference between VBA and VB.Net, that I could find.

'Create an instance of the Form2
Dim frm2 As New Form2

'Now we show it - causes the form to display.
frm2.Show()



Was This Post Helpful? 0
  • +
  • -

#6 Khutchutchu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-March 14

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 08:37 AM

@Charlie
Thanks for the suggestion, but the changes I've made now give me this error: 'syntax error in update statement'
I have updated this part:
            sqlQuery = "UPDATE Table1 SET ([student number]) VALUES ([@student number]) WHERE ID = ID"


            Dim cmd As OleDbCommand = New OleDbCommand(sqlQuery, OleDbConn)

            cmd.Parameters.AddWithValue("@student number", txtStudent_Number.Text)

            cmd.ExecuteNonQuery()



Am not so sure what to put in the ID value because the ID record is auto-created in the database.
Was This Post Helpful? 0
  • +
  • -

#7 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 190
  • View blog
  • Posts: 692
  • Joined: 03-February 10

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 09:20 AM

Here's what I see with your code.
Private Sub txtStudent_Number_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtStudent_Number.TextChanged




Might want to rethink the placement of this - the text changed event could cause the code to run more often than you want or expect.

        Dim stringConn As String
        Dim OleDbConn As OleDbConnection
        Dim sqlQuery As String
        stringConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\StudentProfile.accdb"
        OleDbConn = New OleDbConnection(stringConn)


Looks good so far - might want to think about scoping the variables to the class level.

        
        Try

            
            OleDbConn.Open()

            sqlQuery = "SELECT MAX([student number]) FROM Table1 "
            Dim cmd As OleDbCommand = New OleDbCommand(sqlQuery, OleDbConn)
            cmd.ExecuteNonQuery()
            Dim stringCmd As String = cmd.ExecuteScalar




Here your query statement says you are trying to get the max student number, probably should remove the cmd.ExecuteNonQuery as it is not needed - your execute scalar does what you want.

            If stringCmd Is DBNull.Value Then

                txtStudent_Number.Text = "txtStudent_Number"

            Else

                txtStudent_Number.Text = (Convert.ToInt32(stringCmd) + 1).ToString

            End If

        Catch myException As Exception

            MsgBox("Think again!")
        Finally


If there is no value returned you are setting a text box to the text 'txtStudent_Number' and this should be a number rather than a string value (text) - based on what you are expecting to insert.

            Dim DoCmd As Object

            sqlQuery = "INSERT INTO Table1 ([student number]) VALUES ([@student number]) "
            DoCmd.OpenForm ("Form2"), WHERE ID:= "ID=" & Me!ID
            Dim cmd As OleDbCommand = New OleDbCommand(sqlQuery, OleDbConn)
            cmd.Parameters.AddWithValue("@student number", txtStudent_Number.Text)
            cmd.ExecuteNonQuery()


Remove the DoCmd items from this block of code and it should be good. Note - as a check you for debugging you can change the following line:

cmd.ExecuteNonQuery() to Dim queryReturnValue As Integer = cmd.ExecuteNonQuery()

The reason being you can then debug and see what value is returned.

Quote

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.


From OleDbCommand.ExecuteNonQuery Method


        End Try
        OleDbConn.Close()
    End Sub

Was This Post Helpful? 0
  • +
  • -

#8 Khutchutchu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-March 14

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 10:43 AM

Thank you for checking through my code as a whole, I appreciate that. I have made the changes necessary, but still I get this error:'syntax error in UPDATE statement' through this line of code:
Dim queryReturnValue As Integer = cmd.ExecuteNonQuery()

Was This Post Helpful? 0
  • +
  • -

#9 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1726
  • View blog
  • Posts: 5,704
  • Joined: 25-September 09

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 10:51 AM

Replace [@Student Number] with just @Student and fix the name in the parameter. Not sure parameters can contain spaces or be wrapped with [].

The @parm can be anything and doesn't need to match the name of the field. Just with Oledb, it needs to be created in the order it appears in the statement.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 10:58 AM

In Access this:
DoCmd.OpenForm ("Form2"), WHERE ID:= "ID=" & Me!ID

would be this:
DoCmd.OpenForm "Form2", , ,"ID = " & Me!ID

Was This Post Helpful? 0
  • +
  • -

#11 Khutchutchu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-March 14

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 11:17 AM

@Charlie
I just made the change at that part, but no, it's not working out. Before I added the UPDATE statement and WHERE clause, the said error was not showing.
Was This Post Helpful? 0
  • +
  • -

#12 Khutchutchu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-March 14

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 11:31 AM

@andrewsw
What do you mean Andy? I have been already discouraged from using that part of code and am glad for that because it was underlined with a wavy line indicating some kind of error.
Was This Post Helpful? 0
  • +
  • -

#13 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 190
  • View blog
  • Posts: 692
  • Joined: 03-February 10

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 11:43 AM

Could you please post your current code - you have made some changes and I at least am not sure what state your code is in. Thank you.
Was This Post Helpful? 0
  • +
  • -

#14 Khutchutchu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-March 14

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 11:54 AM

Here is the code:
Private Sub txtStudent_Number_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtStudent_Number.TextChanged

        Dim stringConn As String
        Dim OleDbConn As OleDbConnection
        Dim sqlQuery As String
        stringConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\StudentProfile.accdb"
        OleDbConn = New OleDbConnection(stringConn)
        
        Try

            
            OleDbConn.Open()

            sqlQuery = "SELECT MAX([student number]) FROM Table1 "
            Dim cmd As OleDbCommand = New OleDbCommand(sqlQuery, OleDbConn)

            Dim stringCmd As String = cmd.ExecuteScalar

            If stringCmd Is DBNull.Value Then

                txtStudent_Number.Text = "txtStudent_Number"

            Else

                txtStudent_Number.Text = (Convert.ToInt32(stringCmd) + 1).ToString

            End If
        Catch myException As Exception

            MsgBox("Think again!")
        Finally
             

            sqlQuery = "UPDATE Table1 SET ([student number]) VALUES = @student WHERE ID = ID"

            Dim cmd As OleDbCommand = New OleDbCommand(sqlQuery, OleDbConn)
            cmd.Parameters.AddWithValue("@student number", txtStudent_Number.Text)
            Dim queryReturnValue As Integer = cmd.ExecuteNonQuery()


        End Try
        OleDbConn.Close()
    End Sub

Was This Post Helpful? 0
  • +
  • -

#15 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 190
  • View blog
  • Posts: 692
  • Joined: 03-February 10

Re: Inserting data into one record of a database from different forms

Posted 07 April 2014 - 04:13 PM

I have to admit that I am not exactly sure what this code is trying to accomplish.

The way that I read it is:
When the text box changes value 
     Select the max value from the [student number] field.
     If the maxStudentNumberValue is null Then
          set the text box value to a string const 'txtStudent_Number'
     Else
          set the text box value to the maxStudentNumberValue + 1

     Then even if the previous code causes an error
          Lets update the database 
          Get the value in the text box (number or text)
          Attempt to Update Table1 with the text box value (number or text).



Does that sound about right?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2