Insert to access database via vb.net 2003

[Mike.Kanet@gmail.com]

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.Ins ertCommand.Para meters(0).Value =
txtProfileLastN ame.Text
DataAdapter.Ins ertCommand.Para meters(1).Value =
txtProfileFirst Name.Text
.....
DataAdapter.Ins ertCommand.Exec uteNonQuery()

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]

Re: Insert to access database via vb.net 2003

On 26 Apr 2007 21:41:35 -0700, Mike.Kanet@gmai l.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
¤ 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.Ins ertCommand.Para meters(0).Value =
¤ txtProfileLastN ame.Text
¤ DataAdapter.Ins ertCommand.Para meters(1).Value =
¤ txtProfileFirst Name.Text
¤ ....
¤ DataAdapter.Ins ertCommand.Exec uteNonQuery()
¤
¤ 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.C ommandText?


Paul
~~~~
Microsoft MVP (Visual Basic)

[Mike.Kanet@gmail.com]

Re: Insert to access database via vb.net 2003

On Apr 27, 7:09 am, Paul Clement
<UseAdddressAtE ndofMess...@sws pectrum.comwrot e:

On 26 Apr 2007 21:41:35 -0700, Mike.Ka...@gmai l.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
¤ 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.Ins ertCommand.Para meters(0).Value =
¤ txtProfileLastN ame.Text
¤ DataAdapter.Ins ertCommand.Para meters(1).Value =
¤ txtProfileFirst Name.Text
¤ ....
¤ DataAdapter.Ins ertCommand.Exec uteNonQuery()
¤
¤ 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.C ommandText?
>
Paul
~~~~
Microsoft MVP (Visual Basic)

Ok, here is the full code, including the InsertCommand.C ommandText.
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.In sertCommand.Par ameters(0).Valu e =
strAddress1
daNewProfile.In sertCommand.Par ameters(1).Valu e =
strAddress2
daNewProfile.In sertCommand.Par ameters(2).Valu e =
strAddressNum
daNewProfile.In sertCommand.Par ameters(3).Valu e =
dtBirthdate
daNewProfile.In sertCommand.Par ameters(4).Valu e = strCity
daNewProfile.In sertCommand.Par ameters(5).Valu e =
strCountry
daNewProfile.In sertCommand.Par ameters(6).Valu e =
strCountryBirth
daNewProfile.In sertCommand.Par ameters(7).Valu e =
strCountryCitiz en
daNewProfile.In sertCommand.Par ameters(8).Valu e =
strCountryResid ent
daNewProfile.In sertCommand.Par ameters(9).Valu e =
strDeptName
daNewProfile.In sertCommand.Par ameters(10).Val ue = strEmail
daNewProfile.In sertCommand.Par ameters(11).Val ue =
dtEndDate
daNewProfile.In sertCommand.Par ameters(12).Val ue =
strFirstName
daNewProfile.In sertCommand.Par ameters(13).Val ue =
strFullTimeorPa rtTime
daNewProfile.In sertCommand.Par ameters(14).Val ue =
strGender
daNewProfile.In sertCommand.Par ameters(15).Val ue =
strJobTitle
daNewProfile.In sertCommand.Par ameters(16).Val ue =
strJVisitorCat
daNewProfile.In sertCommand.Par ameters(17).Val ue =
strLastName
daNewProfile.In sertCommand.Par ameters(18).Val ue =
strMaritalStatu s
daNewProfile.In sertCommand.Par ameters(19).Val ue =
strMiddleName
daNewProfile.In sertCommand.Par ameters(20).Val ue = strNotes
daNewProfile.In sertCommand.Par ameters(21).Val ue =
dtPassportExpDa te
daNewProfile.In sertCommand.Par ameters(22).Val ue = strPhone
daNewProfile.In sertCommand.Par ameters(23).Val ue =
strProfileStatu s
daNewProfile.In sertCommand.Par ameters(24).Val ue =
strProfileType
daNewProfile.In sertCommand.Par ameters(25).Val ue =
intSalary
daNewProfile.In sertCommand.Par ameters(26).Val ue =
strSevisID
daNewProfile.In sertCommand.Par ameters(27).Val ue =
dtStartDate
daNewProfile.In sertCommand.Par ameters(28).Val ue = strState
daNewProfile.In sertCommand.Par ameters(29).Val ue = strZip

daNewProfile.In sertCommand.Exe cuteNonQuery()
Catch ex As Exception
MsgBox(ex)
End Try

