VB.net/SQL: Passing data to array to be used in an algorithm

[ken1307]

Hi guys

I'm working on a project in which I have to create an small database to keep information about some products (name, contribution, min, max..etc). I need to perform some calculations on the input data (min,max) so I'm just wondering if there are any ways to pass/read the data for the DB the so I could use them in the function/algorithm?

thanks in advance
ken

 

[TrUz]

You could populate and ArrayList with the data from the database and pass it to where you need it.

TrUz

 

[ken1307]

You could populate and ArrayList with the data from the database and pass it to where you need it.

TrUz

thanks for the reply but can you be more specific? im kinda noob at this thing

 

[ken1307]

anyone has any ideas? im desperate for help now


thanks

p/s: any Manchester coders around ?

 

[akakjs]

a quick and dirty hack is to join the data in the array into a delimited string. Not pritty but it works (mostly).

akakjs

 

[SnAzBaZ]

What calculations do you want to perform on the data ?

SQL has the ability to perform a whole range of calculations on the data in a database.

Andrew

 

[happytechie]

just open a data reader on the database based on a query.

go and look at the following in the msdn library

oledb http://msdn.microsoft.com/library/d...html/3c5e2dd5-35e5-4a93-ac3a-3818bb43bbf8.asp

data access
http://msdn2.microsoft.com/en-us/data/default.aspx

datareader
http://msdn2.microsoft.com/en-us/library/haa3afyz.aspx

you should then be able to read the data you need into an array like this:

while datareader.read(){
myarray[0] = datareader[0];
myarray[1] = datareader[1];
myarray[2] = datareader[2];
}

then call your method with the array

this.myMethod(myarray);

I know my code(sic) is in C# not in VB but you'll figure it out

and so on

 

[Fourstar]

I need to perform some calculations on the input data (min,max)

Do the MIN and MAX functions in SQL fit your needs?

For example find the most expensive product in each product type:

SELECT ProductType, MAX(price) AS "Maximum Price"
FROM Products
GROUP BY ProductType

 

[ken1307]

@SnAzBaZ and Fourstar:

Basically I have an small database consists of Category, Contribution, Min, Max, Allocated, , Total Available, Ref. The users will input data into that table and based on their inputs (Contribution of each category, Min and Max number of block that can be allocated to it and the total number block available), I would need to calculate (by the algorithm) the optimize number of block that should be allocated each Category in order to generate the maximum sale/revenue.

here is the algorithm in case anyone wants to look at:





What I plan to do is pass the data in Min/Max and Allocated (contains the results of the algorithm) columns into 3 arrays and work on it. Not sure if its the best approach to this problem but it seems to be do-able to me. Another the option is multi-dimension array but I havent looked into that yet. Any suggestions are greatly appreciated

@happytechie: I have tried this

        Dim MinArrayList As ArrayList = New ArrayList
        Dim strConn As String = "server=localhost;uid=;pwd=;database=asa"
        Dim MySQL As String = "Select Min from tblMain"
        Dim MyConn As New SqlConnection(strConn)
        Dim objDR As SqlDataReader
        Dim Cmd As New SqlCommand(MySQL, MyConn)
        MyConn.Open()
        objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        While objDR.Read()
            MinArrayList.Add(objDR("Min"))
        End While

but got error on this line: MyConn.Open()

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

 

[ken1307]

anyone?

 

[happytechie]

That connection string is never going to work, you either need to specify a specific password or to use Integrated Security (windows authentication).

if you are using SQL Server 2005, when you installed it you had to give a password for the sa user, in my example this password is manager. In that case the connection string would be as below to connect to the database called asa on the local default SQL Server instance.

"server=localhost;uid=sa;pwd=manager;database=asa"

Have you looked at the tutorials on MSDN yet?

Paul

 

[ken1307]

That connection string is never going to work, you either need to specify a specific password or to use Integrated Security (windows authentication).

if you are using SQL Server 2005, when you installed it you had to give a password for the sa user, in my example this password is manager. In that case the connection string would be as below to connect to the database called asa on the local default SQL Server instance.

"server=localhost;uid=sa;pwd=manager;database=asa"

Have you looked at the tutorials on MSDN yet?

Paul

Hi there, thanks for the reply I dont know about the password for sa user because when I installed Visual Studio 2k5 , it didnt ask me for password or anything. I created the database (asa) using the "Add connection" with Windows Authentication which created an "offline" DB I think Im requesting an server name/username/password for SQL Server from my Uni but for the time being, how should I change the code to make it read from the offline DB one?

the Server Explorer looks like this:

the Connection String in Properties looks like this:

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Administrator\Desktop\ASA\ASA\asa.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

I will look at those tutorials properly today (just finished another coursework yesterday), thanks for the links

cheers
ken

 

[ken1307]

I just got login details but now it said

"Cannot open database requested in login 'mca4dt3'. Login fails.
Login failed for user 'mcai4dt3'."

any ideas?