demausdauth's Profile User Rating: -----

Reputation: 174 Stalwart
Group:
Expert
Active Posts:
629 (0.41 per day)
Joined:
03-February 10
Profile Views:
12,433
Last Active:
User is offline Apr 18 2014 07:57 AM
Currently:
Offline

Previous Fields

Country:
US
OS Preference:
Windows
Favorite Browser:
Chrome
Favorite Processor:
Intel
Favorite Gaming Platform:
PC
Your Car:
Saturn
Dream Kudos:
50
Icon   demausdauth Getting frustrated with paydays -- nothing left :(

Posts I've Made

  1. In Topic: Inserting data into one record of a database from different forms

    Posted 8 Apr 2014

    So, form1 initially saves the student record with all empty fields except the autonumber ID. Then form2 is opened and you want to enter the information that will be filled in for the record.

    If so then the best way would be to have form1 pass the ID value from the newly created record to form2. Reason being, if there are more than one instances of this app running, selecting max just might get you a different student.

    You could create a public property on form2:
    Public Property StudentId As Integer
    
    


    Or you could overload the New constructor and include the value to pass there:
    Dim studentId As Integer
    Public Sub New(ByVal passStudentId As Integer)
         studentId = passStudentId
    End Sub
    
    


    In my opinion and experience-

    The advantage to this is that you won't even need the text box change event. I assume (and maybe I am wrong to assume it) that there is a save button (maybe you call it a close button) on form2. That said, in the save button event you would validate the data, then perform the Update using the ID value you passed to form2 which is the primary key of the record anyways. This method ensures that you are editing the correct record and your code is cleaner, easier to read and understand.
  2. In Topic: Inserting data into one record of a database from different forms

    Posted 7 Apr 2014

    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?
  3. In Topic: Inserting data into one record of a database from different forms

    Posted 7 Apr 2014

    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.
  4. In Topic: Inserting data into one record of a database from different forms

    Posted 7 Apr 2014

    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
    
  5. In Topic: Inserting data into one record of a database from different forms

    Posted 7 Apr 2014

    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()
    
    
    

My Information

Member Title:
D.I.C Addict
Age:
Age Unknown
Birthday:
February 2
Gender:
Location:
Minnesota
Interests:
my family, reading, programming,
Full Name:
Aaron
Years Programming:
6
Programming Languages:
VB.NET, C#.NET, ASP.NET, SharePoint 2007 development

Contact Information

E-mail:
Private

Comments

Page 1 of 1
  1. Photo

    .nethelp Icon

    07 Apr 2012 - 14:31
    why did u put -1 on my reputation coz u know better? werent u like me before ? thanks anyways this truely shows ur good spirit and what a good mentor u are .
    bye
  2. Photo

    .nethelp Icon

    07 Apr 2012 - 14:31
    why did u put -1 on my reputation coz u know better? werent u like me before ? thanks anyways this truely shows ur good spirit and what a good mentor u are .
    bye
  3. Photo

    Guest 

    29 Jun 2010 - 13:54
    http://www.dreamincode.net/forums/topic/179550-updatepanels-placeholders-and-usercontrols/
  4. Photo

    joker_1 Icon

    13 Jun 2010 - 21:37
    Hi quick question.. What is the "e" argument??
    thanks heaps
  5. Photo

    demausdauth Icon

    11 Jun 2010 - 06:05
    lol -- not trying to break any records
  6. Photo

    CharlieMay Icon

    10 Jun 2010 - 18:31
    Just wanted to say, you keep answering questions like you do and your rep is going to sky-rocket.
Page 1 of 1