How to insert data into database in SQL

Question

0.00/5 (No votes)

See more:

I created a program using SQL to insert data in database. Program run smoothly when I open forms but when I add data into database it gives me the error as follows:

An attempt to attach an auto-named database for file C:\Users\IECUSER\Documents\Visual Studio 2012\Projects\Invoicing Software\Invoicing Software\Database3.md failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

What I have tried:

Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.IO
Public Class CustomerForm
    Dim cnoo As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\IECUSER\Documents\Visual Studio 2012\Projects\Invoicing Software\Invoicing Software\Database3.md;Integrated Security=True")
    Dim cmddd As New SqlCommand
    Dim dr As SqlDataAdapter
    Private Sub CustomerForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        TextBox7.Text = Label8.Text
    End Sub
    Private Sub TextBox3_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox3.TextChanged
        Label8.Text = Val(TextBox3.Text) + Val(TextBox7.Text)
    End Sub

    Private Sub TextBox6_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox6.TextChanged
        Label8.Text = Val(TextBox7.Text) - Val(TextBox6.Text)
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        If TextBox1.Text = "" Then
            MessageBox.Show("Enter Phone_No in TextBox")
        ElseIf TextBox2.Text = "" Then
            MessageBox.Show("Enter Customer_Name TextBox")
        ElseIf TextBox3.Text = "" Then
            MessageBox.Show("Enter Customer_Bus_Name TextBox")
        ElseIf TextBox4.Text = "" Then
            MessageBox.Show("Enter address TextBox")
        Else
            Dim cmded As New SqlCommand
            cmded.Connection = cnoo
            cnoo.Open()
            cmded.CommandText = "insert into CUTable (Customer_Name,Phone_No,Customer_Bus_Name,Address,Balance) values (@Customer_Name,@Phone_No,@Customer_Bus_Name,@Address,@Balance)"
            cmded.Parameters.AddWithValue("@Customer_Name", TextBox1.Text)
            cmded.Parameters.AddWithValue("@Phone_No", TextBox2.Text)
            cmded.Parameters.AddWithValue("@Customer_Bus_Name", TextBox4.Text)
            cmded.Parameters.AddWithValue("@address", TextBox5.Text)
            cmded.Parameters.AddWithValue("@Balance", Label8.Text)
            cmded.ExecuteNonQuery()
            cnoo.Close()
            TextBox2.Text = ""
            TextBox2.Text = ""
            TextBox2.Text = ""
            TextBox2.Text = ""

            MessageBox.Show("Record  SAVED!")
        End If
    End Sub
End Class

Posted 30-Jul-23 7:55am

NyikoB

Updated 30-Jul-23 8:49am

Add a Solution

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

Dave Kreskowiak · Accepted Answer · 2023-07-30T08:19:00

You're creating a connection at the class level, but never disposing it, so it's hanging open every time you open and close your form.

The same is true for the SqlCommand object you're creating up there too.

AWAYS create the connection object when you need it, as late as possible, and create your command object, set it up and execute it, then you MUST call Dispose on the command and connection objects when you're done with the command, disposing them as early as possible. This is what the "using" statement is for:

C#

using (SqlConnection conn = new SqlConnection(connection string)
{
    using (SqlCommand comm = new SqlCommand(statement)
    {
        // Finish setting up your command, like parameters
        // Execute the command
    }
}  // When execution gets to here, the using statements will call
   // Dispose on the object defined in them automatically.

OriginalGriff · Accepted Answer · 2023-07-30T08:49:00

Solution 2

In addition to what Dave has said, you also probably shouldn't be attaching a database at all - it's only there for development and is not available in the production version of SQL Server.

Instead, create the DB in SQL Server (via CREATE TABLE commands or SSMS) and let it handle it.

Posted 30-Jul-23 8:49am

OriginalGriff