SELECT then INSERT +1

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?

Answer 1

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?

Answer 2

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

Answer 3

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