paulhudson

Thankyou! The square brackets worked a treat. I have gotten to using parameters but only when using stored procedures (or saved queries in Access). Is there any benefit in using them otherwise - excepting of course the adage of employing good practice? Thankyou once again.

I have an asp.net page that enables an administrator to reset users passwords. Having entered an unique username and a new password an SQL query is generated. The NEW field in the database is set as Yes/No, if set to Yes then on logon the user is expected to change the password. The following SQL line is generated: UPDATE tbUsers SET new='1', password='xyz' WHERE Username='Under10' But in trying to perform the update the following error occurs: ERROR: Syntax error in UPDATE statement I have tried running the SQL line from a query within the Access database and it works fine. The actual code that generates the SQL line is as follows: sSQL = "UPDATE tbUsers SET new='1', password='" & NewPassword & "' WHERE Username='" & Username & "'" This has me stumped - any suggestions would be greatly appreciated.

I have two functions (GetAllAccounts and GetTeamAccounts) that call another function (GetReader) to pull data from database. See code below. The function GetAllAccounts uses a stored procedure 'spAllAccounts' that does not require parameters. The second function GetTeamAccounts uses stored procedure called 'spTeamAccounts' which requires a parameter. Obviously I could use two separate readers one using the parameter and one without. But I want to save on code. So my question is how, within the GetReader function do I ascertain which function called it? If it is GetTeamAccounts then I can add a parameter (as shown below in red) using IF. Function GetAllAccounts() Dim sSelect As String sSelect = "spAllAccounts" Return GetReader(sSelect) End Function Function GetTeamAccounts(sKey As String) Dim sSelect As String sSelect = "spTeamAccounts" Return GetReader(sSelect) End Function Function GetReader (sSQL As String) As OleDbDataReader Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString")) Dim oCommand As New OleDbCommand(sSQL, oConnect) oCommand.commandType = CommandType.StoredProcedure [color=Red] If ************** then dim oParam as OleDbParameter oParam = oCommand.Parameters.Add("@ParamID", _ OleDbType.Integer) oParam.Direction = ParameterDirection.Input oParam.Value = sKey end if[/color] Try oConnect.Open() Return oCommand.ExecuteReader(CommandBehavior.CloseConnection) Catch oErr As Exception ' be sure to close connection if error occurs If oConnect.State <> ConnectionState.Closed Then oConnect.Close() End If lblErr.Text = oErr.Message & "<p/>" End Try End function Hope someone can help? Thanks.

