We help IT Professionals succeed at work.

update ado.net OleDb problem.

Hi

I have a little problem getting this script to work, so hope someone here can help me.
I have this link on a site updatetest.aspx?id=1  up to 6 when clicking one of the link i go to updatetest.aspx site
where i can update the info, but i cant get to the site.

Im new to this, so hope someone will help me and maybe see if it the right way im doing this.
Main site
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Updateitems.aspx.vb" Inherits="Updateitems" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    



    <asp:Repeater ID="Repeater2" runat="server">
    <HeaderTemplate>Start på Recorder Update<br /></HeaderTemplate>
    <ItemTemplate>
    <asp:TextBox ID="txt_thetitle" runat="server" text="<%# Container.DataItem("the_title")%>"></asp:TextBox>
        <asp:TextBox ID="txt_thetext" runat="server" Text="<%# Container.DataItem("the_text")%>"></asp:TextBox>
        <asp:TextBox ID="txt_thenumber" runat="server" Text="<%# Container.DataItem("the_number")%>"></asp:TextBox>
        <asp:DropDownList ID="txt_thekat" runat="server">
        <asp:ListItem Value="1">Kat1</asp:ListItem>
        <asp:ListItem Value="2">Kat2</asp:ListItem>
        <asp:ListItem Value="3">Kat3</asp:ListItem>
        <asp:ListItem Value="4">Kat4</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="Button2" runat="server" Text="Updater" /></div>
    </ItemTemplate>
    <FooterTemplate>Slut på Recorder Update</FooterTemplate>
    </asp:Repeater>
    </div>
    <br />
        <br />
        <div>
                    <asp:Label ID="lblId" runat="server" Text=""></asp:Label>
    </div>
    </form>
</body>
</html>

Open in new window


