locked
Saving and retrieving from and to sql server with vb RRS feed

  • Question

  • I am facing problem in the following code, help anyone...my  code doesn't show anything when ever I start on my button "start recording" it starts to record every keystroke on the  and show it in a textbox and after that i am trying to get that data into text of a sql 2005 database with vb vs2008 but when ever I click on the button to show me the saved data , retrieve from database, it doesn't show anything, why?

    and I also want to create new row everytime the new object is created...thnx 

    Databse Class

    [code]

     

    Imports System.Data.SqlClient

     

    Public Class DataSaver

        Private sqlConnection1 As SqlConnection

        Private cmd As SqlCommand

        Private reader As SqlDataReader

        Private myCommand As String

     

        Public Sub writtingData(ByVal data As String)

            sqlConnection1 = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\Desktop\Projects\FinalProject\FinalProject\Database1.mdf;Integrated Security=True;User Instance=True")

     

            Try

                sqlConnection1.Open()

            Catch ex As Exception

                MsgBox("Error:" + ex.Message)

            End Try

     

            myCommand = "INSERT INTO SavedData(SavedData) Values('" & data & "')"

            cmd = New SqlCommand(myCommand, sqlConnection1)

     

            sqlConnection1.Close()

        End Sub

     

        Public Function readData() As String

            Dim readerData As String = Nothing

            sqlConnection1 = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\Desktop\Projects\FinalProject\FinalProject\Database1.mdf;Integrated Security=True;User Instance=True")

            myCommand = "SELECT * FROM SavedData"

     

            cmd = New SqlCommand(myCommand, sqlConnection1)

            cmd.Connection = sqlConnection1

            cmd.Connection.Open()

            Try

                reader = cmd.ExecuteReader()

     

                While reader.Read()

                    readerData = reader.ToString()

     

                End While

            Catch ex As Exception

                MsgBox("Error:" + ex.Message)

     

            Finally

                sqlConnection1.Close()

            End Try

            Return readerData

        End Function

    End Class

     

     

    [/code]

     

    Form Code

    [code]

     

    Imports FinalProject.KeyBoard

    Public Class Macro_Recorder

     

        Dim valKey As KeyBoard

        Dim saveData As DataSaver

     

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     

        End Sub

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            valKey = New KeyBoard()

            saveData = New DataSaver()

            Keyboard_Timer.Enabled = True

            Keyboard_Timer.Interval = 1

        End Sub

     

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            Keyboard_Timer.Enabled = False

     

     

        End Sub

     

        Private Sub Keyboard_Timer_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Keyboard_Timer.Tick

            valKey.CheckKeyStrokes() 'every MS this function will be called to check which key was pressed

            TextBox1.Text = valKey.getTxt() 'Getting the value from the keyboard and showing it on the form

            saveData.writtingData(TextBox1.Text)

        End Sub

     

     

        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

            TextBox2.Text = saveData.readData()

        End Sub

    End Class

     

    [/code]

     

     

     

     


    Wednesday, June 29, 2011 9:30 AM

Answers

  • Hi AorangZeb,

    Welcome to the MSDN Forum.

    Please take a look at shifad and Darnold's suggestions, and improve your code performance.

    I have overviewed your code:

    1. in the writtingData subroutine, please try to insert this line code cmd.executenonquery() before this line sqlConnection1.Close().

    2. The keyboard class is a customer class, so please make sure the method getTxT can return the key value.

    If you have any concerns , please feel free to let me know.

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, June 30, 2011 9:34 AM
    Moderator
  • hi mike feng

     

    Thanks for the note

    and you're right the .fill of the data adapter to fill the data table is enough to get the value 

     

    so the code will be 

     

     Dim cmd As New SqlClient.SqlCommand
     Dim dt As New DataTable()
     Dim con As New SqlConnection(GetConnectionString()) 
     cmd.Connection = con
     cmd.CommandType = CommandType.Text
     cmd.CommandText = "select ....."
     Dim adp As New SqlDataAdapter(cmd)
     adp.Fill(dt)
     urvalue = dt.Rows(11).Item(0)
    
    ' or urvalue = dt.Rows(10).Item(0)
    


     

    Regards

     


    Best Regards...Please mark as answer if my post is helpful

    Wednesday, July 6, 2011 12:11 PM