I am trying to update a database from a dataset but keep getting the following error: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE' on the line 3rd from the bottom of this post = oDataAdapter.Update(tpDataSet, "tbTeamPlayer") The page presents a list of team players from a specific match. The opportunity is given that the list of names can be altered. Players can be deleted, new ones added and existing players modified. Presenting the list works fine using this sub whenever the page is first called: Public sub BindData Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString")) Dim oDataAdapter As New OleDbDataAdapter("spTeamByMatchDataID", oConnect) oDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure oDataAdapter.SelectCommand.Parameters.Add("@ParamID", MatchDataID) Dim UpdateCMD As OleDbCommand = New OleDbCommand("UpdateTeamPlayer", oConnect) oDataAdapter.UpdateCommand = UpdateCMD Dim InsertCMD As OleDbCommand = New OleDbCommand("InsertTeamPlayer", oConnect) oDataAdapter.InsertCommand = InsertCMD Dim DeleteCMD As OleDbCommand = New OleDbCommand("DeleteTeamPlayer", oConnect) oDataAdapter.DeleteCommand = DeleteCMD tpDataSet = New Dataset() oDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey Dim dtTeamPlayers As DataTable = new DataTable("tbTeamPlayer") oDataAdapter.Fill(tpDataSet, "tbTeamPlayer") Dim PrimaryKeyColumns(0) As DataColumn PrimaryKeyColumns(0)= dtTeamPlayers.Columns("TeamPlayerID") dtTeamPlayers.PrimaryKey = PrimaryKeyColumns dgr1.DataSource = tpDataSet.Tables("tbTeamPlayer") dgr1.DataBind() Session("tpDataSet") = tpDataSet Session("dtTeamPlayers") = tpDataSet.Tables("tbTeamPlayer") End Sub The datagrid that presents the data has no problems. It contains a button column for deleting the row and the usual EditCommandColumn. When selecting the edit button it enters the edit mode presenting dropdownlists etc. Changing the data works fine. On selecting the update button the datagrid is binded with the new data displayed. If cancelling then the original data is displayed. The problem comes when trying to update to the source database. I know I have probably got a fundamental problem here. Here is the sub that attempts to update the database: Sub SubmitTeam(sender As Object, e As EventArgs) tpDataSet = Session("tpDataSet") dtTeamPlayers = Session("dtTeamPlayers") 'Validation to ensure player number, name and positions are not duplicated 'now to create the commands to update the database Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString")) Dim oDataAdapter As New OleDbDataAdapter("spTeamByMatchDataID", oConnect) oDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure oDataAdapter.SelectCommand.Parameters.Add("@ParamID", MatchDataID) Dim UpdateCMD As OleDbCommand = New OleDbCommand("UpdateTeamPlayer", oConnect) oDataAdapter.UpdateCommand = UpdateCMD Dim InsertCMD As OleDbCommand = New OleDbCommand("InsertTeamPlayer", oConnect) oDataAdapter.InsertCommand = InsertCMD Dim DeleteCMD As OleDbCommand = New OleDbCommand("DeleteTeamPlayer", oConnect) oDataAdapter.DeleteCommand = DeleteCMD Dim CurrModRow As DataRow Dim ModifiedRow() As DataRow = dtTeamPlayers.Select(Nothing,Nothing,DataViewRowState.ModifiedCurrent) If Not (ModifiedRow.Length < 1 ) Then oDataAdapter.UpdateCommand.Parameters.Add("@TeamPlayerID", OleDbType.Integer, "TeamPlayerID") oDataAdapter.UpdateCommand.Parameters.Add("@PlayerID", OleDbType.Integer, "PlayerID") oDataAdapter.UpdateCommand.Parameters.Add("@TPositionID", OleDbType.Integer, "TPositionID") oDataAdapter.UpdateCommand.Parameters.Add("@PNumber", OleDbType.Integer, "PNumber") oDataAdapter.UpdateCommand.Parameters.Add("@MatchDataID", OleDbType.Integer, "MatchDataID") oDataAdapter.UpdateCommand.Parameters.Add("@Goals", OleDbType.Integer, "Goals") End If Dim CurrNewRow As DataRow Dim NewRow() As DataRow = dtTeamPlayers.Select(Nothing,Nothing,DataViewRowState.Added) If Not (NewRow.Length < 1 ) Then oDataAdapter.InsertCommand.Parameters.Add("@TeamPlayerID", OleDbType.Integer, "TeamPlayerID") oDataAdapter.InsertCommand.Parameters.Add("@PlayerID", OleDbType.Integer, "PlayerID") oDataAdapter.InsertCommand.Parameters.Add("@TPositionID", OleDbType.Integer, "TPositionID") oDataAdapter.InsertCommand.Parameters.Add("@PNumber", OleDbType.Integer, "PNumber") oDataAdapter.InsertCommand.Parameters.Add("@MatchDataID", OleDbType.Integer, "MatchDataID") oDataAdapter.InsertCommand.Parameters.Add("@Goals", OleDbType.Integer, "Goals") End If Dim CurrDelRow As DataRow Dim DeletedRow() As DataRow = dtTeamPlayers.Select(Nothing,Nothing,DataViewRowState.Deleted) If Not (DeletedRow.Length < 1 ) Then Dim oParam As OleDbParameter = oDataAdapter.DeleteCommand.Parameters.Add("@TeamPlayerId", "TeamPlayerID") oParam.SourceVersion = DataRowVersion.Original End If oDataAdapter.Update(tpDataSet, "tbTeamPlayer") End Sub Any help would be greatly appreciated! Thanks Paul

Without the semi-colon it still did not work. Regards