Is it okay to use a repeater when its only one record i need to edit or can i get thie showed at an easier way !?
Codebehind
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim updId As String = Context.Request.QueryString("id")

        Try
            openAccessConnection()

            Dim cmd As New OleDbCommand("select * from TBL where the_id =" & updId & "", myAccessConnection)

            ' Mark the Command as a Text
            cmd.CommandType = CommandType.Text

            ' Add Parameters to Command

            Dim myAdapter As New OleDbDataAdapter(cmd)
            Dim myDataSet As New DataSet
            myAdapter.Fill(myDataSet)

            Repeater2.DataSource = myDataSet
            DataBind()

            closeAccessConnection()

        Catch exc As Exception
            closeAccessConnection()

        End Try
    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            openAccessConnection()

            Dim cmd As New OleDbCommand("update TBL set(the_title,the_text,the_number,the_kat) values(@thetitle,@thetext,@thenumber,@thekat) where the_id =" & updId & "", myAccessConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.Add("@thetitle", OleDbType.VarChar).Value = txt_thetitle.Text
            cmd.Parameters.Add("@thetext", OleDbType.VarChar).Value = txt_thetext.Text
            cmd.Parameters.Add("@thenumber", OleDbType.Integer).Value = txt_thenumber.Text
            cmd.Parameters.Add("@thekat", OleDbType.VarChar).Value = txt_thekat.Text


            Dim result As Integer
            result = cmd.ExecuteNonQuery()
            If (result > 0) Then
                lblId.Text = "success med at updater denne record"
            Else
                lblId.Text = "Error"
            End If
            closeAccessConnection()

        Catch ex As Exception
            Response.Write(ex.Message)
            closeAccessConnection()
        End Try

        'Response.Redirect("default.aspx")
    End Sub

Open in new window


Hope someone can help me with this.
Comment
Watch Question

Author

Commented:
have changes a little bit of code, now i get these errors
hope someone can help me, the errors i get is for:
cmd.Parameters.AddWithValue("@thetitle", OleDbType.VarChar).Value = txt_thetitle.Text
cmd.Parameters.AddWithValue("@thetext", OleDbType.VarChar).Value = txt_thetext.Text
cmd.Parameters.AddWithValue("@thenumber", OleDbType.Integer).Value = txt_thenumber.Text
cmd.Parameters.AddWithValue("@thekat", OleDbType.VarChar).Value = txt_thekat.Text
Where it say that upd_thexxxxxxxx is not declared

and
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
here i get a required a withevents...

Author

Commented:
im getting the errors now in this code changes.
    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            openAccessConnection()

            Dim cmd As New OleDbCommand("update TBL set the_title = ?,the_text = ?,the_number = ?,the_kat = ? where the_id =" & updId & "", myAccessConnection)
            cmd.CommandType = CommandType.Text
            'UpdateCommand = cmd

            cmd.Parameters.AddWithValue("@thetitle", OleDbType.VarChar).Value = upd_thetitle.Text
            cmd.Parameters.AddWithValue("@thetext", OleDbType.VarChar).Value = upd_thetext.Text
            cmd.Parameters.AddWithValue("@thenumber", OleDbType.Integer).Value = upd_thenumber.Text
            cmd.Parameters.AddWithValue("@thekat", OleDbType.VarChar).Value = upd_thekat.Text


            Dim result As Integer
            result = cmd.ExecuteNonQuery()
            If (result > 0) Then
                lblId.Text = "success med at updater denne record"
            Else
                lblId.Text = "Error"
            End If
            closeAccessConnection()

        Catch ex As Exception
            Response.Write(ex.Message)
            closeAccessConnection()
        End Try

        'Response.Redirect("default.aspx")
    End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
>Is it okay to use a repeater when its only one record i need to edit or can i get thie showed at an easier way !?

If you are sure that there is only 1 record then there is no point in using a repeater.

I am unable to understand rest of your problem.

Author

Commented:
what can i then use insted of a repeater !?

my problem is that i get a "Where it say that txt_thexxxxxxxx is not declared" in this code
see the end of the lines

cmd.Parameters.AddWithValue("@thetitle", OleDbType.VarChar).Value = txt_thetitle.Text
cmd.Parameters.AddWithValue("@thetext", OleDbType.VarChar).Value = txt_thetext.Text
cmd.Parameters.AddWithValue("@thenumber", OleDbType.Integer).Value = txt_thenumber.Text
cmd.Parameters.AddWithValue("@thekat", OleDbType.VarChar).Value = txt_thekat.Text
Most Valuable Expert 2012
Top Expert 2014

Commented:
You get that error because you can not directly access the controls in repeater (because there may be many instances of the same control). So you can try changing

<asp:Repeater ID="Repeater2" runat="server">
    <HeaderTemplate>Start på Recorder Update<br /></HeaderTemplate>
    <ItemTemplate>
    <asp:TextBox ID="txt_thetitle" runat="server" text="<%# Container.DataItem("the_title")%>"></asp:TextBox>
        <asp:TextBox ID="txt_thetext" runat="server" Text="<%# Container.DataItem("the_text")%>"></asp:TextBox>
        <asp:TextBox ID="txt_thenumber" runat="server" Text="<%# Container.DataItem("the_number")%>"></asp:TextBox>
        <asp:DropDownList ID="txt_thekat" runat="server">
        <asp:ListItem Value="1">Kat1</asp:ListItem>
        <asp:ListItem Value="2">Kat2</asp:ListItem>
        <asp:ListItem Value="3">Kat3</asp:ListItem>
        <asp:ListItem Value="4">Kat4</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="Button2" runat="server" Text="Updater" /></div>
    </ItemTemplate>
    <FooterTemplate>Slut på Recorder Update</FooterTemplate>
    </asp:Repeater>

to


    <asp:TextBox ID="txt_thetitle" runat="server" text="<%# Container.DataItem("the_title")%>"></asp:TextBox>
        <asp:TextBox ID="txt_thetext" runat="server" Text="<%# Container.DataItem("the_text")%>"></asp:TextBox>
        <asp:TextBox ID="txt_thenumber" runat="server" Text="<%# Container.DataItem("the_number")%>"></asp:TextBox>
        <asp:DropDownList ID="txt_thekat" runat="server">
        <asp:ListItem Value="1">Kat1</asp:ListItem>
        <asp:ListItem Value="2">Kat2</asp:ListItem>
        <asp:ListItem Value="3">Kat3</asp:ListItem>
        <asp:ListItem Value="4">Kat4</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="Button2" runat="server" Text="Updater" /></div>

Author

Commented:
okay

but how do i then get this code to write to the asp:textbox, now when i have removed the repeater !?
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            openAccessConnection()

            Dim cmd As New OleDbCommand("select * from TBL where the_id =" & updId & "", myAccessConnection)

            ' Mark the Command as a Text
            cmd.CommandType = CommandType.Text

            ' Add Parameters to Command

            Dim myAdapter As New OleDbDataAdapter(cmd)
            Dim myDataSet As New DataSet
            myAdapter.Fill(myDataSet)

            Repeater2.DataSource = myDataSet
            DataBind()

            closeAccessConnection()

        Catch exc As Exception
            closeAccessConnection()

        End Try
    End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2014
Commented:
You can do something like below


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 Try
  openAccessConnection()
  Dim cmd As New OleDbCommand("select * from TBL where the_id =" & updId & "", myAccessConnection)
  ' Mark the Command as a Text
  cmd.CommandType = CommandType.Text
  Dim dbreader As OleDbDataReader = cmd.ExecuteReader
  if dbreader.HasRows Then
   dbreader.read()
   txt_thetitle.Text = dbreader.item("columnname")
   txt_thetext.Text = dbreader.item("columnname")
   txt_thenumber.text = dbreader.item("columnname")
   ...
  End If
  dbreader.close()           
 Catch exc As Exception
 Finally
  closeAccessConnection()
 End Try
End Sub

Open in new window

Author

Commented:
Hi CodeCruiser

First of all, thx for a greate support/help. U show a almost working ex. and i need to look at it a bit myself to get it work.

THX

It work but i have some error/problems.

1. when adding a new record, it make 2 records, saying the same, the only thing thats not the same is the ID, why !?
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            openAccessConnection()

            Dim cmd As New OleDbCommand("insert into TBL(the_title,the_text,the_number,the_kat) values(@thetitle,@thetext,@thenumber,@thekat)", myAccessConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.Add("@thetitle", OleDbType.VarChar).Value = txt_thetitle.Text
            cmd.Parameters.Add("@thetext", OleDbType.VarChar).Value = txt_thetext.Text
            cmd.Parameters.Add("@thenumber", OleDbType.Integer).Value = txt_thenumber.Text
            cmd.Parameters.Add("@thekat", OleDbType.VarChar).Value = txt_thekat.Text


            Dim result As Integer
            result = cmd.ExecuteNonQuery()
            If (result > 0) Then
                lblId.Text = "success med at oprette denne record"
            Else
                lblId.Text = "Error"
            End If
            closeAccessConnection()

        Catch ex As Exception
            Response.Write(ex.Message)
            closeAccessConnection()
        End Try

        'Response.Redirect("default.aspx")
    End Sub

Open in new window


2. When i want to update the record, i get the items to the text.fields now, (that works, but when i then edit the record, it add a new one to the DB with a new ID, and the old one is still in the DB, Why !?
Dim myAccessConnection As New OleDbConnection(connStr)
    ' postback ID for update
    Dim updId As String = Context.Request.QueryString("id")

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            openAccessConnection()
            Dim cmd As New OleDbCommand("select * from TBL where the_id =" & updId & "", myAccessConnection)
            ' Mark the Command as a Text
            cmd.CommandType = CommandType.Text
            Dim dbreader As OleDbDataReader = cmd.ExecuteReader
            If dbreader.HasRows Then
                dbreader.read()
                upd_thetitle.Text = dbreader.Item("the_title")
                upd_thetext.Text = dbreader.Item("the_text")
                upd_thenumber.Text = dbreader.Item("the_number")
            End If
            dbreader.close()
        Catch exc As Exception
        Finally
            closeAccessConnection()
        End Try
    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            openAccessConnection()

            Dim cmd As New OleDbCommand("update TBL set the_title = ?,the_text = ?,the_number = ?,the_kat = ? where the_id =" & updId & "", myAccessConnection)
            cmd.CommandType = CommandType.Text
            'UpdateCommand = cmd

            cmd.Parameters.AddWithValue("@thetitle", OleDbType.VarChar).Value = upd_thetitle.Text
            cmd.Parameters.AddWithValue("@thetext", OleDbType.VarChar).Value = upd_thetext.Text
            cmd.Parameters.AddWithValue("@thenumber", OleDbType.Integer).Value = upd_thenumber.Text
            cmd.Parameters.AddWithValue("@thekat", OleDbType.VarChar).Value = upd_thekat.Text


            Dim result As Integer
            result = cmd.ExecuteNonQuery()
            If (result > 0) Then
                lblId.Text = "success med at updater denne record"
            Else
                lblId.Text = "Error"
            End If
            closeAccessConnection()

        Catch ex As Exception
            Response.Write(ex.Message)
            closeAccessConnection()
        End Try

        'Response.Redirect("default.aspx")
    End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Are you sure you are clicking the right buttons? The code looks fine. Double records could be due to double clicks.

Author

Commented:
The problem with 2. is that i get the data to the site with page_load and then with button2_click i save the record to the DB, but when i click Button2_Click and call the SUB Button2_Click, do i then call the Page_load again !? can that be the problem !?

Author

Commented:
about the first code, where it add two records, i use this button code
<asp:Button ID="Button1" runat="server" Text="Opret" OnClick="Button1_Click" />
if i delete the OnClick="Button1_Click" so i dont have a OnClick on it, then it work, then it just add one record to my DB.. !? Why.....i dont know, dont i need a OnClick here !?
Most Valuable Expert 2012
Top Expert 2014

Commented:
No. The reason it adds two records is that you have both OnClick as well as Handles button1.click on the method so same method is executed twice.

If you have any code in page load that should only run when the page loads and not when the page posts back then enclose that code in

If (Not Page.IsPostBack ) Then
   ...
End If

block.

Author

Commented:
CodeCruiser

Thx alot, u gave me some very very usefull info, THX. now i will try to look at the delete code...but THX alot again.

One thing, if u want to look at that.
If uddating a record, with  then code, how can i addd a code that say, "are u sure that u will changes the info - and the to buttons YEs NO" !?

Author

Commented:
Great support from this user, THX alot
Most Valuable Expert 2012
Top Expert 2014

Commented:
Glad to help :-)

You would need some javascript code on the client side for showing the confirm box. Here is a starting point

http://www.4guysfromrolla.com/articles/021104-1.aspx

Author

Commented:
Okay thx.

is it possible if i dont hit Cancel that i then can get it to response.Redirect !?
I have this code and its work, but if i hit cancel, it will be greate that it then redirect me and if i hit OK then it just run my TRY....

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'code in page load that should only run when the page loads and not when the page posts back
        If (Not Page.IsPostBack) Then
            Me.Button2.Attributes.Add("onclick", "return confirm('Are you sure you want to update?');")
            Try
                openAccessConnection()
                Dim cmd As New OleDbCommand("select * from TBL where the_id =" & updId & "", myAccessConnection)
                ' Mark the Command as a Text
                cmd.CommandType = CommandType.Text
                ' Use ExecuteReader if u dont fill/use a DataSet/DataTable - Repeater/Gridview and want to use response.write
                Dim dbreader As OleDbDataReader = cmd.ExecuteReader
                If dbreader.HasRows Then
                    dbreader.Read()
                    upd_thetitle.Text = dbreader.Item("the_title")
                    upd_thetext.Text = dbreader.Item("the_text")
                    upd_thenumber.Text = dbreader.Item("the_number")
                End If
                dbreader.Close()
            Catch exc As Exception
            Finally
                closeAccessConnection()
            End Try
        End If
    End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Change this

Me.Button2.Attributes.Add("onclick", "return confirm('Are you sure you want to update?');")

to

Me.Button2.Attributes.Add("onclick", "var con = confirm('Are you sure you want to update?'); if (con) return true; else window.open('newaddress');")

Author

Commented:
Hi CodeCruiser

WOW works fine, only one thing, it open a new tab/window how can i go to the newaddress in the same tab/window !?