Using SQL Statements in Modules

[Guest]

Hello, I am a beginner to VB.NET.

Here is my question:

I currently have my SQL INSERT and UPDATE functions in my form. I'm trying
to clean up the code on my form, and use modules for database functions if
possible.

The problem is - When I cut and paste the SQL code into the module, the
references to my text boxes are no longer recognized. For ex:

UPDATE tbl1 SET Field1 = txtSomeField.text

txtSomeField.text is obviously not recognized, because this code is not in
the form.

Can someone suggest the best way to overcome this, so I can use modules for
my SQL statements?

Thanks
P

 

[Joseph Ferris]

Seperating your database access from your presentation code is a good
thing.

What I personally like to do is abstract all of my database
functionality to a class. Instead of putting the actual SQL in the
underlying code, though, I use stored procedures. That way, any
changes to the SQL occurs on the SQL Server, and doesn't require a
recompile.

Then, each stored procedure is accessible by a function call in the
class. Each function returns a DataTable and accepts parameters that
are used to populate the command object that I use to access the data.

It isn't a perfect solution, since it is possible for the function
signature to change if the inputs to the stored procedure changes, but
most of the time, changes that I find need to be made are usually
sandboxed to the stored procedure. (You can have the function
signature accept an array of Parameter objects so that you don't need
to change the class, but you lose some ease of use, IMHO).

So, for your example, assume that you have a stored procedure to handle
the updates and it is in your new data access class. Your function
signature may be as simple as:

Public Function UpdateSomeField(ByVal NewValue As String) As DataTable

Just a little side note, here. I opt to return a DataTable on Updates
as well, just for consistancy. You can do it however you want to, that
is just the way I like to do it. If there is an exception, I will just
rethrow it from the function.

If your class is instantiated as MyDBAccess, your code might look like:

Try

MyDBAccess.UpdateSomeField(txtSomeField.text)

Catch ex as SqlException

'// Handle exception here.

End Try

HTH,

Joseph

 

[Cor Ligthert [MVP]]

PM,

Using modules is definitly not the best method anymore in VB.Net.

You can try this simple walkthrough I now created for you.

It is easy however shows a lot.

Open a new windows form project

Go to the solution exlorer and select with the right click Add New and than
Add Component, normally you should rename it however don't

Try to drag a xxxdataadapterwizard on it, that does not go because it is a
wizard and not a control or whatever.
Select a database and database tabel in that and create in that wizard the
most simple "select statementd" and do for the rest everything default

xxx stand here for OleDb, Sql or Oracle

On that component form you will see than two icons.

If you rightclick the DataAdapter than you can select a to generate dataset.

Click on than and do for the rest everything default.

Now you go back to the form and drag and drop a datagrid on that.

Click somewhere outside that datagrid on your form.

In the code above the automatic by the click created Form1_Load event you
type
Public dba As new Component1

In the form event you type
dim ds as New Dataset1
dba.xxxDataadapter1.Fill(ds)
DataGrid1.DataSoure = ds.Tables(0)

Click than on debug start.

A windows form should be should (as we both did not make typos or other
errors).

This is in my idea the most simple format, however what you need is all
build for you.

If you have any questions while doing this, just reply

If it did help you than please as well, than we can set it on our website
and spare with that me typing time.



I hope this gives some ideas,

Cor

 

[Dragon]

Hi,

Can someone suggest the best way to overcome this, so I can use modules for
my SQL statements?

Well it may be not the *Best* way, but...

You can make a field in your module, which holds a reference to a form
and use it from functions which return SQL strings, for example:

~
Module SQLStmts
Private m_FormToUse As Form1

Public Sub SetForm(ByVal FormToUse As Form1)
If FormToUse Is Nothing Then Throw New
ArgumentNullException("FormToUse")
m_FormToUse = FormToUse
End Sub

Public Function MyUpdateStmt() As String
If m_FormToUse Is Nothing Then Throw New
InvalidOperationException("You must provide a form first.")
Return "UPDATE tbl1 SET Field1 = " &
m_FormToUse.txtSomeField.Text
End Function
End Module
~

Alternatively, you can pass entire strings (or textboxes) in your
functions, i.e.:

~
Public Function AnotherStmt(ByVal TableName As String) As String
Return "SELECT one, two, three FROM " & TableName & " ORDER BY
two DESC"
End Function
~

Also consider using Shared String.Format() method, because you'll be
able to store strings in a single constant:

~
Const KillSomeDB As String = "DROP DATABASE {0}"

....

Dim MyCmd As String = String.Format(KillSomeDB, "MyDB")
~

I hope this helps,
Roman

 

[Guest]

If your project starts from a subroutine like "Main" then in the module where
Main is at you could declare:

Public MyNewForm as MyBaseForm

where MyBaseForm is the name you gave to your form in it's properties.

Then you could just simply refer to MyNewForm.txtSomeField.Text