|
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.
|
|
|
|
|
The docs on the Beep API function says:
"Windows Me/98/95: On computers with a sound card, the function plays the default sound event. On computers without a sound card, the function plays the standard system beep."
IIRC, MessageBeep(-1) on old OSes does the trick.
Yes, even I am blogging now!
|
|
|
|
|
MessageBeep(-1) did the trick. i am building a temple for you as we speak.
may you win the lottery or something.
thanx.
BTW - do you know a to control the length of the beep? (using a FOR loop requires a lot of CPU time)
|
|
|
|
|
Where can I find A LOT of information about the Excel, the Workbook, and the worksheet objects? Like, their properties, events, methods and such...
10x a G.
Pablo.Ar
|
|
|
|
|
|
I guess I should´ve mentioned I´m using VB6, but anyway that second ref seems pretty damn good. 10x a million, Dave!
Pablo.Ar
|
|
|
|
|