|
 |  |  |  |  |  |  |
| Welcome to Windows Vista Forums. Our forum is dedicated to helping you find solutions with any problems, errors or issues you are experiencing with Windows Vista. The Vista forum also covers news and updates and has an extensive Windows Vista tutorial section that covers a wide range of tips and tricks. |
| |||||||
 |
| |
07-19-2008
| #1 (permalink) |
| | Pass a parameter in sql statement with VB 2005... thank you thats a great place for me to start to learn this! Bill "Jack Jackson" <jjackson@xxxxxx> wrote in message news:if8884trv2n16shsbioa9j50silgb2fqqt@xxxxxx Quote: > On Sun, 20 Jul 2008 17:33:50 -0600, "bill" <bill@xxxxxx> > wrote: > Quote: >>I'm not sure what this line means: >>'Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1)) > Console.WriteLine takes a format string. See String.Format for more > information. The numbers in braces are parameter numbers. In the > code above, {0} is replaced by the next parameter, reader.GetString(0) > (the value of column 0) and {1} by reader.GetString(1) (the value of > column 1). > Quote: >>So to assign say a dataview grid to this set of records is it just >> >>Me.DataGridView1.DataSource = reader because I don't get anything back >>with >>that? > No. A DataReader supplies one row each time. For more information on > this see > <http://msdn.microsoft.com/en-us/library/haa3afyz(VS.71).aspx>. > > The DataGridView.DataSource property takes some kind of list. You > must iterate through the DataReader and populate some kind of list. A > good one to use is BindingList(Of T), as that supplies a lot of > functionality that is useful when binding controls to a list. > > However since you are just getting started with VB .NET, it might be > easier for you to create a DataSet from the DataReader using a > DataAdapter, and bind the DataGridView to the DataSet's DataTable. > Here is some information about this > <http://msdn.microsoft.com/en-us/library/bh8kx08z.aspx> > > By using Google you should be able to find more examples. > > Also, it is probably not necessary to specify the data type on the > Parameters.Add call. I know it is not necessary with SQL Server, but > I'm not sure about OleDB. You probably can just use: > > Cmd.Parameters.Add(New OleDb.OleDbParameter("@fn", "Joe")) > > Quote: >>"bill" <bill@xxxxxx> wrote in message >>news:u9ZxCBs6IHA.1192@xxxxxx Quote: >>>I think this is it: >>> 'Dim Con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial >>> Catalog=MyDatabase;Integrated Security=SSPI") >>> >>> 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 SqlCommand("SELECT FirstName, LastName FROM Employee >>> WHERE >>> FirstName = @fn", con) >>> >>> Dim Cmd As New OleDb.OleDbCommand("SELECT * from tblAssets where >>> asset_tag >>> = @fn", Con) >>> >>> 'cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, >>> 10)).Value >>> = "Joe" >>> >>> Cmd.Parameters.Add(New OleDb.OleDbParameter("@fn", >>> OleDb.OleDbType.VarChar, 30)).Value = "Joe" >>> >>> Try >>> >>> Con.Open() >>> >>> Dim reader As OleDb.OleDbDataReader = Cmd.ExecuteReader() >>> >>> While reader.Read() >>> >>> Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1)) >>> >>> End While >>> >>> reader.Close() >>> >>> Finally >>> >>> Con.Close() >>> >>> End Try >>> >>> >>> >>> Does that look correct? >>> >>> "Jack Jackson" <jjackson@xxxxxx> wrote in message >>> news:dgb784dlfelrapu0dkqo50k7jd01sbeidf@xxxxxx >>>> 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@xxxxxx> >>>> 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@xxxxxx> wrote in message >>>>>news:uXB4Iyo6IHA.1200@xxxxxx >>>>>> 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@xxxxxx> wrote in message >>>>>> news:%23PMUYgo6IHA.2336@xxxxxx >>>>>>> Can this be used with MS access by just changing the connection >>>>>>> string >>>>>>> or >>>>>>> are they only SQL server specific? >>>>>>> >>>>>>> "Miro" <miro@xxxxxx> wrote in message >>>>>>> news:O5O$FQn6IHA.1196@xxxxxx >>>>>>>> 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@xxxxxx> wrote in message >>>>>>>> news:usOac5e6IHA.4468@xxxxxx >>>>>>>>> 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@xxxxxx> wrote in message >>>>>>>>> news:eiTfevd6IHA.1196@xxxxxx >>>>>>>>>>I beleive this is what you are looking for (did some googling)- >>>>>>>>>> >>>>>>>>>> Take a look at this link: >>>>>>>>>> http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message >>>>>>>>>> news:uau6HNd6IHA.3512@xxxxxx >>>>>>>>>>> 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 >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>> >>> |
My System Specs |
|
07-19-2008
| #2 (permalink) |
| | Re: Pass a parameter in sql statement with VB 2005... I beleive this is what you are looking for (did some googling)- Take a look at this link: http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message news:uau6HNd6IHA.3512@xxxxxx Quote: > 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 > > |
| My System Specs |
|
07-19-2008
| #3 (permalink) |
| | Re: Pass a parameter in sql statement with VB 2005... 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@xxxxxx> wrote in message news:eiTfevd6IHA.1196@xxxxxx Quote: >I beleive this is what you are looking for (did some googling)- > > Take a look at this link: > http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message > news:uau6HNd6IHA.3512@xxxxxx Quote: >> 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 >> >> |
| My System Specs |
|
07-20-2008
| #4 (permalink) |
| | Re: Pass a parameter in sql statement with VB 2005... 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@xxxxxx> wrote in message news:usOac5e6IHA.4468@xxxxxx Quote: > 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@xxxxxx> wrote in message > news:eiTfevd6IHA.1196@xxxxxx Quote: >>I beleive this is what you are looking for (did some googling)- >> >> Take a look at this link: >> http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message >> news:uau6HNd6IHA.3512@xxxxxx Quote: >>> 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 >>> >>> > |
| My System Specs |
|
07-20-2008
| #5 (permalink) |
| | Re: Pass a parameter in sql statement with VB 2005... Excellent! Thank you very much for taking the time to explain. I didn't see it declared and was thinking maybe it was some kind of new built in function. Thank you! Bill "Miro" <miro@xxxxxx> wrote in message news:O5O$FQn6IHA.1196@xxxxxx Quote: > 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@xxxxxx> wrote in message > news:usOac5e6IHA.4468@xxxxxx Quote: >> 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@xxxxxx> wrote in message >> news:eiTfevd6IHA.1196@xxxxxx Quote: >>>I beleive this is what you are looking for (did some googling)- >>> >>> Take a look at this link: >>> http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message >>> news:uau6HNd6IHA.3512@xxxxxx >>>> 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 >>>> >>>> >>> >> |
| My System Specs |
|
07-20-2008
| #6 (permalink) |
| | Re: Pass a parameter in sql statement with VB 2005... Can this be used with MS access by just changing the connection string or are they only SQL server specific? "Miro" <miro@xxxxxx> wrote in message news:O5O$FQn6IHA.1196@xxxxxx Quote: > 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@xxxxxx> wrote in message > news:usOac5e6IHA.4468@xxxxxx Quote: >> 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@xxxxxx> wrote in message >> news:eiTfevd6IHA.1196@xxxxxx Quote: >>>I beleive this is what you are looking for (did some googling)- >>> >>> Take a look at this link: >>> http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message >>> news:uau6HNd6IHA.3512@xxxxxx >>>> 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 >>>> >>>> >>> >> |
| My System Specs |
|
07-20-2008
| #7 (permalink) |
| | Re: Pass a parameter in sql statement with VB 2005... 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@xxxxxx> wrote in message news:%23PMUYgo6IHA.2336@xxxxxx Quote: > Can this be used with MS access by just changing the connection string or > are they only SQL server specific? > > "Miro" <miro@xxxxxx> wrote in message > news:O5O$FQn6IHA.1196@xxxxxx Quote: >> 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@xxxxxx> wrote in message >> news:usOac5e6IHA.4468@xxxxxx Quote: >>> 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@xxxxxx> wrote in message >>> news:eiTfevd6IHA.1196@xxxxxx >>>>I beleive this is what you are looking for (did some googling)- >>>> >>>> Take a look at this link: >>>> http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message >>>> news:uau6HNd6IHA.3512@xxxxxx >>>>> 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 >>>>> >>>>> >>>> >>> >>> > |
| My System Specs |
|
07-20-2008
| #8 (permalink) |
| | Re: Pass a parameter in sql statement with VB 2005... 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@xxxxxx> wrote in message news:uXB4Iyo6IHA.1200@xxxxxx Quote: > 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@xxxxxx> wrote in message > news:%23PMUYgo6IHA.2336@xxxxxx Quote: >> Can this be used with MS access by just changing the connection string or >> are they only SQL server specific? >> >> "Miro" <miro@xxxxxx> wrote in message >> news:O5O$FQn6IHA.1196@xxxxxx Quote: >>> 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@xxxxxx> wrote in message >>> news:usOac5e6IHA.4468@xxxxxx >>>> 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@xxxxxx> wrote in message >>>> news:eiTfevd6IHA.1196@xxxxxx >>>>>I beleive this is what you are looking for (did some googling)- >>>>> >>>>> Take a look at this link: >>>>> http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message >>>>> news:uau6HNd6IHA.3512@xxxxxx >>>>>> 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 >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> > |
| My System Specs |
|
07-20-2008
| #9 (permalink) |
| | Re: Pass a parameter in sql statement with VB 2005... 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/libr...parameter.aspx Miro "bill" <bill@xxxxxx> wrote in message news:uXB4Iyo6IHA.1200@xxxxxx Quote: > 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@xxxxxx> wrote in message > news:%23PMUYgo6IHA.2336@xxxxxx Quote: >> Can this be used with MS access by just changing the connection string or >> are they only SQL server specific? >> >> "Miro" <miro@xxxxxx> wrote in message >> news:O5O$FQn6IHA.1196@xxxxxx Quote: >>> 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@xxxxxx> wrote in message >>> news:usOac5e6IHA.4468@xxxxxx >>>> 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@xxxxxx> wrote in message >>>> news:eiTfevd6IHA.1196@xxxxxx >>>>>I beleive this is what you are looking for (did some googling)- >>>>> >>>>> Take a look at this link: >>>>> http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message >>>>> news:uau6HNd6IHA.3512@xxxxxx >>>>>> 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 >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> > |
| My System Specs |
|
07-20-2008
| #10 (permalink) |
| | Re: Pass a parameter in sql statement with VB 2005... 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@xxxxxx> wrote: Quote: >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@xxxxxx> wrote in message >news:uXB4Iyo6IHA.1200@xxxxxx Quote: >> 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@xxxxxx> wrote in message >> news:%23PMUYgo6IHA.2336@xxxxxx Quote: >>> Can this be used with MS access by just changing the connection string or >>> are they only SQL server specific? >>> >>> "Miro" <miro@xxxxxx> wrote in message >>> news:O5O$FQn6IHA.1196@xxxxxx >>>> 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@xxxxxx> wrote in message >>>> news:usOac5e6IHA.4468@xxxxxx >>>>> 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@xxxxxx> wrote in message >>>>> news:eiTfevd6IHA.1196@xxxxxx >>>>>>I beleive this is what you are looking for (did some googling)- >>>>>> >>>>>> Take a look at this link: >>>>>> http://www.java2s.com/Code/VB/Databa...SQLcommand.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...n-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@xxxxxx> wrote in message >>>>>> news:uau6HNd6IHA.3512@xxxxxx >>>>>>> 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 >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> |
| My System Specs |
|
| Thread Tools | |
| |
Similar Threads | ||||
| Thread | Forum | |||
| Possible to pass the empty parameter to a child from Powershell? | PowerShell | |||
| How to pass exceptions message outside the trap statement | PowerShell | |||
| how to pass a parameter to XMLHttp | PowerShell | |||
| How to best control parameter attributes and parameter parsing in your own scripts? | PowerShell | |||
| vista and msmoney 2005 - importing bank statement | Vista General | |||
