3 Replies - 2654 Views - Last Post: 17 June 2008 - 04:40 AM Rate Topic: -----

#1 Fullchaos  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 30-May 08

Modifying an SQL query with user input

Posted 12 June 2008 - 09:40 AM

So long story short, after some help getting connected to mysql, I took some time off to ready the data for entry.

After coming back, the objective hasn't changed. Essentially the goal is to take a string from a user input, and feed it into the SQL string and get back information.

The database will return the table and the grid when the statement is:
 "SELECT family,model_number,operation,description,documentation,tooling FROM 889dmaster "


The string input works as well, as it produces a result upon the button being clicked.

So essentially: Asp.net ------*connected*------ Mysql
User Input ----*connected* ----- string
SQL Statement ------- Mysql ---*returns table*
String -------- --------- SQL statement --------- Mysql

Here's the full code:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Common" %>
<%@ Import Namespace="MySql.Data" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>

<script language = "VB" runat="server" >
	Dim whereval As String
	
	Sub buttonclick1(ByVal sender As Object, ByVal e As System.EventArgs)
		whereval = InputBox("Please enter your model number as it appears.  Replace the footage of your model number with a *", "Model Number Entry")
		
		
	End Sub
		
</script>

<script language = "VB" runat = "server">
	Private Const connectString As String = "Server=localhost;Database=a;User=root;Password=mp;"
  
   Function GetDataAdapter(ByVal sql As String) As DbDataAdapter
	  Return New MySqlDataAdapter(sql, connectString)
   End Function
  
   Sub UpdateDataGrid()
	  Dim myDataAdapter As DbDataAdapter
	  Dim myDataSet As New DataSet

		myDataAdapter = GetDataAdapter("SELECT family,model_number,operation,description,documentation,tooling FROM 889dmaster " & _
									   "WHERE model_number='" & whereval & "'")
		myDataAdapter.Fill(myDataSet, "889dmaster")
	  
	  ' check what we really got
		' Label1.Text = myDataSet.GetXml()
	  ' you may need to bind the table
	  If myDataSet.Tables.Count = 1 Then
		 MySQLDataGrid.DataSource = myDataSet.Tables(0)
		 MySQLDataGrid.DataBind()
	  End If
	  
   End Sub
  
	Protected Overrides Sub onload(ByVal e As System.EventArgs)
		MyBase.onload(e)
		' Label1.Text = "Hit"
		UpdateDataGrid()
	End Sub
</script>



<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
	<title>Database Query</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
   
   
   <%-- <script  runat="server">
	   Sub submit(ByVal sender As Object, ByVal e As EventArgs)
			lbl1.Text = "Your model number is " & txt1.Text
	   End Sub
	</script>--%>

	

	   <asp:Button ID="Button2" onclick="buttonclick1" Text="Enter Model Number" runat="server" />
	<% Response.Write(whereval)%>
	
	
	<%--Enter model number:
	<asp:TextBox id="txt1" runat="server" />
	<asp:Button ID="Button1" onclick="submit" Text="Submit" runat="server" />
	<p><asp:Label id="lbl1" runat="server" /></p>--%>
	</form>
	<asp:DataGrid ID ="MySQLDataGrid" runat="Server" >
	</asp:DataGrid>
	

	
	</div>
	</form>
</body>
</html>

(P.S. Thanks again baavgai)

So what I'm thinking is my problem lies with the WHERE statement since the datagrid still outputs the column names. It does not however output any of the values for them in the database (when a correct model is entered).

Any idea's what could be wrong, or how I can fix it?

Is This A Good Question/Topic? 0
  • +

Replies To: Modifying an SQL query with user input

#2 Fullchaos  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 30-May 08

Re: Modifying an SQL query with user input

Posted 16 June 2008 - 04:49 AM

So, I checked the string and it appears to be correct.

Could it be something with the datagrid?

Anybody have an idea?
Was This Post Helpful? 0
  • +
  • -

#3 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Modifying an SQL query with user input

Posted 16 June 2008 - 09:24 AM

What data type is "model_number" in your database?

If the column names are being created correctly, then the problem is most likely that you are not getting any rows returned from your select statement.

Try running the SELECT statement directly inside a query editor for your database. If it returns results then post back a response and we can look further into your code for the problem.
Was This Post Helpful? 0
  • +
  • -

#4 Fullchaos  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 30-May 08

Re: Modifying an SQL query with user input

Posted 17 June 2008 - 04:40 AM

Using the mysql query browser it produces the correct results. It also produces the correct results through the command line client.

The fields are made up of 6 different columns (varchar ranging from 10 to 255).

None of them are a primary key. Defaults are null. No extra information.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1