Visual Basic

17-Feb-09 23:51

i m a starter to vb.net ..... i m using access database...... wenever i try to update databse using sql commands............ either it doesnt get updated..........smtymes it does on sm values.......or it enter 0 in database fields...

??????whether i use oledb parameters or direct values ......situation remains same?? Frown | :(

Eddy Vluggen18-Feb-09 1:43

Eddy Vluggen

18-Feb-09 1:43

Could you please post the code that you're using to perform the update?

Just a remark from a grumpy old programmer; try and write "real" sentences, with full words (no abbrvs.) and limit the number of dots. It makes your text more readable, which causes it to get read more, which in turn leads to more answers.

..and that's what you want after all, answers Smile | :)

I are troll Smile | :)

Vimalsoft(Pty) Ltd18-Feb-09 1:52

18-Feb-09 1:52

Well my friend below is right.We will never know what is the Problem until you show us the code you have written.
OMG | :OMG:

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za

parkash_C24-Feb-09 0:17

Re: updating database in vb.net [modified]

24-Feb-09 0:17

am i writing update statement wrong.when i take values direct from textboxes i write:

Dim cmd1 As New OleDbCommand("update table2 set grade= ' " & TextBox1.Text & " ' and stream= ' " & TextBox2.Text & " 'where name = ' " & TextBox3.Text & " ' ", con)

con.Open()
cmd1.ExecuteNonQuery()
con.Close()

cmd1.executenonquery returns 1 bt grade remains same in database while stream is reset to 0????? Sniff | :^)

shud i use oledb parameters,,,,,,tel me hw???

Vimalsoft(Pty) Ltd24-Feb-09 0:56

24-Feb-09 0:56

You must start using Parameters. Your Code is untidy

try something like this

Public int Update_Data(String Grade,String Stream ,String Name )
{
 
     con =new OleDbCommand(strcon);
     
     cmd1 = new OleDbCommand();
     cmd1.CommandText = "Update table2 set grade=? And Stream = ? where Name = ?";
     cmd1.Connection = con;
     cmd1.Parameters.Add("@Grade",SqlDbType.NVarChar, 63).Value = Grade;
     cmd1.Parameters.Add("@Stream ",SqlDbType.NVarChar, 63).Value = Stream ;
     cmd1.Parameters.Add("@Name ",SqlDbType.NVarChar, 63).Value = Name ;
     int Results = 0;

     try
     { 
             Con.Open();
                
        Results = cmd1.ExecuteNonQuery();

      }
      catch(SqlException)
        {
          throw;
        }
        finally
            {
                if (con != null)
                {
                    con.Close();
                }

            }
            return Results ;
        }

Try this , it might not be Perfect but now if there a Problem its easy to track.
Note that i wrote it here , so dont paste in VS and expect it to work, am just giving you an idea

Hope it help

modified on Tuesday, February 24, 2009 7:13 AM

parkash_C24-Feb-09 10:13

24-Feb-09 10:13

but u didnt tell me where to take the values from????......
i m using acess database......
Try
Dim i As Integer
Dim a10, a11, a12 As OleDbParameter

Dim cmd1 As New OleDbCommand("update table1 set stream= @a11,grade=@12 where stdid =@10", con)
a10 = cmd1.Parameters.Add("@a10", OleDbType.Integer, 32)
a10.Value = TextBox4.Text
a11 = cmd1.Parameters.Add("@a11", OleDbType.VarChar, 255)
a11.Value = TextBox5.Text
a12 = cmd1.Parameters.Add("@a12", OleDbType.VarChar, 255)
a12.Value = TextBox6.Text
con.Open()
i = cmd1.ExecuteNonQuery()
con.Close()
Label1.Text = i
Catch ex As Exception
Label1.Text = ex.Message.ToString()

End Try

if i put direct values ie stdid = 1 (which z the primary key)den only it gets updated
and strange updation. stream instead of taking value frm textbox5 takes value from textbox4??? Sigh | :sigh:

and its nt givin ane exception???

Vimalsoft(Pty) Ltd24-Feb-09 19:04

24-Feb-09 19:04

Good Morning parkash_C

The ?? that you see is the Parameters that are unknown. So in OLEDB we do things differently , we dont use a "@" as Parameter but we use the "?" to show that the Value is not yet known but it will be known at runtime. So your Update Statement is fine the way i wrote it for you.

The Other reason your code confuses you , is because its not a function. You just wrote the code, that is why you will not understand why it picks the wrong value. i have written an example code for you but you choose to disregard it

parkash_C24-Feb-09 10:21

24-Feb-09 10:21

do u think dere cn be problem in Access database cz i have used the same way before nd it worked

Vimalsoft(Pty) Ltd24-Feb-09 19:05

24-Feb-09 19:05

You connection looks fine , until you get an Error that is related to connection, but at the moment you said you are trying to Update but the changes does not take effect. So your connection is not a problem

parkash_C26-Feb-09 23:50

26-Feb-09 23:50

thanks..i kinda figured out the prob.
its dat i ws using numeric key bt nt converting textbox string into integer..it wrks fyne if key is string
bt i m nt able to find a conversion
i m using CINT nd Val on primary key
bt it updates each field with the primary key???
can u sugest me on conversion???

Thanks!!!plz reply soon

Vimalsoft(Pty) Ltd27-Feb-09 0:04

27-Feb-09 0:04

Try something like this

Convert.ToInt32(TextBox1.Text);

Hope it help.

parkash_C27-Feb-09 0:44

27-Feb-09 0:44

ya it cnverted it
bt chek dis one out

Try
Dim i As Integer

Dim a10, a11 As OleDbParameter

Dim cmd2 As New OleDbCommand("update table1 set stream= @a11 where stdid= @a10", con)
a10 = cmd2.Parameters.Add("@a10", OleDbType.Integer, 32)
a10.Value = Convert.ToInt32(t5.Text)
a11 = cmd2.Parameters.Add("@a11", OleDbType.VarChar, 255, "stream")
a11.Value = t6.Text

con.Open()
i = cmd2.ExecuteNonQuery()
con.Close()
Label8.Text = i

Catch ex As Exception
Label1.Text = ex.Message.ToString()
End Try

if i take stream value from textbox then it wrks....so no prob with the key firld
bt @a11 with stream is not wrking...
i think sm think is wrong the way i took oledb parameters
plz chek it

Vimalsoft(Pty) Ltd27-Feb-09 0:51