Run a query on an sql database in VB.Net
July 14, 2010 - 17:09
Connecting to a microsoft sql database is done by first creating the database on your server and assigning the correct username and password. Once this is done then create your connection string. This will look something like this:
server=127.0.0.1;database=dbname;uid=username;pwd=password;
Make sure to replace the dbname,username and password with your correct details.
I would normally add this as a string into the web.config file as if this is hard coded into all the webpages, any change requires a large search and replace.
Once you have the connection string then you need to make sure you have created the necessary stored procedure on your sql server. In the example we have a stored procedure called spGetUser passing the variables UserID.
<%@ Import Namespace="System.Data.SqlClient,System.Data" %>
dim server_connection as string = "server=127.0.0.1;database=dbname;uid=username;pwd=password;"
Dim myConnection As New sqlconnection(server_connection)
Dim ds as new Dataset()
Dim da as sqldataadapter
da = new sqldataadapter("spGetUser",myconnection)
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.SelectCommand.parameters.add("@UserID","34")
da.fill(ds, "Results")
This then connects to the database and queries the database, with the returned table being stored in the dataset "ds" as a table called "Results". The dataset can then be manipulated and the results displayed.