Tate, This is the code with redirectpage: sub Submit(obj as Object, e as EventArgs) Dim RedirectPage As String If tbTeam.text = "Club" then RedirectPage = "ClubAdmin.aspx" Else RedirectPage = "TeamAdmin.aspx" End If Dim sSQL = "select count(*) from tbusers where username='" & tbUsername.text & "' AND password='" & tbPassword.text & "' AND TeamType='" & tbTeam.text & "';" Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString")) Dim oCommand As New OleDbCommand(sSQL, oConnect) Dim Count As Int32 Count = "0" oConnect.Open() Count = Convert.ToInt32(oCommand.ExecuteScalar()) oConnect.Close() Dim LoginCount As Label LoginCount.Text = Count.ToString() If LoginCount.Text = "1" Then Session("Authorized") = "yes" Session("TeamType") = tbTeam.Text Session("Username") = tbUsername.Text Response.redirect(redirectpage) Else lblmessage.text = "Please verify your login information." End If End Sub

I have a logon page which has three textboxes. On clicking submit the following procedure is called. Needless to say it fails. Essentially I am trying to count a number of records that match username / password etc from within an access database. If the count is one then the logon is a success. Perhaps there is a better way of doing this? (PS what tags do you use to distinguish code in posts to this forum?) The problem occurs here: Count = Convert.ToInt32(oCommand.ExecuteScalar()) With the following error message "Data type mismatch in criteria expression" Here is the code: sub Submit(obj as Object, e as EventArgs) Dim RedirectPage As String If tbTeam.text = "Club" then RedirectPage = "ClubAdmin.aspx" Else RedirectPage = "TeamAdmin.aspx" End If Dim sSQL = "select count(*) from tbusers where username='" & tbUsername.text & "' AND password='" & tbPassword.text & "' AND TeamType='" & tbTeam.text & "';" Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString")) Dim oCommand As New OleDbCommand(sSQL, oConnect) Dim Count As Int32 Count = "0" oConnect.Open() Count = Convert.ToInt32(oCommand.ExecuteScalar()) oConnect.Close() Dim LoginCount As Label LoginCount.Text = Count.ToString() If LoginCount.Text = "1" Then Session("Authorized") = "yes" Session("TeamType") = tbTeam.Text Session("Username") = tbUsername.Text Else lblmessage.text = "Please verify your login information." End If End Sub EDIT: added vb tags - see here for code formatting details.

Excellent - just what was missing. It all works now - thanks for all your help kahlua001!

Ok, so I have been a bit dippy on this one. When leaving edit mode of datagrid, I need to extract the new values and modify the relevant row in the dataset. I've got this far, with one slight problem. When I try to find the row using a uniqueID I get an error message: Input string was not in a correct format The column is created thus: tpDataColumn = New DataColumn() tpDataColumn.DataType = System.Type.GetType("System.Int32") tpDataColumn.ColumnName = "UniqueID" tpDataColumn.ReadOnly = True tpDataColumn.Unique = True tpDataColumn.AutoIncrement = True And the find command is (error on last line): Dim KeyID As Integer KeyID = CType(e.Item.FindControl("UniqueID"), Label).Text Dim foundRow As DataRow foundRow = dtTeamPlayers.Rows.Find("KeyID") Could this be something to do with the auto-increment feature?

acceptchanges to dataset I have done as suggested with the page load, and I have posted my update script below. I call the acceptchanges, then refresh the session with the dataset and then bind the datagrid to the same dataset - but no changes are shown. Code: Sub DoItemUpdate(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) tpDataSet.AcceptChanges() Session("tpDataSet") = tpDataSet dgr1.EditItemIndex = -1 dgr1.DataSource = tpDataSet.Tables("TeamPlayers") dgr1.DataBind()

acceptchanges to dataset I have now used session state to hold the dataset. So now the dataset is displayed in edit mode ok. But on selecting update the dataset has no changes applied. No errors occur. Sub DoItemEdit(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs) dgr1.EditItemIndex = CInt(E.Item.ItemIndex) Dim tpDataSet As New DataSet() tpDataSet = Session("tpDataSet") dgr1.DataSource = tpDataSet.Tables("TeamPlayers") dgr1.DataBind() End Sub Sub DoItemUpdate(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) Dim tpDataSet As New DataSet() tpDataSet = Session("tpDataSet") tpDataSet.AcceptChanges() dgr1.DataSource = tpDataSet.Tables("TeamPlayers") dgr1.DataBind() End Sub

