Problem Insert into SQL DataBase from VB.NET

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 2292 Views - Last Post: 15 January 2013 - 01:26 AM Rate Topic: -----

#1 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 01:31 AM

I try to insert data from VB.NET form to SQL Database Table. When i press insert Button. It shows Successfully Added. But there is no data in database table. My code is given below.
Private Sub BTNSAVE_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTNSAVE.Click
    CheckMyControls()
    If totflag = False Then
        MessageBox.Show("Give Complete Data!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        coloring()
    ElseIf totflag = True Then
        Dim gend As String
        gend = ""
        If RBMALE.Checked = True And RBFEMALE.Checked = False Then
            gend = RBMALE.Text
        ElseIf RBFEMALE.Checked = True And RBMALE.Checked = False Then
            gend = RBFEMALE.Text
        End If
        Try
            getConnect()
            Dim query As SqlCommand
            Dim strSQL As String
            strSQL = "INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_FNAME,EMP_GENDER,EMP_DOB,EMP_CAST,EMP_DEPART,EMP_DESIG,EMP_DOJ,EMP_SALARY,EMP_PF_ESI,EMP_BRANCH,EMP_CONTACT,EMP_ADDRESS)VALUES(@EMP_ID,@EMP_NAME,@EMP_FNAME,@EMP_GENDER,@EMP_DOB,@EMP_CAST,@EMP_DEPART,@EMP_DESIG,@EMP_DOJ,@EMP_SALARY,@EMP_PF_ESI,@EMP_BRANCH,@EMP_CONTACT,@EMP_ADDRESS)"
            query = New SqlCommand(strSQL, Conn)
            query.Parameters.AddWithValue("@EMP_ID", SqlDbType.Decimal).Value = TXTEMPID.Text
            query.Parameters.AddWithValue("@EMP_NAME", SqlDbType.NVarChar).Value = TXTNAME.Text
            query.Parameters.AddWithValue("@EMP_FNAME", SqlDbType.NVarChar).Value = TXTFNAME.Text
            query.Parameters.AddWithValue("@EMP_GENDER", SqlDbType.Char).Value = gend
            query.Parameters.AddWithValue("@EMP_DOB", SqlDbType.DateTime).Value = DTPEMPDOB.Value
            query.Parameters.AddWithValue("@EMP_CAST", SqlDbType.NVarChar).Value = TXTCASTE.Text
            query.Parameters.AddWithValue("@EMP_DEPART", SqlDbType.NVarChar).Value = CMBDEPT.Text
            query.Parameters.AddWithValue("@EMP_DESIG", SqlDbType.NVarChar).Value = CMBDESIG.Text
            query.Parameters.AddWithValue("@EMP_DOJ", SqlDbType.DateTime).Value = DTPEMPDOJ.Value
            query.Parameters.AddWithValue("@EMP_SALARY", SqlDbType.Money).Value = MTXTSAL.Text
            query.Parameters.AddWithValue("@EMP_PF_ESI", SqlDbType.Money).Value = MTXTPFESI.Text
            query.Parameters.AddWithValue("@EMP_BRANCH", SqlDbType.NVarChar).Value = TXTBRANCH.Text
            query.Parameters.AddWithValue("@EMP_CONTACT", SqlDbType.Decimal).Value = MTXTCONTACT.Text
            query.Parameters.AddWithValue("@EMP_ADDRESS", SqlDbType.Text).Value = RTXTADDRESS.Text
            Conn.Open()
            Dim numAffected = query.ExecuteNonQuery()
            Conn.Close()
            If numAffected > 0 Then
                MessageBox.Show("Successfully Added", "Add", MessageBoxButtons.OK, MessageBoxIcon.Information)
                BTNCLEAR.PerformClick()
            Else
                MsgBox("No record was inserted")
            End If
        Catch ex As Exception
            MsgBox("ERROR: " + ex.Message, MsgBoxStyle.Information, "Add")
        End Try
    End If
End Sub

Check this code and give me a solution. and attached Database table Screenshot

Is This A Good Question/Topic? 0
  • +

Replies To: Problem Insert into SQL DataBase from VB.NET

#2 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 01:35 AM

the exect format for insert statement is.. HERE EMPCON IS MY SQLCONNECTION AND EMPCMD IS MY SQLCOMMAND

 empcmd.CommandText = "insert into empdata(emp_code,emp_name,emp_sname,emp_fname,emp_dob,emp_doj) values (" & _
               txtempcode.Text & ",'" & txtempname.Text & "','" & txtempsname.Text & _
                "','" & txtempfname.Text & "','" & DateValue(txtempdob.Text).ToString("MM/dd/yyyy") & _
                "','" & DateValue(txtempdoj.Text).ToString("MM/dd/yyyy") & "')"

 empcon.Open()
        empcmd.Connection = empcon
        empcmd.ExecuteNonQuery()
        empcon.Close()



This post has been edited by kai_itz me: 14 January 2013 - 01:40 AM

Was This Post Helpful? 0
  • +
  • -

#3 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 01:39 AM

View Postkai_itz me, on 14 January 2013 - 01:35 AM, said:

the exect format for insert statement is..

 empcmd.CommandText = "insert into empdata(emp_code,emp_name,emp_sname,emp_fname,emp_dob,emp_doj) values (" & _
               txtempcode.Text & ",'" & txtempname.Text & "','" & txtempsname.Text & _
                "','" & txtempfname.Text & "','" & DateValue(txtempdob.Text).ToString("MM/dd/yyyy") & _
                "','" & DateValue(txtempdoj.Text).ToString("MM/dd/yyyy") & "')"

 empcon.Open()
        empcmd.Connection = empcon
        empcmd.ExecuteNonQuery()
        empcon.Close()



for date i am using date time picker....
Was This Post Helpful? 0
  • +
  • -

#4 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 01:52 AM

empcmd.CommandText = "insert into empdata(emp_code,emp_name,emp_sname,emp_fname,emp_dob,emp_doj) values (" & _
               txtempcode.Text & ",'" & txtempname.Text & "','" & txtempsname.Text & _
                "','" & txtempfname.Text & "'," & dateTimePicker.Value.Date & _
                "," & dateTimePicker.Value.Date & ")"


        empcon.Open()
        empcmd.Connection = empcon
        empcmd.ExecuteNonQuery()
        empcon.Close()


Was This Post Helpful? 0
  • +
  • -

#5 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 02:10 AM

still if u got problem let me know

This post has been edited by kai_itz me: 14 January 2013 - 02:13 AM

Was This Post Helpful? 0
  • +
  • -

#6 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 02:19 AM

Quote

query.Parameters.AddWithValue("@EMP_ID", SqlDbType.Decimal).Value = TXTEMPID.Text 


i think it must be like

query.Parameters.AddWithValue("@emp_id",txtTextBox.Text) 



for datetime picker

cmd.CommandText = "INSERT INTO empdata (emp_dob) VALUES(@date)"
            cmd.Parameters.Add(new SqlParameter("@date", dateTimePicker.Value.Date))


This post has been edited by kai_itz me: 14 January 2013 - 02:23 AM

Was This Post Helpful? 0
  • +
  • -

#7 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 02:51 AM

@kai_itz me
I try with
cmd.Parameters.Add(new SqlParameter("@date", dateTimePicker.Value.Date))



Still same problem.. There is no data in Database table

Now my code is

Private Sub BTNSAVE_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTNSAVE.Click
        CheckMyControls()
        If totflag = False Then
            MessageBox.Show("Give Complete Data!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            coloring()
        ElseIf totflag = True Then
            Dim gend As String
            gend = ""
            If RBMALE.Checked = True And RBFEMALE.Checked = False Then
                gend = RBMALE.Text
            ElseIf RBFEMALE.Checked = True And RBMALE.Checked = False Then
                gend = RBFEMALE.Text
            End If
            Try
                getConnect()
                Dim query As SqlCommand
                Dim strSQL As String
                strSQL = "INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_FNAME,EMP_GENDER,EMP_DOB,EMP_CAST,EMP_DEPART,EMP_DESIG,EMP_DOJ,EMP_SALARY,EMP_PF_ESI,EMP_BRANCH,EMP_CONTACT,EMP_ADDRESS)VALUES(@EMP_ID,@EMP_NAME,@EMP_FNAME,@EMP_GENDER,@EMP_DOB,@EMP_CAST,@EMP_DEPART,@EMP_DESIG,@EMP_DOJ,@EMP_SALARY,@EMP_PF_ESI,@EMP_BRANCH,@EMP_CONTACT,@EMP_ADDRESS)"
                query = New SqlCommand(strSQL, Conn)
                query.Parameters.Add(New SqlParameter("@EMP_ID", TXTEMPID.Text))
                query.Parameters.Add(New SqlParameter("@EMP_NAME", TXTNAME.Text))
                query.Parameters.Add(New SqlParameter("@EMP_FNAME", TXTFNAME.Text))
                query.Parameters.Add(New SqlParameter("@EMP_GENDER", gend))
                query.Parameters.Add(New SqlParameter("@EMP_DOB", DTPEMPDOB.Value.Date))
                query.Parameters.Add(New SqlParameter("@EMP_CAST", TXTCASTE.Text))
                query.Parameters.Add(New SqlParameter("@EMP_DEPART", CMBDEPT.Text))
                query.Parameters.Add(New SqlParameter("@EMP_DESIG", CMBDESIG.Text))
                query.Parameters.Add(New SqlParameter("@EMP_DOJ", DTPEMPDOJ.Value.Date))
                query.Parameters.Add(New SqlParameter("@EMP_SALARY", MTXTSAL.Text))
                query.Parameters.Add(New SqlParameter("@EMP_PF_ESI", MTXTPFESI.Text))
                query.Parameters.Add(New SqlParameter("@EMP_BRANCH", TXTBRANCH.Text))
                query.Parameters.Add(New SqlParameter("@EMP_CONTACT", MTXTCONTACT.Text))
                query.Parameters.Add(New SqlParameter("@EMP_ADDRESS", RTXTADDRESS.Text))
                Conn.Open()
                Dim numAffected = query.ExecuteNonQuery()
                'MessageBox.Show(numAffected)
                Conn.Close()
                If numAffected > 0 Then
                    MessageBox.Show("Successfully Added", "Add", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    BTNCLEAR.PerformClick()
                Else
                    MsgBox("No record was inserted")
                End If
            Catch ex As Exception
                MsgBox("ERROR: " + ex.Message, MsgBoxStyle.Information, "Add")
            End Try
        End If
    End Sub

Was This Post Helpful? 0
  • +
  • -

#8 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 03:57 AM

Quote

When i press insert Button. It shows Successfully Added. But there is no data in database table


ARE YOU SAVING YOUR DATA BY PRESSING SAVE BUTTON. BECOZ ONLY AFTER PRESSING SAVE BUTTON YOUR DATA WILL BE INSERTED IN TABLE.
Was This Post Helpful? 0
  • +
  • -

#9 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 04:04 AM

ARE YOU SAVING YOUR DATA BY PRESSING SAVE BUTTON. BECOZ ONLY AFTER PRESSING SAVE BUTTON YOUR DATA WILL BE INSERTED IN TABLE.
[/quote]

I press the save button it shows messagebox Added Successfully. But no data in the database table. My database table is given below

Posted Image
Was This Post Helpful? 0
  • +
  • -

#10 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 04:17 AM

go to your table in sql database.. right click ... edit top 200 rows.. and show me what it has inside.
Was This Post Helpful? 0
  • +
  • -

#11 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 04:50 AM

View Postkai_itz me, on 14 January 2013 - 04:17 AM, said:

go to your table in sql database.. right click ... edit top 200 rows.. and show me what it has inside.


Nothing in my Table...
Was This Post Helpful? 0
  • +
  • -

#12 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 05:30 AM

change your code line from 39 to 42... now check what happens

If numAffected > 0 Then 
                  call getconnect()
  MessageBox.Show("Successfully Added", "Add", MessageBoxButtons.OK,  MessageBoxIcon.Information)  
 BTNCLEAR.PerformClick()  
 Else 
                   MsgBox("No record was inserted")  




This post has been edited by kai_itz me: 14 January 2013 - 05:30 AM

Was This Post Helpful? 0
  • +
  • -

#13 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 10:12 PM

Now i find the problem. But i don't know how to clear the issue. Actually i have the database file in Project folder. The same file copy in Debug folder. i attach these two files in SQL Server. File in project folder table contains Null value in all field. But there is data in the file attached from Debug folder.
Was This Post Helpful? 0
  • +
  • -

#14 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Problem Insert into SQL DataBase from VB.NET

Posted 14 January 2013 - 10:56 PM

i really have no idea how to solve that issue..but i found something that might help u.

"In Visual Studio, there is an option to copy your database file to the output folder of your project. Usually this is done with an Access MDB file, or a SQL Server MDF file, when you add the database file to your project.This copy options applies each time you run the application. Therefore, although the database may in fact be updated, the updates are going to the copy instead of the original file that you expect. The key to this is the “Copy to Output Directory” property on the database file in your project. This is typically set to “Copy always” by default. What this means is that every time you build, run, or debug your application, Visual Studio copies the project file over to the output directory, and then that’s what the app connects to.In these cases, you will probably also be using a connection string that includes the "|DataDirectory|" option. For non-Web apps, this is the project output folder where your executable is created (usually bin\debug). In order to ensure that this connection string will work, Visual Studio chooses the "Copy Always" setting by default. You can keep the connection string as-is, but change the copy option to "Do not copy", and in that case, you should see your changes persist between application runs, or if you check the database outside of the app. make sure to check the copy in the output folder. Alternatively, you can change the connection string *and* the copy property, and point to the file you actually want to connect to.This applies to Access MDB files, as well as SQL Server MDF files, as long as you are connecting to the MDFs file at runtime by specifying the location in the connection string with the AttachDBFilename keyword. Access databases are always file-based, so there is no need for a special keyword.There is a utility you can use to see which database file is actually being used by the application: FileMon by http://www.sysinternals.com. Start tracing before you run your application, then watch to see which file is actually accessed. Keep in mind that the trace may also include any file copies, so make sure you search for all occurrences of your filename, to be sure that all of them are using the file you expect."

This post has been edited by kai_itz me: 14 January 2013 - 10:57 PM

Was This Post Helpful? 1
  • +
  • -

#15 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Problem Insert into SQL DataBase from VB.NET

Posted 15 January 2013 - 01:10 AM

View Postkai_itz me, on 14 January 2013 - 10:56 PM, said:

i really have no idea how to solve that issue..but i found something that might help u.

"In Visual Studio, there is an option to copy your database file to the output folder of your project. Usually this is done with an Access MDB file, or a SQL Server MDF file, when you add the database file to your project.This copy options applies each time you run the application. Therefore, although the database may in fact be updated, the updates are going to the copy instead of the original file that you expect. The key to this is the “Copy to Output Directory” property on the database file in your project. This is typically set to “Copy always” by default. What this means is that every time you build, run, or debug your application, Visual Studio copies the project file over to the output directory, and then that’s what the app connects to.In these cases, you will probably also be using a connection string that includes the "|DataDirectory|" option. For non-Web apps, this is the project output folder where your executable is created (usually bin\debug). In order to ensure that this connection string will work, Visual Studio chooses the "Copy Always" setting by default. You can keep the connection string as-is, but change the copy option to "Do not copy", and in that case, you should see your changes persist between application runs, or if you check the database outside of the app. make sure to check the copy in the output folder. Alternatively, you can change the connection string *and* the copy property, and point to the file you actually want to connect to.This applies to Access MDB files, as well as SQL Server MDF files, as long as you are connecting to the MDFs file at runtime by specifying the location in the connection string with the AttachDBFilename keyword. Access databases are always file-based, so there is no need for a special keyword.There is a utility you can use to see which database file is actually being used by the application: FileMon by http://www.sysinternals.com. Start tracing before you run your application, then watch to see which file is actually accessed. Keep in mind that the trace may also include any file copies, so make sure you search for all occurrences of your filename, to be sure that all of them are using the file you expect."



Thank you my friend.. Thank you so much for your valuable support....
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2