|
here is my code
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
clientName = txtClientName.Text
contactName = txtContactName.Text
address = txtAdr.Text
city = txtCity.Text
Results()
end sub
Public Function Results()
Try
objConn.Open()
objDA.SelectCommand = New OleDbCommand
objDA.SelectCommand.Connection = objConn
objDA.SelectCommand.CommandText = "SELECT * from Companies"
objDA.SelectCommand.CommandType = CommandType.Text
objDA.SelectCommand.ExecuteNonQuery()
objDA.Fill(objDS, "Companies")
objConn.Close()
Catch ex As Exception
MessageBox.Show("fail connection in Client Search")
End Try
grdResults.DataSource = objDS
grdResults.DataMember = "Companies"
numRows = objDS.Tables("Companies").Rows.Count
End Function
Lisa
|
|
|
|
|
OK. The problem is your not clearing out the recordset you keep using, objDS. Your using "global" variables to hold these objects and that's just not a good idea. Also, there is nothing in your Results sub that is filtering the recrods to get the one that was specified. Results is also a Function that doesn't return anything. It's a Sub that just fills the recordset with the exact same data every time it's called. Your not "searching" for anything. I suspect that your Companies table in your database has a single record in it?
I'm not rewriting your code because it would just take way too long and I have a job to do, but here's your code "patched" to return just a single copy of the data...
Public Function Results()
Try
objConn.Open()
objDA.SelectCommand = New OleDbCommand
objDA.SelectCommand.Connection = objConn
objDA.SelectCommand.CommandText = "SELECT * from Companies"
objDA.SelectCommand.CommandType = CommandType.Text
objDA.SelectCommand.ExecuteNonQuery()
objDS.Clear()
objDA.Fill(objDS, "Companies")
objConn.Close()
Catch ex As Exception
MessageBox.Show("fail connection in Client Search")
End Try
grdResults.DataSource = objDS
grdResults.DataMember = "Companies"
numRows = objDS.Tables("Companies").Rows.Count
End Function
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Thank you for your time. I know you have your job to do and busy..could you just tell me what do I need to add into my code? like you can tell me I need to clear the dataset, to filter data..because I know which step I need to code into my code, then I can find some more info to read and study them for because I'm starting vb last month, so it seems my coding so bad to you..I need to know what do I need to use in my code, then I can search and study those those..but you remind me the filter things,I can search the filter and learn more about it and try to more complicate my coding..
Lisa
|
|
|
|
|
I can't tell you what to add to your code because it would take a major rewrite of your database code to get it to work properly. If I tell you to add this little thing or change that little thing, chances are it'll break something somewhere else because your using "global" variables and sharing these object all over the place. I'll post a little bit of code from a project I worked on to show you what should be going on.
There's three functions in here. One that opens the database for the other two functions, and two functions that return requested data. One is a parameter out of the database and the other is a dataset that is bound to a datagrid somewhere else.
Private Shared Function GetSystemParameter(ByVal sParamName As String, ByRef objReturnValue As Object) As ParameterStatus
Dim oConnection As SqlConnection
Dim oCommand As SqlCommand
Dim retValue As Integer
Try
oConnection = GetDatabaseConnection()
oCommand = New SqlCommand
oCommand.Connection = oConnection
oCommand.CommandText = "SQLGetSystemParameter"
oCommand.CommandType = CommandType.StoredProcedure
Dim sqlParam As New SqlParameter("@ParmName", SqlDbType.NVarChar, 25)
sqlParam.Value = sParamName
oCommand.Parameters.Add(sqlParam)
Dim sqlpParamValue As New SqlParameter("@ParmValue", SqlDbType.NVarChar, 255)
sqlpParamValue.Direction = ParameterDirection.Output
oCommand.Parameters.Add(sqlpParamValue)
sqlParam = New SqlParameter("RETURN_VALUE", SqlDbType.Int)
sqlParam.Direction = ParameterDirection.ReturnValue
oCommand.Parameters.Add(sqlParam)
oConnection.Open()
oCommand.ExecuteNonQuery()
oConnection.Close()
Dim iReturnCheck As Integer = Integer.Parse(oCommand.Parameters("RETURN_VALUE").Value)
If iReturnCheck = 1 Then
retValue = ParameterStatus.ParameterInvalid
Else
retValue = ParameterStatus.ParameterOK
objReturnValue = sqlpParamValue.Value
End If
Catch ex As Exception
LogErrorEvent("SQLServices", "GetSystemParameter", ex.Message, ex.StackTrace)
retValue = ParameterStatus.ParameterInvalid
Throw ex
Finally
If Not oCommand Is Nothing Then
oCommand.Dispose()
End If
If Not oConnection Is Nothing Then
If Not oConnection.State = ConnectionState.Closed Then
oConnection.Close()
End If
oConnection.Dispose()
End If
End Try
Return retValue
End Function
Public Shared Function GetSystemParameters() As DataSet
Dim oConnection As SqlConnection
Dim oCommandSelect As SqlCommand
Dim oDataAdapter As SqlDataAdapter
Dim oDataSet As DataSet
Dim retValue As Integer
Try
oConnection = GetDatabaseConnection()
oCommandSelect = New SqlCommand("SELECT * FROM System")
oCommandSelect.Connection = oConnection
oDataAdapter = New SqlDataAdapter(oCommandSelect)
oDataSet = New DataSet("SystemParameters")
oDataAdapter.Fill(oDataSet)
oDataSet.Tables(0).TableName = "System"
Catch ex As Exception
LogErrorEvent("SQLServices", "GetSystemParameters", ex.Message, ex.StackTrace)
Throw ex
Finally
If Not oCommandSelect Is Nothing Then
oCommandSelect.Dispose()
End If
If Not oConnection Is Nothing Then
If Not oConnection.State = ConnectionState.Closed Then
oConnection.Close()
End If
oConnection.Dispose()
End If
End Try
Return oDataSet
End Function
Public Shared Function GetDatabaseConnection() As SqlConnection
Dim sConnectionString As String
Dim oConnection As SqlConnection
Try
sConnectionString = ConfigurationSettings.AppSettings("DataConnection")
oConnection = New SqlConnection(sConnectionString)
Catch ex As Exception
LogErrorEvent("SQLServices", "GetDatabaseConnection", ex.Message, ex.StackTrace)
Throw ex
End Try
Return oConnection
End Function
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Thanks Dave..that's so helpful for me to fellow your code alone and understand what's a good coding and what do I need to do it step by step.
I have few questions want to ask you.
1. where do you put the dataconnection? what is the best way to do it? I use the public module to store the dataconnection string, will it hard to change when your app. stored into other data?
2. is it more easy to use stored procedure than the command text? can you give me the example above the 3 parameters' stored procedure? I have not experience of the stored procedures, but I think it's the time to learn about it and try using in my code.
3. can the public shared function be called in any other classes?
Thank you very much!
Lisa
|
|
|
|
|
Lisana wrote:
1. where do you put the dataconnection? what is the best way to do it? I use the public module to store the dataconnection string, will it hard to change when your app. stored into other data?
I take it you're talking about the connection string? It's sitting in the application's config file. Since this is just an XML text file, it's very easy to change.
Lisana wrote:
2. is it more easy to use stored procedure than the command text? can you give me the example above the 3 parameters' stored procedure? I have not experience of the stored procedures, but I think it's the time to learn about it and try using in my code.
Much easier! You also get the benefit of better performance because the stored procedures are compiled and used by the SQL server. You'll find that, when done properly, the database can "run" itself entirely through stored procedures. Your application is just there to call the procedures with the right data and in the right order to make the database "run".
This is an example of a stored procedure from the same database. There are actually two return values. A hidden one, essentially called @ReturnValue, and the @ParmValue. The @ReturnValue will tell the caller if @ParmValue is an actual value being returned.
CREATE PROCEDURE dbo.GetSystemParameter
@ParmName NVARCHAR(25),
@ParmValue NVARCHAR(255) OUTPUT
AS
BEGIN
SELECT @ParmValue = ParmValue FROM [System] WHERE ParmName = @ParmName
IF @@ROWCOUNT = 0
RETURN 1
ELSE
RETURN 0
END
Lisana wrote:
3. can the public shared function be called in any other classes?
That's why I used public shared methods. Public Shared means that any other class can call the method without instantiating an object. All you have to do is Import the namespace that this class of database methods sits in. In my case, its "Imports MNFPool.SQLServices.SQL". All of the methods I posted before sits in a class called "SQL".
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
how can I change this to stored procedures? I'm stuck in the middle of it.
THose are my vb code:
client = txtClientNo.Text
clientName = txtClientName.Text
contactName = txtContactName.Text
address = txtAdr.Text
city = txtCity.Text
If client = "" Then
clientSearch = " "
Else
clientSearch = " AND client_number LIKE '%" & client & "%'"
End If
If clientName = "" Then
clientNameSearch = " "
Else
clientNameSearch = " AND client_name LIKE '%" & clientName & "%'"
End If
If contactName = "" Then
contactNameSearch = " "
Else
contactNameSearch = " AND contact LIKE '%" & contactName & "%'"
End If
If address = "" Then
addressSearch = " "
Else
addressSearch = " AND address_1 LIKE '%" & address & "%' OR address_2 LIKE '%" & address & "%'"
End If
If city = "" Then
citySearch = " "
Else
citySearch = " AND city LIKE '%" & city & "%'"
End If
If client = "" And clientName = "" And contactName = "" And address = "" And city = "" Then
emptySearch = " AND province='XX'"
Else
emptySearch = " "
End If
Dim DA As New OleDbDataAdapter
Dim DS As New DataSet
Try
objConn.Open()
DA.SelectCommand = New OleDbCommand
DA.SelectCommand.Connection = objConn
DA.SelectCommand.CommandText = _
"SELECT client_number, client_name, contact, phone_number, address_1, entity_id FROM Companies WHERE ( status = 1 " & emptySearch _
& clientSearch & clientNameSearch & contactNameSearch & addressSearch & citySearch & " ) ORDER BY client_name ASC"
DA.SelectCommand.CommandType = CommandType.Text
DA.SelectCommand.ExecuteNonQuery()
client_s = ""
DS.Clear()
DA.Fill(DS, "Companies")
objConn.Close()
Catch ex As Exception
MessageBox.Show("fail connection in Client Search")
End Try
I'm trying to change them to stored procedure, but I don't know how to set " & emptySearch & clientSearch & clientNameSearch & contactNameSearch & addressSearch & citySearch " into my stored procedures.
CREATE PROC PDQ
@client_number varchar(100),
@client_name varchar(100),
@contact_name varchar(100),
@address varchar(100),
@phone_number varchar(20),
@empty varchar(100)
AS
BEGIN
SELECT client_number, client_name, contact, phone_number, address_1, entity_id
FROM Companies
WHERE status = 1 **stuck in here**
END
How can I write this stored procedure to fit what I need in the above vb code?
Lisa
|
|
|
|
|
For this one, you'll write the bulk of this in SQL and forget putting the SQL string together in your code. Your code will have to pass ALL of your fields as parameters to the stored procedure, even if they are empty. The stored procedure will do the work of figuring out what fields need to be searched on. Basically, in SQL:
CREATE PROCEDURE dbo.SearchForWhatever
@client_number varchar(100),
@client_name varchar(100),
@contact_name varchar(100),
@address varchar(100),
@phone_number varchar(20)
AS
BEGIN
DECLARE @SearchClientNumber varchar(100)
IF LEN(@client_number) > 0
SET @SearchClientNumber = '%' + @client_number + '%'
ELSE
SET @SearchClientNumber = '*'
DECLARE @SearchClientName varchar(100)
IF LEN(@client_name) > 0
SET @SearchClientName = '%' + @client_name + '%'
ELSE
SET @SearchClientName = '*'
DECLARE @SearchContactName varchar(100)
IF LEN(@contact_name) > 0
SET @SearchContactName = '%' + @contact_name + '%'
ELSE
SET @SearchContactName = '*'
DECLARE @SearchAddress varchar(100)
IF LEN(@address) > 0
SET @SearchAddress = '%' + @address + '%'
ELSE
SET @SearchAddress = '*'
DECLARE @SearchPhoneNumber varchar(20)
IF LEN(@phone_number) > 0
SET @SearchPhoneNumber = '%' + @phone_number + '%'
ELSE
SET @SearchPhoneNumber = '*'
SELECT client_number, client_name, contact, phone_number, address_1
FROM Companies
WHERE status = 1
AND client_number LIKE @SearchClientNumber
AND client_name LIKE @SearchClientName
AND contact LIKE @SearchContactName
AND phone_number LIKE @SearchPhoneNumber
AND address_1 LIKE @SearchAddress
END
This may not be the prettiest way to do it, but it'll work.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Please Help in Datagrid.
In Datagrid how to select a row on single click and edit row on double click.
Thanx in advance
with regards
Irsh
|
|
|
|
|
here is the code for select a row on single click:
Private Sub dataGrid1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dataGrid1.MouseUp
Dim pt = New Point(e.X, e.Y)
Dim hti As DataGrid.HitTestInfo = dataGrid1.HitTest(pt)
If hti.Type = DataGrid.HitTestType.Cell Then
dataGrid1.CurrentCell = New DataGridCell(hti.Row, hti.Column)
dataGrid1.Select(hti.Row)
End If
End Sub
Lisa
|
|
|
|
|
Am working in asp.net in the webforms, this code is not working, could you please give me some other alternative for ths.
Thanx
|
|
|
|
|
Your question would be better answered in the ASP.NET Forum.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Sir,
I need a help.
I have develop a Explorer band using VB 6.0 for IE.
In that I placed a Webbrowser Control. The delete key is not functioning in textbox When a webpage is loaded in the webbrowser control.
I don't get understand the problem.
If anybody knows. help me.
Thanks in advance
Suresh VS
|
|
|
|
|
how can i disable or hide the taskbar in windows 2000/XP? i really need it so i appreciate it if you can help me out! thanks!
|
|
|
|
|
You can't disable or hide the TaskBar. But, if you take a look at this[^] little tidbit on MSDN, you'll find out how you can have your application, in full screen mode only, cover the taskbar so the user can't get to it using the mouse. Then it's just a matter of disabling the Minimize and Maximize functions of your form, and writing a little keyboard hook to eat the Window's key.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I have a form A, inside the form have some usercontrols and and different kind
of controls in there.
When form A is close, dispose or set to nothing, does all the controls inside that form
automatic get dispose, and clean it up by itself without setting it to dull or
dispose it or we have to manually clean it up?
Expert, Please help
|
|
|
|
|
All the usercontrols created using .NET would be handled properly by the GC but not the unmanaged ones. For them you need to implement a Dispose method that executes the necessary cleanup code for the object
If you refer GC help in MSDN, its explained nicely there.
Check "Programming for Garbage Collection" in MSDN
Sanjay Sansanwal
www.sansanwal.com
|
|
|
|
|
I have an app that stores numeric information into an access database. This information is then charted (using Dundas chart tool - "very nice"). My problem is that after about 200 to 250 rows of infor, the chart starts doing strange things such as drawing back over itself and missing values. Suggesting that the data in the DB is in need of repair.
(An indeed, the XML file created by the chart control, clearly shows the strange values being sent to the chart.)
I went into access DB and set it to "compact on close," and that cleared the problem up.
So, I am wondering, is it better to have access repair the DB on close, or is it better to write code in vb.net to repair the DB from inside my app. I am thinking that if I do the latter, I will end up having to manipulate 2 DBs (the original and the repaired one)instead of one - something I am not sure I know how to do.
Any suggestions
|
|
|
|
|
It's better to have Access do it on close because any problems with the repair caught and show to the user sitting right there. You could do it in code, but it would make your apps startup time longer and Access already has the capability built in to to it automatically.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I have various classes in my data access layer that are talking with the database. for all the methods I pass in the connection ref. Now if the connection object is being passed in from the outside and within the da classes there is a try catch block is it necessary to enclose the methods call within a try catch finally block to close the connection in the finally? You would think that any exceptions that are thrown will be caught internally and there for the connection will still be able to be close even if it is not in a finally block in the outer function.
private sub formMethod()
...
conn.open 'not sure if this has to be in the t/c block
someDAMethod()
conn.close 'connection should still be closed, right?
end sub
..in da module
public sub someDAMethod()
try
...
catch sqlEx as sqlException
end try
end sub
I guess you could say that you are only catching sqlExceptions so you still have to do the other catch, but you could always catch the base exception, as long as you know that the update didn't go through properly.
I am still new to dealing with exceptions properly, so if it is a dumass question sorry. I am just curious.
|
|
|
|
|
kowplunk wrote:
conn.open 'not sure if this has to be in the t/c block
someDAMethod()
conn.close 'connection should still be closed, right?
Actually, your connection code should be in your someDAmethod code, not on your form. This way, your connection is handled where it belongs, in your Data Access method. In there, your connection can be closed in the Try/Catch/Finally block (inside Finally). It would go something like this:
Try
' setup our database connection.
Dim conn As SqlConnection = GetConnection()
Dim comm As New SqlCommand("blaa blaa blaa")
Dim param As New SqlParameter(blaa blaa blaa)
.
.
(more code to setup and execute our data access)
Catch ex As Exception
' Code to log the exception, throw a custom exception, or whatever to handle a failed request.
Finally
' Check to see if the connection is anything but closed! Don't check for Open!
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
comm = Nothing
conn.Dispose()
End Try
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
ok, I figured that sometime there was a few different sprocs that were run, that passing in the connection would prevent the opening and closing of each one, but I see how keeping everything together is a good thing as well.
Thanks for the tips
|
|
|
|
|
Hi, I need to retrieve data from a printer. I found some information about GDI and function ReadPrinter but I do not know how to properly use it. There are only examples that show how to send data to a printer. Can somebody help me? It is very important for me.
|
|
|
|
|
ReadPrinter is very poorly documented, but from what I can gather, you need to know what commands the print provider (aka print driver) understands before you can get any information from it. Just executing a ReadPrinter wont return and data unless you first call OpenPrinter with the name of the printer and the mode you want to open it in (can't remember off the top of my head exactly what options to use to get bidi support), and then tell the provider what data you want using WritePrinter with the appropriate commands. There's the problem, I can't find any doc's on those commands, or schema options. They're vendor specific, so your best place to find anything would be to contact developer support for your printer manufacturer.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
hi..
i'm going through hell trying to activate the system speaker. when i try to use the "kernel32" Lib API function "BEEP", i only get a beep in the soundcard's speakers. if they happen to be turned off - i get nothing.
note that:
1) i am using windows 98
2) there is a built-in motherboard system speaker. it's device driver is VMM32.VXD, and the devicer manager says it's functioning properly.
3) the internal speaker itself IS working (it beeps every time i turn the computer on).
4) there is a sound card installed. it's an avance ALS120, and it's driver is ALSWDM.SYS.
if anyone knows anything about this - it doesn't matter if it's in visual C++ or visual basic or whatever, just make the thing beep..
and thanx anyway.
|
|
|
|
|