我想从一个按钮使用更新或插入查询 - 可能 - ？ [英] I want to use update or insert query from one button - is that possible -- ?

问题描述

嗨



我想从一个按钮使用更新和插入查询 - 是否可能 - ？我尝试使用datareader，但有错误没有数据存在原始/列来了!!



任何人都可以建议我解决方案？我想在点击编辑按钮后出现TxtEdit。



我的代码是

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

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

推荐答案

不，你不能做一个查询，但你可以使用参数化查询作为存储过程：

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

PARAMETERS stuId LONG
SELECT *
FROM Students
WHERE StudentID = stuId

你可以这样称呼：

Than you can call it like this:

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建议您阅读MSDN网站上的这篇文章：演练：创建网页显示访问数据库数据 [ ^ ]和所有相关文章。



知道什么是SQL注入 [ ^ ]。

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[^].

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()

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()

这篇关于我想从一个按钮使用更新或插入查询 - 可能 - ？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！