Re: updating database in vb.net - Visual Basic Discussion Boards

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 | :(

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 | :)

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

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???

You must start using Parameters. Your Code is untidy

try something like this

C#

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

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

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

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???

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

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

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

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

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

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

Try something like this

Convert.ToInt32(TextBox1.Text);

Hope it help.

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

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

Make sure that the Data type and Dimension Mach your Table

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

[Message Deleted]

No! work it out yourself. It's your project not ours

Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.

well, thanks for the reply.
Smile | :)

)

Its your project, not ours. You do it.

..and given the question, a pretty poor choice of user name, as you clearly aren't Smile | :)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

This sounds much like a homework assignment. That's why you're getting the answers you are.

And they're right, even if it's not homework. If someone else writes the code for you, it's not your work you're turning in and you don't deserve to get the credit for it. And, you just doing yourself a diservice because you won't be prepared for either the next class, or the next project assignment you get.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

Hi, I'm using ado.net to connect to an access database. There are 2 tables "Collezione", bound to dgv1, and "Mazzo", bound to dgv2.
I have a problem with a button (see below for the code) that take a row from dgv1 and copy it in the dataset2 (das2) that is linked to dgv2. When the datarow that I want to copy it's just present in dataset2 I must increase the value in the quantity field in da2 without copy the row.

The code works, but when I copy the 3th different row it stops. Same thing when I try to increase the amount of the second row that I moved :S

Where I'm wrong? Thanks for help!

Here's the code of the button:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

Dim dr As DataRow
dr = das2.Tables("Mazzo").NewRow()

'copy the data from dgv1 to a datarow
Dim i, indexRiga1, colonne1, righeDas2 As Integer
colonne1 = dgv1.ColumnCount - 1
indexRiga1 = dgv1.CurrentRow.Index
If dgv1.SelectedRows.Count > 0 Then
For i = 0 To colonne1
dr(i) = dgv1.Item(i, indexRiga1).Value
Next
dr(11) = 1 'it's the amount index
End If

'if dataSet2 is empty, add the row
righeDas2 = das2.Tables("Mazzo").Rows.Count - 1
If righeDas2 < 0 Then
das2.Tables("Mazzo").Rows.Add(dr)
End If

'if dataSet2 is not empy, try to find if datarow is just present
For i = 0 To righeDas2
If das2.Tables("Mazzo").Rows(i).Item("Nome") = dr(1) And _
das2.Tables("Mazzo").Rows(i).Item("Col") = dr(5) And _
das2.Tables("Mazzo").Rows(i).Item("Tipo") = dr(6) Then

dr(11) = das2.Tables("Mazzo").Rows(i).Item("Giocate") + 1 'increase the amount
das2.Tables("Mazzo").Rows(i).Delete()
das2.Tables("Mazzo").Rows.Add(dr)

End If
Else
das2.Tables("Mazzo").Rows.Add(dr)
End If
Next

End Sub

I'd love to be able to help you, but this code doesn't make any sense and your description of what you need it to do doesn't either.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

I try to be more clear...
I have a access database. This database have 2 tables. The first table name is "Collezione" and the second "Mazzo".
In a form I have two datagridview one called "dgv1" and the last "dgv2". The first is bound to datas that are in "Collezione" and the other to data in "Mazzo".
I make a button that copy the report selected by user from dgv1 and add it to dgv2.

the problem is that the code doesn't work Sniff | :^)

How can I Connect database with Disconnected Architecture?

Good Morning Umesh_86

I Forgive you because you are new to this Forum. But next time Before you Post you must First Google. Promise me you will never commit this offense again.

here is your Answer

http://www.google.co.za/search?hl=en&q=Introduction+to+ADO.NET+in+VB.NET&meta=[^]

Hope it helps

Thank you

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

Hi all
i am working on a VB.net Project. this is my first project in vb.net and i need a autocomplete textbox which return entered text id..like dropdownlist.
I have seen some properties of textbox regarding autocomplete but that not return value of entered text.

Please Help
Thanks
Jagwinder Singh

Er. Jagwinder Singh

You need to set AutoCompleteMode and AutoCompleteSource to do this. If you set AutoCompleteSource as CustomSource, then AutoCompleteCustomSource should also be populated with list of values.

The word "politics" describes the process so well: "Poli" in Latin meaning "many" and "tics" meaning "bloodsucking creatures."

जय हिंद