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