I am trying to pull a single item out of the database programaticlly, take the value of that item and set it to a varible so I can work with it in other areas.

Here is basically what I have this is on the vb page in a sub

Dim connString As String = _
ConfigurationManager.ConnectionStrings("ConnectioString").ConnectionString
'Create a SqlConnection instance
Using myConnection As New SqlConnection(connString)
'Specify the SQL query
Const sql As String = "SELECT Price FROM Plans Where PlainID='" & PlanId & "'"
'Create a SqlCommand instance
Dim myCommand As New SqlCommand(sql, myConnection)
'Get back a DataSet
Dim myDataSet As New DataSet
'Create a SqlDataAdapter instance
Dim ReadData As New SqlDataReader(myCommand)
 
'Bind the DataSet to the GridView
MonthlyPrice = ReadData("Price")
'Close the connection
myConnection.Close()
End Using

I just want to get the price of the plan where planID is equal to the value of my varible planID. I want to set that equal to MonthlyPrice which is a decimal so I can add it into some calculations and return a value. I don't really want to bind the data to anything but everytime I search google I can't find anything excpet for binding data to grideview and things like that.

I have tried several diffrent approaches that I have found out there but have gotten errors or and things I am not understanding.

I originally was trying to access a datasource I already have defined that is on the aspx page.

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT * FROM [Plans] WHERE ([PlanID] = @PlanID2)">

<SelectParameters>

<asp:FormParameter FormField="grSelectPlan" Name="PlanID2" Type="Int32" />

</SelectParameters>

</asp:SqlDataSource>

 Is there a way for me to grab this datasource which works and is tied to a detailed view, take the value in the price collom and stick it in the MonthlyPrice varible on my vb page? That is the approach that seems the most logical to me.

If someone has better way I would really like to know.

Thanks

Hi

May i know this in more clearly in easy language .......Explained clearly what you want to do? 

To bring back a single data item, you can use ExecuteScalar. If you can produce a table definition script, I can produce some sample code for you this evening. (after19:00 hrs UTC) 

I think every thing seems to be fine except the databind.There is no need to bind the data.Here is solution for u r code 

Dim connString As String = _
ConfigurationManager.ConnectionStrings("ConnectioString").ConnectionString
'Create a SqlConnection instance
Using myConnection As New SqlConnection(connString)
'Specify the SQL query
Const sql As String = "SELECT Price FROM Plans Where PlainID='" & PlanId & "'"
'Create a SqlCommand instance
Dim myCommand As New SqlCommand(sql, myConnection)
Dim ReadData As New SqlDataReader
try
   myConnection.open
 ReadData=cmd.ExecuteDatareader
 ReadData.Read 
dim  MonthlyPrice as integer --datatype as u r requirement
 MonthlyPrice = ReadData("Price")
--if u want use the variable in remaining part of application u can  store this one in sessions
 Session("MonthlyPrice")=MonthlyPrice
or
 Session("MonthlyPrice") = ReadData("Price")
 
myConnection.Close()

 Catc ex as Exception

 lblmessage.text=ex.message 

con.close 

End Try

Please correct me if i m wrong.
 

In using your code I get to errors:

1. On the line that has Dim ReadData As New SqlDataReader

ReadData says has no constructors and is underlined in blue

then on the line that has ReadData = cmd.ExcuteDatareader

cmd is underlined in blue and says it is not declared.

When I try and run it, it returns with build errors.

Do I need to import a Namespace or something??
 

srijaya_ramaraju@yahoo.com:

I think every thing seems to be fine except the databind.There is no need to bind the data.Here is solution for u r code 

Dim connString As String = _
ConfigurationManager.ConnectionStrings("ConnectioString").ConnectionString
'Create a SqlConnection instance
Using myConnection As New SqlConnection(connString)
'Specify the SQL query
Const sql As String = "SELECT Price FROM Plans Where PlainID='" & PlanId & "'"
'Create a SqlCommand instance
Dim myCommand As New SqlCommand(sql, myConnection)
Dim ReadData As New SqlDataReader
try
   myConnection.open
 ReadData=cmd.ExecuteDatareader
 ReadData.Read 
dim  MonthlyPrice as integer --datatype as u r requirement
 MonthlyPrice = ReadData("Price")
--if u want use the variable in remaining part of application u can  store this one in sessions
 Session("MonthlyPrice")=MonthlyPrice
or
 Session("MonthlyPrice") = ReadData("Price")
 
myConnection.Close()

 Catc ex as Exception

 lblmessage.text=ex.message 

con.close 

End Try

Please correct me if i m wrong.
 

 I am interested in your concept and would like examples if possible. I am going to be working with this database a lot through out the application and would like to see as many examples as I can of how to pull out data and so on. If you find the time to post a few examples I would greatly appreciate it.

TATWORTH:

To bring back a single data item, you can use ExecuteScalar. If you can produce a table definition script, I can produce some sample code for you this evening. (after19:00 hrs UTC) 

 Is Linq to SQL something I should look into for doing this type of work? I found some tutorials on it when doing some searches and don't know if I should go through them. If it will help with my current situation I'll be all over it.

Thanks 

 > I can of how to pull out data and so on. If you find the time to post a few examples I would greatly appreciate it.

If you post a table definition script (select one of your table in SSMS Object explorer, right click, select Script table, create to, new query window and copy the out put to this thread. 

Hi

In srijaya_ramaraju@yahoo.com code replace the cmd to myCommand  and use the following line also

insted of this

 Dim ReadData As new SqlDataReader

use this

Dim ReadData As SqlDataReader 

Instead of this

ReadData=cmd.ExecuteDatareader

use this

 ReadData=myCommand.ExecuteDatareader

I am sure the above code works for you......... 

Sorry replace cmd with myCommand. Still if u get the errors plz post the error message.

 As I offered earlier, if you post a table definition script (select one of your table in SSMS Object explorer, right click, select Script table, create to, new query window and copy the out put to this thread, I will write some TSQL and wrapper code in either C# or VB.NET.

TATWORTH:

 As I offered earlier, if you post a table definition script (select one of your table in SSMS Object explorer, right click, select Script table, create to, new query window and copy the out put to this thread, I will write some TSQL and wrapper code in either C# or VB.NET.

I don't understand what you are asking. The only explorers I know ar the server explorer, and solutions explorer. I am not sure what the SSMS is?

I found a tutorial on datasets and .xsd documents and I followed that and got what I needed to work. Excpet it is a whole diffrent approach and it is going to make it easier fo me on every page to connect and run the queries I want. So my problem is solved. Thank you, everyone for your input. Tatworth if you do want to post back about the TSQL I am still interested in it, since I don't know what it is. It may help me down the road. I also answered my questino on Linq I can't use it in the way I saw done on version 2.0 of the .net framework. But if I get to go to 3.5 I will proboly be studying that too.

Thanks again for everyone's help. 

 SSMS is SQL Server Management Studio - a Database tool using the Visual Studio Shell used by designing, developing and maintaining SQL Server 2005 (and when released SQL2008). It replaced Enterprise Manager in SQL2000 and before.

TSQL - short for Transact SQL and is the SQL used by Sybase and Microsoft SQL. Up to SQL2000 they were essentially the same however since then the extensions of 2000, 2005 and 2008 means they are different dialects.