Count records using a wildcard...
-
I am working with VB.Net 2005 and I am looking at an Access 2003 mdb. I need to look into a table and see if any records exists that have a value in a certain field based on what is keyed in from a text box...here is what I have so far. I think I am close but when I get to the line "Count2 = CInt(cmd.ExecuteScalar)" I get error..."No value given for one or more required parameters." So I am certainly missing something. 'Check to make sure the Current Department already exisits Dim Count2 As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Comet 631 Databases\Converted to 2003\C022008.mdb") Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(ActAcct) as ExistingRecords From [SHACT] where (@CurrDepart)=?", Connection) cmd.Parameters.AddWithValue("@CurrDepart", OleDbType.VarChar).Value = Me.txtCurGL.Text.ToString + "%" Connection.Open() Count2 = CInt(cmd.ExecuteScalar) End Using 'if the Current Department doesn't exisit then throw message and exit sub If Count2 <= 0 Then MessageBox.Show("The GL Department " & Me.txtCurGL.Text.ToString & " does not exisit.", _ "Copy Department", MessageBoxButtons.OK) Me.txtCurGL.Focus() Me.txtCurGL.SelectAll() Return End If
-
I am working with VB.Net 2005 and I am looking at an Access 2003 mdb. I need to look into a table and see if any records exists that have a value in a certain field based on what is keyed in from a text box...here is what I have so far. I think I am close but when I get to the line "Count2 = CInt(cmd.ExecuteScalar)" I get error..."No value given for one or more required parameters." So I am certainly missing something. 'Check to make sure the Current Department already exisits Dim Count2 As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Comet 631 Databases\Converted to 2003\C022008.mdb") Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(ActAcct) as ExistingRecords From [SHACT] where (@CurrDepart)=?", Connection) cmd.Parameters.AddWithValue("@CurrDepart", OleDbType.VarChar).Value = Me.txtCurGL.Text.ToString + "%" Connection.Open() Count2 = CInt(cmd.ExecuteScalar) End Using 'if the Current Department doesn't exisit then throw message and exit sub If Count2 <= 0 Then MessageBox.Show("The GL Department " & Me.txtCurGL.Text.ToString & " does not exisit.", _ "Copy Department", MessageBoxButtons.OK) Me.txtCurGL.Focus() Me.txtCurGL.SelectAll() Return End If
For as far as I can see.. you are requesting a fieldname in your table with a parameter :
"...where (@CurrDepart)=?"...Which is something you can't do in access... you must use an actual string in the Sql command.. Looking at your code.. i'm gessing you made a mistake cause you don't do any check on the me.txtCurGL.text.tostring string and even add a % ( which is not valid for a column name so...with cmd
.connection = connection
.commandtext = "select count(ActAcct) as existingRecords from [SHACT] where yourtablenamehere = @CurrDepart"
.parameters.addwithvalue("@CurrDepart",me.txtCurGL.text.Tostring & "%")
end with
connection.open()Should work a bit better
-
For as far as I can see.. you are requesting a fieldname in your table with a parameter :
"...where (@CurrDepart)=?"...Which is something you can't do in access... you must use an actual string in the Sql command.. Looking at your code.. i'm gessing you made a mistake cause you don't do any check on the me.txtCurGL.text.tostring string and even add a % ( which is not valid for a column name so...with cmd
.connection = connection
.commandtext = "select count(ActAcct) as existingRecords from [SHACT] where yourtablenamehere = @CurrDepart"
.parameters.addwithvalue("@CurrDepart",me.txtCurGL.text.Tostring & "%")
end with
connection.open()Should work a bit better
Thank you for your reply Noctris. This is what I have now, but I don't think the wildcard is working because no matter what I enter it doesn't find a Count (always 0). Any idea what I might be doing wrong now? 'Check to make sure the Current Department already exisits Dim Count2 As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Comet 631 Databases\Converted to 2003\C022008.mdb") Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct)= @CurrDepart", Connection) cmd.Parameters.AddWithValue("@CurrDepart", Me.txtCurGL.Text.ToString & "%") Connection.Open() Count2 = CInt(cmd.ExecuteScalar) End Using 'if the Current Department doesn't exisit then throw message and exit sub If Count2 <= 0 Then MessageBox.Show("The GL Department " & Me.txtCurGL.Text.ToString & " does not exisit.", _ "Copy Department", MessageBoxButtons.OK) Me.txtCurGL.Focus() Me.txtCurGL.SelectAll() Return End If
-
Thank you for your reply Noctris. This is what I have now, but I don't think the wildcard is working because no matter what I enter it doesn't find a Count (always 0). Any idea what I might be doing wrong now? 'Check to make sure the Current Department already exisits Dim Count2 As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Comet 631 Databases\Converted to 2003\C022008.mdb") Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct)= @CurrDepart", Connection) cmd.Parameters.AddWithValue("@CurrDepart", Me.txtCurGL.Text.ToString & "%") Connection.Open() Count2 = CInt(cmd.ExecuteScalar) End Using 'if the Current Department doesn't exisit then throw message and exit sub If Count2 <= 0 Then MessageBox.Show("The GL Department " & Me.txtCurGL.Text.ToString & " does not exisit.", _ "Copy Department", MessageBoxButtons.OK) Me.txtCurGL.Focus() Me.txtCurGL.SelectAll() Return End If
CCG3 wrote:
("Select Count(ActAcct) as ExistingRecords From [SHACT] where (@CurrDepart)=?", Connection) cmd.Parameters.AddWithValue("@CurrDepart", OleDbType.VarChar).Value = Me.txtCurGL.Text.ToString + "%"
I think all that is wrong is (@CurrDepart) should be ActAcct (based on your first posting and this one. Your bsic query should be select count(*) from tablename where columnname = parameter Hope this helps
Bob Ashfield Consultants Ltd
-
CCG3 wrote:
("Select Count(ActAcct) as ExistingRecords From [SHACT] where (@CurrDepart)=?", Connection) cmd.Parameters.AddWithValue("@CurrDepart", OleDbType.VarChar).Value = Me.txtCurGL.Text.ToString + "%"
I think all that is wrong is (@CurrDepart) should be ActAcct (based on your first posting and this one. Your bsic query should be select count(*) from tablename where columnname = parameter Hope this helps
Bob Ashfield Consultants Ltd
thanks, this is what I did to get past this issue and I have tested it several times and it works fine... 'Check to make sure the Current Department already exisits Dim Count2 As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Comet 631 Databases\Converted to 2003\C022008.mdb") Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct) Like '" & Me.txtCurGL.Text & "%'", Connection) Connection.Open() Count2 = CInt(cmd.ExecuteScalar) End Using 'if the Current Department doesn't exisit then throw message and exit sub If Count2 <= 0 Then MessageBox.Show("The GL Department " & Me.txtCurGL.Text.ToString & " does not exisit.", _ "Copy Department", MessageBoxButtons.OK) Me.txtCurGL.Focus() Me.txtCurGL.SelectAll() Return End If
-
thanks, this is what I did to get past this issue and I have tested it several times and it works fine... 'Check to make sure the Current Department already exisits Dim Count2 As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Comet 631 Databases\Converted to 2003\C022008.mdb") Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct) Like '" & Me.txtCurGL.Text & "%'", Connection) Connection.Open() Count2 = CInt(cmd.ExecuteScalar) End Using 'if the Current Department doesn't exisit then throw message and exit sub If Count2 <= 0 Then MessageBox.Show("The GL Department " & Me.txtCurGL.Text.ToString & " does not exisit.", _ "Copy Department", MessageBoxButtons.OK) Me.txtCurGL.Focus() Me.txtCurGL.SelectAll() Return End If
Hi.. Happy for you it works.. i would however keep working with the parameter seeing it is a bit safer ( if someone put's a " ' " in the textbox, the appllication will fail right now.. You should do:
'Check to make sure the Current Department already exisits
Dim Count2 As Integer = 0
Using Connection As New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Comet 631 Databases\Converted to 2003\C022008.mdb")
Dim cmd As new OleDbCommand
with cmd
.commandtext = "Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct) Like @department"
.parameters.addwithvalue("@department",me.txtCurGL.text & "%")
.connection = connection
end with
Connection.Open()
Count2 = CInt(cmd.ExecuteScalar)
End Using
'if the Current Department doesn't exisit then throw message and exit sub
If Count2 <= 0 Then
MessageBox.Show("The GL Department " & Me.txtCurGL.Text.ToString & " does not exisit.", _
"Copy Department", MessageBoxButtons.OK)
Me.txtCurGL.Focus()
Me.txtCurGL.SelectAll()
Return
End IfIt's a bit cleaner and safer..
-
Hi.. Happy for you it works.. i would however keep working with the parameter seeing it is a bit safer ( if someone put's a " ' " in the textbox, the appllication will fail right now.. You should do:
'Check to make sure the Current Department already exisits
Dim Count2 As Integer = 0
Using Connection As New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Comet 631 Databases\Converted to 2003\C022008.mdb")
Dim cmd As new OleDbCommand
with cmd
.commandtext = "Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct) Like @department"
.parameters.addwithvalue("@department",me.txtCurGL.text & "%")
.connection = connection
end with
Connection.Open()
Count2 = CInt(cmd.ExecuteScalar)
End Using
'if the Current Department doesn't exisit then throw message and exit sub
If Count2 <= 0 Then
MessageBox.Show("The GL Department " & Me.txtCurGL.Text.ToString & " does not exisit.", _
"Copy Department", MessageBoxButtons.OK)
Me.txtCurGL.Focus()
Me.txtCurGL.SelectAll()
Return
End IfIt's a bit cleaner and safer..
Thank you very much Noctris, I will give that a shot. But most importantly I will remember this advice. In this case the text boxes are masked and only accept numeric characters, no symbols at all (not even positive/negative). But thanks again for helping you do a great job and it is much appreciated!!!