Running a Query, getting a value, then update record in ASP.net (VB)

Honest, I am really trying to learn this stuff. I've been using Classic ASP for years and just switching over to .net. So far, I'm not having much fun, but I'm trying and I'm not going to quit. One of the small pieces I am struggling with is running a query then, updating the record. Even googling for examples, I having a tough time figuring out how to do something simple like:

    Set objRS = Server.CreateObject ("ADODB.RecordSet")


ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=G:\Inetpub\wwwroot\TestPage\TheData\TestData.mdb;" & _
          "Persist Security Info=False"

SqlStr = "SELECT * " & _
         "FROM Techs " & _
         "WHERE UserID = " & UserID & " "

objrs.Open SqlStr, ConStr, adOpenStatic, adLockOptimistic,adCmdText
If Objrs.recordCount <> 0 Then
   TechUserName = Objrs("TechUserName")
   Objrs.Update
      Objrs("LastLogin") = Now()
   Objrs.Update
Else
   Objrs.AddNew
      Objrs("UserID") = UserID
   Objrs.Update
End If
Objrs.Close

Set objRS = Nothing

Is it even possible? Can 开发者_C百科someone please help me do the above code in ASP.net (VB) or point me to a really good thorough tutorial on how to do this. Thanks in advance.

ah.. first off - you are trying to do classic vb stuff with .net. Scrap it. There are no more cursors. Its client side data you basically get back in a dataset or a data reader (or a single value)

See roughly: http://msdn.microsoft.com/en-us/library/bh8kx08z%28v=VS.100%29.aspx

They miss the spot where they get a connection, which is basically Dim connection as New SqlConnection("server=localhost;uid=username;pwd=whatver;")

make sure you dispose of everything when done connection.Dispose()

once you have your dataset back - just (c# syntax)

foreach(DataRow row in yourDataSet.Tables[0].Rows)
{
   Debug.WriteLine(row["YourFieldName"])
}

For a data reader, see: http://www.developerfusion.com/article/4278/using-adonet-with-sql-server/2/

The difference is a dataset has ALL data loaded on the client side. Quite a bit different than the server side cursor stuff with ado. A DataReader will stream the results as you scroll through them - the overhead of forming this large dataset in memory isn't there so its a bit faster.

hope this gets you started - remember SCRAP the ADO stuff. Its not used anymore.

Woo Hoo I got it!

Dim SqlStr As String
Dim ConStr As String = ConfigurationManager.ConnectionStrings("TCConStr").ConnectionString

SqlStr = "SELECT * " & _
         "FROM TechUsers " & _
         "WHERE TechWWID = " & Chr(34) & TechWWID & Chr(34) & " " & _
         "AND TechEmplNum = " & TechEmplNum & " "

Dim CN As OleDbConnection = New OleDbConnection(ConStr)
CN.Open()
Dim DA As OleDbDataAdapter = New OleDbDataAdapter(SqlStr, CN)
Dim DS As New DataSet
DA.Fill(DS, "TechUsers")
Dim DT As DataTable = DS.Tables("TechUsers")
Dim RecCount As Integer = DT.Rows.Count
Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(DA)

If RecCount = 0 Then
    DA.InsertCommand = CB.GetInsertCommand()
    Dim DR As DataRow = DT.NewRow()
    DR("TechName") = TechName
    DR("TechWWID") = TechWWID
    DR("TechEmplNum") = TechEmplNum
    DR("FirstLogin") = Date.Now()
    DR("LastLogin") = Date.Now()
    DR("LoginCount") = 1
    DT.Rows.Add(DR)
    DA.Update(DS, "TechUsers")
Else
    Dim DR As DataRow = DT.Rows(0)
    Dim LoginCount As Integer = DR("LoginCount")
    TestStuff.InnerHtml = TestStuff.InnerHtml & "<br > " & LoginCount
    DA.UpdateCommand = CB.GetUpdateCommand()
    DR("LastLogin") = Date.Now()
    DR("LoginCount") = LoginCount + 1
    DA.Update(DS, "TechUsers")
End If

CN.Close()

Thanks everyone for the clues to get this done.

Do as NoAlias told you, but watch out not make a false start. Forget about inserting text into your SQL, remember that quotes have to doubled, etc.

Try the parameterized sql statements, like in this sample:

I have a table with 4 colunms, CollCode and CollSeq are the key, TermType and TermText are the modifiable data The code explains how to insert, update or delete a row with parameters instaed if textvalues in the SQL. The code is valid only for ACCESS, SQL SERVER or MYSQL require different code for the template and have different DbTypes

in the first part of the program:

        ' select
    Dim SQLaxSelect As String = "SELECT DISTINCT CollSeq FROM SearchTerms WHERE CollCode = ? ORDER BY CollSeq"
    Dim DRaxSelect As OleDbDataReader = Nothing
    Dim DCaxSelect As OleDbCommand
    Dim axSelP1 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4)
    DCaxSelect = New OleDbCommand(SQLaxSelect, DbConn)
    DCaxSelect.Parameters.Add(axSelP1)


    ' Insert 
    Dim DbConn As New OleDbConnection(SqlProv)
    Dim SQLTwInsert As String = "INSERT INTO SearchTerms (CollCode, CollSeq, TermType, TermText) VALUES (?, ?, ?, ?)"
    Dim DRTwInsert As OleDbDataReader = Nothing
    Dim DCCTwInsert As OleDbCommand
    Dim TwInsP1 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4)
    Dim TwInsP2 As New OleDbParameter("@CollSeq", OleDbType.Integer, 4)
    Dim TwInsP3 As New OleDbParameter("@TermType", OleDbType.VarChar, 4)
    Dim TwInsP4 As New OleDbParameter("@TermText", OleDbType.VarChar, 255)
    DCCTwInsert = New OleDbCommand(SQLTwInsert, DbConn)
    DCCTwInsert.Parameters.Add(TwInsP1)
    DCCTwInsert.Parameters.Add(TwInsP2)
    DCCTwInsert.Parameters.Add(TwInsP3)
    DCCTwInsert.Parameters.Add(TwInsP4)

    ' Delete 
    Dim SQLTwDelete As String = "DELETE FROM SearchTerms WHERE CollCode = ? AND CollSeq = ? AND TermType = ? AND TermText = ?"
    Dim DRTwDelete As OleDbDataReader = Nothing
    Dim DCCTwDelete As OleDbCommand
    Dim TwDelP1 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4)
    Dim TwDelP2 As New OleDbParameter("@CollSeq", OleDbType.Integer, 4)
    Dim TwDelP3 As New OleDbParameter("@TermType", OleDbType.VarChar, 4)
    Dim TwDelP4 As New OleDbParameter("@TermText", OleDbType.VarChar, 255)
    DCCTwDelete = New OleDbCommand(SQLTwDelete, DbConn)
    DCCTwDelete.Parameters.Add(TwDelP1)
    DCCTwDelete.Parameters.Add(TwDelP2)
    DCCTwDelete.Parameters.Add(TwDelP3)
    DCCTwDelete.Parameters.Add(TwDelP4)

    ' Update 
    Dim SQLTwUpdate As String = "UPDATE SearchTerms SET TermType = ?, TermText = ? WHERE CollCode = ? AND CollSeq = ? AND TermType = ? AND TermText = ?"
    Dim DRTwUpdate As OleDbDataReader = Nothing
    Dim DCCTwUpdate As OleDbCommand
    Dim TwUpdP1 As New OleDbParameter("@TermType", OleDbType.VarChar, 4)
    Dim TwUpdP2 As New OleDbParameter("@TermText", OleDbType.VarChar, 255)
    Dim TwUpdP3 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4)
    Dim TwUpdP4 As New OleDbParameter("@CollSeq", OleDbType.Integer, 4)
    Dim TwUpdP5 As New OleDbParameter("@oldTermType", OleDbType.VarChar, 4)
    Dim TwUpdP6 As New OleDbParameter("@oldTermText", OleDbType.VarChar, 255)
    DCCTwUpdate = New OleDbCommand(SQLTwUpdate, DbConn)
    DCCTwUpdate.Parameters.Add(TwUpdP1)
    DCCTwUpdate.Parameters.Add(TwUpdP2)
    DCCTwUpdate.Parameters.Add(TwUpdP3)
    DCCTwUpdate.Parameters.Add(TwUpdP4)
    DCCTwUpdate.Parameters.Add(TwUpdP5)
    DCCTwUpdate.Parameters.Add(TwUpdP6)

