|
Hello there,
I have an MS Access query that works as expected from its UI, but when invoked from vb app using OleDb.OleDbCommand it does not. This is problaby silly, but I can't get around it. Could somebody kindly offer some advice?
Here is the query def:
SELECT [Id_UCAB], Sum([Copias]) AS Cant, ([Paginas]*Sum([Copias])) AS CantPag
FROM PrintedDocs AS A, Docs AS B
WHERE A.DocId=B.DocId And (Id_UCAB Like IIf(ISNULL([@DocId]),Id_UCAB,[@DocId]) And (Fecha>=IIf(ISNULL([@FechaIni]),Fecha,[@FechaIni]) And Fecha<=IIf(ISNULL([@FechaFin]),Fecha,[@FechaFin])))
GROUP BY [Id_UCAB], [Paginas]
ORDER BY IIf([@OrdCod]=1,[Id_UCAB],([Paginas]*Sum([Copias])));
As you can see it has 4 optional parameters that are requested by Access in the following order: @DocId, @FechaIni, @FechaFin, @OrdCod.
When I invoke it from Access UI giving its first param the value: "*ABM*"; it works ok and retrieves the 3 records from table Docs where Id_UCAB like "*ABM*". So far so good.
When I invoke this query from VB.Net app using OleDb.OleDbCommand the story changes. If I ommit the value for the first param, and play around with the other 3 it works as expected, but it does not work when given the same "*ABM* value to the first param(it returns no records).
Here is the declaration of the params:
With cmd.Parameters
.Add(New OleDb.OleDbParameter("@DocId", OleDb.OleDbType.VarChar, 12, Input, 0, 0, Nothing, DataRowVersion.Current, False, Nothing))
.Add(New OleDb.OleDbParameter("@FechaIni", OleDb.OleDbType.Integer, 4, Input, 10, 0, Nothing, DataRowVersion.Current, False, Nothing))
.Add(New OleDb.OleDbParameter("@FechaFin", OleDb.OleDbType.Integer, 4, Input, 10, 0, Nothing, DataRowVersion.Current, False, Nothing))
.Add(New OleDb.OleDbParameter("@OrdCod", OleDb.OleDbType.TinyInt, 2, Input, 10, 0, Nothing, DataRowVersion.Current, False, Nothing))
End With
Here is how I assign the values before calling the Fill method of the Oledb.Oledb.DataAdapter:
With Cmd
If (DocId <> "") Then
.Parameters(0).Value = CType(DocId, String)
Else
.Parameters(0).Value = Global.System.DBNull.Value
End If
If (FechaIni <> "") Then
.Parameters(1).Value = CType(FechaIni, Integer)
Else
.Parameters(1).Value = Global.System.DBNull.Value
End If
If (FechaFIn <> "") Then
.Parameters(2).Value = CType(FechaFIn, Integer)
Else
.Parameters(2).Value = Global.System.DBNull.Value
End If
If (OrdCod <> "") Then
.Parameters(3).Value = CType(OrdCod, Byte)
Else
.Parameters(3).Value = Global.System.DBNull.Value
End If
End With
What I am missing? 
Thanks for your time and help.
Best Regards.
|
|
|
|
|
Your first parameter is a string. Try enclosing it using two double quotes.
|
|
|
|
|
Hi Dragon_
I appreciate your idea, but the actual content of the variable itself is already being cast as a string; any how, since you took the time to make the suggestion, I decided to take the time to try it by forcing a set of doulbe quotes, and it did not work either.
I have even tested it from a VB6 small app I made just for this purpose and get the same result.
I am convinced it must be something so obvious that I can't pin-point-it.
Thanks any ways.
|
|
|
|
|
alexvw,
I had a almost similar encounter when I was programming with MS Access. I had a SQL query, in it was an IN (..) statement with string data types. What I did in VB was iterate each string and enclosed it with quotes. Like ab would be 'ab'. It would be "....somefield IN ('condvalue1', 'condvalue2'..) AND anotherfield=""ab"".... " But I did not use the parameter field. You can try and assigning values with quotes on the parameter field and see what the return value is. Maybe it formats the values you pass to it.
Regards,
dragon_
|
|
|
|
|
Hi there,
After some extensive online search I found the solution. Ms Access db engine uses "*" as a wild character, while ADO components do not.
If you create a query in Access using the LIKE operator, you must use the "*" character for your search to work, and if you try to create it using the SQL wild character "%" it will fail to compile.
On the other hand, if you issue a call to an Access query from a VB App through ADO using the "*" charater your LIKE operator will return nothing, but if you use the SQL wild character "%" you'll obtain a populated recordset.
So basically, the query must be:
Select a, b, c, From ThisTable
Where MyField LIKE @Param
Where @Param (from your VB App) should the proper combination of % & strvalue you require; e.g. "%strvalue" | "%strvalue%" | "strvalue%".
Thank you all.
|
|
|
|
|
Hi im here again,
i got this code that show me the name of the controls in my form
Public Sub GetControls(ByVal owner As Form)
Dim fis As System.Reflection.FieldInfo()
fis = owner.GetType().GetFields(Reflection.BindingFlags.NonPublic Or _
Reflection.BindingFlags.Instance Or _
Reflection.BindingFlags.IgnoreCase)
For Each fi As Reflection.FieldInfo In fis
If TypeOf (fi.GetValue(owner)) Is Control Then
MessageBox.Show(fi.Name)
End If
Next
End Sub
I'm trying this because i wanna put this in the load form, but i wanna make a comparation like this,
if fi.name = "101" then button.BackColor = Green something like this , how can i do?, only i wanna change the backcolor of the button with that name, only if the comparation is true
|
|
|
|
|
spawneditions wrote: if fi.name = "101" then button.BackColor = Green
if fi.name = "101" then fi.BackColor = Green
ftfy.
|
|
|
|
|
thanks, i wrote an error yes the name was "fi" but that is not the error i wanna change the color searching by the name, the name is a string and the button is a control, so i can't make it .
|
|
|
|
|
i get the answer.
Public Sub GetControls(ByVal owner As Form)
Dim fis As System.Reflection.FieldInfo()
fis = owner.GetType().GetFields(Reflection.BindingFlags.NonPublic Or _
Reflection.BindingFlags.Instance Or _
Reflection.BindingFlags.IgnoreCase)
For Each fi As Reflection.FieldInfo In fis
If TypeOf (fi.GetValue(owner)) Is Control Then
Try
boton = CType(Me.Controls(fi.Name.Substring(4, 6)), Button)
' MessageBox.Show(fi.Name.Substring(4, 6))
If boton.Name = "100" Then
boton.BackColor = Color.Gray
End If
Catch ex As Exception
End Try
End If
Next
End Sub
i create a var called "boton as Button" then use Ctype to get the number and then make the comparation, if is true change the color of the button.
i make a substring because the name of the buttons start in "_p1" and a number like 100,101.
that is all.
Thanks
|
|
|
|
|
Why use reflection for this?
If you know the name of the button, use this:
Button myButton = this.Controls["buttonName"] as Button;
myButton.BackColor = Color.Red;
This is a C# code and I assume you can write the equivalent in VB. Here "this" refers to the form in which the button is placed. In case the button is placed in some other container, use the control collection of that container.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Hi All,
I have a question about “Redim”. Consider a Class containing some Share public variant, for example double. The class is created in the main class as array with length ‘2’. If procedure repeats, it is needed that share public variant to be nothing or zero. For this, I Redim the array object created from class containing share public variant. The problem would be accrued when the object new again. In this condition the share parameter which had been deleted is valued again.
Public Shared MyParametr As Double
Public Sub DoDesign()
MyParametr+=3
End Sub
End Class
Public Class MainForm
Public MyObject(1) As clsTest
Private Sub frmMain_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
MyObject(1) = New clsTest
MyObject(1).DoDesign()
Text1.Text= MyObject(1). MyParametr.Tostring()
End Sub
Public Sub DeletingDesignResult()
Ridim MyObject(0)’object=Nothing
Ridim MyObject(1)
End Sub
End Class
When MainForm Shows again and object is newed again, the value of MyParametr is ‘3’ and notzero.
|
|
|
|
|
I can't really understand what you are trying to achieve which makes your question difficult to understand.
Looks like you just need to rethink how your objects are instanced and relate to each other and redesign the code.
You maybe also need to do some testing of the shared object before setting a value to make sure it really needs to be set.
|
|
|
|
|
Experts,
I am using a vb6 application with ADO data controls, i am using a search option where i can find a particular record.
Current code: for search button
Adodc1.Recordset.Find "First_Name = '" & txtFirstName.Text & "'", , adSearchForward
this works fine, what i would like to be able to do is add the text "yes" to record Returned on a particular record by pressing a button Returned. (hope that makes sence!)
Intially the user will search for a record by typing a name in e.g bob
the curser thing of the database will then find record contiang bob and select that record
i then want a button to appear (btnRecord.show ) which will then allow that particular record selected in one of the fields to then have a "yes" on it.
If anybody can help that would be great.
Cheers
Dan
|
|
|
|
|
Hi All,
I have created an application in Visual Studio 2005 .NET, which I installed on my client's machine. This application/project has one file, 'Form2.vb', which needs some updation.
Now I want to update the installed application with this new/updated file without uninstalling the current version installed on my client's machine.
Can someone Please let me know how to do this. I dont want web updation, I can go to my client's machine and update the installed application.
Hope I am clear.
Regards,
R.S.
|
|
|
|
|
You just copy the resulting file of your project.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
I have created a setup & deployment project and installed the exe on my client's machine. So there is no way to update that file on my client's machine.
One way is to recreate the whole SEtup & deployement project with the updated file and re-install on my client's PC, but I dont want to un-install the current version; I just want by anyhow if I can update the application.
|
|
|
|
|
Ok i am doing a login and creating account form i need help with the coding
I will show you guys what i have hopefully could get some guide on modifying the code
This is The Create Button...
If TextBox1.Text = "" Then
MsgBox("ERROR,Your Account/Username must have one letter in it!", MsgBoxStyle.Critical)
Else
If TextBox2.Text = "" Then
MsgBox("ERROR,Your Account/Password must have one letter in it!", MsgBoxStyle.Critical)
Else
Try
MkDir("C:\ACCOUNTS" + TextBox1.Text)
Dim username As New System.IO.StreamWriter("C:\ACCOUNTS" + TextBox1.Text + "\" + "username.txt")
username.Write(TextBox1.Text)
username.Close()
Dim password As New System.IO.StreamWriter("C:\ACCOUNTS" + TextBox1.Text + "\" + "password.txt")
password.Write(TextBox2.Text)
password.Close()
Catch ex As Exception
MsgBox("ACCOUNT CREATED", MsgBoxStyle.Information, "ACCOUNT CREATED")
End Try
End If
End If
This is the login button
If My.Computer.FileSystem.DirectoryExists(" C:\ACCOUNTS" + TextBox1.Text + "\") Then
Dim USERREAD As System.IO.StreamReader = New System.IO.StreamReader("C:\ACCOUNTS" + TextBox1.Text + "\" + "USERNAME.TXT")
Dim userline As String
Dim PASSREAD As System.IO.StreamReader = New System.IO.StreamReader("C:\ACCOUNTS" + TextBox1.Text + "\" + "PASSWORD.TXT")
Dim PASSLINE As String
Do
PASSLINE = TextBox2.Text
userline = USERREAD.ReadLine
Console.WriteLine(PASSLINE)
Console.WriteLine(userline)
Loop Until userline Is Nothing
MainScreen.Show()
Me.Hide()
If TextBox2.Text = "" Then
MsgBox("error, PLEASE INPUT A PASSWORD", MsgBoxStyle.Critical)
Else
If PASSLINE = PASSREAD.ReadLine() = True Then
End If
End If
Else
MsgBox("THE USERNAME DONT EXIT", MsgBoxStyle.Critical)
End If
I don need the database the information can be save in the mobile itself thanks
There is 1 using C# to make the login form but unfortunately my range is only on VB.net itself and i can't touch C# with this project
Will Be Grateful for all the peeps here that could guide me on it
Thanks
modified on Tuesday, January 19, 2010 3:36 AM
|
|
|
|
|
Why don't you try telling us what you want it to do?
|
|
|
|
|
Ok i just want a login system or let's say an login application for smart devices
it like when the application is activate it will prompt you to sign in to ur account and at the same time a form to create account for the login system . . .
but i don wan the normal source i want some source code for vb.net smart devices that's something that makes me very headache about
Sorry About That,
Thanks For Helping Out,
|
|
|
|
|
I'm not sure about the rest of the code, but this block concerns me:
Catch ex As Exception
MsgBox("ACCOUNT CREATED", MsgBoxStyle.Information, "ACCOUNT CREATED")
End Try
If an exception is caugth, that most likely means an error was encountered, which may mean that the account was not created. Catching all exceptions is probably not a great idea either, but that is beyond the scope of this question. I would suggest moving the "Account Created" message to immediately before the catch block and using the cath block to report any errors.
|
|
|
|
|
Thanks For The Advice,
My Main problem is i wanna create a login application that is for vb.net smart devices,cus i am totally new to vb.net smart device.
that code that i provide is the normal application i wan it to be the vb.net smart devices version. . .
maybe you can give some guide or like provide some source code for me for making the login system on vb.net smart devices.
Thanks
|
|
|
|
|
In the application I'm developing, I'd like to give the user the option to automatically start the application when windows starts. From my searches it looks like setting a registry key is one of the best ways to do it.
I haven't ever played with the Registry in my programs before (I've only ever manually edited the registry), so I want to make sure I get it right.
I came across MatrixCoder's article "Autorun Applications" which is quite clear and straight forward.
He uses the following sub and passes it System.Reflection.Assembly.GetEntryAssembly.Location for path in order to set the registry key.
Private Sub AddCurrentKey(ByVal name As String, ByVal path As String)
Dim key As RegistryKey = Registry.CurrentUser.OpenSubKey("Software\Microsoft\Windows\CurrentVersion\Run", True)
key.SetValue(name, path)
End Sub
If the key already exists, will this just overwrite it, or will it add another key each time AddCurrentKey() is called?
If it would keep on adding further keys, how would the code best be modified to check if the key already exists?
Regards
Paul Hasler
modified on Tuesday, January 19, 2010 5:03 AM
|
|
|
|
|
Adding that key will not make your program automatically start when Windows starts. It will make your application start when a user logs on to Windows. When the user logs off the system, the program will be terminated if it is still running.
To make your application start when Windows starts, you need to make your application a "Windows Service". If you search for "Windows Service" you will get plenty of information on how to make it.
Good luck and happy programming!
|
|
|
|
|
This doesn't make the application start when Windows starts. This makes it start whenever the user logs in on the machine.
If the name is the same as an existing value, then that value will get overwritten. it will NOT create another copy of the same name/value pair.
|
|
|
|
|
Thank you once again Dave.
I used the wrong terminology. I do actually want the application to start when the user logs on.
You see so many programs today with preference options such as for example "Start Skype when windows starts", that I fell into the trap of using the same sloppy terminology.
With your explanation of how this will behave, I can finish off my app.
Cheers again
Paul
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
