ASP.NET - VB.NET input into MS-ACCESS with autoincrement

Question

I have an ms-access database called db.mdb and it contains various table. I;m creating a Account Creation page with ASP.NET and VB.NET.

I'm trying to input fields within the page into ms-acess db but when I open the db there are no values entered despite web develop not showing any errors.

Can you kindly help me?

This is the code:

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Odbc



Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub TextBox1_TextChanged(sender As Object, e As System.EventArgs) Handles txtName.TextChanged

    End Sub

    Protected Sub btnCreateAccount_Click(sender As Object, e As System.EventArgs) Handles btnCreateAccount.Click

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Brian\Documents\Visual Studio 2010\WebSites\WebSite3\db.mdb;User Id=admin;Password=;")

        Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO [USER] (UserName, UserSurname, Address, Country, TelNo, UserLogin, UserPassword, UserTypeID) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", conn)




        If txtPass.Text = txtCPass.Text Then

            conn.Open()


            cmd.Parameters.Add("@UserName", OleDbType.VarChar, 255).Value = txtName.Text
            cmd.Parameters.Add("@UserSurname", OleDbType.VarChar, 255).Value = txtSurname.Text
            cmd.Parameters.Add("@Address", OleDbType.VarChar, 255).Value = txtUsername.Text
            cmd.Parameters.Add("@Country", OleDbType.VarChar, 255).Value = txtCountry.Text
            cmd.Parameters.Add("@TelNo", OleDbType.Integer).Value = txtTelNo.Text
            cmd.Parameters.Add("@UserLogin", OleDbType.VarChar, 255).Value = txtUsername.Text
            cmd.Parameters.Add("@UserPassword", OleDbType.VarChar, 255).Value = txtPass.Text
            cmd.Parameters.Add("@UserTypeID", OleDbType.VarChar, 255).Value = "U"





            cmd.ExecuteNonQuery()

            conn.Close()
            lblAccount.Visible = True



        End If

    End Sub
End Class

EDIT:

So I'm trying to autonumber the UserID field. It's set as auto number from the access database. Whenever I try to input the details into the page from the page, it won't allow me, giving me an error that the AutoNumber field cannot be NULL. So I added the piece of code :

newID = Int32.Parse(cmd2.ExecuteScalar()) + 1

So that I get the maximum number from that column in the table and increment it by 1 and then added with the other command.

cmd.Parameters.Add("@UserID", OleDbType.VarChar, 255).Value = newID

However its giving me an error saying

Conversion from type 'DBNull' to type 'String' is not valid.

Min Min · Accepted Answer · 2012-03-24T17:59:24.087

0

You haven't open the connection before calling cmd2.ExecuteScalar() function. And first parameter is wrong. Should be @UserID instead of @Address.

conn.Open()
newID = Int32.Parse(cmd2.ExecuteScalar()) + 1
'Not sure what is column type (Varchar or Int)
cmd.Parameters.Add("@UserID", OleDbType.Integer, 2).Value = newID
...
cmd.ExecuteNonQuery()
conn.Close()

edited Mar 24 '12 at 17:59

answered Mar 24 '12 at 17:53

Min Min

6,188
2
19
17

ahh ok thanks. but now its giving me a new error in this line `Dim cmd2 As OleDbCommand = New OleDbCommand("SELECT MAX(UserID) FROM USER", conn)` saying theres an error in the 'FROM' – Brian Mar 24 '12 at 18:00
Try - SELECT MAX(UserID) FROM [USER] – Min Min Mar 24 '12 at 18:01
Thanks.. every problem seems to be leading to another though. I updated the question with the new error – Brian Mar 24 '12 at 18:09
If UserID is autonumber in Column, should not be putting in Insert Statement. Access will set it to the next value by itself. – Min Min Mar 24 '12 at 18:17
Ok so I removed that code and its giving me the usual error (Data type mismatch in criteria expression.) at the line cmd.ExecuteNonQuery() – Brian Mar 24 '12 at 18:22
Did you removed BOTH in Insert Statement (INSERT INTO [USER] .. and Parameter? – Min Min Mar 24 '12 at 18:24
Is it all your Access table columns are varchar datatype? – Min Min Mar 24 '12 at 18:30
No, the UserID is Autonumber, TelNo and UserTypeID are NUmbers. Changed the original code because I forgot to change them and I still get the same error – Brian Mar 24 '12 at 18:34
Ohh sorry, the others are Text (which is VarChar as I presume) – Brian Mar 24 '12 at 18:35
Failed to convert parameter value from a String to a Int32. this is the new error sorry – Brian Mar 24 '12 at 18:38
You still missing to convert for TelNo at parameter. Convert.ToInt32(txtTelNo.Text) – Min Min Mar 24 '12 at 18:39
Thanks I fixed it.. there was en error in the relationships in the db! – Brian Mar 24 '12 at 18:42

ASP.NET - VB.NET input into MS-ACCESS with autoincrement

1 Answers1