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
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 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....
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 SqlDataReaderstReader = cmdSearch.ExecuteReader
If stReader.Read ThenMe.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 SubIf 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 UsingTry
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)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
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'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
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
detailsAfter 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.
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 structureIn 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
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 = @AccountA 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 SubHi 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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.