I want to use update or insert query from one button - is that possible -- ?

Question

1.00/5 (1 vote)

See more:

hi

I want to use update and insert query from one button - is that possioble -- ? I tried for it using datareader but there is error " No data exist raw/column" is come !!

any one can suggest me solution ? I want TxtEdit to appear after click on edit button.

My code is

VB

Imports System.Data.OleDb.OleDbCommand
Imports System.Data.OleDb.OleDbDataReader
Imports System.Data.OleDb
Imports System.Data
Imports System.Data.OleDb.OleDbConnection
Imports System.IO
Imports System.Data.OleDb.OleDbDataAdapter
Partial Class StudentInfo
    Inherits System.Web.UI.Page
    Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Dim EditFlag As Integer
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cn = New OleDbConnection("Provider = Microsoft.Jet.OleDb.4.0;Data source=" & Server.MapPath("DEO.mdb"))
        cn.Open()
        cmd = New OleDbCommand("Select * from Student", cn)
        cmd.ExecuteNonQuery()
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        '// this is Submit Button

        Dim DR As OleDbDataReader
        cn = New OleDbConnection("Provider = Microsoft.Jet.OleDb.4.0;Data source=" & Server.MapPath("DEO.mdb"))
        cn.Open()
        cmd = New OleDbCommand("Select * from Student", cn)
        Dim cmdstr As String
        DR = cmd.ExecuteReader
        While DR.Read()
            TxtSrNo.Text = DR.Item(0)
            TxtSchoolName.Text = DR.Item(1)
            TxtVillage.Text = DR.Item(2)
            CmbTaluko.Text = DR.Item(3)
            CmbStd.Text = DR.Item(4)
            CmbCast.Text = DR.Item(5)
            CmbSex.Text = DR.Item(6)
            TxtTotal.Text = DR.Item(7)
        End While

        'while loop 
        If TxtSrNo.Text = DR.Item(0)
            cmdstr = "UPDATE Student set Total='" & TxtTotal.Text & "'"
        Else
            cmdstr = " INSERT INTO Student(SrNo,SchoolName,Village,Taluka,Std,SCast,Sex,Total) Values (" & TxtSrNo.Text & ",'" & TxtSchoolName.Text & "', '" & TxtVillage.Text & "','" & CmbTaluko.Text & "'," & CmbStd.Text & ",'" & CmbCast.Text & "','" & CmbSex.Text & "'," & TxtTotal.Text & ")"
        End If

        cmd.CommandText = cmdstr
        DR.Close()
        cmd.ExecuteNonQuery()

        TxtSrNo.Text = ""
        TxtSchoolName.Text = ""
        TxtVillage.Text = ""
        CmbTaluko.Text = ""
        CmbStd.Text = ""
        CmbCast.Text = ""
        CmbSex.Text = ""
        TxtTotal.Text = ""
    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        '// This is Edit Button


        cn = New OleDbConnection("Provider = Microsoft.Jet.OleDb.4.0;Data source=" & Server.MapPath("DEO.mdb"))
        cn.Open()
        cmd = New OleDbCommand("Select * from Student ", cn)
        Dim DataStr As String
        DataStr = " Select * from Student where SrNo=" & TxtEdit.Text & ""

        Dim editstr As String

        editstr = TxtEdit.Text
        Session.Item("Editor") = editstr

        MsgBox(Session.Item("Editor"))

        cmd.CommandText = DataStr

        dr = cmd.ExecuteReader
        dr.Read()
        TxtSrNo.Text = dr.Item(0)
        TxtSchoolName.Text = dr.Item(1)
        TxtVillage.Text = dr.Item(2)
        CmbTaluko.Text = dr.Item(3)
        CmbStd.Text = dr.Item(4)
        CmbCast.Text = dr.Item(5)
        CmbSex.Text = dr.Item(6)
        TxtTotal.Text = dr.Item(7)
        dr.Close()
        cmd.ExecuteNonQuery()

    End Sub
