VB.net 2008/2005 DB search and list

[smile_pls]

Hello DW!,
I am in a beginning stage vb user. I need a small help.

I need to create a program, if I entered the "product code" in a text box / search box, it wants to display its full details from database, such as Product code, product name, description and picture.

so, can any one tell me how am I wanna start this?

I dont need complete codes, I need advices and the facts that I need to consider, i like to try it my own.

thanks in advance.
Anee

[ntiyiso]

Hi,

I assume u r using SQL server 2005/2008, rty the code below,
How ever to display your picture I would suggest you store the directory of your picture in the database.

Dim connectionStr As String = "Data Source=.;Initial Catalog=VideoShop_SMDB;Integrated Security=True" --Database connection
Dim conn As New SqlConnection(connectionStr)

--Put this code under your button.
Try
conn.Open()

Dim cmdSearch As New SqlCommand("select * from your table where productcode = " & Me.txtproductCode.Text & "" )

Dim stReader As SqlDataReader

stReader = cmdSearch.ExecuteReader
If stReader.Read Then

Me.txtproductCode.Text = stReader(0)
Me.txtName.Text = stReader(1)
Me.txtDescription.Text = stReader(2)
Me.txtPicture.Text = stReader(3)

stReader.Close()
conn.Close()

Else
MsgBox("Record could not be found")
stReader.Close()
conn.Close()

End If

Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try

[navinkumar]

I also kinda have the same problem....
I have installed visual studio 2008
I have installed sql server 2005
Now the problem is i want to make a application
in which i enter the product id in text box and i shud get all other details such as cost quantity in cost textbox and quantity textbox respectively..
I want the database to be in sql....
Plz giv me an entire tutorial on how to do dis....
Also if ne 1 knows something about crystal reports then plz guide me thru....

[ZidaneXero]

Hi,

I assume u r using SQL server 2005/2008, rty the code below,
How ever to display your picture I would suggest you store the directory of your picture in the database.

Dim connectionStr As String = "Data Source=.;Initial Catalog=VideoShop_SMDB;Integrated Security=True" --Database connection
Dim conn As New SqlConnection(connectionStr)

--Put this code under your button.
Try
conn.Open()

Dim cmdSearch As New SqlCommand("select * from your table where productcode = " & Me.txtproductCode.Text & "" )

Dim stReader As SqlDataReader

stReader = cmdSearch.ExecuteReader
If stReader.Read Then

Me.txtproductCode.Text = stReader(0)
Me.txtName.Text = stReader(1)
Me.txtDescription.Text = stReader(2)
Me.txtPicture.Text = stReader(3)

stReader.Close()
conn.Close()

Else
MsgBox("Record could not be found")
stReader.Close()
conn.Close()

End If

Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try

I tried this and goes directly to the Exception Message it doesnt connect to the database, can you please help me with this issue here is my code

Imports System.Data.SqlClient

Public Class Ventas

    Dim connectionStr As String = "Data Source=.\ISCDEV;Initial Catalog=mangiamos;Integrated Security=True" '--Database connection
    Dim conn As New SqlConnection(connectionStr)


    Private Sub Ventas_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

    End Sub

    Private Sub go_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles go.Click
        Try
            conn.Open()

            Dim cmdSearch As New SqlCommand("Select * from Clientes = " & Me.telefonobox.Text & "")


            Dim stReader As SqlDataReader

            stReader = cmdSearch.ExecuteReader

            If stReader.Read Then

                Me.clientebox.Text = stReader(0)
                Me.telefono.Text = stReader(1)
                Me.nombrebox.Text = stReader(2)
                Me.dirbox.Text = stReader(3)
                Me.detallesbox.Text = stReader(4)
                ' Me.txtPicture.Text = stReader(3)

                stReader.Close()
                conn.Close()

            Else
                MsgBox("No entry has been found", MsgBoxStyle.Information = MsgBoxStyle.OkOnly)
                stReader.Close()
                conn.Close()

            End If

        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Information = MsgBoxStyle.Information)
        End Try
    End Sub

[TomW]

If that much isnt working then either the name given to your DataSource or Database is incorrect. Unless of course its not actually windows authenticated and you need a login & password.

Dim strCon As String = ""
strCon = "Data Source=.\ISCDEV;Initial Catalog=mangiamos;Integrated Security=True" 

Using con As New SqlConnection(strCon)
    Try
        con.Open
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Using

[TomW]

