Insert,delete,Update codings in VB.NET?
 

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?

Re: Insert,delete,Update codings in VB.NET?
 

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

Re: Insert,delete,Update codings in VB.NET?
 

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

Re: Insert,delete,Update codings in VB.NET?
 

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?

Re: Insert,delete,Update codings in VB.NET?
 

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

Re: Insert,delete,Update codings in VB.NET?
 

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

Re: Insert,delete,Update codings in VB.NET?
 

i think its textbox

Re: Insert,delete,Update codings in VB.NET?
 

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.