How do I connect to my database. 1

[sthmpsn1]

I am new to ASP.NET and I have been using ASP for some time now. My old statements of connecting to a database do not work anymore. below is what I tried to use but it said set is no longer supported. How would you put this statement now to connect to a SQL database using ASP.NET???

Public Sub Page_Load(Source as Object, E As EventArgs)
if Not Page.IsPostBack Then
Dim dbconn
Dim dbrs
Dim AccessLevel
set dbconn = server.createobject("ADODB.Connection&quot
dbconn.Open "Provider=SQLOLEDB;Data Source=AM1ST_FS1;" _
& "Initial Catalog=HRINFO;User Id=sa;" _
& "Connect Timeout=15;"
set dbrs = server.createobject("ADODB.Recordset&quot
dbrs.open ("Select * from employeeinfo where userID = '" & request.servervariables("AUTH_USER&quot & "'&quot, dbconn, 0, 1
If dbrs.EOF = True Then
dbrs.close
dbrs.open ("Select * from employeeinfo where userID = 'AM1ST_DOMAIN\" & request.servervariables("AUTH_USER&quot & "'&quot, dbconn, 0, 1
End If
End If
End Sub

 

[Zarcom]

trade your old code for something that models this.
Also use if not IsPostBack.

[Visual Basic]

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind&quot

Dim selectCMD As SqlCommand = New SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
selectCMD.CommandTimeout = 30

Dim custDA As SqlDataAdapter = New SqlDataAdapter
custDA.SelectCommand = selectCMD

nwindConn.Open()

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers&quot

nwindConn.Close()


It may be a good idea to check out some of the documentation that comes along with .Net.

If you don't have .Net the documentation is all available online at msdn.microsoft.com

This should get you started if you have any more specific questions feel free to ask

 

[sthmpsn1]

Is a dataset what a recordset use to be???

 

[Zarcom]

Yes sort of. The dataset object is actually a very robust tool when it is used together with a dataAdapter. It contains a copy of the data that you have requested. When information within the dataset is changed you can send it back to the dataAdapter which will update the proper fields in the database.
The other option is a dataReader which is a one way pipe for reading data very quickly.
Which one you use depends if you wish to update or change the data or simply to display it.

 

[sthmpsn1]

Man you are very knowledgable, thank you so much for your help. Can I ask you two other questions. Say I want a text field in my form to hold a value from the database. Before I would use something like this <%=(dbrs.Fields.Item(&quot;personalTime&quot.Value)%> What would you use now with a dataset? Also can you still use session variables in .net?

 

[Zarcom]

Are you sure you want the text box to hold the value? If only one record is ever coming back and you are using a dataset then this should work perfectly for you.


datasetname.tablename(0).fieldname


The dataset object can actually hold more than one table which is nice in certain situations. A dataset is really pure XML that is generated for you by VS.Net. As long as you the dataset is strongly typed (meaning the schema is predefined) then the tablename will be brought up in the drop down box that appears when you type . Hmm that's all I can think of on that for now. Once again if you have any more specific questions feel free to ask.

Oh the session variables. Yes they are used exactly the same as in classic asp. I do caution using them however. You might want to take a look at this article

http://www.aspfaqs.com/webtech/faq/Advanced/faq4.shtml

Thanks for the compliment, we all start as begginners, if you like my answers show your opinion by marking the post as helpful or expert.

Hope this can get you started

 

[sthmpsn1]

well you asked an interesting question so I am currious to why you asked &quot;Are you sure you want the text box to hold the value?&quot; Basically I need to pull a value from a field called loginID based on the sql query above. Whith this information I am going to create a new record in a different table based with this value. the new table is going to run an employee evaluation application. The user fills out the forms for the evaluation and then that information is stored along with the user that filled out the form. That is why I want to pull the one field. So I will through all the form field information plus there loginID in the new table. Does this make sense? would you not request the loginID?

 

[Zarcom]

Sorry reading over my reply I realized it was a little vague and fuzzy. What you have explained is probably exactly the way I would do this.

I had just thought that you might be getting multiple records, in which case a datagrid would be the better option. Since you only are ever expecting a single record back it's better to keep things simple. The old KISS rule.

 

[sthmpsn1]

Okay so now I am trying to display my database field and it says the table name I put isn't part of the dataset.

Dim empDA As SqlDataAdapter = New SqlDataAdapter
empDA.SelectCommand = selectCMD
dbconn.Open()
Dim empDS As DataSet = New DataSet
empDA.Fill(empDS, &quot;employeeinfo&quot
dbconn.Close()
lblloginID.text = &quot;<b>Welcome to the Year End Evaluation system&quot; & empDS.employeeinfo(0).loginID & &quot;&quot;
End If

 

[Zarcom]

This is because your dataset is not a strongly typed one. In other words the schema for the dataset has not been set. If you want a fast and easy way to do this then you should design all your controls using the WYSIWYG interface for the aspx page in VS.Net.

If you don't want to go through all of this then you can use this syntax instead.

empDS.Tables(0).Rows(0).Item(5)

where the item index is the column that loginID is located.
As you can see things are much simpler if you use strongly typed datasets.

Keep in mind this is unlikely the only way to do things merely my methodology. (though I think mine works fairly well

Hope that does it for you