Jag behöver kolla om värdet som användaren matar in i "tbHandle" redan finns, om så är fallet ska han skickas tillbaka med ett felvärde, annars ska den nya sql-satsen köras.

I det gammla asp kollade jag det med recset.recordcount, men det verkar inte gå längre, så hur gör man nu?

	Sub Submit(obj as Object, e as EventArgs)
		If Page.IsValid Then
			'Databastjaffs...
			Dim objConn As OleDbConnection
			Dim strSQL As String
			Dim objCmd As OleDbCommand
			Dim strDSN As String
			Dim secWork As String
			Dim ipAdress as String
			Dim username as String
			secWork = String.Join(", ", Request.Form.GetValues("lbSecWork"))
			ipAdress = Request.ServerVariables("REMOTE_ADDR")
			username = Request.form("tbHandle")

			strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("path...")
			objConn = New OleDbConnection(strDSN)
			strSql = "SELECT * FROM ansokan WHERE username = '" & username &"'"
			If objConn.recordcount <= 1 Then
				response.Write("Användaren finns redan")
			Else
				strSQL = "INSERT INTO ansokan (ip,username,email...) VALUES ('" & ipAdress & "' , '" & tbHandle.text & "' , '" & tbEmail.text & "'...)"
				objConn = New OleDbConnection(strDSN)
				objCmd = New OleDbCommand(strSQL, objConn)
		
				response.Write(strsql)
				objCmd.Connection.Open()
				objCmd.ExecuteNonQuery()
				objCmd.Connection.Close()
			End If
		End If
	End Sub

Borde inte
<code>
If objConn.recordcount <= 1 Then
</code>
vara:
<code>
If objConn.recordcount >= 1 Then
</code>

// MvH Björne

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30456: 'recordcount' is not a member of 'System.Data.OleDb.OleDbConnection'.

Source Error:

Line 20: 			objConn = New OleDbConnection(strDSN)
Line 21: 			strSql = "SELECT * FROM ansokan WHERE username = '" & username &"'"
Line 22: 			If objConn.recordcount >= 1 Then
Line 23: 				response.Write("Användaren finns redan")
Line 24: 			Else

Ah, okey

Testa det här:
<code>
Sub Submit(ByVal obj As Object, ByVal e As EventArgs)
If Page.IsValid Then
'Databastjaffs...
Dim objConn As OleDbConnection
Dim strSQL As String
Dim objCmd As OleDbCommand
Dim objDA As OleDbDataAdapter 'new
Dim objDT As DataTable 'new
Dim strDSN As String
Dim secWork As String
Dim ipAdress As String
Dim username As String
secWork = String.Join(", ", Request.Form.GetValues("lbSecWork"))
ipAdress = Request.ServerVariables("REMOTE_ADDR")
username = Request.Form("tbHandle")

strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("path...")
objConn = New OleDbConnection(strDSN)
strSQL = "SELECT * FROM ansokan WHERE username = '" & username & "'"
objDA = New OleDbDataAdapter(strSQL, objConn) 'new
objDA.Fill(objDT) 'new

If objDT.Rows.Count >= 1 Then 'new
Response.Write("Användaren finns redan")
Else
strSQL = "INSERT INTO ansokan (ip,username,email...) VALUES ('" & ipAdress & "' , '" & tbHandle.text & "' , '" & tbEmail.text & "'...)"
objConn = New OleDbConnection(strDSN)
objCmd = New OleDbCommand(strSQL, objConn)

Response.Write(strSQL)
objCmd.Connection.Open()
objCmd.ExecuteNonQuery()
objCmd.Connection.Close()
End If
End If
End Sub
</code>

// MvH Björne

Hej igen.

Lite extrainformation:
1. Du borde använda Parameterized SQL för att undvika SQL-injections
2. Du borde bara fråga efter antalet användare för lite lite mera prestanda

Ex. (obs, har bara kollat syntax, ej att det fungerar, men det borde fungera)
<code>
...
strSQL = "SELECT count(*) FROM ansokan WHERE username = ?"
objCmd.Parameters.Add(username)
objCmd = New OleDbCommand(strSQL, objConn)
Dim numberOfUsers As Int32 = Convert.ToInt32(objCmd.ExecuteScalar())

If numberOfUsers >= 1 Then
Response.Write("Användaren finns redan")
Else
...
</code>

// MvH Björne

Får följande fel nu:
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 22: strSQL = "SELECT count(*) FROM ansokan WHERE username = ?"
Line 23:
Line 24: objCmd.Parameters.Add(username)
Line 25: objCmd = New OleDbCommand(strSQL, objConn)
Line 26: Dim numberOfUsers As Int32 = Convert.ToInt32(objCmd.ExecuteScalar())


Source File: D:\- Work -\- web -\ordningsvakterna.se\newmember.aspx Line: 24

Koden:

