New In .Net But Learning SqlConnection, SqlCommand And SqlDataReader How In Code_behind
Feb 6, 2007
Hi.
I want to now a little about CodeBehind how to use it with SqlConnection, SqlCommand and SqlDataReader.
How i use:
Web.Config
<connectionStrings>
<add name="ConnectionStringTest" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|DSNbase.mdb;Persist Security Info=True" providerName="System.Data.OleDb"/>
</connectionStrings>
Default.aspx
<asp:Repeater ID="test" runat="server" DataSourceID="SqlDataSourcetest">
...
..
<asp:SqlDataSource ID="SqlDataSourcetest" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>"
ProviderName="<%$ ConnectionStrings:ConnectionStringTest.ProviderName %>"
SelectCommand="SELECT [ID], [Name], [Sex], [Born], [Color], [Dog], [Images] FROM [Dogs] WHERE ([Dog] = 'YES') ORDER BY [ID]">
</asp:SqlDataSource>
How can i take my asp:SqlDataSource and put it in my CodeBehind, so it use the ConnectionString from my Web.Config and so i can use my Repeater on my default.aspx site.. !?
Maybe if u got a great link that show "How to do" maybe om MSDN/MSDN2.
Im just curious, can anyone explain what exactly the methods do within the objects (sqlconnection, sqlCommand, SqlDataReader) . and why it takes three objects to carry out a connection. Php only used one object I believe.
But most of all i would like to know how they work. like sqlCommand carrys the connection (sqlcommand.connection) while sql connection retrieves the address connection (sqlconnection(connectionstring)). what does Sqlconnection do once it gets this address. and what does it give sqlcommand when its ready to take out the query and put it in the reader once the sqlconnection.open() to make it simple. let me put it this way sqlConnection con = new SqlConnection(_connectionstring); // server address comes through here. along with security and database name what exactly does the object con do ? it runs through the constructor and does what ? SqlCommand cmd = new SqlCommand(); cmd.Connection = con; //So now what ever the SqlConnection did with the _connectionstring. it now passes it through the connection property. the connection property does something with value of con. what is it ?? cmd.CommandText = 'select, ID, Name, Price.....' // I think i know what that does con.Open() // I know that the connection opens here. but what exactly does this method do to make it open ???????? sqlDataReader reader = cmd.ExecuteReader(); im some what confused with this one. sqldataReader reader was never declared and its not even a static method. What exactly is it ????? cmd.ExecuteReader() now takes the property connection which did something with the con value. then it used the commandtext property to request the query. it takes con.open does something and now its execute reader. Im a lil confused last but not least, con.close() what does that do ?? its alot but can anyone fill me in. or should i say fill(me); in lol, get it. never mind.
Hi,When using the following controls....System.Data.SqlClient.SqlConnection System.Data.SqlClient.SqlCommandIf I want to change my SQL command and execute the query once again what cleanup do I need to do first?Do I need close and dispose the SqlConnection?Do I need to dispose the SqlCommand?Can I use the SqlConnection for more than one SqlCommand?Thanks,Scott
Hi,I'm new to ASP.NET, and am currently looking into XML.I'm trying to write XML using data from an SQL Server 2000 table. But I seem to be getting the following error regarding the SQL Server connection:Compiler Error Message: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid argumentsSource Error:Line 23: { Line 24: SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); Line 25: mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString); Line 26: mySqlDataAdapter.Fill(myDataSet); Line 27: return myDataSet;Source File: c:InetpubwwwrootmappingcreateGeoRSSFile.aspx.cs Line: 25 This is my code:using System; using System.Data; using System.Data.SqlClient ; using System.Configuration; using System.Collections; using System.Text; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Xml;
public partial class createGeoRSSFile : System.Web.UI.Page { protected void Page_Load(object sender, DataSet myDataSet, EventArgs e) { string connString = "server=SQLSERV1;database=Historical_Statistics;UID=dbuser;PWD=Password"; string queryString = "SELECT Town, PostCode, Latitude, Longitude FROM UKPostCodes";
using (SqlConnection mySqlConnection = new SqlConnection(connString)) { SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString); mySqlDataAdapter.Fill(myDataSet); return myDataSet; }
// Create a new XmlTextWriter instance XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.Unicode);
Hi, not sure if this is the right place to put this but as its the beginners forum I thought I'd take a chance.
What's becoming more evident to me is my weak SQL skills. I'd like some advice on the best place to learn properly. It's starting to become a part of my role and i'm struggling a bit.
Should I go on a course? I have a number of teach yourself books and an evaluation version of the software but is this enough? I've worked through the books but struggle to apply the book to the real situation i.e subqueries
How did the more proficient of you learn SQL?
I want to get to a stage where I can help others with their SQL problems rather than always being the one helped.
I do not work with SQL but I do have good computer knowledge and experience of MS Access and visual basic. Would it be plausible for me to complete such a creditation without the work experience or is it asking too much? I have a lot of time to focus on it at the minute. Does anyone have any knowledge of this particular company or can you recommend a better one?
Also, is there any way I could complete this course on a Mac (pre intel)?
I am thinking about doing a computer course.It is learning about sqlto become an MCDBA.I am trying to find out how difficult this could beas I will be doing it from home and it is going to cost over £4000 forall the CD ROM's,books etc.You understand I don't want to start thecourse only to find I can't do it.I have owned my own computer for 18months and enjoy it.If anyone with experience can reply it would beappreciated.
I started a course and got to the lab section. It asked for a password giving me the name of student. I don't know if I was given a password when subscribing for the course. Certainly no in the confirmation email.
I have four questions about Tsql language: 1. Is it important for a web developer to learn Tsql ,if he uses Data driven sites or just learn (select ,update,insert,order by ...I mean the most popular statements) ? 2. Where to learn Tsql ,is there any free books ,videos,tutorials,articles ,websites (ofcourse sql server 2005 with it's enhanced Tsql syntax? 3. Can I use a Tsql generator tool or Builder instead of learning the language ? 4. Where I can find such a tool?
If you do not know all the answers ,tell me what you know ,I will appreciate all the answers,and plz need help from the moderators also. Thanks
I'm used to programming in PHP and need helping learning how to do SQL stuff in VB (using Visual Studio Pro, if that makes a difference).
For instance, in PHP I might do something like this: //some code to connect to the database $result = mysql_query("SELECT * FROM `table_name` WHERE `some_field` = true") OR die("ERROR: ".mysql_error()); while ($row = mysql_fetch_assoc($result)) { extract($row); //do what needs to be done with the variables}
How can I do that in Visual Basic? This is what I have so far and that's only from cutting and pasting from some websites. 1 Using connection As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") 2 connection.Open() 3 Dim command As SqlCommand = New SqlCommand("SELECT * FROM GE_Data", connection) 4 'now how do I get to the data??? 5 6 connection.Close() 7 End Using
I'm looking for a book or a couple of books to learn SQL (in general aswell as various versions from different vendors). A couple of books that would take me from beginner through advanced topics. Any good suggestions?
Hi, I am new to this forum. I have a question about learning SQL. I never really had much of a reason to learn anything dealing with server programs. I now would like to learn about SQL. Is there a program that anyone knows about that can help me learn?
Well, as a VB/VBA applications developer I'm not well prepared for this, but it looks like I will be riding herd on a production SQL Server.
TSQL I know well enough to get along, but where can I get a fast fix on all the logins, security, and process management info? Today we had a DTS package crash overnight and it took me forever to figure out that it had left half a dozen tables locked. (Note that the scripts for the DTS package are being re-written as we speak with use of transactions and NOLOCK.) Meanwhile tech support was handling a whole mess of grumpy users.
Are there any books you would recommend as resources/references? Is there a particular author who is good at writing the stuff you really need to know in English that can be read by a mere mortal like I? I am fond of the Microsoft resources/help files but I'd like to have somthing that holds highlighter and post-it flags a bit better. Not to mention something that focuses more on the beast as a whole rather than the minutia at length.
Hi,I have been trying to register for E-Learning for MS-SQL2005 ... but IE aborts any time before the registration is finished ... (after sign-up, or information fill up, or before those steps): "Microsoft Internet Explorer a rencontré un problème et doit fermer. Nous vous prions de nous excuser pour le désagrément encouru.". I don't believe this working as designed despite this pb shows me that Microsoft has still progressed to make in quality ... (my computer is running W2K and is up to date with Windows Update).Any idea to access knowledge courses on internet for MS-SQL2005 are welcome :) Jean-Marc
I discovered that many of my very busy colleagues are having anextremely difficult time pulling themselves away to take vitaltraining when it requires being away for consecutive days. I justcompleted training for .NET in Chicago through a mentored learningprogram that personally helped me to tackle that same issue. Thoughthe mentored learning training allows complete interaction withclassroom version labs and interaction with an on staff expert on thetopic, it does require some discipline in that I was responsible forthe pace I was moving. The benefit for me is that I did not have tocommit myself to a set schedule of consecutive days beinginaccessible. I had the flexibility to engage in the mentoredlearning program once a week until I completed the course. Plus thementored learning lab was very comfortable and for some of mycolleagues who would are able to travel, it is near O'Hare Airport andthere is a hotel next door.Once I get more help in our data center, I do plan on spending sometime, and some of our firm's money on some instructor-led classes thatwill require me to be away for consecutive days and I can get done alot quicker. This particular training centers line-up of classes andits personnel impressed me. I thought I would alert the rest of youto this attractive alternative and would be happy to provide you thedetails on a need to know basis either through this discussion groupor you can send me an email to Join Bytes!.
Hi group.I have been working with ColdFusion and SQL Server for some timenow....abour 4 years I guess. I have developed various web applicationssuccessfully. The scenario I am in is that a company would like me to comeon with them as a lead database admin type guy. I would still do a little CFdevelopment....but mostly it will be data management with SQL Server.Now....like I said...I have worked with databases for quite awhile...specifically SQL Server, and I am quite comfortable with it......butIm kinda worried that the position involves a lot more than I have done.Mind you I will have first hand knowledge of the database in place which iswhat this position is for....so I have somewhat of an advantage. But....Imjust wondering if I should do a short course that teaches me the ins andouts again. It's like I develop databases by winging it. Know what I mean. Ithink I know what I am doing...but what if I don't? Just wondering what yourthoughts are.
Can somebody tell me if the microsoft time series (MTS) algorithm use the unknown p approach in order to learn an ART model, cited in Autoregressive Tree Models for Time-series Analysis by Meek, Chickering and Heckerman ?
If the answer is true, what is the pmax that the MTS algorithm uses?. Is there a parameter to control this value?
If the answer is false, what approach MTS algorithm uses in order to learn the structure of a ART(p)?
Hi guys. I'm having trouble declaring an sqlcommand. What I want to do is declare a global sqlcommand and I would want this sqlcommand to vary depending on the conditions on my page_load. Here's the code....
Dim p_s_syounin2 As New SqlCommand Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then p_s_syounin2 = ("INSERT INTO (p_s_syounin2) SELECT syain_hnm FROM TR_syainID WHERE TR_syainID.syozokubu_id=20 AND TR_syainID.syozokuka_id=21 AND TR_syainID.kaikyuu_id=23, cnn") '''' THIS DOES NOT WORK! End If End Sub p_s_syounin2 .ExecuteNonQuery()
What is the correct way of declaring p_s_syounin2? Thanks.
Hi, I am new to SSIS. 1) I want to create a SSIS package to execute 5 stored procedures one by one. Stored procedures has parameters. 2) I want to create a SSIS package for ETL. To extract data from different tables from a database and insert into a new table in the same database on a monthly basis. How do i do these two? Is there a good material i can go through to achieve these tasks?
I have Oracle 9i personal edition on my laptop which I use to learn Oracle/SQL including creating and quering tables. With this I have been able to go into jobs confident with being able to query Enterprise implementations of Oracle in a commercial environment.
Now I would like to learn Microsoft/SQL and I was wondering whether Microsoft's SQL Server 2005 Express will be enough for me to learn MS/SQL like Oracle personal edition has.
I'm very interested in learning SQL and SQL Server 2000. I am of above average intelligence, but have no prior programming experience. I am curious as to how long this may take to learn/establish "the basics". I apologize in advance if my question comes off as being foolish.
I've read that you're able to successfully use SQL Server 2000 without prior SQL experience. Is this true?
Ultimately, what would anyone suggest in terms of how to begin the learning (self-teaching) process? What are some good, recommended resources I may use either on-line or purchase, i.e. video tutorials, books, software, etc, etc. Any info or assistance would be greatly appreciated. I've run across an SQL Server 2000 tutorial video online and am considering purchasing it. The demo clips seemed well organized.
I appreciate any advice, suggestions, and recommendations.
I'm pretty familiar with SQL syntax. I am interesting in learning more of admin information.
I've got a lot to learn here and would like some discussion and/or links to helpful sites.
One of the most important things: I'm trying to understand how the heirachry of groups and users work better. I noticed it is integerated with our active directory from windows server 2000. Is this just becuase of I choose intergrated security when I installed this?
I have many other questions but I would like to try and understand one thing at a time before moving on, so somebody encourage this discussion with me!
Hi, Can any body tell me how can I learn about Sql. I am now a days learning asp.net using C#. bUT can not under stand how to work with database. Can any body tell me the online book that is easy to learn (Examples etc ).
I am using the book SQL Server 2005 Analysis Services by Reed Jacobson and Stacia Misner Step by Step.
For building My First Cube, in the Cube Wizard, when the Radio button is checked for "Build the cube without using a data source", the Checkbox for "Use a cube template" is dimmed.
How do you remove the dimming so that the database "Adventure Works Standard Edition" can be entered for providing the template.
I find two files of Adventure Works in Visual Studio 8Microsoft.NET Framework... and three more Adventure Works files in in Visual Studio 8SDK....
Somehow the Adventure Works is not in the right path?
I am a retailer whose software back-end has just been upgraded from Accessto SQL Server 2000. (The front-end is an independently-written shop Point OfSale system)While I know a fair bit about computers, I know nothing about SQL Server.And frankly, I don't want to know much. I don't need to build any newdatabases, program SQL, or "be an administrator". All I need to know aboutis how to go into the raw data like I did in Access and change that data.(The front-end software has some limitations, and I have found it easier inthe past to go into the dB through Access and search-and-replace, andgenerally edit the data as though I was in Excel).What is the easiest and quickest way to learn the skills I need for doingthe same thing under SQL Server 2000?Is there a particular book or site that you could recommend?Is there in fact a tool in existence that mimics Excel in the editing of SQLdata??
Hello, I am experimenting with indexes and hope people can shed lighton some of my problems.I am using SLQ 2000 on Win 2000 Server. Using the following query fordiscussion;--------------------------------SELECT TOP 1000000E.EUN_Numeric, -- Primary KeyE.EUN_CODE, -- VarCharE.[timestamp] --,--E.Model -- Computed column (substring of EUN_CODE)FROM dbo.Z1_EUNCHK E--WHERE E.[timestamp]DATEADD ( wk , -48, getdate() ) AND-- E.[timestamp]< DATEADD ( wk , -4, getdate() )ORDER BY E.[timestamp] DESC-----------------------------------Problem 1) If I set up a single Index on the TimeStamp (plus the PK onEUN_Numeric) then there is not improvement in performance.It is only when I set up an Index on the Timestamp,EUN_Numeric,EUN_Codethen I get a good improvement. This is also thecase with the "where" clause added. I am using query analyser. Theimprovement is 14 secs to 3 secs (mainly with the removal of the sortprocess)Why?My expectation is that if my query uses [timestamp] column then surelyan index only on this is adequate.Problem 2) Introducing the simple computed column into the query takesthe time to 15 secs (with Sort processes involved).Why does revert back to sorting process when previous the index wasused ?Regards JC......
How can I practice the use of SQL server without changing mygirlfriends laptop to XP Pro (which anyway I dont have)?Using (unfortunately) Windows XP Home not Pro, Visual Studio 2005.Trying to learn SQL server with asp, javascript, vbsrcipt and HTML-new to this.Headbutted the wall for a few hours trying to install SQL Serverproperly. The long and short is no IIS with Win XP Home so doesnt seemto work properly. I did manage to connect to Northwinds and publs DBso can use them to learn but I need to be able to ceate own database.Ie 3 tables.For the moment I have created a table in Northwind which I will try tofill with datafrom a notepad file I have...Dan
