ASP.NET & MS SQL

I need help im a new user to this and previoully have used asp.net with MS Access. But now I am making a new project in ASP.NET with MS SQL So far what I have done is the following but still does not run. I am really strugling at the moment. Please help me, thanks

Im not sure if this is correct
Dim strConnection As String = "Provider=Microsoft.Jet.sql.4.0;"

or this:
Dim objCOmmand as New sqlCommand(strSQL, objConnection)

But this is the code below so far which is corrected:

<%@. Page Language="vb" Debug="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SqlClient" %
<script runat="server"
Sub Page_Load()
Dim strConnection As String = "Provider=Microsoft.Jet.sql.4.0;"
strConnection += "server=ldnlt0433;uid=web;pwd=**secret**; database=JoinersAndLeavers"

''set up command with SQL details and connection details
Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "select * from JoinRequest;"

Dim objCOmmand as New sqlCommand(strSQL, objConnection)
'record set object declared to store records
Dim objDataReader As SqlDataReader

Dim ResultText As String
Dim ResultBit As Boolean

try
objConnection.Open() 'open the connection to the DB

'execute the command with its SQl and connection
' the results go into the record set
objDataReader = objCOmmand.ExecuteREader()

Do While objDataReader.Read()=True
ResultText += objDataReader("AccountType")
ResultText += " "
ResultBit += objDataReader("OutlookMail")
ResultBit += " "
ResultText += objDataReader("PhoneLogin")
ResultText += " "
ResultText += objDataReader("Desktop")
ResultText += " "
ResultText += objDataReader("Laptop")
ResultText += " "
ResultText += objDataReader("Token")
ResultText += " "
ResultText += objDataReader("Mobile")
ResultText += " "
ResultText += objDataReader("XDA")
ResultText += " "
ResultText += objDataReader("Blackberry")
ResultText += " "
ResultText += objDataReader("USBkey")
ResultText += "<br>"
Loop
' response.write( ResultText + "<br> results are out <br>" )

objDataReader.Close()
objConnection.Close()

If IsPostBack Then 'add the new data on if PostBack
' Create and open the connection object
' the conection objects path was set up earlier
objConnection.Open()

' set the SQL string
strSQL = "INSERT INTO JoinRequest ( AccountType, OutlookMail, PhoneLogin, Desktop, Laptop, Token, Mobile, XDA, Blackberry, USBkey ) VALUES ( '" + AccountType.Text + "' , '" + OutlookMail.Text + "', '" + PhoneLogin.Text + "', '" + Desktop.Text + "', '" + Laptop.Text + "', '" + Token.Text + "', '" + Mobile.Text + "', '" + XDA.Text + "', '" + Blackberry.Text + "', '" + USBKey.Text + "')"
' execute the command
' Create the Command and set its properties
objCOmmand = New SqlCommand(strSQL, objConnection)

' execute the command
objCommand.ExecuteNonQuery()

objConnection.Close()
End If

'read back from file and display
objConnection.Open()

strSQL = "SELECT * FROM JoinRequest"
objCOmmand = New SqlCommand(strSQL, objConnection)
dg1.DataSource = objCOmmand.ExecuteREader(CommandBehavior.CloseConnection)
dg1.databind()

objDataReader.Close()
objConnection.Close()

catch e as Exception
Response.Write("Connection failed to open successfully.<br>" + e.ToString())
end try

end sub

r1cz:

Im not sure if this is correct
Dim strConnection As String = "Provider=Microsoft.Jet.sql.4.0;"

or this:
Dim objCOmmand as New sqlCommand(strSQL, objConnection)

Those are 2 different things. The first statement is just initialization of a string with Jet as the DB Provider. The second statement is initialization of SQLCommand.

You mentioned your DB is MS SQL Server.So your connection information would look like:

Dim strConnection As String = "Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

Protected objConnection As New SqlConnection(strConnection )
Dim objCOmmand as New sqlCommand(strSQL, objConnection)

Check outwww.connectionstrings.com for sample connection strings. Also check out the Tutorials section of these forums (Its in the menu at the top of the page)

And you should be using Parameterized Queries. You can either google or search these forums for more information on why/how you should use Parameterized Queries.

|||

Thank you that was helpful I found parameters and got that done aswell thank you for your advice. Im jus stuck at the moment now becuase I got 1 form but I want to open two tables in the database. The 1 form will enter details for two tables.

1 table will have employee information
1 table employee's request information

They both link by using a foreign key so in the form I will have some user information i.e. name and then their request as check boxes. I have a look at different forums and google tryin to get information but no luck. Is this possible if so how? Thanks.

|||

You could send all the values to a stored proc and from the stored proc isnert/update all the tables you need to. That way you could get it all done in one trip and your code is cleaner.You could start by writing a stored proc that works as you want it to and test it via Query Analyzer. Having done that check out the ExecuteNonQuery method.