none
Insert Into

    Question

  • provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
            datafile = "E:\Software Development\Student Profiler\Student Profiler\School.accdb"
            connString = provider & datafile
            myConnection.ConnectionString = connString
            myConnection.Open()
            Dim str As String
            str = "Insert into Basic Information([ID],[Admitted In The Class],[Subject],[First Name Of The Student],[Middle Name Of The Student],[Last Name Of The Student],[Fathers Name],[Mothers Name],[Name Of The Railway Gurdian],[Post],[Department],[Date Of Birth],[Address],[Permanent Address],[Distance From The School],[Religion],[Nationality],[Category],[Name Of The Cast],[Gender],[Name Of The Last School],[Marks Obtained],[OUt Of],[Percentage],[Result],[Dice Code Of The Last School]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            cmd.Parameters.Add(New OleDbParameter("ID", CType(IDTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Admitted In The Class", CType(Admitted_In_The_ClassComboBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Subject", CType(SubjectComboBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("First Name Of The Student", CType(First_Name_Of_The_StudentTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Middle Name Of The Student", CType(Middle_Name_Of_The_StudentTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Last Name Of The Student", CType(Last_Name_Of_The_StudentTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Fathers Name", CType(Fathers_NameTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Mothers Name", CType(Mothers_NameTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Name Of The Railway Gurdian If Any", CType(Name_Of_The_Railway_Gurdian__If_Any_TextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Post", CType(PostTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Department", CType(DepartmentTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Date Of Birth", CType(Date_Of_BirthDateTimePicker1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Address", CType(AddressTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Permanent Address", CType(Permanent_AddressTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Distance From The School", CType(Distance_From_The_SchoolTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Religion", CType(ReligionComboBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Nationality", CType(NationalityComboBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Category", CType(CategoryComboBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Name Of The Cast", CType(Name_Of_The_CastTextBox.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Gender", CType(GenderComboBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Name Of The Last School", CType(Name_Of_The_Last_SchoolTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Marks Obtained", CType(Marks_ObtainedTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("OUt Of", CType(OUt_OfTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Percentage", CType(PercentageTextBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Result", CType(ResultComboBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Dice Code Of The Last School", CType(Dice_Code_Of_The_Last_SchoolTextBox1.Text, String)))
    
            Try
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                myConnection.Close()
                MsgBox("Data Saved Sucessfully")
                IDTextBox1.Clear()
                Admitted_In_The_ClassComboBox1.Items.Clear()
                SubjectComboBox1.Items.Clear()
                First_Name_Of_The_StudentTextBox1.Clear()
                Middle_Name_Of_The_StudentTextBox1.Clear()
                Last_Name_Of_The_StudentTextBox1.Clear()
                Fathers_NameTextBox1.Clear()
                Mothers_NameTextBox1.Clear()
                Name_Of_The_Railway_Gurdian__If_Any_TextBox1.Clear()
                PostTextBox1.Clear()
                DepartmentTextBox1.Clear()
                AddressTextBox1.Clear()
                PercentageTextBox1.Clear()
                Distance_From_The_SchoolTextBox1.Clear()
                ReligionComboBox1.Items.Clear()
                NationalityComboBox1.Items.Clear()
                CategoryComboBox1.Items.Clear()
                Name_Of_The_CastTextBox.Clear()
                GenderComboBox1.Items.Clear()
                Name_Of_The_Last_SchoolTextBox1.Clear()
                Marks_ObtainedTextBox1.Clear()
                OUt_OfTextBox1.Clear()
                PercentageTextBox1.Clear()
                ResultComboBox1.Items.Clear()
                Dice_Code_Of_The_Last_SchoolTextBox1.Clear()
    
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
    


    Wednesday, April 05, 2017 6:34 PM

All replies

  • Please Help
    Wednesday, April 05, 2017 6:37 PM
  • We have no clue to what the problem is. All you did was post code (and you should use the code block option into the toolbar for this).

    So please provide detail information.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 05, 2017 9:29 PM
    Moderator
  • For starters Basic Information should be in brackets [Basic Information] if that is a table name with a space in it.

    Second think I noticed is that you added the paramaters to the collection, but you need to include those names in the sql command.

    Also, you may want to create a DataSet, and OleDataAdapter? Use the Adapter to fill the dataset.

    If you create a DataRow from the DataSet, it will automatically configure it to the correct schema. Something like this: Can't promise its perfect. I'm a bit new myself.

        Private Sub InsertRecord()
            Using conn As New OleDbConnection(conString)
                conn.Open()
                Dim sel As New OleDbCommand()
                Dim ins As New OleDbCommand()
                sel = conn.CreateCommand()
                ins = conn.CreateCommand()
    
                sel.CommandText = "SELECT TOP 1 * FROM [Basic Information]"
                ins.CommandText = "INSERT INTO [Basic Information] ([ID],[Admitted In The Class],[Subject],[First Name Of The Student]) " +
                                        "VALUES(@Param1, @Param2, @Param3, @Param4)"
                Dim da As New OleDbDataAdapter(sel)
    
                da.InsertCommand = ins
    
                da.InsertCommand.Parameters.Add("@Param1", OleDbType.Numeric, 0, "[ID]")
                da.InsertCommand.Parameters.Add("@Param2", OleDbType.VarChar, 50, "[Admitted In The Class]")
                da.InsertCommand.Parameters.Add("@Param3", OleDbType.VarChar, 50, "[Subject]")
                da.InsertCommand.Parameters.Add("@Param4", OleDbType.VarChar, 50, "[First Name Of The Student]")
    
                Dim ds As New DataSet()
                da.Fill(ds)
                Dim row As DataRow
                row = ds.Tables(0).NewRow()
                row("[ID]") = Integer.Parse(IDTextBox1.Text)
                row("[Admitted In The Class]") = Admitted_In_The_ClassComboBox1.Text
                row("[Subject]") = SubjectComboBox1.Text
                row("[First Name Of The Student]") = First_Name_Of_The_StudentTextBox1.Text
    
                ds.Tables(0).Rows.Add(row)
                da.Update(ds)
                conn.Close()
    
            End Using
        End Sub



    • Edited by HTHP Wednesday, April 05, 2017 11:07 PM wrong language
    Wednesday, April 05, 2017 9:52 PM
  • Hi

    This is a VB.NET forum.


    Regards Les, Livingston, Scotland

    Wednesday, April 05, 2017 10:44 PM
  • Sorry, I translated it to VB.NET
    Wednesday, April 05, 2017 11:07 PM
  • Sorry, I translated it to VB.NET

    Hi

    Thank you. Much better :)


    Regards Les, Livingston, Scotland

    Wednesday, April 05, 2017 11:11 PM
  • For starters Basic Information should be in brackets [Basic Information] if that is a table name with a space in it.

    Second think I noticed is that you added the paramaters to the collection, but you need to include those names in the sql command.

    No despite Microsoft does in their MSDN documentation if OleDB used named parameters, is the name of the parameter complete uninmportant. It is just the order in which it is given which has be the same as in the SQL transact. Like you can see in the code given which seems to be made with a wizard do they use in their software simply in their SQL transact code  a ?. 

    I think that it is better to wait what the OP sais that the problem is. 


    Success
    Cor



    Thursday, April 06, 2017 12:43 AM
  • I think HTHP pointed out your first problem (table name). If it fixes your issue then mark his response as the answer. Otherwise, if you are still encountering errors then please identify.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, April 06, 2017 2:36 AM
  • What Data type should i use to get % values and store for this textbox
    Thursday, April 06, 2017 4:47 PM
  • many thanks for your kind advice love
    Thursday, April 06, 2017 4:49 PM
  • Hi

    Perhaps a Function such as this would help. Used anywhere a Double is needed from a string (other types can also be dealt with in the same manner)

        Private Function GetDouble(s As String) As Double
            Dim v As Double = 0.0
            If Double.TryParse(s, v) Then Return v
            Return 0.0
        End Function
    

    The function would be used in place of your Cast

    CType(PercentageTextBox1,Double)
    
    
    would become
    
    
    GetDouble(PercentageTextBox1.Text)


    Regards Les, Livingston, Scotland

    Thursday, April 06, 2017 5:33 PM
  • You need to remove the percent sign before the String can be converted to a Double:

    Dim percentageValue as Double percentageValue = CType(Replace(PercentageTextBox1.Text, "%", ""), Double)

    cmd.Parameters.Add(New OleDbParameter("Percentage", percentageValue))



    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, April 07, 2017 12:53 PM
  • Hi Tanmay,

    Please remember to close your thread by marking helpful post, it is very beneficial to the other communities who face the same issue.

    Thanks for your understanding.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 14, 2017 8:30 AM
    Moderator