The InsertCommand.C ommandText is:

INSERT INTO Profile(Address , Address2, AddressNum, Birthdate, City,
Country, CountryOfBirth, CountryOfCitize nship, CountryOfPermRe s,
DeptName, Email, EndDate, FirstName, FullTimeOrPartT ime, Gender,
JobTitle, JVisitorCategor y, LastName, MaritalStatus, Middle, Notes,
PassportExp, Phone, ProfileStatus, ProfileType, Salary, [SEVIS-ID],
StartDate, State, Zip) VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?)

Hopefully that helps. Thanks again.

[Paul Clement]

Re: Insert to access database via vb.net 2003

On 28 Apr 2007 23:07:26 -0700, Mike.Kanet@gmai l.com wrote:



¤ Ok, here is the full code, including the InsertCommand.C ommandText.
¤ 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.In sertCommand.Par ameters(0).Valu e =
¤ strAddress1
¤ daNewProfile.In sertCommand.Par ameters(1).Valu e =
¤ strAddress2
¤ daNewProfile.In sertCommand.Par ameters(2).Valu e =
¤ strAddressNum
¤ daNewProfile.In sertCommand.Par ameters(3).Valu e =
¤ dtBirthdate
¤ daNewProfile.In sertCommand.Par ameters(4).Valu e = strCity
¤ daNewProfile.In sertCommand.Par ameters(5).Valu e =
¤ strCountry
¤ daNewProfile.In sertCommand.Par ameters(6).Valu e =
¤ strCountryBirth
¤ daNewProfile.In sertCommand.Par ameters(7).Valu e =
¤ strCountryCitiz en
¤ daNewProfile.In sertCommand.Par ameters(8).Valu e =
¤ strCountryResid ent
¤ daNewProfile.In sertCommand.Par ameters(9).Valu e =
¤ strDeptName
¤ daNewProfile.In sertCommand.Par ameters(10).Val ue = strEmail
¤ daNewProfile.In sertCommand.Par ameters(11).Val ue =
¤ dtEndDate
¤ daNewProfile.In sertCommand.Par ameters(12).Val ue =
¤ strFirstName
¤ daNewProfile.In sertCommand.Par ameters(13).Val ue =
¤ strFullTimeorPa rtTime
¤ daNewProfile.In sertCommand.Par ameters(14).Val ue =
¤ strGender
¤ daNewProfile.In sertCommand.Par ameters(15).Val ue =
¤ strJobTitle
¤ daNewProfile.In sertCommand.Par ameters(16).Val ue =
¤ strJVisitorCat
¤ daNewProfile.In sertCommand.Par ameters(17).Val ue =
¤ strLastName
¤ daNewProfile.In sertCommand.Par ameters(18).Val ue =
¤ strMaritalStatu s
¤ daNewProfile.In sertCommand.Par ameters(19).Val ue =
¤ strMiddleName
¤ daNewProfile.In sertCommand.Par ameters(20).Val ue = strNotes
¤ daNewProfile.In sertCommand.Par ameters(21).Val ue =
¤ dtPassportExpDa te
¤ daNewProfile.In sertCommand.Par ameters(22).Val ue = strPhone
¤ daNewProfile.In sertCommand.Par ameters(23).Val ue =
¤ strProfileStatu s
¤ daNewProfile.In sertCommand.Par ameters(24).Val ue =
¤ strProfileType
¤ daNewProfile.In sertCommand.Par ameters(25).Val ue =
¤ intSalary
¤ daNewProfile.In sertCommand.Par ameters(26).Val ue =
¤ strSevisID
¤ daNewProfile.In sertCommand.Par ameters(27).Val ue =
¤ dtStartDate
¤ daNewProfile.In sertCommand.Par ameters(28).Val ue = strState
¤ daNewProfile.In sertCommand.Par ameters(29).Val ue = strZip
¤
¤ daNewProfile.In sertCommand.Exe cuteNonQuery()
¤ Catch ex As Exception
¤ MsgBox(ex)
¤ End Try
¤
¤ The InsertCommand.C ommandText is:
¤
¤ INSERT INTO Profile(Address , Address2, AddressNum, Birthdate, City,
¤ Country, CountryOfBirth, CountryOfCitize nship, CountryOfPermRe s,
¤ DeptName, Email, EndDate, FirstName, FullTimeOrPartT ime, Gender,
¤ JobTitle, JVisitorCategor y, 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/lib...er(VS.80).aspx


Paul
~~~~
Microsoft MVP (Visual Basic)