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 19:25 This is what I've got so far...thank you. I get stuck on the line before the "Try" line. Cmd.Parameters.Add(New oldDB.oldDBParameter("@fn",oledb.?????????? I think the rest is fine? 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 * from tblAssets where asset_tag = @fn", Con) Cmd.Parameters.Add(New OleDb.OleDbParameter("@fn",oledb.oel) 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 "Jack Jackson" <jjackson(a)cinnovations.net> wrote in message news:dgb784dlfelrapu0dkqo50k7jd01sbeidf(a)4ax.com... > 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 >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>> >>> >>
From: bill on 20 Jul 2008 19:28 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(a)cinnovations.net> wrote in message news:dgb784dlfelrapu0dkqo50k7jd01sbeidf(a)4ax.com... > 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 >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>> >>> >>
From: bill on 20 Jul 2008 19:33 I'm not sure what this line means: 'Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1)) 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? "bill" <bill(a)bottlegarden.com> wrote in message news:u9ZxCBs6IHA.1192(a)TK2MSFTNGP05.phx.gbl... >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(a)cinnovations.net> wrote in message > news:dgb784dlfelrapu0dkqo50k7jd01sbeidf(a)4ax.com... >> 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 >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> > >
From: Jack Jackson on 21 Jul 2008 02:03 On Sun, 20 Jul 2008 17:33:50 -0600, "bill" <bill(a)bottlegarden.com> wrote: >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). >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")) >"bill" <bill(a)bottlegarden.com> wrote in message >news:u9ZxCBs6IHA.1192(a)TK2MSFTNGP05.phx.gbl... >>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(a)cinnovations.net> wrote in message >> news:dgb784dlfelrapu0dkqo50k7jd01sbeidf(a)4ax.com... >>> 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 >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >> >> >
From: bill on 21 Jul 2008 03:31
thank you thats a great place for me to start to learn this! Bill "Jack Jackson" <jjackson(a)cinnovations.net> wrote in message news:if8884trv2n16shsbioa9j50silgb2fqqt(a)4ax.com... > On Sun, 20 Jul 2008 17:33:50 -0600, "bill" <bill(a)bottlegarden.com> > wrote: > >>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). > >>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")) > > >>"bill" <bill(a)bottlegarden.com> wrote in message >>news:u9ZxCBs6IHA.1192(a)TK2MSFTNGP05.phx.gbl... >>>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(a)cinnovations.net> wrote in message >>> news:dgb784dlfelrapu0dkqo50k7jd01sbeidf(a)4ax.com... >>>> 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 >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>> >>> >> |