kahlua001 The code is based on my previous posts - but I have been butchering it in order to get it working (unsuccessfully). Below is the full code - it is all there! I have a couple of fundamental flaws that I can identify. On first load all goes well, I fill a dataset from db then create a new dataset copying the data from original dataset and add another column. I have done this as a method of testing, don't see the need to keep it. The datagrid is bound to this data and all looks good. Now in the OnEditCommand, if I call the original sub BindData it goes into the edit mode as I expect (problem is the dataset is reloaded from database - so even if accept changes were successful they would be wiped!) If I try to bind the dataset to datagrid outside of the sub that creates dataset, I get the error message as originally posted. I do not understand how I use the dataset after page has been reloaded. I feel like I am missing a fundamental piece of the jigsaw. Hope my confusion makes sense.... Public sKey as string Public MatchDataID as string Public dsTeamPlayer As Dataset Public dsCopy As DataSet Public tpDataSet as Dataset Public Sub Page_Load() If Not Page.IsPostback Then BindData End If End Sub Public sub BindData() MatchDataID = Session("MatchDataID") sKey = Session("TeamID") Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString")) Dim oDR As OleDbDataAdapter = New OleDbDataAdapter oDR.SelectCommand = New OleDbCommand("spTeamLineUp" , oConnect) oDR.SelectCommand.CommandType = CommandType.StoredProcedure Dim oParam As OleDbParameter = oDR.SelectCommand.Parameters.Add("@ParamID", OleDbType.Integer) oParam.Value = sKey oParam.Direction = ParameterDirection.Input dsTeamPlayer = New Dataset() oDR.MissingSchemaAction = MissingSchemaAction.AddWithKey oDR.Fill(dsTeamPlayer, "LastTeamPlayers") NewDataSet End Sub Public Sub NewDataSet Dim dtTeamPlayers As DataTable = new DataTable("TeamPlayers") Dim tpDataColumn As DataColumn Dim tpDataRow As DataRow tpDataColumn = New DataColumn() tpDataColumn.DataType = System.Type.GetType("System.Int32") tpDataColumn.ColumnName = "PNumber" tpDataColumn.ReadOnly = False tpDataColumn.Unique = True tpDataColumn.AutoIncrement = False dtTeamPlayers.Columns.Add(tpDataColumn) tpDataColumn = New DataColumn() tpDataColumn.DataType = System.Type.GetType("System.Int32") tpDataColumn.ColumnName = "PlayerID" tpDataColumn.ReadOnly = False tpDataColumn.Unique = True tpDataColumn.AutoIncrement = False dtTeamPlayers.Columns.Add(tpDataColumn) tpDataColumn = New DataColumn() tpDataColumn.DataType = System.Type.GetType("System.String") tpDataColumn.ColumnName = "PlayerName" tpDataColumn.ReadOnly = False tpDataColumn.Unique = True tpDataColumn.AutoIncrement = False dtTeamPlayers.Columns.Add(tpDataColumn) tpDataColumn = New DataColumn() tpDataColumn.DataType = System.Type.GetType("System.String") tpDataColumn.ColumnName = "TPosition" tpDataColumn.ReadOnly = False tpDataColumn.Unique = True tpDataColumn.AutoIncrement = False dtTeamPlayers.Columns.Add(tpDataColumn) tpDataColumn = New DataColumn() tpDataColumn.DataType = System.Type.GetType("System.Int32") tpDataColumn.ColumnName = "TPositionID" tpDataColumn.ReadOnly = False tpDataColumn.Unique = True tpDataColumn.AutoIncrement = False dtTeamPlayers.Columns.Add(tpDataColumn) tpDataColumn = New DataColumn() tpDataColumn.DataType = System.Type.GetType("System.Int32") tpDataColumn.ColumnName = "Goals" tpDataColumn.ReadOnly = False tpDataColumn.Unique = False tpDataColumn.AutoIncrement = False dtTeamPlayers.Columns.Add(tpDataColumn) dim tpDataSet As DataSet = New DataSet() tpDataSet.Tables.Add(dtTeamPlayers) Dim PrimaryKeyColumns(0) As DataColumn PrimaryKeyColumns(0)= dtTeamPlayers.Columns("PlayerID") dtTeamPlayers.PrimaryKey = PrimaryKeyColumns Dim DataRowCopy as DataRow for each DataRowCopy in dsTeamPlayer.Tables(0).Rows tpDataRow = dtTeamPlayers.NewRow() tpDataRow("PNumber") = DataRowCopy("PNumber") tpDataRow("PlayerID") = DataRowCopy("PlayerID") tpDataRow("PlayerName") = DataRowCopy("PlayerName") tpDataRow("TPosition") = DataRowCopy("TPosition") tpDataRow("TPositionID") = DataRowCopy("TPositionID") tpDataRow("Goals") = "0" dtTeamPlayers.Rows.Add(tpDataRow) next dgr1.DataSource = tpDataSet.Tables("TeamPlayers") dgr1.DataBind() End sub Function GetTeamPositions() Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString")) Dim oCommand As New OleDbCommand("spTeamPositions", oConnect) oCommand.commandType = CommandType.StoredProcedure Try oConnect.Open() Return oCommand.ExecuteReader(CommandBehavior.CloseConnection) Catch oErr As Exception If oConnect.State <> ConnectionState.Closed Then oConnect.Close() End If lblErr.Text = oErr.Message & "<p/>" End Try End function Function GetTeamPlayers() Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString")) Dim oCommand As New OleDbCommand("spTeamPlayerList", oConnect) oCommand.commandType = CommandType.StoredProcedure dim oParam as OleDbParameter oParam = oCommand.Parameters.Add("@ParamID", _ OleDbType.Integer) oParam.Direction = ParameterDirection.Input oParam.Value = sKey Try oConnect.Open() Return oCommand.ExecuteReader(CommandBehavior.CloseConnection) Catch oErr As Exception If oConnect.State <> ConnectionState.Closed Then oConnect.Close() End If lblErr.Text = oErr.Message & "<p/>" End Try End function Sub DoItemEdit(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs) dgr1.EditItemIndex = CInt(E.Item.ItemIndex) BindData ' [color=Red]this displays the dropdownlists/text boxes of edit mode! But it is refreshing the dataset to original state each time![/color] End Sub Sub DoItemUpdate(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) tpDataSet.AcceptChanges() '[color=Red]this is where to obj ref error comes in![/color] dgr1.DataSource = tpDataSet.Tables("TeamPlayers") dgr1.DataBind() End Sub Sub DoItemCancel(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs) dgr1.EditItemIndex = -1 BindData End Sub

