Insert a row using code from vb page

How do I update a table from a vb page, below is the code I have started but to tell the truth I'm not really sure what I am writing.

Thanks    


Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Delete.Click
        ' Display message box to user to confirm deletion
        Dim userid
        userid = TextBox12.Text
        intReturnValue = MsgBox("Are you sure you wish to delete Patient " + userid + "?", MsgBoxStyle.YesNo)
        If (intReturnValue = MsgBoxResult.Yes) Then
            ' If answer = yes then move patient to tblArchive
            Dim dataset1 As New DataSet

            Dim ConnectionString As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial " & _
            "Catalog=healthdistrict;Packet Size=4096;Workstation ID=JCleary;Use Encryption for Data=False"
            Dim Connection1 As OleDbConnection = New OleDbConnection(ConnectionString)

            Dim query As String = _
                 "Insert into tblArchive from tblPatient where lngpatientid = " + userid  

        End If
        'Deletes patient from table duplicates
        'DataSet21.Tables(0).Rows(rowNo).Delete()
        'SqlDataAdapter2.Update(DataSet21)
    End Sub
running32Asked:
Who is Participating?
 
RonaldBiemansCommented:
First if you use sqlserver use the sqlclient instead of oledb,

Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Delete.Click
        ' Display message box to user to confirm deletion
        Dim userid as string
        userid = TextBox12.Text
        intReturnValue = MsgBox("Are you sure you wish to delete Patient " + userid + "?", MsgBoxStyle.YesNo)
        If (intReturnValue = MsgBoxResult.Yes) Then

            Dim Connection1 As new sqlclient.sqlconnection("workstation id=JCleary;packet size=4096;integrated security=SSPI;data source=JCleary;persist security info=False;initial catalog=healthdistrict)

        Dim cmd As New OleDb.OleDbCommand("Insert into tblArchive from tblPatient where lngpatientid = " & userid,Connection1)
        connection1.open  
        cmd.ExecuteNonQuery()
        connection1.close

        End If
        'Deletes patient from table duplicates
        'DataSet21.Tables(0).Rows(rowNo).Delete()
        'SqlDataAdapter2.Update(DataSet21)
    End Sub

(you still have to delete the record from the dataset though)
0
 
RonaldBiemansCommented:
Do you want to delete or insert

the button says delete but your query says insert
0
 
running32Author Commented:
I need to insert.  The button says delete because that is what the user thinks they are doing.  But I am moving it to an archived table.

Thanks
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
RonaldBiemansCommented:
sorry this should also be

Dim cmd As New OleDb.OleDbCommand("Insert into tblArchive from tblPatient where lngpatientid = " & userid,Connection1)

this

Dim cmd As New sqlclinet.SqlDbCommand("Insert into tblArchive from tblPatient where lngpatientid = " & userid,Connection1)
0
 
RonaldBiemansCommented:
and I think your query should be changed to this

"Insert into tblArchive select * from tblPatient where lngpatientid = " & userid

although I'm not sure, my sql is a bit rusty these days

0
 
running32Author Commented:
Thanks for answering back so quickly.

The problem states connection1.open   not declared and sqlclinet.SqlDbCommand
not defined.
0
 
running32Author Commented:
after adding a " it still comes up with the error SqlClient.SqlDbCommand  not defined.

thanks
0
 
RonaldBiemansCommented:
sorry typo,

it should sqlclient.sqlcommand
0
 
running32Author Commented:
Thanks for helping.

When I try and run this the program breaks on cmd.ExecuteNonQuery()
0
 
running32Author Commented:
Thanks that got me started
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.