Got a question here and as I am no expert programmer, this should be easy for you gurus. I have this fairly generic code I've created where I return data from an SQL table in a DataList control. I want to take it to the next level and return only the last record in the table, but I am unsure of how to do that. Perhaps I shouldn't even be using a DataList control, I'm not sure.
Basically, I have a form I developed in Visual Studio using ASP.NET VB. I submit the form and now I want to recall the last entry into the database I would have just made and display it on the following page (thank you page).
Here is the code I have:
PublicClass WebForm1
Inherits System.Web.UI.Page
ProtectedWithEvents SqlSelectCommand1As System.Data.SqlClient.SqlCommand
ProtectedWithEvents SqlInsertCommand1As System.Data.SqlClient.SqlCommand
ProtectedWithEvents SqlUpdateCommand1As System.Data.SqlClient.SqlCommand
ProtectedWithEvents SqlDeleteCommand1As System.Data.SqlClient.SqlCommand
ProtectedWithEvents SqlConnection1As System.Data.SqlClient.SqlConnection
ProtectedWithEvents SqlDataAdapter1As System.Data.SqlClient.SqlDataAdapter
ProtectedWithEvents DataSet1As System.Data.DataSet
ProtectedWithEvents DataList1As System.Web.UI.WebControls.DataList
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()>PrivateSub InitializeComponent()
Me.SqlSelectCommand1 =New System.Data.SqlClient.SqlCommand()
Me.SqlInsertCommand1 =New System.Data.SqlClient.SqlCommand()
Me.SqlUpdateCommand1 =New System.Data.SqlClient.SqlCommand()
Me.SqlDeleteCommand1 =New System.Data.SqlClient.SqlCommand()
Me.SqlConnection1 =New System.Data.SqlClient.SqlConnection()
Me.SqlDataAdapter1 =New System.Data.SqlClient.SqlDataAdapter()
Me.DataSet1 =New System.Data.DataSet()
CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM" & _
" authors"
Me.SqlSelectCommand1.Connection =Me.SqlConnection1
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, " & _
"contract) VALUES (@.au_id, @.au_lname, @.au_fname, @.phone, @.address, @.city, @.state," & _
" @.zip, @.contract); SELECT au_id, au_lname, au_fname, phone, address, city, state" & _
", zip, contract FROM authors WHERE (au_id = @.au_id)"
Me.SqlInsertCommand1.Connection =Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))
'
'SqlUpdateCommand1
'
Me.SqlUpdateCommand1.CommandText = "UPDATE authors SET au_id = @.au_id, au_lname = @.au_lname, au_fname = @.au_fname, ph" & _
"one = @.phone, address = @.address, city = @.city, state = @.state, zip = @.zip, cont" & _
"ract = @.contract WHERE (au_id = @.Original_au_id) AND (address = @.Original_addres" & _
"s OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_au" & _
"_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Origi" & _
"nal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (phon" & _
"e = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL AND" & _
" state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS NUL" & _
"L); SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract" & _
" FROM authors WHERE (au_id = @.au_id)"
Me.SqlUpdateCommand1.Connection =Me.SqlConnection1
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))
'
'SqlDeleteCommand1
'
Me.SqlDeleteCommand1.CommandText = "DELETE FROM authors WHERE (au_id = @.Original_au_id) AND (address = @.Original_addr" & _
"ess OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_" & _
"au_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Ori" & _
"ginal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (ph" & _
"one = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL A" & _
"ND state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS N" & _
"ULL)"
Me.SqlDeleteCommand1.Connection =Me.SqlConnection1
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=NDAVENPORT2;initial catalog=pubs;persist security info=False;user id=" & _
"sa;workstation id=NDAVENPORT2;packet size=4096"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.DeleteCommand =Me.SqlDeleteCommand1
Me.SqlDataAdapter1.InsertCommand =Me.SqlInsertCommand1
Me.SqlDataAdapter1.SelectCommand =Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "authors",New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("au_id", "au_id"),New System.Data.Common.DataColumnMapping("au_lname", "au_lname"),New System.Data.Common.DataColumnMapping("au_fname", "au_fname"),New System.Data.Common.DataColumnMapping("phone", "phone"),New System.Data.Common.DataColumnMapping("address", "address"),New System.Data.Common.DataColumnMapping("city", "city"),New System.Data.Common.DataColumnMapping("state", "state"),New System.Data.Common.DataColumnMapping("zip", "zip"),New System.Data.Common.DataColumnMapping("contract", "contract")})})
Me.SqlDataAdapter1.UpdateCommand =Me.SqlUpdateCommand1
'
'DataSet1
'
Me.DataSet1.DataSetName = "NewDataSet"
Me.DataSet1.Locale =New System.Globalization.CultureInfo("en-US")
CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).EndInit()
EndSub
PrivateSub Page_Init(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
EndSub
#EndRegion
PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load
SqlDataAdapter1.Fill(DataSet1)
DataList1.DataSource = DataSet1
DataList1.DataBind()
EndSub
EndClass
Any corrections, thoughts, comments, ideas, or criticism is welcome. Thanks.
Try sorting it usingdesc andtop 1 inselect statement.|||Simple...great! Thanks for redirection.
Found that I could not order descending o_id field in my query. The syntax was fine, but it would not return the rows in descending order. I simply changed it wihin SQL Server itself (design) and returned top 1 and it's very nice now. Thanks!
No comments:
Post a Comment