CODEWITHSUNDEEP
vb.net
Alex browning
Alex browning

Reputation: 1

Adding data from a textbox to an SQL Query

Currently I am using this code to read in data from a database into a chart:

Dim Conn As OleDbConnection = New OleDbConnection
Dim provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
Dim dataFile = "\\sch5409.poole.sch.uk\public\HomeDirs\Students\11\11browningale\My Documents\CornmarketCPDDatabase.accdb"
'UserGDCNumber = GDCNumber.Text
Conn.ConnectionString = provider & dataFile
Conn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT [Type Of CPD], [Amount of Hours], [GDC Number] FROM [CPD Table], [Amount of CPD Hours]", Conn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
While dr.Read
    Chart1.Series("Amount of Hours").Points.AddXY(dr("Type Of CPD").ToString, dr("Amount of Hours").ToString)
End While
dr.Close()
cmd.Dispose()

cmd = New OleDbCommand("SELECT [Type of CPD], [Amount of Hours] FROM [CPD Table], [Amount of CPD Hours] WHERE [CPD Table].[CPD ID] = [Amount of CPD Hours].[CPD ID] AND [Amount of CPD Hours].[GDC Number] = GDCNumber.Text", Conn)
dr = cmd.ExecuteReader

The problem is with this line:

cmd = New OleDbCommand("SELECT [Type of CPD], [Amount of Hours] FROM [CPD Table], [Amount of CPD Hours] WHERE [CPD Table].[CPD ID] = [Amount of CPD Hours].[CPD ID] AND [Amount of CPD Hours].[GDC Number] = GDCNumber.Text", Conn)

It works fine without the AND [Amount of CPD Hours].[GDC Number] = GDCNumber.Text

What I would like my program to do is read the data from the textbox GDCNumber.Text (I know I should assign it to a variable first) and then use that data in my query.

This is the error message:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: No value given for one or more required parameters."

Upvotes: 0

Views: 38

Answers (1)

Slugsie
Slugsie

Reputation: 905

Your SQL query is referring to a thing called GDCNumber.Text, but as this is on the VB.Net end of things it means nothing to SQL Server. Instead you want something like:

cmd = New OleDbCommand("SELECT [Type of CPD], [Amount of Hours] FROM [CPD Table], [Amount of CPD Hours] WHERE [CPD Table].[CPD ID] = [Amount of CPD Hours].[CPD ID] AND [Amount of CPD Hours].[GDC Number] = " & GDCNumber.Text, Conn)

This assumes that GDCNumber is just a number. If it's not then you want:

cmd = New OleDbCommand("SELECT [Type of CPD], [Amount of Hours] FROM [CPD Table], [Amount of CPD Hours] WHERE [CPD Table].[CPD ID] = [Amount of CPD Hours].[CPD ID] AND [Amount of CPD Hours].[GDC Number] = '" & GDCNumber.Text & "'", Conn)

That's the simple solution. However, constructing SQL like this does open you up to something called a SQL Injection attack - i.e. someone could write just the right sort of SQL code in that textbox and cause all sorts of havoc in your code. To fix this you should learn about Parameterised Queries.

Upvotes: 2

Related Questions