Copy value From table to onther table in same Database in VS2010

[Abdelaziz_1]

Hey Guys i need to copy value from table as integer to onther table in same data base
like.. My program is to Add Silk Or Money into database by the user write the username into textbox1 and the amount of silk or money into textbox2 and the record need a value from onther database i need to find that value from Username..
need JID Which From Username can get his JID then insert into Direct
i need to Get JID From StrUSerID Then Put the the JID Into onther table by write the StrUserID into textbox1 and amount silk or money into textbox2
Click Here
then put it here
Click Here
but i got dat error
Click Here

insert query code:

Query = "insert into SK_Silk (JID,silk_own) Values ('" & TextBox1.Text & "' , '" & TextBox2.Text & "') Select JID From TB_User  where JID='" & TextBox1.Text & "'"

full code:

Imports System.Data.SqlClient
Public Class addsilk
    Public mysqlconn As SqlConnection
    Public command As SqlCommand




    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        mysqlconn = New SqlConnection
        mysqlconn.ConnectionString = "Data Source=" + Form2.txtServerName.Text + ";initial catalog=" + Form2.txtdatabasename.Text + ";user id=" + Form2.textusername.Text + ";password=" + Form2.txtpassword.Text

        Dim reader As SqlDataReader


        Try


            mysqlconn.Open()
            Dim Query As String


            Query = "insert into SK_Silk (JID,silk_own) Values ('" & TextBox1.Text & "' , '" & TextBox2.Text & "') Select JID From TB_User  where JID='" & TextBox1.Text & "'"

            command = New SqlCommand(Query, mysqlconn)
            reader = command.ExecuteReader
            ' NotifyIcon1.ShowBalloonTip(1000, "Information", "Has Been added Silk To" & TextBox2.Text, ToolTipIcon.Info)
            NotifyIcon1.ShowBalloonTip(1000, "Information", " Has Been Add " & TextBox2.Text & " Silk To " & TextBox1.Text, ToolTipIcon.Info)
            'MessageBox.Show("  Has Been Added " & TextBox2.Text & " Silk  To Username " & TextBox1.Text)
            mysqlconn.Close()



        Catch ex As Exception
            MessageBox.Show(ex.Message)



        End Try


    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        mysqlconn = New SqlConnection
        mysqlconn.ConnectionString = "Data Source=" + Form2.txtServerName.Text + ";initial catalog=" + Form2.txtdatabasename.Text + ";user id=" + Form2.textusername.Text + ";password=" + Form2.txtpassword.Text

        Dim reader As SqlDataReader


        Try


            mysqlconn.Open()
            Dim Query As String
            'Query = "ALTER TABLE TB_User ADD CONSTRAINT UQ_StrUserID_ID UNIQUE "
            'Query = "ALTER TABLE TB_User ADD CONSTRAINT UQ_USER_ID UNIQUE (StrUserID)"
            'Query = "Update TB_User set StrUserID='" & TextBox1.Text & "' ,passwod='" & TextBox2.Text & "' where StrUserID='" & TextBox1.Text & "'"
            'Query = "insert into SK_Silk (JID,silk_own)  Values ('" & TextBox1.Text & "' , '" & TextBox2.Text & "')"
            Query = "Update SK_Silk set JID='" & TextBox1.Text & "' ,silk_own='" & TextBox2.Text & "' where JID='" & TextBox1.Text & "'"
            ' Query = "insert into TB_User (StrUserID,password)  Values ('" & TextBox1.Text & "' , '" & TextBox2.Text & "')"
            'Query = "Alert  TB_User ADD CONSTRAINT UQ_StrUserID_UNIQUE  ('" & TextBox1.Text & "' , '" & TextBox2.Text & "')"
            command = New SqlCommand(Query, mysqlconn)
            reader = command.ExecuteReader
            ' NotifyIcon1.ShowBalloonTip(1000, "Information", "Has Been added Silk To" & TextBox2.Text, ToolTipIcon.Info)
            NotifyIcon1.ShowBalloonTip(1000, "Information", " Has Been Update " & TextBox2.Text & " Silk To " & TextBox1.Text, ToolTipIcon.Info)
            'MessageBox.Show("  Has Been Added " & TextBox2.Text & " Silk  To Username " & TextBox1.Text)
            mysqlconn.Close()



        Catch ex As Exception
            MessageBox.Show(ex.Message)



        End Try
    End Sub
