?
Solved

vb.net interface with SQL database 2005,

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how the fundamental information of how to create a table.
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.

719 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