Pass a parameter in sql statement with VB 2005...
in [Dot Net]
|
Prev: rundll errors
Next: CD will not eject
From: bill on 20 Jul 2008 12:45 Can this be used with MS access by just changing the connection string or are they only SQL server specific? "Miro" <miro(a)beero.com> wrote in message news:O5O$FQn6IHA.1196(a)TK2MSFTNGP05.phx.gbl... > It is your own variable / parameter holder ( as long as it starts with the > @ ) symbol. > > You can name it @bill > If you have multiple parameters then they all must be unique in the > statement. > > example: Select * from @bla where @bill = @miro > > therefore It would expect me to add 3 parameters via the > cmd.Parameters.Add > > one for @bla, one for @bill and one for @miro > > Miro > > "bill" <bill(a)bottlegarden.com> wrote in message > news:usOac5e6IHA.4468(a)TK2MSFTNGP02.phx.gbl... >> Thank you for your reply. Can you explain to me what this is since it >> doesn't apprear to be an assigned variable name? I haven't seen this >> before. "@fn" >> Thank you! >> Bill >> >> "Miro" <miro(a)beero.com> wrote in message >> news:eiTfevd6IHA.1196(a)TK2MSFTNGP05.phx.gbl... >>>I beleive this is what you are looking for (did some googling)- >>> >>> Take a look at this link: >>> http://www.java2s.com/Code/VB/Database-ADO.net/PassParameterintoSQLcommand.htm >>> >>> and look at the line that says: >>> cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, 10)).Value >>> = "Joe" >>> >>> take note of the @fn which is in the line above: >>> Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee >>> WHERE FirstName = @fn", con) >>> >>> you DO NOT want to do >>> >>> "Select * from Employee where FirstName = " + Text1.Text >>> >>> You might be using a Combo Box. If your combo box is generated by you, >>> then you are ok. But if the user generates the data within the combo >>> box - then be careful.... >>> >>> because of SQL injections. >>> Skim this article: >>> http://www.sitepoint.com/article/sql-injection-attacks-safe ( at page 2 >>> you will see the basic reason ) >>> or by the middle of this article: >>> http://blog.colinmackay.net/archive/2007/06/24/77.aspx >>> >>> basically someone can execute sql within your sql and change your data / >>> bypass your security / delete your data. >>> >>> >>> Hope this helps. >>> >>> Miro >>> >>> >>> >>> "bill" <bill(a)bottlegarden.com> wrote in message >>> news:uau6HNd6IHA.3512(a)TK2MSFTNGP02.phx.gbl... >>>> Can someone please show me an example of passing a string value into an >>>> sql statement in vb 2005? Something like this is what I'm after: >>>> Dim sqlButton1 As String = "Select * from tblAssets where Asset_Tag = >>>> Me.cboAsset.Text" >>>> >>>> Thank you, >>>> >>>> Bill >>>> >>>> >>> >> >> >
From: bill on 20 Jul 2008 13:17 Ya, I need adodb so this probably won't work with an access database right? I've been using dataTables up until now. "bill" <bill(a)bottlegarden.com> wrote in message news:%23PMUYgo6IHA.2336(a)TK2MSFTNGP03.phx.gbl... > Can this be used with MS access by just changing the connection string or > are they only SQL server specific? > > "Miro" <miro(a)beero.com> wrote in message > news:O5O$FQn6IHA.1196(a)TK2MSFTNGP05.phx.gbl... >> It is your own variable / parameter holder ( as long as it starts with >> the @ ) symbol. >> >> You can name it @bill >> If you have multiple parameters then they all must be unique in the >> statement. >> >> example: Select * from @bla where @bill = @miro >> >> therefore It would expect me to add 3 parameters via the >> cmd.Parameters.Add >> >> one for @bla, one for @bill and one for @miro >> >> Miro >> >> "bill" <bill(a)bottlegarden.com> wrote in message >> news:usOac5e6IHA.4468(a)TK2MSFTNGP02.phx.gbl... >>> Thank you for your reply. Can you explain to me what this is since it >>> doesn't apprear to be an assigned variable name? I haven't seen this >>> before. "@fn" >>> Thank you! >>> Bill >>> >>> "Miro" <miro(a)beero.com> wrote in message >>> news:eiTfevd6IHA.1196(a)TK2MSFTNGP05.phx.gbl... >>>>I beleive this is what you are looking for (did some googling)- >>>> >>>> Take a look at this link: >>>> http://www.java2s.com/Code/VB/Database-ADO.net/PassParameterintoSQLcommand.htm >>>> >>>> and look at the line that says: >>>> cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, >>>> 10)).Value = "Joe" >>>> >>>> take note of the @fn which is in the line above: >>>> Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee >>>> WHERE FirstName = @fn", con) >>>> >>>> you DO NOT want to do >>>> >>>> "Select * from Employee where FirstName = " + Text1.Text >>>> >>>> You might be using a Combo Box. If your combo box is generated by you, >>>> then you are ok. But if the user generates the data within the combo >>>> box - then be careful.... >>>> >>>> because of SQL injections. >>>> Skim this article: >>>> http://www.sitepoint.com/article/sql-injection-attacks-safe ( at page >>>> 2 you will see the basic reason ) >>>> or by the middle of this article: >>>> http://blog.colinmackay.net/archive/2007/06/24/77.aspx >>>> >>>> basically someone can execute sql within your sql and change your data >>>> / bypass your security / delete your data. >>>> >>>> >>>> Hope this helps. >>>> >>>> Miro >>>> >>>> >>>> >>>> "bill" <bill(a)bottlegarden.com> wrote in message >>>> news:uau6HNd6IHA.3512(a)TK2MSFTNGP02.phx.gbl... >>>>> Can someone please show me an example of passing a string value into >>>>> an sql statement in vb 2005? Something like this is what I'm after: >>>>> Dim sqlButton1 As String = "Select * from tblAssets where Asset_Tag = >>>>> Me.cboAsset.Text" >>>>> >>>>> Thank you, >>>>> >>>>> Bill >>>>> >>>>> >>>> >>> >>> >> > >
From: bill on 20 Jul 2008 14:26 I'm thinking something like this but I get stuck: Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;" & "data source=c:\_Archive\Documentation - Projects\Hardware Tracking - 2008\IT_Assets.mdb") Dim cmd As New OleDb.OleDbCommand("SELECT FirstName, LastName FROM Employee WHERE FirstName = @fn", Con) cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, 10)).Value = "Joe" "bill" <bill(a)bottlegarden.com> wrote in message news:uXB4Iyo6IHA.1200(a)TK2MSFTNGP04.phx.gbl... > Ya, I need adodb so this probably won't work with an access database > right? I've been using dataTables up until now. > > "bill" <bill(a)bottlegarden.com> wrote in message > news:%23PMUYgo6IHA.2336(a)TK2MSFTNGP03.phx.gbl... >> Can this be used with MS access by just changing the connection string or >> are they only SQL server specific? >> >> "Miro" <miro(a)beero.com> wrote in message >> news:O5O$FQn6IHA.1196(a)TK2MSFTNGP05.phx.gbl... >>> It is your own variable / parameter holder ( as long as it starts with >>> the @ ) symbol. >>> >>> You can name it @bill >>> If you have multiple parameters then they all must be unique in the >>> statement. >>> >>> example: Select * from @bla where @bill = @miro >>> >>> therefore It would expect me to add 3 parameters via the >>> cmd.Parameters.Add >>> >>> one for @bla, one for @bill and one for @miro >>> >>> Miro >>> >>> "bill" <bill(a)bottlegarden.com> wrote in message >>> news:usOac5e6IHA.4468(a)TK2MSFTNGP02.phx.gbl... >>>> Thank you for your reply. Can you explain to me what this is since it >>>> doesn't apprear to be an assigned variable name? I haven't seen this >>>> before. "@fn" >>>> Thank you! >>>> Bill >>>> >>>> "Miro" <miro(a)beero.com> wrote in message >>>> news:eiTfevd6IHA.1196(a)TK2MSFTNGP05.phx.gbl... >>>>>I beleive this is what you are looking for (did some googling)- >>>>> >>>>> Take a look at this link: >>>>> http://www.java2s.com/Code/VB/Database-ADO.net/PassParameterintoSQLcommand.htm >>>>> >>>>> and look at the line that says: >>>>> cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, >>>>> 10)).Value = "Joe" >>>>> >>>>> take note of the @fn which is in the line above: >>>>> Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee >>>>> WHERE FirstName = @fn", con) >>>>> >>>>> you DO NOT want to do >>>>> >>>>> "Select * from Employee where FirstName = " + Text1.Text >>>>> >>>>> You might be using a Combo Box. If your combo box is generated by >>>>> you, then you are ok. But if the user generates the data within the >>>>> combo box - then be careful.... >>>>> >>>>> because of SQL injections. >>>>> Skim this article: >>>>> http://www.sitepoint.com/article/sql-injection-attacks-safe ( at page >>>>> 2 you will see the basic reason ) >>>>> or by the middle of this article: >>>>> http://blog.colinmackay.net/archive/2007/06/24/77.aspx >>>>> >>>>> basically someone can execute sql within your sql and change your data >>>>> / bypass your security / delete your data. >>>>> >>>>> >>>>> Hope this helps. >>>>> >>>>> Miro >>>>> >>>>> >>>>> >>>>> "bill" <bill(a)bottlegarden.com> wrote in message >>>>> news:uau6HNd6IHA.3512(a)TK2MSFTNGP02.phx.gbl... >>>>>> Can someone please show me an example of passing a string value into >>>>>> an sql statement in vb 2005? Something like this is what I'm after: >>>>>> Dim sqlButton1 As String = "Select * from tblAssets where Asset_Tag = >>>>>> Me.cboAsset.Text" >>>>>> >>>>>> Thank you, >>>>>> >>>>>> Bill >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> > >
From: Miro on 20 Jul 2008 14:36 I have only been reading up on Sql Express - sorry I have no experience with ..net and access tables. but yes I do beleive you can do parameters for access. I dont see why you would not be as that would be a pretty big hole if you could not for security reasons. http://www.vbdotnetforums.com/showthread.php?t=36 and http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx Miro "bill" <bill(a)bottlegarden.com> wrote in message news:uXB4Iyo6IHA.1200(a)TK2MSFTNGP04.phx.gbl... > Ya, I need adodb so this probably won't work with an access database > right? I've been using dataTables up until now. > > "bill" <bill(a)bottlegarden.com> wrote in message > news:%23PMUYgo6IHA.2336(a)TK2MSFTNGP03.phx.gbl... >> Can this be used with MS access by just changing the connection string or >> are they only SQL server specific? >> >> "Miro" <miro(a)beero.com> wrote in message >> news:O5O$FQn6IHA.1196(a)TK2MSFTNGP05.phx.gbl... >>> It is your own variable / parameter holder ( as long as it starts with >>> the @ ) symbol. >>> >>> You can name it @bill >>> If you have multiple parameters then they all must be unique in the >>> statement. >>> >>> example: Select * from @bla where @bill = @miro >>> >>> therefore It would expect me to add 3 parameters via the >>> cmd.Parameters.Add >>> >>> one for @bla, one for @bill and one for @miro >>> >>> Miro >>> >>> "bill" <bill(a)bottlegarden.com> wrote in message >>> news:usOac5e6IHA.4468(a)TK2MSFTNGP02.phx.gbl... >>>> Thank you for your reply. Can you explain to me what this is since it >>>> doesn't apprear to be an assigned variable name? I haven't seen this >>>> before. "@fn" >>>> Thank you! >>>> Bill >>>> >>>> "Miro" <miro(a)beero.com> wrote in message >>>> news:eiTfevd6IHA.1196(a)TK2MSFTNGP05.phx.gbl... >>>>>I beleive this is what you are looking for (did some googling)- >>>>> >>>>> Take a look at this link: >>>>> http://www.java2s.com/Code/VB/Database-ADO.net/PassParameterintoSQLcommand.htm >>>>> >>>>> and look at the line that says: >>>>> cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, >>>>> 10)).Value = "Joe" >>>>> >>>>> take note of the @fn which is in the line above: >>>>> Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee >>>>> WHERE FirstName = @fn", con) >>>>> >>>>> you DO NOT want to do >>>>> >>>>> "Select * from Employee where FirstName = " + Text1.Text >>>>> >>>>> You might be using a Combo Box. If your combo box is generated by >>>>> you, then you are ok. But if the user generates the data within the >>>>> combo box - then be careful.... >>>>> >>>>> because of SQL injections. >>>>> Skim this article: >>>>> http://www.sitepoint.com/article/sql-injection-attacks-safe ( at page >>>>> 2 you will see the basic reason ) >>>>> or by the middle of this article: >>>>> http://blog.colinmackay.net/archive/2007/06/24/77.aspx >>>>> >>>>> basically someone can execute sql within your sql and change your data >>>>> / bypass your security / delete your data. >>>>> >>>>> >>>>> Hope this helps. >>>>> >>>>> Miro >>>>> >>>>> >>>>> >>>>> "bill" <bill(a)bottlegarden.com> wrote in message >>>>> news:uau6HNd6IHA.3512(a)TK2MSFTNGP02.phx.gbl... >>>>>> Can someone please show me an example of passing a string value into >>>>>> an sql statement in vb 2005? Something like this is what I'm after: >>>>>> Dim sqlButton1 As String = "Select * from tblAssets where Asset_Tag = >>>>>> Me.cboAsset.Text" >>>>>> >>>>>> Thank you, >>>>>> >>>>>> Bill >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> > >
From: Jack Jackson on 20 Jul 2008 17:31
When using an OleDbCommand you should not use a SqlParmeter, as that is for SQL Server. Use OleParameter instead. How are you stuck? On Sun, 20 Jul 2008 12:26:41 -0600, "bill" <bill(a)bottlegarden.com> wrote: >I'm thinking something like this but I get stuck: >Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;" & >"data source=c:\_Archive\Documentation - Projects\Hardware Tracking - >2008\IT_Assets.mdb") > >Dim cmd As New OleDb.OleDbCommand("SELECT FirstName, LastName FROM Employee >WHERE FirstName = @fn", Con) > >cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, 10)).Value = >"Joe" > >"bill" <bill(a)bottlegarden.com> wrote in message >news:uXB4Iyo6IHA.1200(a)TK2MSFTNGP04.phx.gbl... >> Ya, I need adodb so this probably won't work with an access database >> right? I've been using dataTables up until now. >> >> "bill" <bill(a)bottlegarden.com> wrote in message >> news:%23PMUYgo6IHA.2336(a)TK2MSFTNGP03.phx.gbl... >>> Can this be used with MS access by just changing the connection string or >>> are they only SQL server specific? >>> >>> "Miro" <miro(a)beero.com> wrote in message >>> news:O5O$FQn6IHA.1196(a)TK2MSFTNGP05.phx.gbl... >>>> It is your own variable / parameter holder ( as long as it starts with >>>> the @ ) symbol. >>>> >>>> You can name it @bill >>>> If you have multiple parameters then they all must be unique in the >>>> statement. >>>> >>>> example: Select * from @bla where @bill = @miro >>>> >>>> therefore It would expect me to add 3 parameters via the >>>> cmd.Parameters.Add >>>> >>>> one for @bla, one for @bill and one for @miro >>>> >>>> Miro >>>> >>>> "bill" <bill(a)bottlegarden.com> wrote in message >>>> news:usOac5e6IHA.4468(a)TK2MSFTNGP02.phx.gbl... >>>>> Thank you for your reply. Can you explain to me what this is since it >>>>> doesn't apprear to be an assigned variable name? I haven't seen this >>>>> before. "@fn" >>>>> Thank you! >>>>> Bill >>>>> >>>>> "Miro" <miro(a)beero.com> wrote in message >>>>> news:eiTfevd6IHA.1196(a)TK2MSFTNGP05.phx.gbl... >>>>>>I beleive this is what you are looking for (did some googling)- >>>>>> >>>>>> Take a look at this link: >>>>>> http://www.java2s.com/Code/VB/Database-ADO.net/PassParameterintoSQLcommand.htm >>>>>> >>>>>> and look at the line that says: >>>>>> cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, >>>>>> 10)).Value = "Joe" >>>>>> >>>>>> take note of the @fn which is in the line above: >>>>>> Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee >>>>>> WHERE FirstName = @fn", con) >>>>>> >>>>>> you DO NOT want to do >>>>>> >>>>>> "Select * from Employee where FirstName = " + Text1.Text >>>>>> >>>>>> You might be using a Combo Box. If your combo box is generated by >>>>>> you, then you are ok. But if the user generates the data within the >>>>>> combo box - then be careful.... >>>>>> >>>>>> because of SQL injections. >>>>>> Skim this article: >>>>>> http://www.sitepoint.com/article/sql-injection-attacks-safe ( at page >>>>>> 2 you will see the basic reason ) >>>>>> or by the middle of this article: >>>>>> http://blog.colinmackay.net/archive/2007/06/24/77.aspx >>>>>> >>>>>> basically someone can execute sql within your sql and change your data >>>>>> / bypass your security / delete your data. >>>>>> >>>>>> >>>>>> Hope this helps. >>>>>> >>>>>> Miro >>>>>> >>>>>> >>>>>> >>>>>> "bill" <bill(a)bottlegarden.com> wrote in message >>>>>> news:uau6HNd6IHA.3512(a)TK2MSFTNGP02.phx.gbl... >>>>>>> Can someone please show me an example of passing a string value into >>>>>>> an sql statement in vb 2005? Something like this is what I'm after: >>>>>>> Dim sqlButton1 As String = "Select * from tblAssets where Asset_Tag = >>>>>>> Me.cboAsset.Text" >>>>>>> >>>>>>> Thank you, >>>>>>> >>>>>>> Bill >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> >> > |