Hello,

I'm very new to using VB.Net so I found it quite difficult to understand other answers that I searched for.

Basically, I have an project to develop where a user can create an account, and log in with it.

So far I have :

Created a database in ms access with the relevant fields (2003 edition)
Created a VB form with the correct text boxes etc. and linked the text boxes to the database.

What I need :

I would like to be able to code it so that when the form is opened, a new blank record in the database is automatically generated.
When a button is pressed, information in the text boxes will be updated into the new record.

Any help would be appreciated. (I'm very new to VB so the simplest ways would be the best)

Thanks

I think that I might be close, this is what I've got so far :

Imports System.Data.OleDb


Public Class frmCreateAccount

Dim Mycn As OleDbConnection 
    Dim Command As OleDbCommand
    Dim icount As Integer
    Dim SQLstr As String

    Private Sub btnCancel_Click(sender As System.Object, e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
        frmLogin.Show()
    End Sub

    Private Sub frmCreateAccount_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DatabaseDataSet.Users' table. You can move, or remove it, as needed.
        Me.UsersTableAdapter.Fill(Me.DatabaseDataSet.Users)
        txtForename.Text = ""
        txtSurname.Text = ""
        txtUsername.Text = ""
        txtPassword.Text = ""
        txtRepPassword.Text = ""
        txtDob.Text = ""
        txtAddress.Text = ""
        txtLicNo.Text = ""
    End Sub



    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

        Try
            Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Matt Wagner\Downloads\Work\New\Login\Database.mdb;")
            Mycn.Open()

            SQLstr = "INSERT INTO Users (Username, Password) VALUES (txtUsername.Text, txtPassword.text)"

            Command = New OleDbCommand(SQLstr, Mycn)
            icount = Command.ExecuteNonQuery
            MessageBox.Show(icount)

        Catch ex As Exception
            MsgBox("ERROR")
            Mycn.Close()
        End Try
    End Sub

    Private Function cmd() As Object
        Throw New NotImplementedException
    End Function

End Class

However I'm getting the message box that says "ERROR" can anyone point out what I'm doing wrong?

Thanks

I think you are close too.

SQLstr = "INSERT INTO Users (Username, Password) VALUES (" & txtUsername.Text & ", " & txtPassword.text & ")"

Also, try to get the exception to appear in your message box - not just the word error.

Edited 4 Years Ago by KenSquare: Missed the comma

I replaced the error box message with

MessageBox.Show(ex.Message & " - " & ex.Source)

Now the error message says

Syntax error in INSERT INTO statement. - Microsoft JET Database Engine

Any ideas

Thanks

Try

SQLstr = "INSERT INTO Users (Username,Password) VALUES '" & txtUsername.text & "','" & txtPassword.Text & "'"

Edited 4 Years Ago by Begginnerdev: n/a

I'm still getting the same Syntax error in INSERT INTO statement.

I've chesked the obvious like the database location and field names etc.

Anywhere else I might be going wrong?

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

        Try
            Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents\Computing\Work\New\Login\Database.mdb;")
            Mycn.Open()

            SQLstr = "INSERT INTO Users (Username,Password) VALUES '" & txtUsername.Text & "','" & txtPassword.Text & "'"

            Command = New OleDbCommand(SQLstr, Mycn)
            icount = Command.ExecuteNonQuery
            MessageBox.Show(icount)

        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
            Mycn.Close()
        End Try
    End Sub

Thanks

I think it's the missing parrenthesis in values. Change your SQL string to :

SQLstr = "INSERT INTO Users (Username,Password) VALUES ('" & txtUsername.Text & "','" & txtPassword.Text & "')"

I've added the parrenthesis and i'm still getting the same error, However i noticed that in the Immediate Window that it says "A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll"

Any More Ideas?

Thanks

Wrap the code in a try/catch block. Then use msgbox(ex.message) in the catch.

Haven't I already done that? If not how do I?

Thanks

Sorry, my fault. I had forgotten you did so.

Have you checked to be sure you have permissions to access the database?

Yes, the database isn't password protected and I can view the current content of a database through a datagrid, so I dont think that permisions are the problem?

The errors are showing as the SQLString, I've made sure that the table names are correct and other obvious things, i'm really at a dead end.

Any help would be appriciated.
Thanks

Just for the sake of redundency, have you tried:

SQLstr = "INSERT INTO users (username,password) VALUES ('" & txtUserName.text & "','" & txtPassword.text & "'"

Maybe it has something to do with case sensitivity?

If not, have you checked to make sure that the names are correct? (I know it's stupid, but it helps eliminate problems")

I'm not sure about this, but could it be that either users,username or password is reserved word for Access and that is giving you errors? try brackets around this words and see if it helps.

You also have to be careful if there is no value entered VB form side then you dont update this field

What does that mean..well as you create your insert SQL you must craft this by hand...ie if there is value in Forename then ok I need to add that to Insert.... and build up sql

ok your new so switch to access and goto table view open the table, now add a record...understand what it takes to add a record ie do all fields have to be entered? what minimium values must have...also note if you have autonumber it adds itself in...

You should also be able to find complete samples of what your doing on the web...if you cant message me I will point you to right place

Okay, I completley started again from scratch but i'm still having the same problems

Syntax Error in INSERT INTO STATEMENT

I rewrote my code so that it is different to before.

Public Class frmCreateAccount

    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim inc As Integer
    Dim MaxRows As Integer

    Private Sub btnCancel_Click(sender As System.Object, e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
        frmLogin.Show()
    End Sub

    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

        txtUsername.Clear()
        txtPassword.Clear()
        txtForename.Clear()
        txtSurname.Clear()
        txtRepPassword.Clear()
        txtDob.Clear()
        txtAddress.Clear()
        txtLicNo.Clear()

        'If inc <> -1 Then
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("Database").NewRow()
        dsNewRow.Item("Username") = txtUsername.Text
        dsNewRow.Item("Password") = txtPassword.Text
        dsNewRow.Item("First Name") = txtForename.Text
        dsNewRow.Item("Second Name") = txtSurname.Text
        dsNewRow.Item("DOB") = txtDob.Text
        dsNewRow.Item("Address") = txtAddress.Text
        dsNewRow.Item("Licence Number") = txtLicNo.Text
        ds.Tables("Database").Rows.Add(dsNewRow)
        da.Update(ds, "Database")
        MsgBox("User Created")
        'End If

    End Sub

    Private Sub frmCreateAccount_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = H:\MyWork\LOGIN SYSTEM\New\Login\Database.mdb"
        con.ConnectionString = dbProvider & dbSource
        con.Open()

        sql = "SELECT * FROM Users"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Database")
        MaxRows = ds.Tables("Database").Rows.Count
        inc = -1

        con.Close()

    End Sub
End Class

But VB is showing an error in the line

da.Update(ds, "Database")

Is there something I might have not put in?

Any help appriciated

Thanks

I see that you are clearing your text boxes then asigning them to the ds. Are any of your fields set to be not null? If so, then you may be having a problem there.

I just noticd that the textboxes were being cleared, so I corrected that, But i'm still having the same problem...

I use a datatable method, which is close to what you are doing.

Here is what I do:

'Where table name is the table in the db.
 Dim dt As DataTable = ds.Tables("tablename")

 Dim newRow As DataRow
'Setting up the table for the data
 newRow = dt.NewRow()
'You should replace "column1" and "column2" with the column name in your db.
 newRow("column1") = TextBox1.Text
 newRow("column2") = TextBox2.Text
 dt.Rows.Add(newRow)

'Updating db
da.Update(ds, "tablename")

See if this helps.

Edited 4 Years Ago by Begginnerdev: n/a

I have also had issues in the past when using the wildcard, see if you can directly reference the names to the columns. I have had the wildcard jumble everything together with no order. It seems to do it mostly when there are empty columns in the tables, so it pushes the rest of the data together.

I use a datatable method, which is close to what you are doing.

Here is what I do:

'Where table name is the table in the db.
 Dim dt As DataTable = ds.Tables("tablename")

 Dim newRow As DataRow
'Setting up the table for the data
 newRow = dt.NewRow()
'You should replace "column1" and "column2" with the column name in your db.
 newRow("column1") = TextBox1.Text
 newRow("column2") = TextBox2.Text
 dt.Rows.Add(newRow)

'Updating db
da.Update(ds, "tablename")

See if this helps.

I tried that and didn't get the usual error but instead got.

NullReferenceException was unhandled
Object reference not set to an instance of an object.

Am I closer?

EDIT: In the line :

"newRow = dt.NewRow()"

Edited 4 Years Ago by mattwagner: Missed line out

Sorry forgot to add:

Dim newRow As DataRow

My appologies friend.

I've added that now and have even another error. This time it is:

InvalidOperationException was unhandled
Update requires a valid InsertCommand when passed DataRow collection with new rows.

which shows up in the line

da.Update(ds, "Users")

Any more help is greatly appriciated.
Thank you for helping me so far :)

dim sqlString As String = "INSERT INTO Users (Username,Password,First Name,Second Name,DOB,Address,License Number) VALUES ('" & txtUsername.text & "','" & txtPassword.text & "','" & txtForename.Text & "','" txtSurname.Text & "','" & txtDob.text & "','" & txtAddress.Text & "','" & txtLicNo.Text & "')"


da.UpdateCommand = New OleDbCommand(sqlString, connection)

You might want to check the sql string for errors, just typed it into the text widow.

Edited 4 Years Ago by Begginnerdev: n/a

Okay, where abouts should it go in the code, and

da.UpdateCommand = New OleDbCommand(sqlString, connection)

It says connection is not declared, should I just declare it as a string?

Connection will be the name of the your connection.

For you connection would be "con"

so

da.UpdateCommand = New OleDBCommand(sqlString, con)

You will be able to place the code anywhere after the declaration of the data adapter and string, but before the da.Update is called.

Edited 4 Years Ago by Begginnerdev: n/a

mattwagner, could you go to your MS Access table that you are trying to update/Insert to and copy/paste the Column names here? I am curious from reading your thread and the code samples, that you have column names with spaces in which case you will get the errors you are receiving. If your sql's column names are not in [Column One] format it will not work.

Ken

Sorry for the late reply.

My Coulumn names had spaces but i have corrected them and my code so that the collum names are:

ID / Username / Password / FirstName / SecondName / DOB / Address / LicenceNumber

and my code is

Imports System.Data.OleDb

Public Class frmCreateAccount

    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim inc As Integer
    Dim MaxRows As Integer

    Private Sub frmCreateAccount_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = H:\MyWork\LOGIN SYSTEM\New\Login\Database.mdb"
        con.ConnectionString = dbProvider & dbSource
        con.Open()

        sql = "SELECT * FROM Users"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Users")
        MaxRows = ds.Tables("Users").Rows.Count
        inc = -1

        con.Close()

    End Sub

    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click


        Dim dt As DataTable = ds.Tables("Users")

        Dim newRow As DataRow

        newRow = dt.NewRow()

        newRow("ID") = txtid.Text
        newRow("Username") = txtUsername.Text
        newRow("Password") = txtPassword.Text
        newRow("FirstName") = txtForename.Text
        newRow("SecondName") = txtSurname.Text
        newRow("DOB") = txtDob.Text
        newRow("Address") = txtAddress.Text
        newRow("LicenceNumber") = txtLicNo.Text
        dt.Rows.Add(newRow)

        Dim sqlString As String = "INSERT INTO Users (Username,Password,FirstName,SecondName,DOB,Address,LicenseNumber) VALUES ('" & txtUsername.Text & "','" & txtPassword.Text & "','" & txtForename.Text & "','" & txtSurname.Text & "','" & txtDob.Text & "','" & txtAddress.Text & "','" & txtLicNo.Text & "')"
        da.UpdateCommand = New OleDbCommand(sqlString, con)


        da.Update(ds, "Users")
    End Sub
End Class

And the error i'm getting at the moment is

InvalidOperationException was unhandled

Update requires a valid InsertCommand when passed DataRow collection with new rows.

Any help appriciated.

First I would us a Global DataTable/DataSet, OleDbDataAdapter and BindingSource. Fill your table and bind it to your DataGridView through the BindingSource. When you fill your DataSet Add the CommandBuilder to the OleDbDataAdapter. Add or Change current values in your datagridview/DataTable as you see fit then call the OleDbDataAdapter Update method.

Here is a simple Example. It reads all the records from the .mdb table displays them in a datagridview, which is bound to a the filled DataTable by a BindingSource. When Ever a change is made to it or a row is added it will be reflected back to the .mdb table. I added a Sub that adds a row by code. An Image of the .mdb table is attached

Let me know if you need any more help

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
  Dim wrkDir As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location())
  Dim da As New OleDbDataAdapter
  Dim ds As New DataSet
  Dim bs As New BindingSource
  Dim edit As Boolean
  'Coupon_Tracker.mdb
  Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                  "Data Source=" & wrkDir & "\Coupon_Tracker.mdb")


  ''' <summary>
  ''' Update The DataBase
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    If edit Then
      da.Update(ds, "EXTENSIONS")
      edit = False
    End If

  End Sub

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    'Set the dataGridView's DataSource
    dgv1.DataSource = bs
  End Sub

  ''' <summary>
  ''' Adds a row by code
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub tsBtnLoad_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) Handles tsBtnLoad.Click

    ds.Tables("EXTENSIONS").Rows.Add(".TEST", "Dani_Web_Example", "NOTHING OPENS THIS FILE")


  End Sub

  Private Sub dgv1_CellEndEdit(ByVal sender As Object, _
                               ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
                               Handles dgv1.CellEndEdit
    edit = True
  End Sub

  ''' <summary>
  ''' Loads all the records to the DataGridView
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub btnLoadAll_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) Handles btnLoadAll.Click
    ds.Tables.Clear()

    Dim sql As String = "SELECT * FROM(Extensions);"

    Dim cmd As New OleDbCommand(sql, conn)

    da.SelectCommand = cmd
    Dim cmdBuilder As New OleDbCommandBuilder(da)
    da.Fill(ds, "EXTENSIONS")
    bs.DataSource = ds.Tables(0)

  End Sub

  ''' <summary>
  ''' Sets edit to true when user deletes a row or rows
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub dgv1_RowsRemoved(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowsRemovedEventArgs) Handles dgv1.RowsRemoved
    edit = True
  End Sub
End Class

Edited 4 Years Ago by Phasma: Added Images

Attachments ExtensionsTable.jpg 80.7 KB Form1.jpg 54.36 KB
This article has been dead for over six months. Start a new discussion instead.