in the processing part of the program:

        'select
                    axSelP1.Value = requested key value CollCode
                    Try
                        DRaxSelect = DCaxSelect.ExecuteReader()
                        Do While (DRaxSelect.Read())
                            'get value, first SELECTed value has index 0
                            CollSeq = GetDbIntegerValue(DRaxSelect, 0) ' routine to convert NULL in 0
                        Loop
                    Catch ex As Exception
                        your type of report exception 
                    Finally
                        If Not (DRaxSelect Is Nothing) Then
                            DRaxSelect.Dispose()
                            DRaxSelect.Close()
                        End If
                    End Try

        ' Update  
                    TwUpdP1.Value = new value TermType
                    TwUpdP2.Value = new value TermText
                    TwUpdP3.Value = key value CollCode
                    TwUpdP4.Value = key value CollSeq
                    TwUpdP5.Value = old value TermType to avoid updating a row that 1 millisecond earlier was modified by someone else
                    TwUpdP6.Value = old value TermText
                    Try
                        DRTwUpdate = DCCTwUpdate.ExecuteReader()
                    Catch ex As Exception
                        your type of report exception 
                    Finally
                        If Not (DRTwUpdate Is Nothing) Then
                            DRTwUpdate.Dispose()
                            DRTwUpdate.Close()
                        End If
                    End Try

    ' Insert  
                    TwInsP1.Value = new key value CollCode
                    TwInsP2.Value = new key value CollSeq
                    TwInsP3.Value = value TermType  
                    TwInsP4.Value = value TermText
                    Try
                        DRTwInsert = DCCTwInsert.ExecuteReader()
                    Catch ex As Exception
                        your type of report exception 
                    Finally
                        If Not (DRTwInsert Is Nothing) Then
                            DRTwInsert.Dispose()
                            DRTwInsert.Close()
                        End If
                    End Try
    ' Delete  
                    TwDelP1.Value = key value CollCode
                    TwDelP2.Value = key value CollSeq
                    TwDelP3.Value = old value TermType to avoid deleting a row that 1 millisecond earlier was modified by someone else
                    TwDelP4.Value = old value TermText
                    Try
                        DRTwDelete = DCCTwDelete.ExecuteReader()
                    Catch ex As Exception
                        your type of report exception 
                    Finally
                        If Not (DRTwDelete Is Nothing) Then
                            DRTwDelete.Dispose()
                            DRTwDelete.Close()
                        End If
                    End Try

my routine (in a Module)

    Friend Function GetDbIntegerValue(ByVal Dr As OleDbDataReader, ByVal nr As Integer) As Integer
    If IsDBNull(Dr.Item(nr)) Then
        Return 0
    Else
        Return Dr.GetInt32(nr)
    End If
End Function