Try
            conn.Open()

            'There are a few problems with "cmdSearch"
            '01) Your query has no Where clause
            '02) Single quotes are not placed around your comparison
            '03) Concatenating strings wastes memory resources 
            '    and should be using parameters anyway
            '04) the command isnt given a connection to use
            Dim cmdSearch As New SqlCommand("Select * from Clientes = " & Me.telefonobox.Text & "")


            Dim stReader As SqlDataReader

            stReader = cmdSearch.ExecuteReader

            If stReader.Read Then

                'you are applying an object to a text value
                'without explicitly retrieving its proper datatype value
                'also if any of these values are null
                'you will receive an error since you
                'are not checking for null values
                Me.clientebox.Text = stReader(0)
                Me.telefono.Text = stReader(1)
                Me.nombrebox.Text = stReader(2)
                Me.dirbox.Text = stReader(3)
                Me.detallesbox.Text = stReader(4)
                ' Me.txtPicture.Text = stReader(3)

                stReader.Close()
                conn.Close()

            Else
                MsgBox("No entry has been found", MsgBoxStyle.Information = MsgBoxStyle.OkOnly)
                stReader.Close()
                conn.Close()

            End If

            'Could be written here once
            'instead of twice above
            'stReader.Close()
            'conn.Close()

        Catch ex As Exception
            'Option Strict would disallow the below conversion
            MsgBox(ex.Message.ToString, MsgBoxStyle.Information = MsgBoxStyle.Information)

[ZidaneXero]

Try
            conn.Open()

            'There are a few problems with "cmdSearch"
            '01) Your query has no Where clause
            '02) Single quotes are not placed around your comparison
            '03) Concatenating strings wastes memory resources 
            '    and should be using parameters anyway
            '04) the command isnt given a connection to use
            Dim cmdSearch As New SqlCommand("Select * from Clientes = " & Me.telefonobox.Text & "")


            Dim stReader As SqlDataReader

            stReader = cmdSearch.ExecuteReader

            If stReader.Read Then

                'you are applying an object to a text value
                'without explicitly retrieving its proper datatype value
                'also if any of these values are null
                'you will receive an error since you
                'are not checking for null values
                Me.clientebox.Text = stReader(0)
                Me.telefono.Text = stReader(1)
                Me.nombrebox.Text = stReader(2)
                Me.dirbox.Text = stReader(3)
                Me.detallesbox.Text = stReader(4)
                ' Me.txtPicture.Text = stReader(3)

                stReader.Close()
                conn.Close()

            Else
                MsgBox("No entry has been found", MsgBoxStyle.Information = MsgBoxStyle.OkOnly)
                stReader.Close()
                conn.Close()

            End If

            'Could be written here once
            'instead of twice above
            'stReader.Close()
            'conn.Close()

        Catch ex As Exception
            'Option Strict would disallow the below conversion
            MsgBox(ex.Message.ToString, MsgBoxStyle.Information = MsgBoxStyle.Information)

Ok so where is my error or what do i try? cuz i see the same code :S

[TomW]

There are many errors. The first of which seems to be creating the connection to the database. First resolve the connection issue. (see my first post for an explaination), Im guessing that the datasource is incorrect but only you know the name and place of your datasource & database, so I cant give you the exact answer. For corrections to your comand object take a look at the below:

Dim strCon As String = ""

        strCon = "Data Source=.\ISCDEV;Initial Catalog=mangiamos;Integrated Security=True"

        'The using block is usefull for limiting
        'the life of object created within it.
        'Also when it hits the end using, the 
        'obj (con) is automatically disposed of

        Using con As New SqlConnection(strCon)
            Dim cmdSelect As New SqlCommand
            Try
                con.Open()


                '01) Assign the connect to the command object
                '02) Specify whether your inclusing a query string or using a stored procedure
                '03) Added "Where" statement, single quotes around comparison
                '    are no longer needed since a parameter will be used instead
                '04) Created a parameter and assigned it to the name given in the query
                '    also assigned its value.

                cmdSelect.Connection = con  '01
                cmdSelect.CommandType = CommandType.Text    '02
                cmdSelect.CommandText = "Select * from Where Clientes = @Telefono"  '03
                cmdSelect.Parameters.AddWithValue("@Telefono", txtTelefonobox.Text.Trim) '04

                'Before I go further I wanted to ask whether the 
                'reader will be enough? As it applies, it will only
                'display data. If you need to add, delete, manipulate and/or update
                'this data, the reader should not be used and instead be replaced
                'to hold the data within a dataset/datatable.

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                con.Close()
                cmdSelect.Dispose()
            End Try
        End Using 'con automatically disposed of

[ZidaneXero]

'Before I go further I wanted to ask whether the 'reader will be enough? As it applies, it will only 'display data. If you need to add, delete, manipulate and/or update 'this data, the reader should not be used and instead be replaced 'to hold the data within a dataset/datatable.

What i need to do in this program is only display the information found by the telephone, like ClientID, Telephone, Name, Address and Details, in some text boxes I named in previous post

clientebox
telefono
namebox
dirbox
details

After that im printing it like a receipt, this program will function as a Delivery Food Label in a box, just making it simpler for the user to find a client's information and printing it in a pizza box, making it easier for the delivery boy to have details on where to take the pizza

[TomW]

What i need to do in this program is only display the information found by the telephone, like ClientID, Telephone, Name, Address and Details, in some text boxes I named in previous post

clientebox
telefono
namebox
dirbox
details

After that im printing it like a receipt, this program will function as a Delivery Food Label in a box, just making it simpler for the user to find a client's information and printing it in a pizza box, making it easier for the delivery boy to have details on where to take the pizza

