Insert,delete,Update codings in VB.NET?

[preethi_ga]

Hello,
In .NET im using the backend as SQL Server. but when i write codings for all insert,update and delete in .NET and run the form, the error it shows like
"Oledb exception was unhandled by the user" and error in SQL query.
the codings are....follows..

imports system
imports system.data
imports system.data.sqlclient
imports dataset1.

And in Page_Load..
dim con as sqlconnection
dim cmd as sqlcommand
dim adp as sqladapter
dim data as dataset1
con.connectionstring="Provider=SQLOLEDB;initialcatalog=preethi;database=ACER;integrated security=true"
cmd.connection=con
cmd=new sqlcommand("select * from emp"),con
adp=new sqldataadapter(cmd)
adp.Fill(data)

'since i have only 3 fields
textbox1.text=data.tables(1).rows(0).item(0)
textbox2.text=data.tables(1).rows(0).item(1)
textbox3.text=data.tables(1).rows(0).item(2)

In Insert button-click event.....
' its bcoz the second textbox has numeric value and 1st and 3rd have only string values.

cmd = new sqlcommand("insert into emp values( ' " & textbox1.text & " ', " & textbox2.text & ", ' " & textbox3.text & " ')" , con
cmd.executeNonQuery()
Response.write(" One record inserted").

So these are the codings im using. for delete also the same typeim using. but im getting error, can anyone help me to solve this?

[anansiva]

Hi,
I think that your command will be the problem for you..please replace "cmd=new sqlcommand("select * from emp",con)" in your code and test it whether it is working or not..
I dont get any other bug from your code.....Thanks

[dadelsen]

Think it is an ASP.NET application. I made some changes, marked with XXX. On my system it works. Maybe the changes help you to get your program to work. Databaseprogramming is a wide area.

Imports System
Imports System.data
Imports System.data.sqlclient
'XXX used a standard  dataset since I do not have yours Imports dataset1 
Public Class WebForm1
    Inherits System.Web.UI.Page

'XXX con needs to be in scope when the Insert-Button Click event is handled    
Dim con As SqlConnection 

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'XXX Dim con As SqlConnection 
        Dim cmd As SqlCommand
        Dim adp As SqlDataAdapter 'XXX use SQLDataAdapter Dim adp As sqladapter
        Dim data As DataSet

        'XXX I do not have your database, please change connect string as required. 
        'XXX If you use the SQLClient dataprovider, you do not need the provider keyword.
        'XXX instantiated the con object
        'con.ConnectionString = "Provider=SQLOLEDB;initialcatalog=preethi;database=ACER;integrated security=true"
        con = New SqlConnection
        con.ConnectionString = "database=.;initial catalog=contacts;user=ASPNET;"

        cmd = New SqlCommand  'XXX instantiated the command object 
        'XXX connection is assigned below
        'cmd.Connection = con  
        'XXX cmd=new sqlcommand("select * from emp"),con
        cmd = New SqlCommand("select * from contacts", con) 'XXX

        adp = New SqlDataAdapter(cmd)
        data = New DataSet 'instatiated dataset
        adp.Fill(data)
        
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click

        Dim sSQL As String = "INSERT INTO contacts( contactid, firstname, lastname) VALUES (20, 'User20', 'User20Firstname')"

        Dim cmd As SqlCommand = New SqlCommand(sSQL, con)
        con.Open() 'XXX connection must be opened before command is executed
        cmd.ExecuteNonQuery()
        con.Close() 'XXX 
        Response.Write(" One record inserted")

    End Sub
End Class

[preethi_ga]

Hi,
First of all thanks. the Insert command is working. but if i want to insert at run time, how can i give the coding?

But the delete command is not working.

[ Dim sSql as String = " Delete From tablename where name = ' " & Textbox1.Text & " ' " ]
[ Com = New SqlCommand(sSql,Con) ]
[ Com.ExecuteNonQuery() ]
[ Response.Write(" One Record Deleted ") ]

Can you tell me any other coding?

[dadelsen]

In most cases you would use a dataset object to keep a copy of database data inside your program, then insert, change and delete the rows in the dataset and, when done, you would use dataadapters to write the changes from the dataset to the database. But this is too much to be solved in a forum thread. There are books available, for example David Sceppa, Programming ADO.NET.

For directly inserting and deleting data into a database (using a literal on the form to show feedback):

Insert:

Private Sub btnInsertDirect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnInsertDirect.Click
        Dim iRet As Integer
        Dim iNextId As Integer
        Dim sConnectionString As String = "server=(local);database=contacts;user=ASPNET"
        Dim conn As New SqlConnection(sConnectionString)
        Dim sSQL As String

        sSQL = "INSERT INTO contacts (contactid, firstname, lastname)" & _
           " VALUES (@contactid, @firstname, @lastname)"

        Dim cmd As New SqlCommand(sSQL, conn)
        iNextId = 123
        cmd.Parameters.Add(New SqlParameter("@contactid", 123))
        cmd.Parameters.Add(New SqlParameter("@firstname", txtFirstName.Text))
        cmd.Parameters.Add(New SqlParameter("@lastname", txtLastName.Text))

        conn.Open()
        Try
            iRet = cmd.ExecuteNonQuery()
            litMsg.Text = String.Format("Inserted {0} records", iRet)
        Catch ex As System.Exception
            litMsg.Text = String.Format("Error: {0}", ex.ToString)
        Finally
            conn.Close()
        End Try

    End Sub

Delete:

Private Sub btnDeleteDirect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteDirect.Click
        Dim iRet As Integer
        Dim sConnectionString As String = "server=(local);database=contacts;user=ASPNET"
        Dim conn As New SqlConnection(sConnectionString)
        Dim sSQL As String

        sSQL = "DELETE FROM contacts WHERE lastname = '" & txtLastName.Text & "'"

        Dim cmd As New SqlCommand(sSQL, conn)
        conn.Open()
        Try
            iRet = cmd.ExecuteNonQuery()
            litMsg.Text = String.Format("Deleted {0} records", iRet)
        Catch ex As System.Exception
            litMsg.Text = String.Format("Error: {0}", ex.ToString)
        Finally
            conn.Close()
        End Try
    End Sub

[preethi_ga]

Hi, In the TRY .....CATCH block,
u have written some thing like...
[ litMsg.text ] what is this ? is it textbox or anything??

[Jx_Man]

i think its textbox

[dadelsen]

litMsg is a "literal", a Server Control from the toolbox/Web Forms Tab.

When you first posted the thread you wrote

"And in Page_Load.." and "Response.Write"

so I thought your application must be an ASP. NET application, since in a Windows Forms Application the event would be "Form... Load", and there normally is no "Resonse.Write" in a Windows Forms application.

[olobo]

am new to vb.net. i use sql 3.5 compact as my database. how can i save(update) to my database? Please help me