locked
SELECT then INSERT +1 RRS feed

  • Question

  • User192952860 posted

    I have a SELECT statement as follows:

    "SELECT TOP 1 [day].daynumber FROM [day] WHERE [day].entryid =" & CInt(sender.CommandArgument) & " ORDER BY [day].daynumber DESC "

    I then want to INSERT into my Access database the value retrieved + 1.

    How do I do this?

    Tuesday, March 31, 2009 1:20 PM

Answers

  • User192952860 posted

    Solved it.

    Here's my solution if anyone finds it helpful:

    Dim SqlStr As String
            SqlStr = "SELECT TOP 1 [day].daynumber FROM [day] WHERE [day].entryid =" & CInt(sender.CommandArgument) & " ORDER BY [day].dayid DESC "
            Dim ContinueDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0603630.mdb"))
            Dim ContinueCommand As New OleDbCommand(SqlStr, ContinueDataConn)
            ContinueDataConn.Open()
            Dim ContinueReader As OleDbDataReader = ContinueCommand.ExecuteReader()
            If ContinueReader.Read() Then
                Session("daynumber") = ContinueReader("daynumber")
            End If
            ContinueDataConn.Close()

            Dim SqlStr3 As String
            SqlStr3 = "INSERT INTO [day](daynumber, entryid) VALUES (" & Session("daynumber") + 1 & ", " & CInt(sender.CommandArgument) & ")"
            Dim NextDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0603630.mdb"))
            Dim NextCommand As New OleDbCommand(SqlStr3, NextDataConn)
            NextDataConn.Open()
            NextCommand.ExecuteNonQuery()
            NextDataConn.Close()
            GridView4.DataBind()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 1, 2009 6:05 AM

All replies

  • User192952860 posted

    Thought I'd found a solution in  this:

    Dim SqlStr As String
            SqlStr = "SELECT TOP 1 [day].daynumber FROM [day] WHERE [day].entryid =" & CInt(sender.CommandArgument) & " ORDER BY [day].dayid DESC "
            Dim ContinueDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0603630.mdb"))
            Dim ContinueCommand As New OleDbCommand(SqlStr, ContinueDataConn)
            ContinueDataConn.Open()
            Dim ContinueReader As OleDbDataReader = ContinueCommand.ExecuteReader()
            If ContinueReader.Read() Then
                Dim myDayNo = ContinueReader.GetValue(0)
                Dim SqlStr3 As String
                SqlStr3 = "INSERT INTO [day](daynumber, entryid) VALUES (" & myDayNo + 1 & ", " & CInt(sender.CommandArgument) & ")"
                Dim NextDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0603630.mdb"))
                Dim NextCommand As New OleDbCommand(SqlStr3, NextDataConn)
                NextDataConn.Open()
                NextCommand.ExecuteNonQuery()
                NextDataConn.Close()
                GridView4.DataBind()
            End If
            ContinueDataConn.Close()

     

    But it doesn't work.

     Any suggestions?

    Tuesday, March 31, 2009 3:52 PM
  • User192952860 posted

    Solved it.

    Here's my solution if anyone finds it helpful:

    Dim SqlStr As String
            SqlStr = "SELECT TOP 1 [day].daynumber FROM [day] WHERE [day].entryid =" & CInt(sender.CommandArgument) & " ORDER BY [day].dayid DESC "
            Dim ContinueDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0603630.mdb"))
            Dim ContinueCommand As New OleDbCommand(SqlStr, ContinueDataConn)
            ContinueDataConn.Open()
            Dim ContinueReader As OleDbDataReader = ContinueCommand.ExecuteReader()
            If ContinueReader.Read() Then
                Session("daynumber") = ContinueReader("daynumber")
            End If
            ContinueDataConn.Close()

            Dim SqlStr3 As String
            SqlStr3 = "INSERT INTO [day](daynumber, entryid) VALUES (" & Session("daynumber") + 1 & ", " & CInt(sender.CommandArgument) & ")"
            Dim NextDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0603630.mdb"))
            Dim NextCommand As New OleDbCommand(SqlStr3, NextDataConn)
            NextDataConn.Open()
            NextCommand.ExecuteNonQuery()
            NextDataConn.Close()
            GridView4.DataBind()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 1, 2009 6:05 AM
  • User-1199946673 posted

    Your 'solution' probably works, but it could be much simpler and better:

    Dim SqlStr As String = "INSERT INTO [day](daynumber, entryid) SELECT TOP 1 ([day].daynumber + 1), [day].entryid FROM [day] WHERE [day].entryid =? ORDER BY [day].dayid DESC "
    Using Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=|DataDirectory|0603630.mdb"))
      Using Command As New OleDbCommand(SqlStr, Conn)
        Command.Parameters.AddWithValue("entryid", sender.CommandArgument)
        Conn.Open()
        Command.ExecuteNonQuery()
      End Using
    End Using
    GridView4.DataBind() 
    
    

    Maybe you should read some articles, so you understand what the above is doing

    http://www.mikesdotnetting.com/Article.aspx?ArticleID=78

    http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

    http://www.pluralsight.com/community/blogs/fritz/archive/2005/04/28/7834.aspx

     

    Wednesday, April 1, 2009 8:03 AM