 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / VB.NET / April 2007Insert to access database via vb.net 2003
I have been trying for the last couple of weeks to insert a new record to a database. I have been able to update the existing records but when I try to insert it does nothing, not even give me an error code. Below is a sample of what I've tried lately: Try DataAdapter.InsertCommand.Parameters(0).Value = txtProfileLastName.Text DataAdapter.InsertCommand.Parameters(1).Value = txtProfileFirstName.Text .... DataAdapter.InsertCommand.ExecuteNonQuery() Catch ex As Exception MsgBox(ex) End Try Some other facts that may help. I used the wizard to add the dataadapters, datasets, and connection. The primary key for the table in the database is an autonumber if that makes a difference. I am not passing any values for it. Any help would be great. I have tried everything I have found in these forums and others and none have worked. Thanks in advance. Mike ¤ I have been trying for the last couple of weeks to insert a new record ¤ to a database. I have been able to update the existing records but ¤ when I try to insert it does nothing, not even give me an error code. ¤ Below is a sample of what I've tried lately: ¤ ¤ Try ¤ DataAdapter.InsertCommand.Parameters(0).Value = ¤ txtProfileLastName.Text ¤ DataAdapter.InsertCommand.Parameters(1).Value = ¤ txtProfileFirstName.Text ¤ .... ¤ DataAdapter.InsertCommand.ExecuteNonQuery() ¤ ¤ Catch ex As Exception ¤ MsgBox(ex) ¤ End Try ¤ ¤ ¤ Some other facts that may help. I used the wizard to add the ¤ dataadapters, datasets, and connection. The primary key for the table ¤ in the database is an autonumber if that makes a difference. I am not ¤ passing any values for it. Any help would be great. I have tried ¤ everything I have found in these forums and others and none have ¤ worked. Thanks in advance. What is the value of InsertCommand.CommandText? Paul ~~~~ Microsoft MVP (Visual Basic) On Apr 27, 7:09 am, Paul Clement <UseAdddressAtEndofMess...@swspectrum.com> wrote: > ¤ I have been trying for the last couple of weeks to insert a new record > ¤ to a database. I have been able to update the existing records but [quoted text clipped - 26 lines] > ~~~~ > Microsoft MVP (Visual Basic) Ok, here is the full code, including the InsertCommand.CommandText. It's really a lot more complicated than I originally stated. I tried to simplify it. The strings were set to values from textboxes in a separate function. Try daNewProfile.InsertCommand.Parameters(0).Value = strAddress1 daNewProfile.InsertCommand.Parameters(1).Value = strAddress2 daNewProfile.InsertCommand.Parameters(2).Value = strAddressNum daNewProfile.InsertCommand.Parameters(3).Value = dtBirthdate daNewProfile.InsertCommand.Parameters(4).Value = strCity daNewProfile.InsertCommand.Parameters(5).Value = strCountry daNewProfile.InsertCommand.Parameters(6).Value = strCountryBirth daNewProfile.InsertCommand.Parameters(7).Value = strCountryCitizen daNewProfile.InsertCommand.Parameters(8).Value = strCountryResident daNewProfile.InsertCommand.Parameters(9).Value = strDeptName daNewProfile.InsertCommand.Parameters(10).Value = strEmail daNewProfile.InsertCommand.Parameters(11).Value = dtEndDate daNewProfile.InsertCommand.Parameters(12).Value = strFirstName daNewProfile.InsertCommand.Parameters(13).Value = strFullTimeorPartTime daNewProfile.InsertCommand.Parameters(14).Value = strGender daNewProfile.InsertCommand.Parameters(15).Value = strJobTitle daNewProfile.InsertCommand.Parameters(16).Value = strJVisitorCat daNewProfile.InsertCommand.Parameters(17).Value = strLastName daNewProfile.InsertCommand.Parameters(18).Value = strMaritalStatus daNewProfile.InsertCommand.Parameters(19).Value = strMiddleName daNewProfile.InsertCommand.Parameters(20).Value = strNotes daNewProfile.InsertCommand.Parameters(21).Value = dtPassportExpDate daNewProfile.InsertCommand.Parameters(22).Value = strPhone daNewProfile.InsertCommand.Parameters(23).Value = strProfileStatus daNewProfile.InsertCommand.Parameters(24).Value = strProfileType daNewProfile.InsertCommand.Parameters(25).Value = intSalary daNewProfile.InsertCommand.Parameters(26).Value = strSevisID daNewProfile.InsertCommand.Parameters(27).Value = dtStartDate daNewProfile.InsertCommand.Parameters(28).Value = strState daNewProfile.InsertCommand.Parameters(29).Value = strZip daNewProfile.InsertCommand.ExecuteNonQuery() Catch ex As Exception MsgBox(ex) End Try The InsertCommand.CommandText is: INSERT INTO Profile(Address, Address2, AddressNum, Birthdate, City, Country, CountryOfBirth, CountryOfCitizenship, CountryOfPermRes, DeptName, Email, EndDate, FirstName, FullTimeOrPartTime, Gender, JobTitle, JVisitorCategory, LastName, MaritalStatus, Middle, Notes, PassportExp, Phone, ProfileStatus, ProfileType, Salary, [SEVIS-ID], StartDate, State, Zip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hopefully that helps. Thanks again. ¤ Ok, here is the full code, including the InsertCommand.CommandText. ¤ It's really a lot more complicated than I originally stated. I tried ¤ to simplify it. The strings were set to values from textboxes in a ¤ separate function. ¤ ¤ Try ¤ daNewProfile.InsertCommand.Parameters(0).Value = ¤ strAddress1 ¤ daNewProfile.InsertCommand.Parameters(1).Value = ¤ strAddress2 ¤ daNewProfile.InsertCommand.Parameters(2).Value = ¤ strAddressNum ¤ daNewProfile.InsertCommand.Parameters(3).Value = ¤ dtBirthdate ¤ daNewProfile.InsertCommand.Parameters(4).Value = strCity ¤ daNewProfile.InsertCommand.Parameters(5).Value = ¤ strCountry ¤ daNewProfile.InsertCommand.Parameters(6).Value = ¤ strCountryBirth ¤ daNewProfile.InsertCommand.Parameters(7).Value = ¤ strCountryCitizen ¤ daNewProfile.InsertCommand.Parameters(8).Value = ¤ strCountryResident ¤ daNewProfile.InsertCommand.Parameters(9).Value = ¤ strDeptName ¤ daNewProfile.InsertCommand.Parameters(10).Value = strEmail ¤ daNewProfile.InsertCommand.Parameters(11).Value = ¤ dtEndDate ¤ daNewProfile.InsertCommand.Parameters(12).Value = ¤ strFirstName ¤ daNewProfile.InsertCommand.Parameters(13).Value = ¤ strFullTimeorPartTime ¤ daNewProfile.InsertCommand.Parameters(14).Value = ¤ strGender ¤ daNewProfile.InsertCommand.Parameters(15).Value = ¤ strJobTitle ¤ daNewProfile.InsertCommand.Parameters(16).Value = ¤ strJVisitorCat ¤ daNewProfile.InsertCommand.Parameters(17).Value = ¤ strLastName ¤ daNewProfile.InsertCommand.Parameters(18).Value = ¤ strMaritalStatus ¤ daNewProfile.InsertCommand.Parameters(19).Value = ¤ strMiddleName ¤ daNewProfile.InsertCommand.Parameters(20).Value = strNotes ¤ daNewProfile.InsertCommand.Parameters(21).Value = ¤ dtPassportExpDate ¤ daNewProfile.InsertCommand.Parameters(22).Value = strPhone ¤ daNewProfile.InsertCommand.Parameters(23).Value = ¤ strProfileStatus ¤ daNewProfile.InsertCommand.Parameters(24).Value = ¤ strProfileType ¤ daNewProfile.InsertCommand.Parameters(25).Value = ¤ intSalary ¤ daNewProfile.InsertCommand.Parameters(26).Value = ¤ strSevisID ¤ daNewProfile.InsertCommand.Parameters(27).Value = ¤ dtStartDate ¤ daNewProfile.InsertCommand.Parameters(28).Value = strState ¤ daNewProfile.InsertCommand.Parameters(29).Value = strZip ¤ ¤ daNewProfile.InsertCommand.ExecuteNonQuery() ¤ Catch ex As Exception ¤ MsgBox(ex) ¤ End Try ¤ ¤ The InsertCommand.CommandText is: ¤ ¤ INSERT INTO Profile(Address, Address2, AddressNum, Birthdate, City, ¤ Country, CountryOfBirth, CountryOfCitizenship, CountryOfPermRes, ¤ DeptName, Email, EndDate, FirstName, FullTimeOrPartTime, Gender, ¤ JobTitle, JVisitorCategory, LastName, MaritalStatus, Middle, Notes, ¤ PassportExp, Phone, ProfileStatus, ProfileType, Salary, [SEVIS-ID], ¤ StartDate, State, Zip) VALUES ¤ (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ¤ ¤ Hopefully that helps. Thanks again. I'm can't really be sure from your code that you've actually added any Parameter objects to the Parameters collection. See if the following helps: http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter(VS.8 0).aspx Paul ~~~~ Microsoft MVP (Visual Basic) Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.