|
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
|
|
|
|
|
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
|
|
|
|
|
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 If
It'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!!!
|
|
|
|
|
Hello,
I've been given a sample XML file in a structure that I haven't handled in VB.NET before and would appreciate some guidance. The structure is as follows:
<Interface CompanyCode="COMP01" CreatedOn="2007-05-27T10:20:00" CreatedBy="PRODUCT" CreatorKey="PROD" SequenceNo="1" CreatorReference="NEWPRODUCT"><br />
<Product ProductCode="P1"><br />
<Description>Product 1 Description</Description><br />
</Product><br />
<Product ProductCode="P2"><br />
<Description>Product 2 Description</Description><br />
<Value EffectiveDate="2006-04-01"><br />
<Price>5.50</Price><br />
<Currency>GB</Currency><br />
</Value><br />
</Product><br />
<Product ProductCode="P3"><br />
<Description>Product 3 Description</Description><br />
</Product><br />
</Interface>
When I read the xml file in with ...
Dim ds As DataSet = New DataSet("dataset")<br />
<br />
ds.ReadXml("prod2.xml")<br />
<br />
With DataGridView1<br />
.DataSource = ds<br />
.DataMember = ds.Tables(2).TableName<br />
End With
... I can see that I've got 3 x tables ... "Interface", "Product", "Value".
What I'm wondering is how do I get the "Value" table linked to "Product"? I was expecting (rightly or wrongly) to see some inherited values from product displayed in my DataGridView. Looking at the structure of the XML file, it seems to imply that there should be some inheritance???
|
|
|
|
|
By the look of things, the table Value is already linked to Product, the problem is just that this example only contains data (price, currency, etc) for Product 2.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi Guys
I am new VB.Net and has managed to do some coding and has a small question. I am developing an application for our company's internal use, using VS2008 with VB.Net and SQL Express. I have designed the application following the N-Tier methodology. Can I use the Data Acess Layer class which I have developed to have more than One SQLDataAdaptor for different SQL data sources, sharing all other events and properties from my Data Access Layer Class.
I hope I haven't confused anyone.....
|
|
|
|
|
Hi
try to download the SQLHelper class provided microsoft it will be very helpfull for your work and it covers all cases and they will give u a demo project see it.
Mohammad Al Hoss
|
|
|
|
|
Thank you I'll give it a shot
|
|
|
|
|
Is it possible to have a datagridview with different cell types in each column?
That is, I have a datagridview which i am binding to a datasource. For the first 11 rows, column 1 is a static value but for the final 4 rows I need the user to select a value from a combobox. Is it possible for column 1 of the datagridview control to have 11 textbox fields and then 4 combobox fields?
(I don't need examples, would just like to know if it can be done before I waste any more time on it (I have searched Google BTW!))
|
|
|
|
|
Although not necessarily the best answer, one possibility is using HitTestInfo on the mousedown event of your datagrid. There are several articles about it on this site.
I am using such a method in one of my own apps, and part of the process is first looping through the rows and then the columns to determine whether I want to show a combobox in a certain column. I would think that it should be possible to narrow the same trick down by specifically determining the row (index > 11) as well.
Hope it helps,
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
Cool thanks, I will give it a try 
|
|
|
|
|
pls sombody help me on how to read a file another proces is reading can a copy it to temp file and read from there
|
|
|
|
|
A couple of posts lower there is a simular question.
If you are only going to read a file which another process is also reading, don't bother doing a copy of it. Use your filestream as following:
dim fs as new system.io.filestream("yourfilenamehere", FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
This way, it can share access with a different process...
|
|
|
|
|
Please only post your question in the relevant forum, not everywhere. Also try searching first, this has been asked many times.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
pls how can i serched a prtivious question. but pls will this code enable me to read all line in a file being read by another process
Dim fs As New FileStream("ftpLogDirectory", FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
s = File.ReadAllLines(ftpLogDirectory)
thanks
|
|
|
|
|
Ebube wrote: pls how can i serched a prtivious question
try the search box at the top of the screen
Ebube wrote: will this code enable me to read all line in a file being read by another process
Dim fs As New FileStream("ftpLogDirectory", FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
s = File.ReadAllLines(ftpLogDirectory)
Probably. Why not try it and see?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i put the this code within try catch block and it is giving me objecte refrence not set to an instance of an object exception . can someone help
Dim fs As New FileStream(ftpLogDirectory, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
Dim ds As New StreamReader(fs)
Dim line, le As String
line = ds.ReadLine
While line <> ""
Me.ListBox3.Items.Add(line)
line = ds.ReadLine
le = Mid$(line, 1, 1)
If le <> "#" Then
Dim ns As String = line
Dim RFpath, RFName, RFolderName, FullPath As String
'If InStr(ns.ToLower, "sent") > 0 Then Me.ListBox2.Items.Add(ns)
If InStr(ns.ToLower, "sent") > 0 Then
RFpath = ns.Substring(InStr(ns.ToLower, "sent") + 4)
RFName = RFpath.Substring(InStrRev(RFpath, "/"))
RFName = Replace(RFName, "+", " ")
RFolderName = RFpath.Substring(1)
FullPath = "/" & RFolderName
If InStr(RFolderName, "/") > 1 Then
RFolderName = RFolderName.Substring(0, InStr(RFolderName, "/") - 1)
End If
Dim newns As String = ns.Substring(0, InStr(ns, "[") - 2)
IpAddress = newns.Substring(0, InStrRev(newns, " ") - 1)
IpAddress = IpAddress.Substring(InStr(newns, " "))
newns = newns.Substring(InStrRev(newns, " "))
If Not InStr(newns, ".") > 0 Then
ReciepientName = newns
End If
If InStr(FullPath, dPath) > 0 Then Return True
' Return True
'Else
' Return False
End If
'
End If
End While
|
|
|
|
|
1. At which line? that shoud give you a clue
2. Try writing .net code, not vb6/vba code, you will have less errors
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have a datagridview and am adding several rows through code. I have a combobox that is databound to a datatable for the items to select. So far everything works great except that I cannot set the selectedvalue of the combobox for the current row I am adding. Can anyone offer any suggestions, please?
Thank you, Chester
|
|
|
|
|
What are you populating the combobox with? If it is just a string then try selectedText or selectedItem.
|
|
|
|
|
The selectedvalue is an integer and the displaymemeber is a strig. I want to set the selected value to the integer value and then have the combox display the string that goes with that integer value.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
