Explanation Parameterised Queries
-
Is there anyone who can explain Parameterised Queries. I don't get it where the parameters are related to. How do i write an Updatecommand with parameters, i tried something but it does not work. Dim command As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand("Update(Agent)" & _ " SET Nummer_Naam = @NummerNaam, Agentnummer = @AgentNummer, Agentnaam = @AgentNaam " & _ " WHERE(Agentnummer = @agentNummerOud)") command.Parameters.Add("@NummerNaam", OleDb.OleDbType.VarChar, 20, "NummerNaam") command.Parameters.Add("@AgentNummer", OleDb.OleDbType.Integer, 10, "AgentNummer") command.Parameters.Add("@AgentNaam", OleDb.OleDbType.VarChar, 20, "AgentNaam") Dim parameter As System.Data.OleDb.OleDbParameter = command.Parameters.Add( _ "@agentNummerOud", OleDb.OleDbType.Integer, 10, "AgentNummer") parameter.SourceVersion = DataRowVersion.Original daAgent.UpdateCommand = command daAgent.Update(dsAgent)
-
Is there anyone who can explain Parameterised Queries. I don't get it where the parameters are related to. How do i write an Updatecommand with parameters, i tried something but it does not work. Dim command As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand("Update(Agent)" & _ " SET Nummer_Naam = @NummerNaam, Agentnummer = @AgentNummer, Agentnaam = @AgentNaam " & _ " WHERE(Agentnummer = @agentNummerOud)") command.Parameters.Add("@NummerNaam", OleDb.OleDbType.VarChar, 20, "NummerNaam") command.Parameters.Add("@AgentNummer", OleDb.OleDbType.Integer, 10, "AgentNummer") command.Parameters.Add("@AgentNaam", OleDb.OleDbType.VarChar, 20, "AgentNaam") Dim parameter As System.Data.OleDb.OleDbParameter = command.Parameters.Add( _ "@agentNummerOud", OleDb.OleDbType.Integer, 10, "AgentNummer") parameter.SourceVersion = DataRowVersion.Original daAgent.UpdateCommand = command daAgent.Update(dsAgent)
keninfo wrote:
i tried something but it does not work.
What do you mean it doesn't work? Do you want us to guess? My guess would be that you have not supplied the values for the parameters properly (hint: the last argument in
Parameters.Add). These are literal values. They will be the same every time the application is run. You might want to change these to the values you want the parameters to have.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
keninfo wrote:
i tried something but it does not work.
What do you mean it doesn't work? Do you want us to guess? My guess would be that you have not supplied the values for the parameters properly (hint: the last argument in
Parameters.Add). These are literal values. They will be the same every time the application is run. You might want to change these to the values you want the parameters to have.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
But i don't know what @AgentNaam corresponse with, it should be a parameter, i guess. Can you explain the following command.Parameters.Add("@AgentNaam", OleDb.OleDbType.VarChar, 20, "AgentNaam") I need to update a table with new values, which are inserted via textfields. I don't get the way from textfield inserting till the actual update I know, it seems stupid that i cannot manage this. But i need some help to start.
-
But i don't know what @AgentNaam corresponse with, it should be a parameter, i guess. Can you explain the following command.Parameters.Add("@AgentNaam", OleDb.OleDbType.VarChar, 20, "AgentNaam") I need to update a table with new values, which are inserted via textfields. I don't get the way from textfield inserting till the actual update I know, it seems stupid that i cannot manage this. But i need some help to start.
keninfo wrote:
But i don't know what @AgentNaam corresponse with, it should be a parameter, i guess. Can you explain the following command.Parameters.Add("@AgentNaam", OleDb.OleDbType.VarChar, 20, "AgentNaam")
Did you not read the documentation?
@AgentNaamis your parameter name (think of is as a variable name). When you use@AgentNaamin your SQL you get the value of the parameter. Just like you would a variable in VB.NET The last argument is the value you want the parameter to have. Putting in "AgentNaam" will mean the parameter will always have that value. It is like writing the following in VB.NET:Dim agentNaam As String = "AgentNaam"keninfo wrote:
I need to update a table with new values, which are inserted via textfields. I don't get the way from textfield inserting till the actual update
Where you currently have the literal string values (That's the last argument of the
Addmethod) you should change that to the appropriate values that have come from the text boxes.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
keninfo wrote:
But i don't know what @AgentNaam corresponse with, it should be a parameter, i guess. Can you explain the following command.Parameters.Add("@AgentNaam", OleDb.OleDbType.VarChar, 20, "AgentNaam")
Did you not read the documentation?
@AgentNaamis your parameter name (think of is as a variable name). When you use@AgentNaamin your SQL you get the value of the parameter. Just like you would a variable in VB.NET The last argument is the value you want the parameter to have. Putting in "AgentNaam" will mean the parameter will always have that value. It is like writing the following in VB.NET:Dim agentNaam As String = "AgentNaam"keninfo wrote:
I need to update a table with new values, which are inserted via textfields. I don't get the way from textfield inserting till the actual update
Where you currently have the literal string values (That's the last argument of the
Addmethod) you should change that to the appropriate values that have come from the text boxes.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
Thanks for the reply, Ok, can i use it like this? command.Parameters.Add("@AgentNaam", OleDb.OleDbType.VarChar, 20, txtAgentNummer.Text) If so, why is my table not updated after? parameter.SourceVersion = DataRowVersion.Original daAgent.UpdateCommand = command daAgent.Update(dsAgent)
-
Thanks for the reply, Ok, can i use it like this? command.Parameters.Add("@AgentNaam", OleDb.OleDbType.VarChar, 20, txtAgentNummer.Text) If so, why is my table not updated after? parameter.SourceVersion = DataRowVersion.Original daAgent.UpdateCommand = command daAgent.Update(dsAgent)
Why are you using a data adapter?
command.ExecuteNonQuery()is all you need (and an open connection)
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website