Posts by savedlema

Reverend, Everything you wrote in your reply is new to me!, So, first I want to thank you!

To begin with it, put your code into a form I created as it is, then to experiment with it, I assumed your code is supposed to open cmd.exe, and I think, open the directory "/c dir /s e:\series") so I changed "/c dir /s e:\series" to "/c dir /s e:\CCNA" (there is a folder in my E: named CCNA in my computer) I think it was supposed to open the directory e:/ccna, but nothing happens when I load the form.

Did I misunderstand something?

I hope you ain't fed up helping the youngman.

Reverend, I apologize for the violation. I've read the Daniweb rules again.(But I think there should be a way of asking the asker of a solved thread how he/she solved it if he did not say in the thread?, I understand we're not allowed to ask by email or private msg)

And, Thank you for your response, I tried with the code you gave me, but I still got "The system can not find the file specified" error msg. Yet, if I take the contents of (txtProgram.Text) and join with contents of (txtArguments.Text) in a command prompt, it works well.

Any help please?

Hi all!

I'm stuck somewhere with starting a process from vb.net.
I want to start a mysqldump process from my vb.net. This code works perfect if I run it from windows command prompt:

 "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --user=root --password=mypassword --host=localhost --port=3306 --database sakila > "C:\backup\sakilabackup.sql"""

Then I took it to my VB App and put it in :

Process.Start ( "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --user=root --password=mypassword --host=localhost --port=3306 --database sakila > "C:\backup\sakilalbackup.sql""")

But If I put this to VB, it will only ofcourse, read only to the second " (that's "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" )

So, I decided to break the code into blocks as follows:

        Dim stat As String
        Dim stat2 As String
        Dim stat3 As String
        Dim stat4 As String

        stat4 = """"
        stat = "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe"""
        stat2 = "--user=root --password=mkwatuke --host=localhost --port=3306 --database sakila >"
        stat3 = "C:\backup\shekelbackup.sql"""

Then I joined all these stats to produce one line of code by this:

txtPath.Text = stat4 & "" & stat & " " & stat2 & "" & stat4 & stat3 & stat4 & stat4 & "" (I have a textbox called txtPath)

On this txtPath textbox, the code looks similar to the one I executed with command prompt.

Then I did this:

Process.Start(txtPath.Text)

but, upon running this, I get the error "The system cannot find the file specified"
I even tried Process.Start (and put the all the 'stats' here), but I get the same problem.

For the connection:

Dim con as New MysqlConnection
Dim connectionstring As String

con = New MysqlConnection
connectionstring = ("Server = 127.0.0.1; User = root; password = mypassword; database = databasename")
con.ConnectionString = connectionstring 'the one defined in the previous line

con.open 'open the connection

'do what you want to do

con.close 'close the connection

(My code is for mysql, but i hope it works with SQL too.)
Try it and come back here if there is anything.

IIM..I can't thank you enough. Your solution worked for me.
But, since the database 'dbname' already existed, I decided to run 'delete database if exists dbname' first.

So, your solution works, and now I'm ready to take the code with me to the VB.NET app that I'm developing.

Thank you for your continued support, forgive me when I couldn't follow instructions.

Also, thanks for your great blog:http://adityangoyal.blogspot.in/2013/04/importexport-mysql-database.html

I hope this thread will help someone else in the future.
Again, thank you (and all others out there who contributed)

Pixelsoul... See, I have attached the text I took from the command prompt.

I'd also like to update that, I tried to add "" (to get: mysql "–uroot -ppassword -hlocalhost dbname < C:\Backup\dbname.sql"

to which I now get the error msg "Error 1045 (28000) access denied for user 'odbc'@'localhost' (Using password NO)

First, I think may be the correct syntax should include " at the beginning and the end (as my try)
If that's so, then I need to solve the "Access denied error":

-There is no such a user as 'odbc' in my mysql database.
-The user 'root' is indeed there, with all priviledges.
-I have supplied the correct password.
-From google search, somebody said when he changed 'root' to 'odbc' in the code, it worked, but not for me.
-I'm continuing my google search on this error but haven't found a solution yet, though some people have got this error.

I will appreciate more help, please.

