I want to update my VB.NET project to ms access database

Question

0.00/5 (No votes)

See more:

I have Textbox1, TextBox2, TextBox3 and RichTextBox1

Database name is : Database
Table Name is : SONGS
Column 1 (CODE)
Column 2 (SONG_ETITLE)
Column 3 (SONG_TTITLE)
Column 4 (SONG_SONGS1) in Ms access database

and i want reedit some information and update... But ms access database not updated what i was edited or re enter details...

What I have tried:

If TextBox1.Text <> "" Then
            SONGSBindingSource.EndEdit()
            SONGSTableAdapter.Update(DatabaseDataSet.SONGS)
            MsgBox("Records Updated", MsgBoxStyle.Information, "Updated")
            TextBox1.ReadOnly = True
            TextBox2.ReadOnly = True
            TextBox3.ReadOnly = True
            RichTextBox1.ReadOnly = True
        Else
            MsgBox("Name cannot be empty", MsgBoxStyle.Exclamation, "Error")
        End If

    End Sub

Posted 7-Jan-20 7:11am

Member 14621280

Updated 20-Jan-20 3:47am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2020-01-07T08:26:00

VB

command = "Update SONGS set [SONG_ETITLE]='" & TextBox2.Text & "',[SONG_TTITLE]='" & TextBox2.Text & "',[SONG_SONGS1]='" & RichTextBox1.Text & "' where [CODE]=" & TextBox1.Text & ""

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

RickZeeland · Answer 2 · 2020-01-07T07:49:00

Solution 1

Here is an example: Insert, Update, Delete & Search Values in MS Access 2003 with VB.NET 2005[^]

The example shown however is vulnerable to SQL injection: never concatenate SQL strings like that !
See example here how to use a safer method: asp.net - SQL Injection prevention with Microsoft Access and VB.NET - Stack Overflow[^]

I would also recommend placing your database in another directory than the Debug directory, maybe it is overwritten when you build your application.

Posted 7-Jan-20 7:49am

RickZeeland

Updated 7-Jan-20 9:25am

v2

Comments

Member 14621280 7-Jan-20 14:01pm

pro = Provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\Praise\Praise\bin\Debug\Database.accdb"
connString = pro
myConnection.ConnectionString = connString
myConnection.Open()
command = "Update SONGS set [SONG_ETITLE]='" & TextBox2.Text & "',[SONG_TTITLE]='" & TextBox2.Text & "',[SONG_SONGS1]='" & RichTextBox1.Text & "' where [CODE]=" & TextBox1.Text & ""
Dim cmd As OleDbCommand = New OleDbCommand(command, myConnection)
MsgBox("Update Success")
Try
cmd.ExecuteNonQuery()
cmd.Dispose()
myConnection.Close()
TextBox1.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
RichTextBox1.Enabled = False

Catch ex As Exception
MsgBox(ex.Message)

End Try
End Sub

I was re edit this but no use ... Please correct me

Patrice T 7-Jan-20 14:22pm

Use Improve question to update your question.
So that everyone can pay attention to this information.

Member 14621280 7-Jan-20 14:02pm

System.ArgumentException was unhandled
HResult=-2147024809
Message=Format of the initialization string does not conform to specification starting at index 0.
Source=System.Data
StackTrace:
at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.OleDb.OleDbConnectionString..ctor(String connectionString, Boolean validate)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at System.Data.OleDb.OleDbConnection.ConnectionString_Set(DbConnectionPoolKey key)
at System.Data.OleDb.OleDbConnection.set_ConnectionString(String value)
at Praise.SongsList.btnUpdate_Click(Object sender, EventArgs e) in F:\Praise\Praise\SongsList.vb:line 218
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.RunDialog(Form form)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at System.Windows.Forms.Form.ShowDialog()
at Praise.Main.SongDisplayToolStripMenuItem_Click(Object sender, EventArgs e) in F:\Praise\Praise\Main.vb:line 20
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(

RickZeeland 7-Jan-20 15:35pm

Your connection string seems to be wrong, are you sure the location of: F:\Praise\Praise\bin\Debug\Database.accdb
is correct ?

Member 14621280 8-Jan-20 4:53am

Yes

Robert Bettinelli · Answer 3 · 2020-01-20T03:48:00

Solution 3

-- On a small side note ...

The

If TextBox1.Text <> "" Then

Could be .. Jut to be a little better..

If String.IsNullOrEmpty(Textbox1.Text) Then 
   ...
Endif

Posted 20-Jan-20 3:48am

Robert Bettinelli

Comments

Richard Deeming 21-Jan-20 10:09am

You'll need a Not in there; otherwise, you've just inverted the meaning of the condition. :)