mmedi005
asked on
How do I make a connection to the database in ASP .NET?
In classic ASP I would connect to any database like this
Set conn = Server.CreateObject("ADODB .Connectio n")
conn.Open = "Provider=SQLOLEDB.1;Data Source=......."
Set com = Server.CreateObject("ADODB .Command")
com.CommandType = 1
Set rs = Server.CreateObject("ADODB .Recordset ")
Set com.ActiveConnection = conn
Is this the same way I would do it in ASP .NET 2.0?
I want to have as much flexibility when I execute any SQL statement.
Thanks in advance...
Set conn = Server.CreateObject("ADODB
conn.Open = "Provider=SQLOLEDB.1;Data Source=......."
Set com = Server.CreateObject("ADODB
com.CommandType = 1
Set rs = Server.CreateObject("ADODB
Set com.ActiveConnection = conn
Is this the same way I would do it in ASP .NET 2.0?
I want to have as much flexibility when I execute any SQL statement.
Thanks in advance...
http://www.codeproject.com/aspnet/WebFarmConnStringsNet20.asp
Private MyStr as String = "Provider=SQLOLEDB.1;Data Source=......."
Private Conn as New SQLConnection(MyStr)
Sub OpenDB
Conn.Open()
End Sub
Private Conn as New SQLConnection(MyStr)
Sub OpenDB
Conn.Open()
End Sub
Sorry, you must import
On the top of the class,
Imports System.Data.SqlClient
On the top of the class,
Imports System.Data.SqlClient
ASKER
that easy? is it pretty much all the same code?
Yes. It's nothing complicated.
Just take not if your are using sqlclient to connect, the connection string is something like
"Provider=SQLOLEDB.1...... .......... .......... .."
If you are using SQL Server Connection, you can try something like:
(and take note where is your SQL Server, save if using IP Address and assign a Port Number)
"Data Source=xxx.xxx.xxx.xxx,Por tNumber;In itial Catalog=DBName;UID=YourID; Pwd=YourPw d;"
Just take not if your are using sqlclient to connect, the connection string is something like
"Provider=SQLOLEDB.1......
If you are using SQL Server Connection, you can try something like:
(and take note where is your SQL Server, save if using IP Address and assign a Port Number)
"Data Source=xxx.xxx.xxx.xxx,Por
ASKER
then how do I execute sql statements, get record sets?
What I'm trying to ask is, how do set up the command and recordset?
Old way you had cmd.CommandText = "", cmd. Execute, or Set rs = cmd.Execute
What I'm trying to ask is, how do set up the command and recordset?
Old way you had cmd.CommandText = "", cmd. Execute, or Set rs = cmd.Execute
Well, sounds like another question though...
Various ways available, you can use Datareader or DataAdapter with Dataset to read/or store your
resultset(s).
For executing SQL, you can ExecuteNonQuery for non-result returning SQL
Use ExecuteScalar for only return one value
For DataReader, once you finished reading, there's no way you can get back the resultset but
reading is fast.
Something like:
Private Sub CheckPrice()
Dim Cmd As New SqlCommand("SP_Check_Price ", Conn)
Cmd.CommandType = CommandType.StoredProcedur e 'For Stored Procedure
Cmd.CommandType = CommandType.CommandText ''For SQL or don't need to declare for SQL
'Below are parameters declared for Stored Procedures
With Cmd.Parameters
.Add(New SqlParameter("@Customer_Co de", Trim(cbCust.Text)))
.Add(New SqlParameter("@Outlet_Code ", Trim(cbOutlet.Text)))
.Add(New SqlParameter("@Item_Code", Trim(cbItem.Text)))
.Add(New SqlParameter("@UOM_Code", Trim(cbUOM.Text)))
End With
'Declare a DataReader here and read the resultset
Dim RD As SqlDataReader = Cmd.ExecuteReader
Try
While RD.Read
MyStrings += RD(0).ToString
End While
Catch ex As Exception
MsgBox("Error while executing (" & Cmd.CommandText & ")" & vbCrLf & "Details: " & _
ex.Message, MsgBoxStyle.Exclamation, "MSIS Server")
Finally
RD.Close()
Cmd.Dispose()
End Try
End Sub
'For dataset, you can take a look at:
http://www.codeproject.com/vb/net/Working_with_Dataset_Grid.asp
Various ways available, you can use Datareader or DataAdapter with Dataset to read/or store your
resultset(s).
For executing SQL, you can ExecuteNonQuery for non-result returning SQL
Use ExecuteScalar for only return one value
For DataReader, once you finished reading, there's no way you can get back the resultset but
reading is fast.
Something like:
Private Sub CheckPrice()
Dim Cmd As New SqlCommand("SP_Check_Price
Cmd.CommandType = CommandType.StoredProcedur
Cmd.CommandType = CommandType.CommandText ''For SQL or don't need to declare for SQL
'Below are parameters declared for Stored Procedures
With Cmd.Parameters
.Add(New SqlParameter("@Customer_Co
.Add(New SqlParameter("@Outlet_Code
.Add(New SqlParameter("@Item_Code",
.Add(New SqlParameter("@UOM_Code", Trim(cbUOM.Text)))
End With
'Declare a DataReader here and read the resultset
Dim RD As SqlDataReader = Cmd.ExecuteReader
Try
While RD.Read
MyStrings += RD(0).ToString
End While
Catch ex As Exception
MsgBox("Error while executing (" & Cmd.CommandText & ")" & vbCrLf & "Details: " & _
ex.Message, MsgBoxStyle.Exclamation, "MSIS Server")
Finally
RD.Close()
Cmd.Dispose()
End Try
End Sub
'For dataset, you can take a look at:
http://www.codeproject.com/vb/net/Working_with_Dataset_Grid.asp
ASKER
It seems so much easier in classic ASP.
I would iterate a SELECT statement with a while loop....
While Not rs.EOF
s = rs("something").value
Wend
Now I have to declare numerous objects and stick information inside arrays....
Is there a way to grab the information much easier or in the similiar fashion?
I would iterate a SELECT statement with a while loop....
While Not rs.EOF
s = rs("something").value
Wend
Now I have to declare numerous objects and stick information inside arrays....
Is there a way to grab the information much easier or in the similiar fashion?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
adriankohws,
thanks for the help, I'm sure I will like VB .NET better, like anything, takes getting use to, but this will work. I just want to create a function for my web page that can retrieve the info without writing all that.
Thanks again......
thanks for the help, I'm sure I will like VB .NET better, like anything, takes getting use to, but this will work. I just want to create a function for my web page that can retrieve the info without writing all that.
Thanks again......
If you just want to retrieve and display information easily, try explore data binding. It's just few sentences of code to bind a table to a grid.