End Class

Posted 26-Mar-13 6:14am

Md Jamaluddin Saiyed

Updated 26-Mar-13 22:59pm

v4

Add a Solution

Comments

Richard C Bishop 26-Mar-13 12:18pm

It is possible to do both on one button click. You may want to look into paramterized queries so you thwart the sql injection vulnerability you currenlty possess.

Md Jamaluddin Saiyed 26-Mar-13 12:28pm

Thanks !! can you suggest solution for it or Give me example of above question using two things in one button

Sandeep Mewara 26-Mar-13 13:17pm

cmdstr = "UPDATE Student set Total='" & TxtTotal.Text & "'"
You want to update all the totals of the table? Not a specific one? I doubt that would be the case.

ZurdoDev 26-Mar-13 13:24pm

Yes. What problem are you running into?

Md Jamaluddin Saiyed 26-Mar-13 13:27pm

Want to update particular record whose srno is given in TxtEdit textbox

braop 26-Mar-13 13:58pm

You getting that error because your update SQL query is very wrong....its like you are updating nothing which is impossible in real life.

After selecting what you want to update...fill it in TxtTotal...then edit it. and press ur edit button

This should work: try it.

"UPDATE Student Set Total = '"& TxtTotal.Text &"' where Total = '" & TxtTotal.Tag &"'"

NB: Try using parametrized queries if possible and if Total is Integer then single quotes('') ain't necessary.

2 solutions

Solution 1

No, you can't do it one query, but you can use parameterized queries as stored procedure:

SQL

PARAMETERS stuId LONG
SELECT *
FROM Students
WHERE StudentID = stuId

Than you can call it like this:

VB

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
Dim cmd As New OleDbCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MyQueryName"
cmd.Parameters.Add("StringParameter", OleDbType.VarChar).Value = "value1"  ' Add Parameter
cmd.Connection = con
con.Open()
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim ds As DataSet = New DataSet()
da.Fill(ds, "Table1")

I suggest you to read this article on MSDN site: Walkthrough: Creating a Web Page to Display Access Database Data [^] and all related articles.

It's good to know what is SQL Injection[^].

Posted 26-Mar-13 10:13am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Md Jamaluddin Saiyed · Accepted Answer · 2013-04-01T19:33:00

