the steps you followed for creating the DSN are absolutely correct. but there is no need to create that. it is always recommended that you use a DSN-Less connection. It becomes more faster.
ok...here is a sample connection snippet for you. please note that this code uses DSN-Less connection.
just goto Project->References and add Microsoft Activex Data Objects Library into your project.
<strong>''code for making a connection with the sql server database</strong>
Private Sub Form_Load()
Dim gcn as New ADODB.Connection
gcn.connectionstring="Provider=SQLOLEDB.1;Persist Security Info=False;<strong>User ID=bs</strong>;<strong>Initial Catalog=BILLING_SYSTEM</strong>"
gcn.open
msgbox "A connection to the database is now established"
End Sub
<strong>''code for retrieving some rows from a table</strong>
Private sub Command1_Click()
Dim rs as New ADODB.Recordset
Dim str as String
Dim li as ListItem
str="select * from branch_details order by branch_id"
rs.open str,gcn,1,2
if rs.Recordcount>0 then
rs.MoveFirst
While not rs.EOF()
with Listview1
set li=.listitems.add(,,(branch_id))
li.subitems(1)=rs!branch_name
li.subitems(2)=rs!address
end with
rs.MoveNext
Wend
else
msgbox "No record found." & vbcrlf & "Please add some records."
end if
if rs.State=adStateOpen then rs.close
set rs=Nothing
End Sub
look for theBolded Parts in the connection string. the first one should be replaced by yours database user name and the second one is by your original database name.
for inserting values from textbox to your sql server database use the following code :-
Dim rs as New ADODB.Recordset
if rs.state=adStateopen then rs.close
rs.open "select * from <strong>branch_details</strong>",gcn,1,2
rs.AddNew
rs!branch_id=txtID.text
rs!branch_name=trim(txtName.text)
rs!address=trim(txtAddress.text)
rs.Update
if rs.state=adStateopen then rs.close
set rs=nothing
Msgbox "New record added."
look into theBolded Part. it must be replaced by your table name where you wish save your records.
hope this helps you.
try this and give me a feedback.
regards
Shouvik
