Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

vb.net interface with SQL database 2005,

Posted on 2011-03-19
3
Medium Priority
?
607 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 500 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 500 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

916 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