error line The error occurs at the line: tpDataSet.AcceptChanges() Unsure how to answer your other questions. I'm fairly recent to ASP.NET and I haven't been using visual studio. If you can advise how to get more information it would be much appreciated! All I can give is the stack trace: [NullReferenceException: Object reference not set to an instance of an object.] ASP.TeamList_aspx.DoItemUpdate(Object sender, DataGridCommandEventArgs e) in c:\admin\teamlist.aspx:186 System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) +111 System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +553 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +31 System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +120 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +31 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +122 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +288 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +5 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +166 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5157

I have a datagrid bound to a dataset. When selecting the onupdatecommand to accept changes I get the error message: "Object reference not set to an instance of an object" The datagrid: <asp:DataGrid id="dgr1" runat="server" DataKeyField="PlayerID" OnEditCommand="DoItemEdit" OnUpdateCommand="DoItemUpdate" OnCancelCommand="DoItemCancel"> Sub DoItemUpdate(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) tpDataSet.AcceptChanges() dgr1.DataSource = dsTeamPlayer.Tables("LastTeamPlayers") dgr1.DataBind() End Sub Any ideas? Any help would make my day!

Object reference not set to an instance of an object This did not make any difference. I think it is related to the sub: Sub DoItemUpdate(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) dsTeamPlayer.AcceptChanges() dgr1.DataSource = dsTeamPlayer.Tables("LastTeamPlayers") dgr1.DataBind() End Sub But I cannot get my head round it. Thanks