Solved

vb.net interface with SQL database 2005,

Posted on 2011-03-19
3
599 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
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…

738 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