INSERT INTO SELECT using vb.net Can't Insert

Question

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Con.Open()
    Dim Str As String = "INSERT INTO db (ID,companyname,companyaddress,companycontact,contactperson,contactnumber,UserLogin,mDate) SELECT @ID,@companyname,@companyaddress,@companycontact,@contactperson,@contactnumber,@UserLogin,@mDate FROM db WHERE Userlogin = @Userlogin"

    Dim Cmd As New OleDbCommand
    With Cmd

        .CommandText = Str

        .Parameters.AddWithValue("@ID", f1.Text)
        .Parameters.AddWithValue("@companyname", f2.Text)
        .Parameters.AddWithValue("@companyaddress", f3.Text)
        .Parameters.AddWithValue("@companycontact", f4.Text)
        .Parameters.AddWithValue("@contactperson", f5.Text)
        .Parameters.AddWithValue("@contactnumber", f6.Text)
        .Parameters.AddWithValue("@UserLogin", f12.Text)
        .Parameters.AddWithValue("@mDate", DateTimePicker1.Text)
        .Connection = Con

        .ExecuteNonQuery()
    End With
    If Cmd.ExecuteNonQuery() Then
        Con.Close()
        MessageBox.Show("New Record is Added successfully.", "Record Saved")
        Call clear()

    Else

        MsgBox("Could Not Insert Record!!! ", "Already Entered")
        Return
    End If
End Sub

Welcome to SO! What have you tried so far? To help us help you, please add a Minimal, Complete, and Verifiable example Please add some context to explain the code. — abpatil
– abpatil, Commented Jan 18, 2017 at 9:31
Are there any errors? I would look at using .Parameters.Add("@param", SqlDbType).Value = and explicitly set each SqlDataType. — Bugs
– Bugs, Commented Jan 18, 2017 at 9:44

Gustav · Accepted Answer · 2017-01-18 11:01:44Z

1

You must pass the date value:

.Parameters.AddWithValue("@mDate", DateTimePicker1.Value)

answered Jan 18, 2017 at 11:01

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:52:55Z

0

Do you get any errors when calling the ExecuteNonQuery() method? I checked your code and I have two comments:
(1) You are calling the ExecuteNonQuery() twice in your code:
+------------------------+
'First call
.ExecuteNonQuery()
End With
'Second call
If Cmd.ExecuteNonQuery() Then
+------------------------+
Are you calling the method twice on purpose?
2. Make sure that the connection is opened before calling the Cmd.ExecuteNonQuery() method.

If you are still facing problems, I found a related issue here with some valuable information: How can I Insert data into SQL Server using VBNet

I hope this helps!

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Jan 18, 2017 at 11:58

Maen Hasan

1591 silver badge3 bronze badges

Comments

muffi · Accepted Answer · 2017-01-18 09:33:13Z

-1

I don't unterstand why you are using the SELECT? I think you want to do this:

INSERT INTO db(ID,companyname,companyaddress,companycontact,contactperson,contactnumber,UserLogin,mDate) VALUES (@ID,@companyname,@companyaddress,@companycontact,@contactperson,@contactnumber,@UserLogin,@mDate)

answered Jan 18, 2017 at 9:33

muffi

3646 silver badges18 bronze badges

6 Comments

Dessa Over a year ago

For duplication purposes so that is why im trying to use insert into select to avoid double entry

muffi Over a year ago

To avoid this, try REPLACE INTO instead of INSERT INTO. If the primary key exists, MySQL will first delete this row and then do a normal insert.

Dessa Over a year ago

it says Invalid SQL Statement

muffi Over a year ago

OK, I thought, you are using MySQL. For MSSQL add a DELETE in front of the INSERT using your primary key (@id I think), like this: DELETE FROM db WHERE ID=@ID; INSERT INTO db(ID,companyname,companyaddress,companycontact,contactperson,contactnumber,UserLogin,mDate) VALUES (@ID,@companyname,@companyaddress,@companycontact,@contactperson,@contactnumber,@UserLogin,@mDate)

Dessa Over a year ago

im using ms access 2010

|

Collectives™ on Stack Overflow

INSERT INTO SELECT using vb.net Can't Insert

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related