VB

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        '// this is Submit Button
        Dim UpdateFlag As Integer
        'Dim DR As OleDbDataReader
        cn = New OleDbConnection("Provider = Microsoft.Jet.OleDb.4.0;Data source=" & Server.MapPath("DEO.mdb"))
        cn.Open()
        cmd = New OleDbCommand("Select * from Student", cn)
        Dim cmdstr As String
        dr = cmd.ExecuteReader
        UpdateFlag = 0
        'while loop
        While dr.Read()
            If dr.Item(0) = Val(TxtSrNo.Text) Then
                UpdateFlag = 1
            End If
        End While


        If UpdateFlag = 1 Then

            cmdstr = "UPDATE Student set ScBoys9 =" & Val(TxtScBoys9.Text) & ",ScGirls9 = " & Val(TxtScGirls9.Text) & ",StBoys9 = " & Val(TxtStBoys9.Text) & ", StGirls9 = " & Val(TxtStGirls9.Text) & ",ObcBoys9 = " & Val(TxtObcBoys9.Text) & ",ObcGirls9 = " & Val(TxtObcGirls9.Text) & ",GenBoys9 = " & Val(TxtGenBoys9.Text) & ",GenGirls9 = " & Val(TxtGenGirls9.Text) & ", Total9 = " & Val(TxtTotal9.Text) & ", ScBoys10 = " & Val(TxtScBoys10.Text) & ", ScGirls10 = " & Val(TxtScGirls10.Text) & ",StBoys10 = " & Val(TxtStBoys10.Text) & ", StGirls10 = " & Val(TxtStGirls10.Text) & ",ObcBoys10 = " & Val(TxtObcBoys10.Text) & ",ObcGirls10 = " & Val(TxtObcGirls10.Text) & ",GenBoys10 = " & Val(TxtGenBoys10.Text) & ",GenGirls10 = " & Val(TxtGenGirls10.Text) & ", Total10 = " & Val(TxtTotal10.Text) & ", GTotal = " & Val(TxtGTotal.Text) & " where SrNo=" & TxtSrNo.Text & ""
        Else
            cmdstr = "INSERT INTO Student(SrNo,SchoolName,Village,Taluka,ScBoys9,ScGirls9,StBoys9,StGirls9,ObcBoys9,ObcGirls9,GenBoys9,GenGirls9,Total9,ScBoys10,ScGirls10,StBoys10,StGirls10,ObcBoys10,ObcGirls10,GenBoys10,GenGirls10,Total10,GTotal) Values (" & TxtSrNo.Text & ",'" & TxtSchoolName.Text & "', '" & TxtVillage.Text & "','" & CmbTaluko.Text & "'," & Val(TxtScBoys9.Text) & "," & Val(TxtScGirls9.Text) & "," & Val(TxtStBoys9.Text) & "," & Val(TxtStGirls9.Text) & "," & Val(TxtObcBoys9.Text) & "," & Val(TxtObcGirls9.Text) & "," & Val(TxtGenBoys9.Text) & "," & Val(TxtGenGirls9.Text) & "," & Val(TxtTotal9.Text) & "," & Val(TxtScBoys10.Text) & "," & Val(TxtScGirls10.Text) & "," & Val(TxtStBoys10.Text) & "," & Val(TxtStGirls10.Text) & "," & Val(TxtObcBoys10.Text) & "," & Val(TxtObcGirls10.Text) & "," & Val(TxtGenBoys10.Text) & "," & Val(TxtGenGirls10.Text) & "," & Val(TxtTotal10.Text) & ", " & Val(TxtGTotal.Text) & " )"

        End If

        cmd.CommandText = cmdstr
        dr.Close()

        cmd.ExecuteNonQuery()

VB

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        '// This is Edit Button

        cn = New OleDbConnection("Provider = Microsoft.Jet.OleDb.4.0;Data source=" & Server.MapPath("DEO.mdb"))
        cn.Open()

        cmd = New OleDbCommand("Select * from Student ", cn)

        Dim DataStr As String

        DataStr = " Select * from Student where SrNo= " & (TxtEdit.Text) & ""

        cmd.CommandText = DataStr

        dr = cmd.ExecuteReader

        If dr.HasRows = True Then
            dr.Read()
            TxtSrNo.Text = dr.Item(0)
            TxtSchoolName.Text = dr.Item(1)
            TxtVillage.Text = dr.Item(2)
            CmbTaluko.Text = dr.Item(3)
            TxtScBoys9.Text = dr.Item(4)
            TxtScGirls9.Text = dr.Item(5)
            TxtStBoys9.Text = dr.Item(6)
            TxtStGirls9.Text = dr.Item(7)
            TxtObcBoys9.Text = dr.Item(8)
            TxtObcGirls9.Text = dr.Item(9)
            TxtGenBoys9.Text = dr.Item(10)
            TxtGenGirls9.Text = dr.Item(11)
            TxtTotal9.Text = dr.Item(12)
            TxtScBoys10.Text = dr.Item(13)
            TxtScGirls10.Text = dr.Item(14)
            TxtStBoys10.Text = dr.Item(15)
            TxtStGirls10.Text = dr.Item(16)
            TxtObcBoys10.Text = dr.Item(17)
            TxtObcGirls10.Text = dr.Item(18)
            TxtGenBoys10.Text = dr.Item(19)
            TxtGenGirls10.Text = dr.Item(20)
            TxtTotal10.Text = dr.Item(21)
            TxtGTotal.Text = dr.Item(22)
         End If
        dr.Close()
        cmd.ExecuteNonQuery()