attn: vb.net folks

Discussion in 'OT Technology' started by thewise1, Feb 7, 2004.

  1. thewise1

    thewise1 Guest

    Ok, so I finally sort of figured out ADO.net a little.

    I have figured out essentially how it works:

    SQL Database server < - > SQLConnection < - > SQLDataAdapter < - > DataSet

    Simple enough. I can query for information just fine, manipulate the data, change the data in the dataset, but what I haven't been able to figure out is how to update it.

    I have no problem looking up the specific methods/properties - I'm not asking for a complete novel here. However, since I can't really find any documentation that just makes this simple, I'm wondering if I can kinda get the psuedo-code process that I need to go through to update this.

    From my understanding, I need to make a SqlCommand, and set the updatecommand property to the proper UPDATE string, but I keep seeing references to situations where they preface the database field names with the "@" character, and then add a parameter to the SqlCommand for each one.

    All of this makes sense to me, except I'm not sure how to feed it the information to update. Does it simply scan the dataset for information that is changed, and update that? :confused:

    I know I need to buy a book, and I'm going to do so tomorrow, but I would really like to grasp this basic crap tonight :x:
     
  2. VBGOD

    VBGOD Guest

  3. VBGOD

    VBGOD Guest

    As for your question....

    Are you using VB or C#?
     
  4. Poonany

    Poonany Guest

    I'm not a programmer, so forgive me if this is really n00b: If I'm JUST starting out with sql (I know how to install it, but thats about it), where do you recommend I start? Any reading material you think I should look at?
     
  5. VBGOD

    VBGOD Guest

    Basically, you have to look at the UpdateCommand. Can you post some of your code?
     
  6. VBGOD

    VBGOD Guest

    SQL... The best way to learn is by doing. Create some tables, select the data you want, filter that data, update it, delete it, LOOP.

    What are you using? SQL Server? MS Access?
     
  7. thewise1

    thewise1 Guest

    Wow, that's very informative :cool:

    So from what I gather, if I want to query a database for a bunch of specific records, do something with them, and then update them to flag them as dealt with, I would want to do something like this then:

    Create my SqlConnection
    Create my SqlDataAdapter
    Set my updatecommand/deletecommand/insertcommands
    Create a Dataset, fill it via the DataAdapter with the proper query
    Loop through each item in the DataTable that is created in the DataSet and execute the appropriate task
    Update each item, during the loop, to reflect that they have been dealt with
    execute a SqlDataAdapter.update(DataSet) <-- as long as the updatecommand is created properly, this should simply update everything that was changed?

    Thanks for the awesome help man :)
     
  8. Poonany

    Poonany Guest

    Sql server 2000 std and enterprise. I know how to install and set up sql extracts, thats about it. Was looking at some books, but they went over my head (again, I know jack about programming).
     
  9. VBGOD

    VBGOD Guest

    Not to make it sound too simple, but that is basically it. :cool:


    That all sounds like textbook ADO.NET
    Although, don't be surprised if you have to make some slight changes. This is always the case.
     
  10. thewise1

    thewise1 Guest

    :rofl: my code is a horrible mess simply because I've been trying different things to figure it out, but here's what I've got:

    Code:
            
    Dim Dataset1 As New DataSet()
    Dim mySelectCommand As SqlCommand = New SqlCommand("select * from customerDB", SqlConnection2)
    Dim mySqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(mySelectCommand)
    
    
    mySqlDataAdapter.UpdateCommand = New SqlCommand("UPDATE CustomerDB SET LastName = @LastName WHERE CustID = @CustID", SqlConnection2)
    
    mySqlDataAdapter.UpdateCommand.Parameters.Add("@LastName", (not sure of type here), 128, "LastName")
    
    Dim workParm As SqlParameter = mySqlDataAdapter.UpdateCommand.Parameters.Add("@CustID", SqlDbType.Int)
    workParm.SourceColumn = "CustID"
    workParm.SourceVersion = DataRowVersion.Original
    
    mySqlDataAdapter.Fill(Dataset1, "CustomerDB")
    
    Dataset1.Tables(0).Rows(0).Item(2) = txtNewName.Text
    
    DataGrid1.DataSource = Dataset1
    DataGrid1.SetDataBinding(Dataset1, "CustomerDB")
    
    mySqlDataAdapter.Update(Dataset1, "CustomerDB")
    IB you laugh at how inept I am at this :o
     
  11. VBGOD

    VBGOD Guest

    Play around with the SQL Query Analyzer.

    From the main menu within the SQL Server Enterprise Manager. Look at the top menu and go to Tools >> SQL Query Analyzer

    From there, you can do just about anything in that text editor. Things such as

    Code:
    create database test
    You'll get something like
    The CREATE DATABASE process is allocating 0.63 MB on disk 'test'.
    The CREATE DATABASE process is allocating 0.49 MB on disk 'test_log'.


    This is the hardware to do things. Keep in mind, you can do things this way, and/or through the visual design within SQL Server Enterprise Manager.
     
  12. thewise1

    thewise1 Guest

    :eek3: it worked :o
     
  13. thewise1

    thewise1 Guest

    VBGOD, you're my hero :coold:
     
  14. Poonany

    Poonany Guest

    Thank you. I'll play around with it this weekend. Mind if I email you directly if I have any questions? :x:
     
  15. VBGOD

    VBGOD Guest

    I'm just going to correct the things that I see are not correct. For starters, change the following

    mySqlDataAdapter.UpdateCommand.Parameters.Add("@LastName", (not sure of type here), 128, "LastName")

    TO this

    mySqlDataAdapter.UpdateCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 128)

    Holy shit, I can't believe I memorized that. :o :rofl: Basically, it really needs the SQL Datatype.
     
  16. VBGOD

    VBGOD Guest


    :cool:
     
  17. thewise1

    thewise1 Guest

    Yeah, the datatype was ntext; I forgot what I used and had to fire up remote desktop to check it :o
     
  18. thewise1

    thewise1 Guest

    Seriously, thank you so much though - I've been struggling with this for a day and a half now :ugh: :eek4:

    Thankfully, it makes sense to me now
     
  19. VBGOD

    VBGOD Guest


    Anytime. ADO.NET is a weird thing. That's why I created an entire section about it. I don't know if you've ever used ADO (Classic ADO), but the new ADO.NET has a lot more to it. Classic ADO was super, super simple. I kind of miss that, but have to look forward and embrace ADO.NET :hs: :o
     
  20. thewise1

    thewise1 Guest

    Actually I never really got the chance to deal with ADO; I used DAO back in the day with VB6, but I haven't coded my own app in a good 3 years (except gay little parsers or whatever at MS) so things have changed a lot since then.

    Between this and multithreading, my knowledge has increased exponentially in the past week :o
     
  21. VBGOD

    VBGOD Guest


    Ha ha ha! DAO, even simpler! Data1.DatabaseName="blah" Data1.RecordSource="Select Blah" Data1.Refresh!!! I loved it! I used DAO for about 2 1/2 before switching over to ADO.
     
  22. thewise1

    thewise1 Guest

    :werd:

    It was so simple back in the day! :o

    Although really, ADO.net doesn't seem that complicated. Just a new way of thinking - and definitely pretty powerful :eek3:
     
  23. VBGOD

    VBGOD Guest

    So very true. I freaked out when I first starting using databases with my new VB.NET apps - it was totally different. I actually imported the old ADODB.dll file at first. After a few days, I read something about ADO.NET :o Things have been totally different since then. I really wouldn't call myself an ADO.NET expert, but I'm now totally comfortable with it. As you said, it is sooo just a new/different way of looking at things.
     
  24. VBGOD

    VBGOD Guest

    Oh, and don't forget to close the Connection (DataReaders if used too) when you're done. :o
     

Share This Page