Solved

vb.net interface with SQL database 2005,

Posted on 2011-03-19
3
592 Views
Last Modified: 2012-05-11
we are in the process of planning to change our applications that written in VB6 (IDE) to vb.net
One of the questions we have is access data on the SQL.  - we believe the SQL still runs the same
on the new 2008 srv R2.  all the standard query and stored procedures are the same.

but from Vb6, we use the following way to interact with the SQL database on database server.
1. Use ADO recordset.  
   - dim rs_table as recordset
   -   set rs_table = createobject("adodb.recordset")
   -      strsql = "select * from table where ....."
  -      rs_table.open 'conn_db',......
         if rs_table.eof = true a.......
        rs_table.close
       set rs_table = nothing
2.  we use bcp and osq utility on dos command to do some database export/import

The questions is, what will be changed on coding on vb.net?   - I mean very basic 'console'
program that we runs on the background.

Please advice, and if someone can provide us some "sample" vb.net code (console type) will be highly appreciated.   or refer to us some 'book' or reference documents.


0
Comment
Question by:mshox1
3 Comments
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 250 total points
ID: 35171792
hi

use this site as a good reference to VB with sql

http://vyaskn.tripod.com/code.htm
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 250 total points
ID: 35178216
For basic SQL connections where I'm getting results from a stored procedure, I use the following format. I'm assuming you'll import "System.Data.SqlClient" since that makes each of these calls shorter.

Dim ds as New Dataset

Using conn as New SQLConnection(YourConnectionStringHere)
    Dim cmd as New SqlCommand("YourStoredProcName", conn)
    Dim da as New SqlDataAdapter(cmd)
    da.Fill(ds)
End Using

' Now you have your dataset to work with however you want
' This shows a messagebox with the count of rows in your first table
Messagebox.Show(ds.Tables(0).Rows.Count)

Open in new window


The "Using" command ensures that your SQL connection closes when it's done and everything gets cleaned up. You're left with a DataSet with a table in it for each table in  your results - if your stored procedure returned 5 tables, your dataset will have tables 0-4 (note that it's zero-based, so that might cause confusion since VB6 tends to number things starting with one).

Note that if you're only looking for a single value as a return, check out SqlCommand.ExecuteScalar, and if you're not returning any results at all and just want to run a query or stored procedure, you can use SqlCommand.ExecuteNonQuery.
0
 

Author Closing Comment

by:mshox1
ID: 35500198
although this is not the answer to my questions, but I think you everyone's efforts.  I will revisit the questions later.

-- basically,  I am not talking about using store procedures...

I just want to use some very simple query to access 1 table at a time.

thank you for the efforts.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now