<%@ Page Language="VB" debug="true"%>
<%@ Import NameSpace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<script runat="server">
	Sub Submit(obj as Object, e as EventArgs)
		If Page.IsValid Then
			'Databastjaffs...
			Dim objConn As OleDbConnection
			Dim strSQL As String
			Dim objCmd As OleDbCommand
			Dim strDSN As String
			Dim secWork As String
			Dim ipAdress as String
			Dim username as String
			secWork = String.Join(", ", Request.Form.GetValues("lbSecWork"))
			ipAdress = Request.ServerVariables("REMOTE_ADDR")
			username = Request.form("tbHandle")

			strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("...")
			objConn = New OleDbConnection(strDSN)
			'strSQL = "SELECT count(*) FROM ansokan WHERE username = '" & username &"'"
			strSQL = "SELECT count(*) FROM ansokan WHERE username = ?"

			objCmd.Parameters.Add(username)
			objCmd = New OleDbCommand(strSQL, objConn)
			Dim numberOfUsers As Int32 = Convert.ToInt32(objCmd.ExecuteScalar())

            If numberOfUsers >= 1 Then
                Response.Write("Användaren finns redan")
            Else

				strSQL = "INSERT INTO ansokan (ip,username,email,...) VALUES ('" & ipAdress & "' , '" & tbHandle.text & "' , '" & tbEmail.text & "'...)"
				objConn = New OleDbConnection(strDSN)
				objCmd = New OleDbCommand(strSQL, objConn)
		
				response.Write(strsql)
				objCmd.Connection.Open()
				objCmd.ExecuteNonQuery()
				objCmd.Connection.Close()
			End If
		End If
	End Sub
</script>

Då jag tyvärr inte får det att funka så postar jag min befintliga kod här igen och hoppas på det bästa :)

Felmedelandet:

ExecuteScalar requires an open and available Connection. The connection's current state is Closed. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidOperationException: ExecuteScalar requires an open and available Connection. The connection's current state is Closed.

Source Error: 


Line 26: 			objCmd.Parameters.Add(New OleDbParameter("username", username))
Line 27: 			'objCmd.Parameters.Add(username)
Line 28: 			Dim numberOfUsers As Int32 = Convert.ToInt32(objCmd.ExecuteScalar())
Line 29: 
Line 30:             If numberOfUsers >= 1 Then
 

Source File: D:\- Work -\- web -\ordningsvakterna.se\newmember.aspx    Line: 28 

Stack Trace: 


[InvalidOperationException: ExecuteScalar requires an open and available Connection. The connection's current state is Closed.]
   System.Data.OleDb.OleDbConnection.SetStateExecuting(OleDbCommand attempt, String method, Boolean flag) +143
   System.Data.OleDb.OleDbCommand.ValidateConnectionAndTransaction(String method, Int32& localState) +103
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +61
   System.Data.OleDb.OleDbCommand.ExecuteScalar() +87
   ASP.newmember_aspx.Submit(Object obj, EventArgs e) in D:\- Work -\- web -\ordningsvakterna.se\newmember.aspx:28
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1263

 

<%@ Page Language="VB" debug="true"%>
<%@ Import NameSpace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<script runat="server">
	Sub Submit(obj as Object, e as EventArgs)
		If Page.IsValid Then
			Dim objConn As OleDbConnection
			Dim strSQL As String
			Dim objCmd As OleDbCommand
			Dim strDSN As String
			Dim secWork As String
			Dim ipAdress as String
			Dim username as String
			secWork = String.Join(", ", Request.Form.GetValues("lbSecWork"))
			ipAdress = Request.ServerVariables("REMOTE_ADDR")
			username = Request.form("tbHandle")

			strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("databas.mdb")
			objConn = New OleDbConnection(strDSN)
			strSQL = "SELECT count(*) FROM ansokan WHERE username = '" & username &"'"

			
			objCmd = New OleDbCommand(strSQL, objConn)
			objCmd.Parameters.Add(New OleDbParameter("username", username))
			Dim numberOfUsers As Int32 = Convert.ToInt32(objCmd.ExecuteScalar())

            If numberOfUsers >= 1 Then
                Response.Write("Användaren finns redan")
            Else

				strSQL = "INSERT INTO ansokan (ip,username,email,firstname,lastname,description,reqdatum,priwork,secwork,status) VALUES ('" & ipAdress & "' , '" & tbHandle.text & "' , '" & tbEmail.text & "' , '" & tbFName.text & "' , '" & tbLName.text & "' , '" & tbDesc.text & "', '" & now & "' , '" & lbPriWork.SelectedItem.Text & "' , '" & secWork & "', 0)"
				objConn = New OleDbConnection(strDSN)
				objCmd = New OleDbCommand(strSQL, objConn)
		
				response.Write(strsql)
				objCmd.Connection.Open()
				objCmd.ExecuteNonQuery()
				objCmd.Connection.Close()
			End If
		End If
	End Sub
</script>

Henrik har rätt. Du har glömt att öppna databaskopplingen.

Du har glömt att stänga den också. Dessutom är det ju helt onödigt att öppna två databaskopplingar.

Ifall du ska använda parameteriserade frågor så är det ju bra om du använder parametern också...

strSQL = "SELECT count(*) FROM ansokan WHERE username = ?"