Thanks pixel, and ops, sorry, I had forgotten the /bin. I tried your two steps and, upon running the second one, it brought me lots of information about mysql version, Oracle...then it brough that list which is always brought to you when you miss a command. It brought me a lot of stuff, which I think, means that we missed something there in the second step. Can you figure it out please?

*Let me also declare that I'm looking for a script/code to take with me to an application I'm developing with VB.NET.

Pixesoul, Sorry, I didn't say; I had tried:

"C:\Program Files\MySQL\MySQL Server 5.5\mysql --user=root --password=password --host= localhost --port= 3306 --database=dbname < C:\Backup\dbname.sql"

That, to no avail. I still get the error about filename,directory name..syntax error. Can you figure out where is the problem? I have tried this on XP and Win 7.

Friends, I have kept on tying and trying. What's wrong with this one?

"C:\Program Files\MySQL\MySQL Server 5.5\mysql --user=root --password=password --host= localhost --port= 3306 --database=dbname < C:/Backup/dbname.sql"

I get "The filename, directory name, or volume lable syntax is incorrect" error messsage. I have googled for this error to no avail as to do with mysql.

• My mysql server is up and running with no problem. (Even the mysqldump command works)

• Problem is the same even if I open the command prompt from C:\Program Files\MySQL\MySQL Server 5.5\mysql
  *I have tried to remove "", did not help.

  For this one, I will really appreciate any help.

Now I don't know why I get "ERROR :2003(HY000):Can't connect to mysql...on localhost?

I use a different port for mysql so I include it in the code:

mysql --u root --pMyPassword --h localhost --port 3301 databasename < C:/Backup/databasename.sql

More help please?

Thanks all,
I had tried to include the full path to my code. For my case, the code would be,

"C:\Program Files\MySQL\MySQL Server 5.5\mysql -u root -pMyPassword databasename < C:\Backup\databasename.sql"

But then it would say, "The filename, directory name, or volume lable syntax is incorrect"

I also tried:

"C:\Program Files\MySQL\MySQL Server 5.5\mysql" -u root -pMyPassword databasename < C:\Backup\databasename.sql

and

"C:\Program Files\MySQL\MySQL Server 5.5\mysql" --u root --pMyPassword databasename < C:\Backup\databasename.sql

With no success.

I would appreciate further help please.

imBaCodes, there I had tried that, and there something that I could not figure out:
-I backed up a database successfully with mysqldump.
-In my live database, I deleted one user from a table that contained users information.
-I restored the database (with the same codes as you gave me) from the backup I created in step one. The command prompt shows that everything went well.
-Now I accessed the database to see if the record/user I deleted is back (since I restored the backup that contained him), the user WAS NOT THERE.
-I tried the same thing over and over, no success. I wonder what this is.
Can you explain that imBaCodes?

Hi friends!
I have some trouble restoring a mysql database that I backed up with mysqldump. I have been trying to do this from a command prompt (Windows)

This is my code:

mysql -u root -pPassword databasename < C:\Backup\databaseNameToBeRestored.sql

With this, I get the message that "mysql" is not recognized as internal or external or external command, operable program or batch file.

I added "" to get "mysql -u root -pPassword databasename < C:\Backup\databaseNameToBeRestored.sql" But to this I get error message "The filename, directory name, or volume lable syntax is incorrect"

This man here said the code I tried worked for him.

