asp.net database connection

siena · May 9, 2003

I am not new to asp but very new to asp.net.
Looking at this, how can I connect to a sql server db with an asp.net connection string.
Here is what I have so far.
This connection works with classic asp but I am having problem converting it to asp.net connection to sql server db.
Thanks in advance.
<%
Dim objConn,strConnString
Set objConn=Server.CreateObject("ADODB.CONNECTION&quot

objConn.Open="dsn=qqwwqq"
'objConn.Open "strConnString"

Dim RS
Set RS=Server.CreateObject("ADODB.RECORDSET&quot

Dim strSQL

strSQL ="SELECT * FROM tbl_events ORDER BY eventID "
RS.open strSQL,objConn
'response.write strSQL
'response.end

%>

bigfoot · May 9, 2003

You might want to read up on data readers...

Dim strQuery As String = "SELECT somefield FROM somedb"
objConnection = New SqlConnection(strConnection)
objConnection.Open()
Dim dbComm As New SqlCommand(strQuery, objConnection)
Dim reader As SqlDataReader = dbComm.ExecuteReader()

reader.Read()

If IsDBNull(reader.GetValue(0)) Then 'First field = 0
txtSomeTextbox.Text = ""
Else
txtSomeTextbox.Text = reader.GetValue(0)
End If

reader.Close()
objConnection.Close()

This is only one way. You'll find many others..

-Gary

bassguy · May 9, 2003

just a bit more to add, you may want to brush up on the variables and declaring them in .net there is no more variant variable so when you are making statements like

dim objConn,strConnString

you have made 2 variables of the type object. this will raise hell with you later when you are trying to use it etc.

bassguy

siena · May 9, 2003

I see, so it should be like:
dim objConn
dim strConnString
if that is the case, that is happening already.
Based on the example given earlier by bigfoot,
I am still getting slammed by a ton of errors.
This is how I tried to integrated bigfoot's code with mine.
Below this code is the errors that it generated.
I will at least like to get beyond this connections thing.
This is not the way I had wanted to be initiated to this .net framework.
Please take a look:

<%@ Page Language="vb" Debug="true" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="ListJustification.WebForm1"%>
<HTML>
<HEAD>
<TITLE>View Justifications</TITLE>
</HEAD>
<BODY>
Dim strQuery As String = "SELECT * FROM tbl_events ORDER BY eventID" 
objConnection = New SqlConnection(strConnection) 
objConnection.Open() 
Dim dbComm As New SqlCommand(strQuery, objConnection) 
Dim reader As SqlDataReader = dbComm.ExecuteReader() 
 
reader.Read() 
 
If IsDBNull(reader.GetValue(0)) Then  'First field = 0 
    txtSomeTextbox.Text = "" 
Else 
    txtSomeTextbox.Text = reader.GetValue(0) 
End If 
 
reader.Close() 
objConnection.Close() 
<link rel="alternate" media="print" href="printversion.ext">
List Of Justifications<hr>
<ul>
<%
dim RS
Do While NOT RS.EOF
%>
<li>
<a href='displayJustification.asp?eventID=<%=RS.Fields("eventID&quot

%>'>
<%=RS.Fields("SpecName&quot

%>
</a>
 
<%
RS.Movenext
loop
%>
</li>
</ul>
</BODY>
</HTML>

/***************************************
errors that followed
***************************************
<TABLE width="100%" bgColor=#ffffcc>
<TBODY>
<TR>
<TD><CODE>The source code that generated this unhandled exception can only be
shown when compiled in debug mode. To enable this, please follow one of the
below steps, then request the URL: 1. Add a "Debug=true" directive at the
top of the file that generated the error. Example: <%@ Page
Language="C#" Debug="true" %> or: 2) Add the following section
to the configuration file of your
application: <configuration> <system.web> <compilation
debug="true"/> </system.web> </configuration> Note
that this second technique will cause all files within a given application to be
compiled in debug mode. The first technique will cause only that particular file
to be compiled in debug mode. Important: Running applications in debug
mode does incur a memory/performance overhead. You should make sure that an
application has debugging disabled before deploying into production
scenario.</CODE> </TD></TR></TBODY></TABLE> Stack Trace: 
<TABLE width="100%" bgColor=#ffffcc>
<TBODY>
<TR>
<TD><CODE><PRE>
[NullReferenceException: Object variable or With block variable not set.]
Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
ASP.WebForm1_aspx.__Render__control1(HtmlTextWriter __output, Control parameterContainer) in

http://localhost/ListJustification/WebForm1.aspx:28

System.Web.UI.Control.RenderChildren(HtmlTextWriter writer)
System.Web.UI.Control.Render(HtmlTextWriter writer)
System.Web.UI.Control.RenderControl(HtmlTextWriter writer)
System.Web.UI.Page.ProcessRequestMain()
</PRE></CODE>

bassguy · May 9, 2003

are you using visual Studio?

if you are there is a file called web.config if you set the debug thingy like listed you can see more info when you run the page

bassguy

bassguy · May 9, 2003

hi again,

there are a few things that are different in aspx. one is that a lot of this code could go in a "Code Behind Page" which is a full blown Visual Basic page. then that page would have all of these calls on it. lacking that, you can still do it the old way but you have to understand some of the changes in .net first and formost in this case....there are no recordsets. there are a few options but I will give you an example with something called a datareader as it acts the same as what your recordset was going to anyway. also, debugging cannot be dome from a remote maching without making a web.config file and putting it in the same directory as the aspx file. without that you have to TS into the webserver to see the debug.
Finally, the first line of code in your aspx points to the codebehind page, if you are not using one you have to take it out.

here is the code to a table in a sql server that should help you out:

Code:

<%@ Page Language=&quot;vb&quot; Debug=&quot;true&quot; AutoEventWireup=&quot;false&quot;%>

<HTML>
	<HEAD>
		<TITLE>View Justifications</TITLE>
	</HEAD>
	<BODY>
		<font size=&quot;+3&quot; face=&quot;arial&quot;>List Of Justifications</font><hr>
		<%
'Make Your Variables		
		dim SqlConnection1 as  System.Data.SqlClient.SqlConnection
		dim SqlCommand1 as  System.Data.SqlClient.SqlCommand

'Assign them as new things
	   SqlConnection1 = new System.Data.SqlClient.SqlConnection()
      SqlCommand1 = new System.Data.SqlClient.SqlCommand
'the above could have been:
       'dim SqlConnection1 = new  System.Data.SqlClient.SqlConnection()
		'dim SqlCommand1 = new  System.Data.SqlClient.SqlCommand()
'there is no more Recordset use a datareader ( a firehose reader of the data)
 		Dim dreader As System.Data.SqlClient.SqlDataReader
' insert your connection string here
       SqlConnection1.ConnectionString = &quot;data source=COUINTRA;initial catalog=CountyVendors;password=sqlatad;persist secur&quot; & _
        &quot;ity info=True;user id=sa;workstation id=OTC1269423;packet size=4096&quot;
' i am using a stored proc but your select * from would go here
        SqlCommand1.CommandText = &quot;dbo.[BusinessTypeList]&quot;
' for a SQL Qry use System.Data.CommandType.Text
         SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
         SqlCommand1.Connection = SqlConnection1
' if you had a parameter add it like this
         'SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter(&quot;@RETURN_VALUE&quot;, System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), CType(0, Byte), &quot;&quot;, System.Data.DataRowVersion.Current, Nothing))
'open the connection
         sqlconnection1.open
'set dreader as  the reader 
         dreader =SqlCommand1.ExecuteReader
'loop
          While  dreader.read()
'do stuff
           response.write(dreader(1)+&quot;<BR>&quot;)
' end the loop
            end while  
        %>
	</BODY>
</HTML>

I hope that helps a bit

bassguy

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

asp.net database connection

siena

Technical User

bigfoot

Programmer

bassguy

Programmer

siena

Technical User

bassguy

Programmer

bassguy

Programmer

Similar threads

Part and Inventory Search

Sponsor