Can't insert data with database connection from VB

Question

1.00/5 (1 vote)

See more:

I'm trying to make an application in vb language with an access database, but when the save button is pressed, the data I entered into the database is not saved, but I used the same codes in a project I used to deal with a lot, there was a problem there, I changed a setting not with codes and it was fixed, but I can't remember what I fixed.
Please help me

What I have tried:

VB

Public Class Form1
    Dim n, price, tel As Integer
    Dim isim, plaka, islem1, islem2, islem3, islem4, islem5, islem6, islem7 As String
    Dim MyDate As Date
    Dim baglanti As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=tamir.mdb")
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: Bu kod satırı 'TamirDataSet.islem' tablosuna veri yükler. Bunu gerektiği şekilde taşıyabilir, veya kaldırabilirsiniz.
        Me.IslemTableAdapter.Fill(Me.TamirDataSet.islem)
        listele()

    End Sub
    Private Sub temizle()
        TextBox1.Text = " "
        TextBox2.Text = " "
        TextBox3.Text = " "

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        price = 100
        n = n + 1
        tel = TextBox3.Text
        isim = TextBox1.Text
        plaka = TextBox2.Text
        islem1 = ComboBox1.SelectedItem
        islem2 = ComboBox2.SelectedItem
        islem3 = ComboBox3.SelectedItem
        islem4 = ComboBox4.SelectedItem
        islem5 = ComboBox5.SelectedItem
        islem6 = ComboBox6.SelectedItem
        islem7 = ComboBox7.SelectedItem
        MyDate = CDate(Now())


        baglanti.Open()
        Dim komut As New OleDbCommand("insert into islem(fis_no,müsteri_ismi,müsteri_plaka,islem1,islem2,islem3,islem4,islem5,islem6,islem7,t_fiyat,m_tel,saat)values(" & n & ",'" + isim + "','" + plaka + "','" + islem1 + "','" + islem2 + "','" + islem3 + "','" + islem4 + "','" + islem5 + "','" + islem6 + "','" + islem7 + "'," & price & "," & tel & "," & MyDate & ",)", baglanti)
        komut.ExecuteNonQueryAsync()
        baglanti.Close()
        MessageBox.Show("Kaydınız Eklendi", "Kayıt")
        temizle()
        tablo.Clear()
        listele()

    End Sub
    Dim tablo As New DataTable
    Public Sub listele()
        baglanti.Open()
        Dim adtr As New OleDbDataAdapter("select *from islem", baglanti)
        adtr.Fill(tablo)
        DataGridView1.DataSource = tablo
        baglanti.Close()
    End Sub
End Class

Posted 26-Mar-22 10:29am

mehmet tuğra ünal

Updated 28-Mar-22 8:42am

PIEBALDconsult

v3

Add a Solution

Comments

PIEBALDconsult 26-Mar-22 22:29pm

Well, always use a parameterized statement and many problems go away.

mehmet tuğra ünal 27-Mar-22 1:18am

I am asking as a beginner to the software, how can I use the parameter, can you make changes on the code and show it?

Richard Deeming 29-Mar-22 9:19am

Using komut As New OleDbCommand("insert into islem (fis_no, müsteri_ismi, müsteri_plaka, islem1, islem2, islem3, islem4, islem5, islem6, islem7, t_fiyat, m_tel, saat) values (@fis_no, @musteri_ismi, @musteri_plaka, @islem1, @islem2, @islem3, @islem4, @islem5, @islem6, @islem7, @t_fiyat, @m_tel, @saat)", baglanti)
    komut.Parameters.AddWithValue("@fis_no", n)
    komut.Parameters.AddWithValue("@musteri_ismi", isim)
    komut.Parameters.AddWithValue("@musteri_plaka", plaka)
    komut.Parameters.AddWithValue("@islem1", islem1)
    komut.Parameters.AddWithValue("@islem2", islem2)
    komut.Parameters.AddWithValue("@islem3", islem3)
    komut.Parameters.AddWithValue("@islem4", islem4)
    komut.Parameters.AddWithValue("@islem5", islem5)
    komut.Parameters.AddWithValue("@islem6", islem6
    komut.Parameters.AddWithValue("@islem7", islem7)
    komut.Parameters.AddWithValue("@t_fiyat", price)
    komut.Parameters.AddWithValue("@m_tel", tel)
    komut.Parameters.AddWithValue("@saat", MyDate)
    
    Dim rowCount As Integer = komut.ExecuteNonQuery()
    If rowCount = 1 Then
        MessageBox.Show("Kaydınız Eklendi", "Kayıt")
    Else
        MessageBox.Show("Kayıt ekleme hatası", "Kayıt")
    End If
End Using

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

4 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)