All replies

  • hey,

     

    Intead of using a reader for retrieving data, try using a data adapter

    try this code

     

     

        Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\Desktop\Projects\FinalProject\FinalProject\Database1.mdf;Integrated Security=True;User Instance=True")

        Dim cmd As New SqlCommand

        Dim adptr As New SqlDataAdapter

     

     Public Function readData() As datatable

     

                Dim dt As New DataTable

                cmd.Connection = con

                If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close()

                cmd.Connection.Open()

                cmd.CommandText = "SELECT * FROM SavedData"

                adptr.SelectCommand = cmd

                adptr.Fill(dt)


                cmd.Connection.Close()

                cmd.Dispose()


                Return dt

        End Function

     

     

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

    dim dt_data as datatable

    dt_data=readData()

    for i=0 to dt_data.rows.count-1

    msgbox(dt.rows(i).item(0).tostring())

    next

        End Sub

     

     

     

     

    Wednesday, June 29, 2011 12:45 PM
  • On 6/29/2011 5:30 AM, Aorangzeb wrote:
    > I am facing problem in the following code, help anyone...my code doesn't
    > show anything when ever I start on my button "start recording" it starts
    > to record every keystroke on the and show it in a textbox and after that
    > i am trying to get that data into text of a sql 2005 database with vb
    > vs2008 but when ever I click on the button to show me the saved data ,
    > retrieve from database, it doesn't show anything, why?
    >
     
    1) Your try/catches seem very messy and may not be covering all the code
    that could blow-up before you even start interacting with ADO.NET and
    SQL Command Objects as far as code blowing up before the open, executing
    the dynamic T-SQL or datareader execution .
     
    2) Have you stopped the execution in debug mode, taken the dynamic TSQL
    you generated, using the IDE Intermediate window and copied the dynamic
    TSQL form Intermediate window into the SQL Query pane on SQL Server 2005
    Manager and executed the TSQL to see if it works based on how you have
    generated the TSQL programmically?
     
    3) Have you used the debugger and single-step line by line at the point
    where you start interacting with ADO.NET and SQL Command Objects to see
    what is happening?
     
    Wednesday, June 29, 2011 1:51 PM
  • Hi AorangZeb,

    Welcome to the MSDN Forum.

    Please take a look at shifad and Darnold's suggestions, and improve your code performance.

    I have overviewed your code:

    1. in the writtingData subroutine, please try to insert this line code cmd.executenonquery() before this line sqlConnection1.Close().

    2. The keyboard class is a customer class, so please make sure the method getTxT can return the key value.

    If you have any concerns , please feel free to let me know.

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, June 30, 2011 9:34 AM
    Moderator
  • I have corrected the errors by myself....but now I am reading data from the database with SQLCommand object SQLReader, all I want to know is I want to read a specific row from my column myData...e.g.

    I want to read row 11 or random row from mycolumn, what is the correct syntax

    I am reading all the data(rows) from mycolumn with the command

     

    "SELECT mycolumn from Table1"

     

    what is the syntax to read the 11 or any row of this coulmn? 

    Friday, July 1, 2011 10:20 AM
  • hi

     

    customize this sample for ur purpose

     

     Dim cmd As New SqlClient.SqlCommand
      Dim dt As New DataTable()
      Dim con As New SqlConnection(GetConnectionString()) 
     con.Open()
      cmd.Connection = con
      cmd.CommandType = CommandType.Text
      cmd.CommandText = "select ....."
      cmd.ExecuteNonQuery()
      Dim adp As New SqlDataAdapter(cmd)
      adp.Fill(dt)
      urvalue = dt.Rows(11).Item(0)
    

    if u need the 11 th row so it will be 

     

     urvalue = dt.Rows(10).Item(0)

     

    because the first row is indexed with zero rows(0)


    Best Regards Please mark as answer if my post is helpful
    Saturday, July 2, 2011 10:01 AM
  • hi

     

    customize this sample for ur purpose

     

     Dim cmd As New SqlClient.SqlCommand
     Dim dt As New DataTable()
     Dim con As New SqlConnection(GetConnectionString()) 
     con.Open()
     cmd.Connection = con
     cmd.CommandType = CommandType.Text
     cmd.CommandText = "select ....."
     cmd.ExecuteNonQuery()
     Dim adp As New SqlDataAdapter(cmd)
     adp.Fill(dt)
     urvalue = dt.Rows(11).Item(0)
    

    if u need the 11 th row so it will be 

     

     urvalue = dt.Rows(10).Item(0)

     

    because the first row is indexed with zero rows(0)


    Best Regards Please mark as answer if my post is helpful


    Hi Yweb,

    Based on my understanding, the line  con.Open() and  cmd.ExecuteNonQuery() is excess, am I right?

    If you remove this two line, this should be the OP's answer.

    If you have any concerns, please feel free to let me know.

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Wednesday, July 6, 2011 11:37 AM
    Moderator
  • hi mike feng

     

    Thanks for the note

    and you're right the .fill of the data adapter to fill the data table is enough to get the value 

     

    so the code will be 

     

     Dim cmd As New SqlClient.SqlCommand
     Dim dt As New DataTable()
     Dim con As New SqlConnection(GetConnectionString()) 
     cmd.Connection = con
     cmd.CommandType = CommandType.Text
     cmd.CommandText = "select ....."
     Dim adp As New SqlDataAdapter(cmd)
     adp.Fill(dt)
     urvalue = dt.Rows(11).Item(0)
    
    ' or urvalue = dt.Rows(10).Item(0)
    


     

    Regards

     


    Best Regards...Please mark as answer if my post is helpful

    Wednesday, July 6, 2011 12:11 PM