locked
Insert query into Access database - not working RRS feed

  • Question

  • User-1806542732 posted

    Hi, I hv the following OLEDB query to insert into the access 2007 database.

    there are no error but there is no record in the Access Database

     

     Dim cmd As New OleDbCommand _

                 ("INSERT INTO MEMBER([title],[nameofmember],[nric],[cardid],[address],[dateofbirth],[monthofbirth],[yearofbirth],[gender],[occupationtype]," & _

                "[occupation],[race],[religion],[education],[marital],[nokname],[nokrelation],[nokcontact],[datejoined],[nameofpsy]," & _

                "[locofpsy],[reasonforpsy],[hasnewletter],[remarks],[photo]) VALUES(@title,@membername,@nric,@cardID,@strAaddress,@dob,@mob,@yob,@gender,@occtype,@occp,@race,@religion,@edu,@marital," & _"@nokname,@nokrelation,@nokcontact,@datejoined,@nameofpsy,@locofpsy,@reason,@newsletter,@remarks,@photo)", conn)


         ... cmd.parameters.addwithvalue("@..", XX)

                conn.open()

                cmd.ExecuteNonQuery()

                conn.Close()


      Dim cmd As New OleDbCommand _
                 ("INSERT INTO MEMBER([title],[nameofmember],[nric],[cardid],[address],[dateofbirth],[monthofbirth],[yearofbirth],[gender],[occupationtype]," & _
                "[occupation],[race],[religion],[education],[marital],[nokname],[nokrelation],[nokcontact],[datejoined],[nameofpsy]," & _
                "[locofpsy],[reasonforpsy],[hasnewletter],[remarks],[photo]) VALUES(@title,@membername,@nric,@cardID,@strAaddress,@dob,@mob,@yob,@gender,@occtype,@occp,@race,@religion,@edu,@marital," & _
                "@nokname,@nokrelation,@nokcontact,@datejoined,@nameofpsy,@locofpsy,@reason,@newsletter,@remarks,@photo)", conn)
                cmd.Parameters.AddWithValue("@title", strtitle)
                cmd.Parameters.AddWithValue("@membername", strnameofmember)
                cmd.Parameters.AddWithValue("@nric", strnric)
                cmd.Parameters.AddWithValue("@cardID", strcardid)
                cmd.Parameters.AddWithValue("@strAaddress", strAddress)
                cmd.Parameters.AddWithValue("@dob", dateofbirth)
                cmd.Parameters.AddWithValue("@mob", mthofBirth)
                cmd.Parameters.AddWithValue("@yob", yrofbirth)
                cmd.Parameters.AddWithValue("@gender", strgender)
                cmd.Parameters.AddWithValue("@occtype", occupationtype)
                cmd.Parameters.AddWithValue("@occp", stroccupation)
                cmd.Parameters.AddWithValue("@race", race)
                cmd.Parameters.AddWithValue("@religion", religion)
                cmd.Parameters.AddWithValue("@edu", education)
                cmd.Parameters.AddWithValue("@marital", marital)
                cmd.Parameters.AddWithValue("@nokname", nokname)
                cmd.Parameters.AddWithValue("@nokrelation", nokrelation)
                cmd.Parameters.AddWithValue("@nokcontact", nokcontact)
                cmd.Parameters.AddWithValue("@datejoined", datejoined)
                cmd.Parameters.AddWithValue("@nameofpsy", nameofpsy)
                cmd.Parameters.AddWithValue("@locofpsy", locofpsy)
                cmd.Parameters.AddWithValue("@reason", reasonforpsy)
                cmd.Parameters.AddWithValue("@newsletter", hasnewletter)
                cmd.Parameters.AddWithValue("@remarks", remarks)
                cmd.Parameters.AddWithValue("@photo", photo)
                'cmd.Parameters.Add(New OleDbParameter("@ReturnValue", OleDbType.Integer)).Direction = ParameterDirection.ReturnValue
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()





    Saturday, November 6, 2010 11:19 AM

Answers

  • User1633968479 posted

    Hard to tell. The query seems to be allright, so there can be several reasons...

    - is the event in wich you try to write to the database firing correctly?

    - are you sure errors would be displayed (remove try ... catch ... end try if yoy are using this)

    - could there be a problem in datatype-mismatch, duplicate values, ...?

     

    You could try and post your complete code so we can have a look at it, but right now it is hard to say where the problem is.

     

    Why are yoy storing the date of birth in three different colums (one for day, one for month, ...) Why not just use a datetime-field in MS Access. You can query this single field to select people born in a certain month. ("SELECT * FROM Member WHERE Month(dateofbirth) = 8;") 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 6, 2010 3:41 PM

All replies

  • User1633968479 posted

    Hard to tell. The query seems to be allright, so there can be several reasons...

    - is the event in wich you try to write to the database firing correctly?

    - are you sure errors would be displayed (remove try ... catch ... end try if yoy are using this)

    - could there be a problem in datatype-mismatch, duplicate values, ...?

     

    You could try and post your complete code so we can have a look at it, but right now it is hard to say where the problem is.

     

    Why are yoy storing the date of birth in three different colums (one for day, one for month, ...) Why not just use a datetime-field in MS Access. You can query this single field to select people born in a certain month. ("SELECT * FROM Member WHERE Month(dateofbirth) = 8;") 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 6, 2010 3:41 PM
  • User-1806542732 posted

    Hi JohnyM456, yup, for the insert query there isn't any mistake. I realized that at the solution explorer, we have to click on the database and at the properties > Output > Select Copy if Newer. The database is updated at the Bin>Debug folder.. 

    Thanks

    Wednesday, November 17, 2010 5:58 AM