Hi all, I have decided to do a major IT project on Visual Basic .Net 2005. I know some of the basics and know what i'm doing. But i have come asking for some help in how to program with databases. I was wondering if there was anyone out there that knows what they are doing and if they can help. All I really need to do is to make the program read several different fields and open the as one entry to be edited. and also to find a value in one of the entry's (like a search) and use other entry's in that field to input into the program. Even if you could point me in the direction of some good websites with tutorials etc that would be greatly appreciated. Thanks in advance!

Reply

I've not really used VB.Net 2005. I've been using 2003 for quite a few years already. Things should be almost the same except for a few updates/upgrade of feature from .Net 1.1 to .Net 2.0

There's 2 ways(or more) to deal with database. It all depends on how much control you want to have or how much help you want VB.Net to do for you.
1. Use the build in ADO.Net library. It encapsulate all the database function into a very nice library where a lot of things is done automatically. A lot of .Net control is "data aware", meaning you can set data binding on it, and it will just show the content of the database according to what you specified in the binding. Pretty powerful.
2. Wrap up database function in your own library. If you have enough background in .Net programming, and you want to have full control over all the details in the database function, do this. This is what i been doing. It takes time to built up the whole structure, but it's worth the time. Well at least if you're planning to reuse the code over and over again. Another reason for choosing this method is portability.

Some tips here:
a. Try to utilize OOP as much as possible. My first database wrapper wasn't using OOP at all. I thought it's good enough. Not until i'm bump into it someday, realizing when i need to change something, and need to do a lot of search a replace throughout all my code, then it breaks here and there. Now i have most of it coded OOP way. Like using classes to keep content of the datarow. That way, when you need to change something at the core level, like switch to a diff database environment, you upper level of stuffs (so call presentation layer) won't have to be touched at all.

For more solid tutorial, goto codeproject. http://www.codeproject.com/vb/net/

They have tons and tons of sample and tutorial there, and that's where i learn all the tips and tricks
You might find a lot of similar topics, and might not have time to to finish them all. You don't have to, cause learning is a way of life. You do it bit my bit. While you gain your experience, the next time you read the same article you might gain a different knowledge. Just pick a few related one with higher rating. Read the comment section as well, it's helpful sometimes, as you might bump into problem others did, and the answer is already there.

Another place is google. Use the right term to search. Also, you should try to pick up a bit of C#, as a lot more sample and tutorial, and really cool tricks you'll find in C#. Since both are based on the same .Net Framework. It's not hard to pickup. If you need to translate C# to VB.net. Try this site http://authors.aspalliance.com/aldotnet/ex.../translate.aspx

Should be enough to get you started. I'll not try to spoon feed to too much also.

Hope you didn't get confuse by the way i explain things. If you have further question, don't hesitate to ask.

 

 

 

Reply

Hey! Thanks for all this information! its great stuff and it now makes a bit more sense then before! i'll keep your offer in the back of my mind if i ever need some help!
thanks again!

Reply

Without knowing specifics, I would try data adapters and datasets.

Drag and drop the data adapter from the toolbar. It'll present a wizard-like function that you can follow through to select the tables, columns, etc. It will also allow you to choose insert, delete, update, select. When you're done, the code is generated into your class. It'll at least give you an idea of what's happening behind the scenes.

Your best bet is to search for "datasets" "dataadapters" in google and refer to the VB.NET 2005 doc.

If you're using anything other than MS SQL Server, MS Access, etc you'll need to download the appropriate libraries (for example MySQL).

Reply

If you decide to use MySQL or SQLite with VB.NET/C# and run into any problems - drop me a line anytime. Those fall in my area of expertise.

Though i don't dabble in VB.NET much anymore ( I completely shifted to C# a couple of years back ) it shouldn't be difficult guiding you...

Reply

thanks for your support guys. me, i'll keep your offer in the back of my mind if i start working in that area!

Reply

i may not be yet good at it and my code was still a mess but i have embarked on the multi database system connector by using data providers.

