I am currently trying to create a web application in which a user can enter data about a video game, notably its name, system, completeness (New, CIB, No Manual, or Loose), and condition (poor, average, great), and get back both the basic price of the product as well as our own price. I am using SQL Server for the back end and linking it with Visual Studio to for the actual application. I am trying to create a web application with asp.net. Here is my work on the front end so far.
<form id="form1" runat="server">
<label for="GName">Name:</label>
<input type="text" id="GName" name="GName">
<label for="console">Console:</label>
<select name="console" id="console">
<option value="Xbox 360" selected>Xbox 360</option>
</select>
<label for="completeness">Completeness</label>
<select name="completeness" id="completeness">
<option value="New">New</option>
<option value="CIB" selected>CIB</option>
<option value="No Manual">No Manual</option>
<option value="Loose">Loose</option>
</select>
<label for="condition">Condition</label>
<select name="condition" id="condition">
<option value="1">Poor</option>
<option value="2" selected>Average</option>
<option value="3">Good</option>
</select>
<input type="submit" value="Submit">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mandmbuyguideConnectionString %>" SelectCommand="SELECT [Base_Price_Cash], [Base_Price_Credit] FROM [BasePrice]"></asp:SqlDataSource>
</form>
What I would like to know is how can I turn the value of each input or selection field into a variable that I can use in the database query, that way use it in the where clause to select a spacific set of values. EXAMPLE: SELECT [Base_Price_Cash],[Base_Price_Credit] FROM [BasePrice] WHERE BasePrice.Name=Value of GName
CodePudding user response：
Probably the easiest option is to use System.Data
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(@"
SELECT
[Base_Price_Cash],
[Base_Price_Credit]
FROM [BasePrice]
WHERE BasePrice.Name=@GName", connection))
{
command.Parameters.Add(new SqlParameter("@GName", GName));
await connection.OpenAsync();
using (var reader = await command.ExecuteReaderAsync())
{
//...read data...
}
}
You can find more examples 
Now, it not clear if you cash price and Credit is for each game, or that you have "many" such bids and offers - so if that is the case, then then of course the price and credit would be a separate table.
So, once you have the data base structure? then the code quite much writes it for you!!! (really!!!).
So, we can use say sql studio (or visual studio) to open up say the console table, and manually add the rows (consoles). But, we could even build a form that lets us enter a new game, and if the console is not in the list, then we could get fancy and have a "add" console button to add ot that list (so over time, new consoles could be easy added by the "user" of the software, and not have to stop their data entry, and go open the "console" table directly to add that console, and then go back to data entry. But, lets just add two consoles, by directly editing the consoles table.
So, I have this this grid markup
<div style="width:80%;padding:25px">
<h2>Games</h2>
<div style="float:left">
<h4>Search for</h4>
<asp:TextBox ID="txtSeach" runat="server" Width="203px"></asp:TextBox>
</div>
<div style="float:left;margin-left:30px">
<h4>Console Type</h4>
<asp:DropDownList ID="cboConsole" runat="server" Width="221px" Height="26"></asp:DropDownList>
</div>
<div style="clear:both;height:8px"></div>
<asp:GridView ID="GVGames" runat="server" CssClass="table" AutoGenerateColumns="False"
DataKeyNames="ID" ShowHeaderWhenEmpty="True" >
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Image ID="Image1" runat="server" ImageUrl= '<%# Eval("ImageUrl") %>' Width="148"/>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Console" HeaderText="Console" />
<asp:BoundField DataField="GName" HeaderText="Name" />
<asp:BoundField DataField="Year" HeaderText="Year" />
<asp:BoundField DataField="Complete" HeaderText="Complete" />
<asp:BoundField DataField="Conditition" HeaderText="Conditition" />
<asp:BoundField DataField="Notes" HeaderText="Notes" />
<asp:BoundField DataField="CashPrice" HeaderText="CashPrice" />
<asp:BoundField DataField="Credit" HeaderText="Credit" />
<asp:TemplateField>
<ItemTemplate>
<asp:Button ID="cmdView" runat="server" Text="View" CssClass="btn" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<div style="float:right">
<asp:Button ID="cmdAdd" runat="server" Text=" Add" CssClass="btn btn-info" />
</div>
And now some simple code to load up the grid is this:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
LoadGrid
End If
End Sub
Sub LoadGrid()
GVGames.DataSource = MyRst("SELECT * from VGames ORDER BY GName")
GVGames.DataBind()
End Sub
And when we run above we get this:
So, next up is some markup to edit each row.
So, we can say have something like this on the same page:
<div id="AddRecord" runat="server" style="display:none">
<div style="float:left;width:80%">
<div >
<label>Game</label><asp:TextBox ID="txtGame" runat="server" f="GName" width="280"></asp:TextBox> <br />
<label>Year</label><asp:TextBox ID="txtYear" runat="server" f="Year" Width="75"></asp:TextBox> <br />
</div>
<br />
<div style="float:left;width:90px"><label>Condition</label></div>
<asp:RadioButtonList ID="RadioCondition" runat="server" RepeatDirection="Horizontal"
CssClass="rMyChoice" f="Condition_ID"
DataTextField="Conditition" DataValueField="ID" >
</asp:RadioButtonList>
<br />
<div style="float:left;width:90px"><label>Complete</label></div>
<asp:RadioButtonList ID="RadioComplete" runat="server" RepeatDirection="Horizontal"
CssClass="rMyChoice" f="Complete_ID"
DataTextField="Complete" DataValueField="ID" >
</asp:RadioButtonList>
<br />
<div >
<label>Notes</label>
<asp:TextBox ID="txtNotes" runat="server" Width="400" TextMode="MultiLine" Height="150px" f="Notes" ></asp:TextBox>
</div>
<button id="cmdSave" runat="server" >
<span aria-hidden="true" > Save</span>
</button>
<button id="cmdCancel" runat="server" style="margin-left:15px">
<span aria-hidden="true" > Back/Cancel</span>
</button>
</div>
</div>
And our code behind the "view" button would be like this:
Protected Sub cmdView_Click(sender As Object, e As EventArgs)
' edit one row - click row
Dim btn As Button = sender
Dim gRow As GridViewRow = btn.NamingContainer
Dim PKID As Integer = GVGames.DataKeys(gRow.RowIndex).Item("ID")
Dim rstOneRow As DataTable
rstOneRow = MyRst("SELECT * from Games where ID = " & PKID)
' hide grid
GridHolder.Style.Add("display", "none")
' show our one record edit
AddRecord.Style.Add("display", "normal")
' load up record to controls
' load up complete chocies
RadioComplete.DataSource = MyRst("SELECT ID, Complete FROM Complete ORDER BY Complete")
RadioComplete.DataBind()
' load up condition choices
RadioCondition.DataSource = MyRst("SELECT ID, Conditition FROM Condition ORDER BY Conditition")
RadioCondition.DataBind()
Call fLoader(AddRecord, rstOneRow.Rows(0))
Session("rstOneRow") = rstOneRow
End Sub
And now when we click on a row, we see this:
Now, I did build a helper routine. In fact years ago, the very FIRST time I had to write code to setup controls on the web page? Well, why for the next 10 years of my life would I write the SAME code over and over?
So, I built a routine to take one row of a record, and push it out to my controls. (that's what the f="MyData column name goes here" does.
So, write that code one time, and you for ever more not have to write code to "load up" controls on the form.
that helper routine - I put it in a standard code module - not in the forms code - since all code and all forms can use this code.
That code is this:
Public Sub fLoader(F As HtmlGenericControl, rst As DataRow)
For Each c As System.Web.UI.Control In F.Controls
Select Case c.GetType
Case GetType(TextBox)
Dim ctlC As TextBox = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
ctlC.Text = IIf(IsDBNull(rst(ctlC.Attributes("f"))), "", rst(ctlC.Attributes("f")))
End If
End If
Case GetType(Label)
Dim ctlC As Label = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
ctlC.Text = IIf(IsDBNull(rst(ctlC.Attributes("f"))), "", rst(ctlC.Attributes("f")))
End If
End If
Case GetType(DropDownList)
Dim ctlC As DropDownList = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
ctlC.Text = IIf(IsDBNull(rst(ctlC.Attributes("f"))), "", rst(ctlC.Attributes("f")))
End If
End If
Case GetType(CheckBox)
Dim ctlC As CheckBox = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
ctlC.Checked = rst(ctlC.Attributes("f"))
End If
End If
Case GetType(RadioButtonList)
Dim ctlC As RadioButtonList = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
ctlC.SelectedValue = rst(ctlC.Attributes("f"))
End If
End If
End Select
Next
End Sub
So, that code can now shuffle code from the database to controls on the form - or in near all cases, a "div" tag.
So, when we hit the save button, we reverse the above code, and I have this routine:
I mean, you could code out it this way:
dim rstData as datatable = "query of the one row
dim MyOneRow as dataRow = rstData.Rows(0)
then go
MyOneRow("Notes") = txtNotes.Text
MyOneRow("Condition") = RadioCondtion.SelectedItem.Value
etc. etc. etc. -
' and then save/send the one row back to the database.
However, once again, write the above a few times, and you FAST realize that is the same code over and over - why wear out a keyboard?
So, just like fLoader to load up controls, we cook up a fWriter to do the reverse. It is "more" code the first time to write, but then we can use that same code over and over. So, fWriter looks like this:
Public Sub fWriter(f As HtmlGenericControl, fPK As Integer, strTable As String, Optional strPortalComp As String = "", Optional strCon As String = "")
' opposte of fLoader - write a data form to table
Dim rstData As DataTable
Dim da As SqlDataAdapter = Nothing
Dim strSQL As String
strSQL = "Select * from " & strTable & " where ID = " & fPK
If strCon <> "" Then
rstData = MyrstEdit(strSQL, strCon, da)
Else
rstData = MyrstEdit(strSQL, GetConstr(strPortalComp), da)
End If
Dim rst As DataRow
rst = rstData.Rows(0)
For Each c As System.Web.UI.Control In f.Controls
Select Case c.GetType
Case GetType(TextBox)
Dim ctlC As TextBox = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
rst(ctlC.Attributes("f")) = IIf(ctlC.Text = "", DBNull.Value, ctlC.Text)
End If
End If
Case GetType(Label)
Dim ctlC As Label = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
rst(ctlC.Attributes("f")) = IIf(ctlC.Text = "", DBNull.Value, ctlC.Text)
End If
End If
Case GetType(DropDownList)
Dim ctlC As DropDownList = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
rst(ctlC.Attributes("f")) = IIf(ctlC.Text = "", DBNull.Value, ctlC.Text)
End If
End If
Case GetType(CheckBox)
Dim ctlC As CheckBox = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
rst(ctlC.Attributes("f")) = ctlC.Checked
End If
End If
Case GetType(RadioButtonList)
Dim ctlC As RadioButtonList = c
If Not ctlC.Attributes("f") Is Nothing Then
If rst.Table.Columns.Contains(ctlC.Attributes("f")) Then
rst(ctlC.Attributes("f")) = ctlC.SelectedValue
End If
End If
End Select
Next
' data row is filled, write out changes
da.Update(rstData)
da.Dispose()
End Sub
and myRstEdit:
Public Function MyrstEdit(strSQL As String, Optional strCon As String = "", Optional ByRef oReader As SqlDataAdapter = Nothing) As DataTable
' Myrstc.Rows(0)
' this also allows one to pass custom connection string - if not passed, then default
' same as MyRst, but allows one to "edit" the reocrdset, and add to reocrdset and then commit the update.
If strCon = "" Then
strCon = GetConstr()
End If
Dim mycon As New SqlConnection(strCon)
oReader = New SqlDataAdapter(strSQL, mycon)
Dim rstData As New DataTable
Dim cmdBuilder = New SqlCommandBuilder(oReader)
'oReader.SelectCommand = New SqlCommand(strSQL, mycon)
Try
oReader.Fill(rstData)
oReader.AcceptChangesDuringUpdate = True
Catch
End Try
Return rstData
End Function
So, now our "save" button to save our controls and edits back to the database can look like this:
<asp:LinkButton ID="cmdSave" runat="server" CssClass="btn btn-info">
<span aria-hidden="true" >Save</span>
</asp:LinkButton>
And code for this is :
Protected Sub cmdSave_Click(sender As Object, e As EventArgs) Handles cmdSave.Click
Dim rstOneRow As DataTable = Session("rstOneRow")
Dim PKID = rstOneRow.Rows(0).Item("ID")
Call fWriter(AddRecord, PKID, "Games", My.Settings.Games)
' show grid
GridHolder.Style.Add("display", "normal")
' hide edit div
AddRecord.Style.Add("display", "none")
LoadGrid()
End Sub
Now of course the above is a bare bones setup. We of course would say add a delete button to each gv row. And we have to wire up the "add new" item button we dropped below the GV. But, with the above "helper" routines, you can now shuffle data to/from the database, and do it with a "min" of code an and fuss. In fact, you can quite much just drop as many controls into a "div", set our f="some column name from database", and you quite much off to the races.