End Class

[Santanu.Das]

your query should be

"insert into SK_Silk (JID,silk_own) Values (("Select JID From TB_User where StrUSerID='" & TextBox1.Text & "'") ," & Val(TextBox2.Text)) 

[Abdelaziz_1]

Can you explain more? What Val mean?

[Santanu.Das]

Val() function converts a string value to numerical value.

[Abdelaziz_1]

Ok igot wrror when I put your code can you solve..

[Santanu.Das]

What error you are facing. Pl. Post the exception.

[Abdelaziz_1]

I have send you the link... photo with error above..

[Santanu.Das]

There is a syntax to copy data from one table to another is

INSERT INTO <TABLENAME1> SELECT <COLUMN1>, <COLUMN2>, .......,<COLUMNn> FROM <TABLENAME2> WHERE <CONDITION>

Here the column names and types should be equal in two tables.
You can't use select command as an place holder of a parameter to store value in a table.

You can also use a user defined function to get and store value into the table. like

INSERT INTO <TABLENAME1> VALUES(XX(),VALUE1,VALUE2.....,VALUEn)

Here xx() is a User defined function which can return a value to store in the table.

[Abdelaziz_1]

Bro I don't understand iam beg.. so can you edit the full code for me ... just take it copy and paste in prog... and I will understand what you say.. I need to insert into sk_silk jid and silk_own .. but befot this progress I need select jid by username in table tb_user by textbox1..

[Santanu.Das]

Sorry ! I am not the right person to do your home works.

[Abdelaziz_1]

Good comment you are right.thnx alot for.your help..

[Abdelaziz_1]

But your above codes where I can put textbox1 place..

[Santanu.Das]

I just give you an example where from you can get your ans.

 Private Sub ButtonSave_Click(sender As System.Object, e As System.EventArgs) Handles ButtonSave.Click
        Dim conn As New MySql.Data.MySqlClient.MySqlConnection
        conn.ConnectionString = "Server=127.0.0.1;Port=3306;UID=root;Pwd=1234;Database=new;"

        Try
            If conn.State = System.Data.ConnectionState.Closed Then
                conn.Open()
            End If

            Dim cmd As New MySql.Data.MySqlClient.MySqlCommand
            cmd.CommandType = CommandType.Text

            cmd.CommandType = CommandType.Text
            cmd.CommandText = "Insert Into db_silk Values (@jid, @slkown)"
            cmd.Parameters.AddWithValue("@jid", ReturnJID(TextBox1.Text))
            cmd.Parameters.AddWithValue("@slkown", Val(TextBox4.Text))

            cmd.Connection = conn

            cmd.ExecuteNonQuery()
            cmd.Dispose()

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        Finally
            conn.Close()
        End Try
    End Sub

    'Function to return user ID
    Private Function ReturnJID(struid As String) As Integer
        Dim result As Integer = 0
        Dim conn As New MySql.Data.MySqlClient.MySqlConnection
        conn.ConnectionString = "Server=127.0.0.1;Port=3306;UID=root;Pwd=1234;Database=new;"

        Try
            If conn.State = System.Data.ConnectionState.Closed Then
                conn.Open()
            End If

            Dim cmd As New MySql.Data.MySqlClient.MySqlCommand
            cmd.CommandType = CommandType.Text

            cmd.CommandType = CommandType.Text
            cmd.CommandText = "Select tb_user.jid From tb_user Where tb_user.struserid= @struid"
            cmd.Parameters.AddWithValue("@struid", struid)
            cmd.Connection = conn
            Dim rd As MySql.Data.MySqlClient.MySqlDataReader = cmd.ExecuteReader()

            If rd.HasRows() Then
                rd.Read()
                result = rd("jid")
            End If
            rd.Close()
            cmd.ExecuteNonQuery()
            cmd.Dispose()

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        Finally
            conn.Close()
        End Try

        Return result
    End Function
End Class

*** Always use parameterised query to avoide SQL injections to your database system.