keeping safe SQL commands and using this techinique that i am employing, you can create a vb.net program that can connect to mySQL, SQL, MSSQL, Post Gre, Oracle and others by just switching a data adapter.

i just get if from code project and extends it. the original source code was a c# one which i have just converted and its code base was for studio .NET 2003.. my version port was 2005..

*******
about the mySQL one.. i think consult m^e, there was also a connector from mySQL.com that gives mySQL connection into visual basic .net

*******

Reply

hi all, thanks for all your replies! i am only now just starting to design the project and get more ideas! the first thing i need to know before i start getting too involved is, what type of database can i use? this project is going to be run off my computer and i am going to be making/building/compiling the project on the same computer. Once the project is finished, it is going to be transfered to a different computer to be marked etc.

what i need to know is what type of database can i use on a single computer (a network option i don't think would work) that can work on vb.net 2005 and can be transfered from computer to computer easily (just copying files in one folder and not having to go searching for files all over the computer).

Thanks for your help
-jimmy

Reply

Basically most of it will work in your situation. It still depend on your requirement. Is your database going to be use only locally, or other pc will also access it?

For local access and embedded(without running another app to serve the db), MS Access, SQLite, Firebird or any other that has an embedded support. Not MySQL though, cause it's embedded library support only C++.

But, for me, I'm using MySQL, install as an service, and access directly using the provided assembly. With this method, you'll need to install MySQL on every PC you decide to run your software. That might have an licensing issue you need to look into

Reply

Call a DB Function in VB.NET
Need Help Programming With Databases (VB.NET)

Hello...



I find all of you here very helpful... Could someoen please help me out...



I need an immediate help?.I am working on a project using PDA. I have installed Oracle Lite. I wana call a database function. My code is as follows:



Dim cmd1 As LiteCommand = con.CreateCommand()



Cmd1.CommandText = "fncEncryptPsswd"

Cmd1.CommandType = CommandType.StoredProcedure

Dim p1 As New OracleParameter("p_input1", DbType.String)

P1.Value = UCase(Trim(txtPsword.Text))

P1.Direction = ParameterDirection.Input

Cmd1.Parameters.Add(p1)



Dim p2 As New OracleParameter("output1", DbType.String)

P2.Direction = ParameterDirection.ReturnValue

Cmd1.Parameters.Add(p2)

Cmd1.ExecuteNonQuery()



Con.Close()



It throws the follwoing error at cmd1.ExecuteNonQuery():



37000[POL- 5228] syntax error



I am not sure what to do



As an alternative I tried to convert the same function into .Net...



Original in SQL is:



FUNCTION Fncencryptpsswd(p_input1 IN VARCHAR2) RETURN VARCHAR2 IS

X NUMBER := 31;

S NUMBER := 1;

R1 NUMBER := 29;

R2 NUMBER := 31;

R3 NUMBER := 93;

Ps NUMBER := 0;

Output1 VARCHAR2(100) := NULL;

BEGIN

FOR s IN 1..LENGTH(p_input1) LOOP

X := MOD((((x+TO_NUMBER(ASCII(SUBSTR(p_input1,s,1)))+ ps)*r1)+r2),r3);

Output1 := output1||CHR(x+33);

Ps := x;

END LOOP;

Output1 := REPLACE(output1,'"','0');

Output1 := REPLACE(output1,'@','a');

RETURN output1;

END Fncencryptpsswd;



Now the one I have written in .Net is (not sure how to write it better):



For s = 1 To pass.Length

X = (((x + (Asc(pass.Substring(s, 1))) + ps) * r1) + r2)

Y = x Mod r3

Output = output & Chr(y + 33)

Ps = y

Next s

Output = Replace(output, " ", "", "0")

Output = Replace(output, "@", "a")



The output is totally different from EACH OTHER...If my password is TEST...In sql, (ASCII(SUBSTR(p_input1,s,1))) gives '84' and (Asc(pass.Substring(s, 1))) gives me '69'...Where am I going wrong...



If I pass test I must get "3(W4" as the output from both functions...



Someone Please help me with this...



-reply by Rinjin

Reply