Insert to access database via vb.net 2003

[Mike.Kanet]

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

 

[Paul Clement]

On 26 Apr 2007 21:41:35 -0700, (e-mail address removed) 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
¤ 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)

 

[Mike.Kanet]

On 26 Apr 2007 21:41:35 -0700, (e-mail address removed) 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
¤ 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)

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.

 

[Paul Clement]

On 28 Apr 2007 23:07:26 -0700, (e-mail address removed) wrote:



¤ 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.80).aspx


Paul
~~~~
Microsoft MVP (Visual Basic)