none
insert statement query

    Frage

  • Hi. The following is my vb codes and sql insert query to insert rows into my database( Access 2007)

    No error is been thrown out, but when I opened my Access database and checked. There is no record.

     

        Try
          'get the database connection
          conn = dbConnection.getDBConnection()
    
          'create the database query
          'Dim query As String =
          '("INSERT INTO MEMBER(title,nameofmember,nric,cardid,dateofbirth,monthofbirth,yearofbirth,gender,occupationtype," & _
          '"occupation,race,religion, education,marital,nokname,nokrelation,nokcontact,datejoined,nameofpsy," & _
          '"locofpsy,reasonforpsy, hasnewletter,remarks, photo)" & _
          '"VALUES(" & strtitle & "," & strnameofmember & "," & strnric & "," & strcardid & "," & dateofbirth & "," & mthofBirth & "," & yrofbirth & "," & strgender & "," & occupationtype & "," & stroccupation & "," & race & "," & religion & "," & education & "," & marital & "," & nokname & "," & nokrelation & "," & nokcontact & "," & datejoined & "," & nameofpsy & "," & locofpsy & "," & reasonforpsy & "," & hasnewletter & "," & remarks & "," & photo & ")")
    
          Dim query As String =
           ("INSERT INTO MEMBER(title,nameofmember,nric,cardid,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,@dob,@mob,@yob,@gender,@occtype,@occp,@race,@religion,@edu,@marital," & _
          "@nokname,@nokrelation,@nokcontact,@datejoined,@nameofpsy,@locofpsy,@reason,@newsletter,@remarks,@photo)")
    
          Dim cmd As New OleDbCommand(query, conn)
          cmd.Parameters.AddWithValue("@title", strtitle)
          cmd.Parameters.AddWithValue("@membername", strnameofmember)
          cmd.Parameters.AddWithValue("@nric", strnric)
          cmd.Parameters.AddWithValue("@cardID", strcardid)
          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()
    
    <br/>
    

    Please assist.. thanks..

    Donnerstag, 4. November 2010 14:54

Antworten

Alle Antworten

  • There should be space on both side of tablename and values keyword in the query. Try if changing it to following works or not.

     Dim query As String = "INSERT INTO MEMBER (title,nameofmember,nric,cardid,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,@dob,@mob,@yob,@gender,@occtype,@occp,@race,@religion,@edu,@marital," & _
       "@nokname,@nokrelation,@nokcontact,@datejoined,@nameofpsy,@locofpsy,@reason,@newsletter,@remarks,@photo)"
    
    
    

    Gaurav Khanna
    Donnerstag, 4. November 2010 18:34
  • Hi, 

    I tried it out and nope it didn't work. 

    Is there any difference in accessing sql in Access and SQL? in my understanding, there should be no difference. 

     

    thanks.

    Freitag, 5. November 2010 17:42
  •  

    I saw you use try. Do you throw any error in the catch block?

     

    If these are all correct, I guess you may update in a wrong place.

    FAQ: My database isn't being updated, but no errors occurred in my application

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/ad837d14-31ea-445f-b830-1a8682b69e68

     

    Best regards,

    Ling Wang


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Als Antwort vorgeschlagen Jing0Moderator Donnerstag, 25. November 2010 03:12
    • Als Antwort markiert Helen Zhou Dienstag, 7. Dezember 2010 02:06
    Mittwoch, 10. November 2010 10:22
    Moderator
  •  

    How is this issue now?

     

    I suggest you to show the command text out and check whether there is a error in the insert query string.

     

    MessageBox.Show(cmd.commandtext.tostring())

     

    Best regards,

    Ling Wang


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Donnerstag, 11. November 2010 07:38
    Moderator