Convert Access to SQL

The following is my code for Access... can someone help me convert it to sql:

My Connectionstring is"server=(local);database=Database;trusted_connection=true"

<%

@.PageLanguage="VB" %>

<%

@.ImportNamespace="System.Data" %>

<%

@.ImportNamespace="System.Data.OleDb" %>

<

scriptlanguage="VB"runat="server">Sub btnLogin_OnClick(SrcAsObject, EAs EventArgs)Dim myConnectionAs OleDbConnectionDim myCommandAs OleDbCommandDim intUserCountAsIntegerDim strSQLAsString

strSQL =

"SELECT COUNT(*) FROM tblLoginInfo " _

&

"WHERE username='" & Replace(txtUsername.Text,"'","''") &"' " _

&

"AND password='" & Replace(txtPassword.Text,"'","''") &"';"

myConnection =

New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _

&

"Data Source=" & Server.MapPath("login.mdb") &";")

myCommand =

New OleDbCommand(strSQL, myConnection)

myConnection.Open()

intUserCount = myCommand.ExecuteScalar()

myConnection.Close()

If intUserCount > 0Then

lblInvalid.Text =

""

FormsAuthentication.SetAuthCookie(txtUsername.Text,

True)

Response.Redirect(

"login_db-protected.aspx")Else

lblInvalid.Text =

"Sorry... try again..."EndIfEndSub

</

script>

Reformatted code:

<%@. Page Language="VB" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">

Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim intUserCount As Integer
Dim strSQL As String

strSQL = "SELECT COUNT(*) FROM tblLoginInfo " _
& "WHERE username='" & Replace(txtUsername.Text, "'", "''") & "' " _
& "AND password='" & Replace(txtPassword.Text, "'", "''") & "';"

myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & Server.MapPath("login.mdb") & ";")

myCommand = New OleDbCommand(strSQL, myConnection)

myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()

If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub

</script>

|||

The query language used by SQL(Transact-SQL) is very similar to which used by Access. There is no need to modify the sql command in your case. Just modify the connection string to point to your sql server:

<%@. Import Namespace="System.Data.SqlClient" %>

...

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand

...
myConnection = New SqlConnection("Data Source=myServerName\SqlInstanceName; Integrated Security=SSPI; Database=mydb;")

You can visithttp://www.connectionstrings.com, or refer to MSDN:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

|||

Don't have to, but should:

<%@. Page Language="VB" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">

Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim intUserCount As Integer
Dim strSQL As String

strSQL = "SELECT COUNT(*) FROM tblLoginInfo WHEREusername=@.UserName AND password=@.Password"

myConnection = New SqlConnection({Your Sql Connection string})

myCommand = New SqlCommand(strSQL, myConnection)

myCommand.Parameters.add("@.UserName",sqldbtype.varchar).value=txtUserName.text

myCommand.Parameters.add("@.Password",sqldbtype.varchar).value=txtPassword.text

myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()

If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub

</script>