Any help please? (and if you don't mind, I would appreciate if somebody could told me why changing > to < in the mysqldump command does not work 100% in restoring the backed database)

Hi Gusano!
The trick was to remove all the "" in the second part of the command, and end up with something like this:

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe", --user=root --password =mypassword --host =localhost --port=3301 --databases MySQL databasename -r "C:\GreatestHR Backup\BackUpDataBase.sql"

I have no explanations for that.
Thanks for offering to help.

Rev, I was able to do a lot with Application Settings. But, I realize that, if I use 'user scope', these settings will not be there when the app is restarted, so a user will need to fill them everytime the app is started (okay??)

But, I would like for these settings to be stored somewhere and allow the user to change them whenever needed (may be we changed the mysql server/its IP Address/e.t.c)
I think then this has to be in 'application scope' settings, but VB says they are "Read Only". How do I do this?

I want like, I remember I once installed a library app that asked for database path only the first time it was run. I think you get me.
P.S: I also thought it would be a good idea to start another discussion here

I will appreciate any help on this, or any alternatives.

With VB.NET,
I'm developing an application that uses a MYSQL database. When I will deploy the application to another PC, the app and the MYSQL database will reside in difference computers.

What I'd like: After I install the app to a PC, when it first run, I want it to ask for database connection details (server,username,password,database e.t.c) then, save all of this to the Application Settings and use these everytime the app runs. I want the user to BE ABLE TO CHANGE these settings anytime (in case we change the mysql server e.t.c)

I can: I can code my app to write variables to the Application Settings and also read from them, but I can only work with settings with 'user' scope. If I'm not mistaken, these user-scope settings will not be there when the application is restarted. And this is why I think I need application scope settings, but VB says they are "Read only". I would like for these settings to be there permanently, but be specified by a user when the app runs and store them.

I once installed one library application which asked us to specify where the database is located, that was nice.

Any help on how I can deal with application scope settings? Or any alternatives?
Thanks.

Same problem. I think Lethugs first link is not working? (Sorry for jumping in!)

Tried this in the past, I couldn't do it and gave up. I'm glad to see that you guys could do that, and I have got to study your solution.
Thanks!

Solved it.

Pritaeas, I can't be more more thankful! It worked. I just see removing the "" made it work.

Thank you much, and thank you too for the link to the manual, I will study it.

Will you please let me know your working solution? I'm in need of the same.

Thanks gusano, though sorry I couldn't get you so well. But, I tried to remove all unwanted spaces.
But now I get a different error message:

"Mysqldump: Got error:1044:Access denied for user ''@'localhost' to database ',' when selecting the database. (this is exactly how the error msg says, I didn't remove a thing in the '')

My code for now is:

 "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe", "--user=root --password =mypassword" "--host =localhost --port=3301 --databases MySQL databasename -r ""C:\GreatestHR Backup\BackUpDataBase.sql"""

Please, I would appreciate more help on this.

Thank you all, but I haven't been able to solve the problem:
1. From dorco's suggestion: I followed the link and it says binding would be an issue only if I was connecting to another IP address, say a public IP or just any other. But, for my case, the server is on the same computer (localhost) so I presume the binding is set correctly.But thank you for the article, of course it will help me in the future since I'm going to do that.

2.Pritaes, thank you. I added the = to port but no success sir. I will post my code below.

3.Nickw, thank you. I did what you said (host=localhost) no success.

My code now looks like this:

    "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe", "--user=root --password =mypassword" "--host = localhost --port=3301 --databases MySQL databasename -r ""C:\GreatestHR Backup\BackUpDataBase.sql"""

But now I get a different error message:"Mysqldump: Got error:1044:Access denied for user ''@'localhost' to database ',' when selecting the database. (this is exactly how the error msg says, I didn't remove a thing in the '')

Please, I will appreciate more help.

Hi all!

I have a big problem that I would like some help. I use Mysql Workbenc 5.2CE. I changed the default port to 3301. The mysql server is up and running well (I access it from an app I'm making with VB.NET). The problem is I can't get mysqldump to backup the database. I'm using the following code (in the command prompt)

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe", "--user=root  --password =mypassword" "--host = 127.0.0.1 --port 3301 --databases MySQL databasename -r ""C:\GreatestHR Backup\BackUpDataBase.sql"""

(Note: I would like to use an IP Address for host)

When I run that, I get the error: "mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10049)
when trying to connect ".

Can someone please help me on this? (People are not even talking about mysql's error 10049 online.)

I will appreciate any help.
Thanks.

Thanks gusano. I did what you said and I was able to see this error msg:

"mysqldump:Got error:2005: Unknown MYSQL Server host '127.0.0.1'--databases MySQL databasename -r ""C:\GreatestHR Backup\BackUpDataBase.sql"""

But, the mysql server is really there and working, I can fetch and send data to it with my application. This problem happens only with this backup thing.

• I tried to use "localhost" instead of 127.0.0.1 but same problem. I also tried to remove 'password' and add a port number, same problem.

Any advice?

Hi all!

I'm trying to perform a mysql database from vb.net. I use this code:

 Process.Start("C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.44\mysqldump.exe", "--user=root --password=mypassword " _
                      & "--host=127.0.0.1 --databases MySQL databasename -r ""C:\GreatestHR Backup\BackUpDataBase.sql""")

When this code runs, a command prompt window opens and goes away immediately that i can't even read what it says. When I go to the output folder specified in the code, there is really BackupDatabase.sql but with only 0Kb, while the actual database has data in it. What could be the problem?

I repeat, I use mysql database in Mysql Workbenc 5.2.

F1 please.

Thanks Rev. But, I think you must have forgot in my question I stated I'm using mysql (Mysql Workbench 5.2). Does it make any difference?

First, I would thank all the Daniweb team for making this useful site available for us.
I'm developing with mysql database. I always declared the details about the database I use at the top of every form. I mean, the database name,username,password and everything about it. We know this will not be great when I deploy my app to other computers. I would like to know the best way to save these credentials and allow someone to set them after installling the app (like how it is done with other apps). May be saving them to a text (.txt) file?
How do I save the details/variables to a text file?
How do I read/call these variables to my forms?
P.S: I have read this discussion but everything was strange to me!

I will appreciate any help. Also if there is anything I should read online.

Reverend, in my country, we call high school classes like "form1,form2..." its like 'class1,class2, or 'grade1','grade2' e.t.c. And, "Cooker","MalipoAda (in our local language) are the contributions/payments students are required to pay. (which are added,edited,deleted in the tblcontributions (and everywhere they appear)).
But, I'm considering redoing my tables as you suggested.

Thanks so far.

The structure:
The system I'm creating is for managing students contributions payments. The tables I think are concerned here:

1.tblContributions:(name,amount,form1,form2,form3,form4):this keeps names of contributions,amount and specify which class (form1....)is eligible for this,Yes for eligible and No for not eligible.

2.tlbpayments (studentid,class, [all contributions names in tblcontributions]:Payments made are stored here.
3.tblstudents (studentid,firstname,middlename,surname,class...):keeps information about a student.

I'm done with receiving payments from students and recording them. Now I want to view an individual student account to see if he has any oustanding debts to pay. I've been able to search that student (with student Id) and put his names and class in textboxes on a form. Then on the same form, I want to pull out his payment records (from tblpayments) by searching for his studentid, and using his class(in the textbox) as a search criterion.

I would like to the following results:
Studentid,firstname,midlename,surname, (and only those contributions which has a 'Yes' for this student's class (in the tblcontributis) the reason for this is, there is no point in indicating that a form1 student has not paid a fee for Form2 examination, while he is not supposed to pay it)

I'm trying to attach screenshots of the tables with this (I hope they shows up). Did I make it clear?
I see this is long.
Thanks.

Reverend,
Your code works, but, I've discoverd I need more than what I though. I'm trying to think if what I really want can be done: Is there a way I can instruct your code above that, instead of selecting p.* it should select everything in p but with the condition (SELECT name FROM tblcontributions WHERE form1 ='yes').
To make it clear: the table payments contains details of students paying their contributions. All school contributions are also columns in this table. Now, some contributions are only valid for some classes, so, when I want to to view students's account/made payments, I don't want the column for a contribution that's not valid for him to be shown.I have another table (see my inline code) that can filter contributions by looking if a contribution has a 'Yes' or 'No' for a given class.

-I understand my case may be confusing. Sorry.

Thanks Reverend!

For parameters, I use the following (I learnt this from you!-I appreciate):

        cmd.Parameters.AddWithValue("@studentid", txtAccountId.Text)
        cmd.Parameters.AddWithValue("@class", txtClass.Text)

The error msg I have been getting (with my first code) is "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

I can't see the syntax error it says.

Hi all!
I use VB.NET + Mysql.
I have two tables (tblstudents & tblpayments). I want to view payments details of a selected student. Thus, I'd like to select some values (firstname,middlename,surname) from tblstudents and (all values from tblpayments WHERE class = @class -I know how to deal with @class, its a value in a textbox and present as a column in tblpayments) I want to INNER JOIN the tables with condition that (tblstudents.studentid = tblpayments.studentid).

The table tblpayments has columns (studentid,class and some contributions/payments names) I want to filter these contirbution/payments because some are valid for some classes only.

I have tried the following codes:

        SQL = "SELECT ( SELECT tblstudents.FirstName,tblstudents.MiddleName,tblstudents.Surname, tblpayments.* " _
                & "FROM tblstudents INNER JOIN tblpayments ON " _
                & "tblpayments.studentid = tblstudents.studentid " _
                & "WHERE tblpayments.class = @class AND tblpayments.studentid= @studentid"

but this did not deal with the first WHERE condition (tblpayments.class = @class), it brought results regardless of the @class.
I tried this code:

       SQL = "SELECT (SELECT tblstudents.FirstName,tblstudents.MiddleName,tblstudents.Surname " _
                & "FROM tblstudents WHERE tblpayments.studentid= @studentid) " _
                & "(SELECT tblpayments.* " _
                & "WHERE tblpayments.class = @class)"

but VB said there was an error with the code.
I tried this solution from Daniweb to no avail.

I will appreciate any help please.
Thanks.

Hi friends!
I got one puzzle. I wonder if there is a way I can create a table but get a table name from a value of the textbox/combo box control. Does anyone have an idea?
I mean something like "CREATE TABLE (TextBox1.Text) (".....)"

I wonder if that is really possible. I will appreciate any imputs.
Thanks.

There is a success. Thank you all. SOLVED.

Thanks all..Then, why wouldn't this work? I want to fill the SELECT results to the dataset, and then fetch them from the dataset to textboxes..but this piece of code...

       ConnectionSettings()
        con.Open()
        Dim sql As String
        Dim ds As DataSet = New DataSet
        Dim da As MySqlDataAdapter
        Dim RowsNo As Integer

        cmd = New MySqlCommand("", con)
        sql = "SELECT * FROM tblStudents WHERE StudentID = @studentid"
        cmd.CommandText = sql

        cmd.Parameters.AddWithValue("@studentid", txtStudentID.Text)
        cmd.ExecuteNonQuery()

        da = New MySqlDataAdapter(sql, con)
        da.Fill(ds, "Students")

Does not work. With this, I get the error "The parameter @StudentID must be defined"

If I comment out:

            'da = New MySqlDataAdapter(sql, con)
            'da.Fill(ds, "Students")

I get no error msg (I assume no problem up to that code), even when I add Msgbox ("Success") after the 'cmd.ExecuteNonQuery() 'Sucess' will be displayed, meaning there is no problem to that line. I think I need a little help on how to put the results of the above SELECT to a dataset (MySQL)
Thanks all.

I also wonder why php came in here! Why not just from VB.NET to MySQL as G Waddell said?

Update:
I have been able to solve this by adding:

     cmd = New MySqlCommand("", con)
    cmd.CommandText = "UPDATE tblStudents SET Surname = @Surname

But I would love some explanation please (I'm new to VB+Mysql)

Hi!
I just learnt to use the "Parameters.AddWithValue" to prevent harm of sql injections.However, I have only been able to do this with the INSERT, but I have trouble using that with SELECT and UPDATE. Please Look at my code with UPDATE

        Try
            ConnectionSettings()
            con.Open()

            Dim SqlUpdate As String

            SqlUpdate = "UPDATE tblStudents SET Surname = @Surname"
            cmd.Parameters.AddWithValue("@Surname", txtSurname.Text)

            cmd = New MySqlCommand(SqlUpdate, con)
            cmd.ExecuteNonQuery()

            MsgBox("Updated")
            con.Close()

        Catch ex As MySqlException
            MsgBox("Error " & ex.Message)
        End Try

The cmd.ExecuteNonQuery() was highlighted and I can also see the error msg "The Parameter '@Surname' must be defined", I thought I had defined it by cmd.Parameters.AddWithValue("@Surname", txtSurname.Text)? I get the same with SELECT statement.I've tried a Google search to no avail.

Any help please?

Thanks.

I got through it already. But, I would like to thank you Rev a lot. I had gotten into trouble when a name with an ' would be inserted. I think I'll be looking forward into learning some other SQL Injection prevention methods. Thumbs up for you and other contributors here too!

I find this to be useful tutorial to me, I've just come into a problem while inserting a person's name which has an ' in it.

Will this code provided by Jim work in VB.NET + Access? or its just for SQL?
Using VB.NET with Access, what should be wrong with this code:

cmd.CommandText = "INSERT INTO tblDetails (StudentID, FirstName) " _
                    & (" VALUES @StudentID, @FirstName ")

            cmd.Parameters.AddWithValue("@StudentID ", txtID.Text)
            cmd.Parameters.AddWithValue("@FirstName ", txtFirstName.Text)
                cmd.ExecuteNonQuery()

I get "object reference not set to an instance of an object". Why?
(I hope I didn't offend you by posting this in this thread)

Sounds difficult for Access, well, I found this solution online, try it and see if it works:
`

Just Share your folder database

and change path other user...

like this...

Collapse | Copy Code

Dim MDBConnString_ As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\172.16.0.233\DatabaseFolder\DatabaseName.mdb;"

Let us know what happens.

I had used another long long way to accomplish that, But your solution was the perfect one.

Reverend,
I tried you code, tested for NULL values and it works very well. And the code isn't that bulky!
I would like to thank you sincerely for your geneorous support.
I hope the thread will be useful to others too.
Thank you so much.

Thanks Reverend.
Can you tell what is wrong with this?

"UPDATE tblClass1 SET Total= SUM (IIF(ISNULL([Physics]),0,[Physics])) As Phy, " _
                    + "SUM(IIF(ISNULL([Chemistry]),0,[Chemistry])) As Chem," _
                    + "SUM(IIF(ISNULL([Civics]),0,[Civics])) As Civ, " _
                    + "SUM(IIF(ISNULL([Biology]),0,[Biology])) As Bios," _
                    + "SUM(IIF(ISNULL([Geography]),0,[Geography])) As Geo, " _
                    + "SUM(IIF(ISNULL([Physics]),0,[Physics]))+ " _
                    + "SUM(IIF(ISNULL([Chemistry]),0,[Chemistry]))+ " _
                    + "SUM(IIF(ISNULL([Civics]),0,[Civics]))+ " _
                    + "SUM(IIF(ISNULL([Biology]),0,[Biology]))+ " _
                    + "SUM(IIF(ISNULL([Geography]),0,[Geography])) As Total FROM tblForm1 " _
                    + "WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

The part of the code without "UPDATE tblClass1 SET Total=.." Works fine, (I mean with the INSERT) So, the rest of the code has no problem.
The error msg is "Syntax error (missing operator) in query expression 'SUM(IIF(ISNULL([Physics]),0,[Physics])) As Phy'."

Thanks Reverend!
Please, I want to INSERT the 'Total' obtained by this query into another field in the SAME TABLE. I have tried the following:

INSERT INTO Total(this is a column) SELECT ...

Returned "Could not find output table 'Total'." Error msg.

And,

INSERT INTO [tblForm1.Total] SELECT ...

Returned "Could not find output table 'tblForm1.Total'." Error msg.

And,

INSERT INTO tblForm1 (total) SELECT

returned "Number of query values and destination fields are not the same."

INSERT INTO works fine when moving values from one table to another but hasn't from my case.

Anyway to do that? PS:I thought its good to join this here rather than starting a new thread.

Thank you great friends! The trick however, was that NZ works only within Access database and can't be used from VB. The solution was to use (IIF(ISNULL to all of my code, even to calculating SUM. I hope the thread might be helpful to others.

Thanks all, you are great!

Hi all!
I have a table with (StudentID,Physics,Chemistry,Total,Average)
I want to pull values of Physics,Chemistry and calculate Total and then Average for a specific StudentID. With the following code:

"SELECT SUM (IIF(ISNULL([Physics]),0,[Physics])) As Phy, SUM (IIF(ISNULL([Chemistry]),0,[Chemistry])) As Chem, SUM([Physics])+Sum([Chemistry]) as Total FROM tblForm1 WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

I get the following:

1. If all the values exists in the table, everything is fine, it fetches the values and calculates the sum.
2. But if one or both values are null (not filled) they are replaced with '0' but it does not do the Sum (calculate the total).

Can someone please show me the way through this? I want it to calculate the sum even if one or both values are null (the total will then be '0')

I will appreciate any inputs please. (I use an Access .mdb database)

Thanks Reverend. I decided to do it the long way. Create a table and then drop it. But, thank you so much.
I mark this solved.

Why do I get an error creating a temporary table with something even as simple as this:"CREATE TABLE #Results"? But this "CREATE TABLE Results" works. I thought we put # when we need to create temporary tables (Access), its like I've once done that. Help on that please?
Thanks!