Solved

vb.net interface with SQL database 2005,

Posted on 2011-03-19
3
594 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

785 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