Ok this can work with the reader then.... I also print package labels and just to take a moment to explain how I do it, might give you some ideas for the future.

01) Select statement is created in a stored procedure in a database.
02) A typed dataset is created in the program that matches the data being retrieved.
03) Create a Crystal Report using the dataset as its field structure

In the coding then, all I have to do is assign the SP name and Parameter value to the command object and have it fill the datatable. I then just assign the datatable to the CR report which is sent right to the printer.

[ZidaneXero]

Ok this can work with the reader then.... I also print package labels and just to take a moment to explain how I do it, might give you some ideas for the future.

01) Select statement is created in a stored procedure in a database.
02) A typed dataset is created in the program that matches the data being retrieved.
03) Create a Crystal Report using the dataset as its field structure

In the coding then, all I have to do is assign the SP name and Parameter value to the command object and have it fill the datatable. I then just assign the datatable to the CR report which is sent right to the printer.

Sorry for the late respond, but im kind of new in programming, can you give me the example of how you would do that in code please

by the way i can now connect succesfully to the data base, no errors now!

Thanks in advance

[TomW]

Good to hear you corrected the problem; what was it?

Ok, let me start by asking a few questions?

01) Do you know how to create a stored procedure in your database?
02) Can you create a "typed" dataset
03) Have you created any Crystal Reports yet?
04) Your previous query example is selecting all the records in the table. How are you limiting it to the record that you want display & print?

Creating a Stored Procedure you can do right in the query window of Sql Server. As an example (again I would need to know your columns and what filter critera you want to use).

Create Procedure spClientSelect

	@Account As Int

As

Select
	Client,
	PhoneNo,
	Directory,
	Details
From Clients
Where AccountNumber = @Account

A general example of how I would create my connection, call the stored procedure, fill the dataset, load it to a report and send it to the printer.

Public Sub PrintClientLabel()

        Dim dsClient As New myTypedDataSetNameHere
        Dim report As New MyReport

        Using con As New SqlConnection(strDbConnection)
            Dim cmdSelect As New SqlCommand
            Dim da As New SqlDataAdapter

            With cmdSelect
                .Connection = con
                .CommandType = CommandType.StoredProcedure
                .CommandText = "spClientSelect"
                .Parameters.AddWithValue("@Account", intAccountNumber)
            End With

            da.SelectCommand = cmdSelect
            da.Fill(dsClient, "Clients")

            da.Dispose()
            cmdSelect.Dispose()
        End Using 'automatically closes & disposes the con object

        report.SetDataSource(dsClient)
        report.PrintToPrinter(1, False, 0, 0)

        report.Dispose()
        dsClient.Dispose()
End Sub

[LennieKuah]

Hi Good Guys,
In my VbNet2008 Window application, I have been asked by my Senior Developer to add Crystal Report and use Crystal Report TEXT OBJECT instead of filling it datasource with DataSet or link to TABLE.

I created the DATAREADER which contains 8 rows of data but on the Crystal Report TEXT OBJECT it only display one row containing data from the DATAREADER 8th row.

The question is How to create a New Row in Crystal Report?

Private Sub btnShowReport_Click(ByVal sender As System.Object, 
	ByVal e As System.EventArgs) Handles btnShowReport.Click
 
        Dim strSql As String = Nothing
        strSql &= "Select  OrderID, "
        strSql &= "convert(char(10),OrderDate, 103) as OrderDate, "   
        strSql &= " ProductName"
        strSql &= " From Invoices "
        strSql &= " Where CustomerId = '" & custId & "' "
    
        sqlConn = New SqlConnection(connStr)
        sqlCmd = New SqlCommand(strSql, sqlConn)
        sqlConn.Open()
        sqlDR = sqlCmd.ExecuteReader
      
        Dim objRpt As New CrystalOrderInvoice
        Dim cryTextObject As TextObject = Nothing
 
        Dim cryFolder As String = "H:\VBNet2008CrystalReportApps\CrystalOrderInvoice.rpt"
        objRpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape
        objRpt.Load(cryFolder)
        Me.CrystalReportViewer1.ReportSource = objRpt
        Me.CrystalReportViewer1.Visible = True

        While sqlDR.Read

            'OrderID
            cryTextObject = objRpt.ReportDefinition.ReportObjects("ctxtOrderId")
            cryTextObject.Text = sqlDR("OrderID")

            'OrderDate
            cryTextObject = objRpt.ReportDefinition.ReportObjects("cTxtOrderDate")
            cryTextObject.Text = sqlDR("OrderDate") 

            'productname
            cryTextObject = objRpt.ReportDefinition.ReportObjects("cTxtProduct")
            cryTextObject.Text = sqlDR("ProductName") '+ ChrW(10)      
          End While
    End Sub [

----------------------------
This coding is not creating a new ROW for next record
cryTextObject.Text = sqlDR("ProductName") '+ ChrW(10)

Please help me.