KarstenK · Answer 1 · 2022-03-26T10:50:00

Solution 1

You must check the error code. The most common case is, that the data connection isnt established. Reason like missing rights, or some typos in data connection string are highly likely. Some other reason is missing or incompatible drivers.

Posted 26-Mar-22 10:50am

KarstenK

Comments

mehmet tuğra ünal 26-Mar-22 17:31pm

I'm sorry, but I checked in detail, I added the code I used to the problem, I looked very detailed, but I couldn't find any errors in the code.

KarstenK 27-Mar-22 13:56pm

First thing I see is that the path to the database is a relative path. Use a full path and than try it again with the debugger. What is your first error code?

mehmet tuğra ünal 28-Mar-22 10:46am

I don't have an error code right now I'm having another problem with the database I used a data base file in an application I made before, I created a new database in the application I'm currently using. The problem is, for example, there are 5 data in the database, when I open and close the application and open it again, when I press the save button, it updates the data number 1 from the 5 data.

Richard MacCutchan · Answer 2 · 2022-03-26T22:49:00

Yet another example of bad code.

VB

komut.ExecuteNonQueryAsync()
baglanti.Close()
MessageBox.Show("Kaydınız Eklendi", "Kayıt")

You ignore the return value from ExecuteNonQueryAsync, but you still tell the user that their record has been successfully added. How do you know it was successful? And you really should not be using the Async version of ExecuteNonQuery unless you fully understand thread programming.

RobertSF · Answer 3 · 2022-03-27T12:55:00

Solution 4

This is the difficult way of doing things. You should not use OleDBCommand. You should use the Data Designer, the Form Designer, and BindingSource. This makes things much easier.

Bind your form fields to your database fields via the binding source (working in the Designer), and then, when the user clicks the Ok button, you just have to do something like this:

C#

clientsBindingSource.EndEdit();
clientsTableAdapter.Update(timeTrackDataSet.Clients);

timeTrackDataSet is my DataSet, of course.
Clients is my DataTable.
clientsBindingSource connects my form to my database.

Posted 27-Mar-22 12:55pm

RobertSF

Comments

mehmet tuğra ünal 28-Mar-22 10:49am

I understand a bit, but is there a video or something where I can see this without any problems?

RobertSF 28-Mar-22 13:48pm

Unfortunately, Microsoft used to have some great videos by Beth Massi, but the links are now dead. However, you can find Beth Massi on YouTube. She has worked for Microsoft for a long time and is a great teacher. Also check out Microsoft's Developer website. They have free courses on VB using database. Are you Turkish? Microsoft has a Turkish version https://developer.microsoft.com/tr-TR/

I know it seems very confusing at first, but it will soon seem easy. Hang in there! :)

RobertSF · Answer 4 · 2022-03-28T08:42:00

I've created a basic form application in VB with an Access database, using Visual Studio 2017, that I hope you find helpful. You can download it here: Filebin | ui1471qouqh7lit7[^]

Inside the zip, there is a folder WindowsApp1. Put this folder in your ..\source\repos folder. There is also an Access database. You can put it anywhere, but you will have to change the connection string in these four places. Do it with a text editor, outside of Visual Studio.

..\source\repos\WindowsApp1\WindowsApp1\App.config
Line 7: connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\example.accdb"

..\source\repos\WindowsApp1\WindowsApp1\My Project\Settings.Designer.vb
Line 60: Global.System.Configuration.DefaultSettingValueAttribute("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\example.accdb")> _

..\source\repos\WindowsApp1\WindowsApp1\My Project\Settings.settings
Line 8: <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\example.accdb</ConnectionString>
Line 11: <value profile="(Default)">Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\example.accdb

The application has a form bound to a DataSet via a BindingSource, like I mentioned in a comment. You have to write very little code.

Can't insert data with database connection from VB

4 solutions

Solution 1

Solution 2

Solution 4

Solution 5

Add your solution here

Preview 0