|
|
Hi, I am using Visual Studio Community 2017 and i created a table called Workstations
with my .cs code i am traing to insert data into the table. I am also struggling to add the dateTimePicker into the Purch_Date and WExpiry_Date fields. i am receive this error when doing a debug on the script "
Cannot insert the value NULL into column 'Status_Id', table 'ITInventory.dbo.Workstations'; column does not allow nulls. INSERT fails. "below is my code. Hope someone can help me with this. Thank you in advance
.cs code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
namespace InventoryStock
{
public partial class Workstations : Form
{
public object WExpiry_Date { get; private set; }
public Workstations()
{
InitializeComponent();
}
private void label1_Click(object sender, EventArgs e)
{
}
private void label1_Click_1(object sender, EventArgs e)
{
}
private void Workstations_Load(object sender, EventArgs e)
{
this.workstationsTableAdapter.Fill(this.iTInventoryDataSet.Workstations);
comboBox3.SelectedIndex = 0;
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=sh-jasonk\dev;Initial Catalog=ITInventory;Integrated Security=True");
con.Open();
bool Wkst_Status = false;
if (comboBox3.SelectedIndex == 0)
{
Wkst_Status = true;
}
else
{
Wkst_Status = false;
}
SqlCommand cmd = new SqlCommand
(@"INSERT INTO [dbo].[Workstations]
(
[Emp_Name]
,[Emp_Surname]
,[Department]
,[Company]
,[Hostname]
,[Wkst_Status]
,[Make]
,[Model]
,[SerialNumber]
,[ProductNumber]
,[Purch_Date]
,[WExpiry_Date]
,[Memory]
,[Processor]
,[HDD]
,[OS])
VALUES
('" + txtName.Text + "','" + txtSurname.Text + "','" + comboBox1.Text + "','" + comboBox2.Text + "','" + txtHostName.Text + "','" + comboBox3.SelectedIndex + "','" + txtMake.Text + "','" + txtModel.Text + "','" + txtSN.Text + "','" + txtPN.Text + "','" + dateTimePicker1.Value.ToString("yyyy/MM/dd") + "','" + dateTimePicker1.Value.ToString("yyyy/MM/dd") + "','" + txtMem.Text + "','" + txtProc.Text + "','" + txtHDD.Text + "','" + txtOS.Text + "')",con);
cmd.ExecuteNonQuery();
con.Close();
SqlDataAdapter sda = new SqlDataAdapter(@"Select [Emp_Name][Emp_Surname],[Department],[Company],[Hostname],[Wkst_Status],[Make],[Model]
,[SerialNumber],[ProductNumber],[Purch_Date],[WExpiry_Date],[Memory],[Processor],[HDD]
,[OS] From [dbo].[Workstations]", con);
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView1.Rows.Clear();
foreach (DataRow item in dt.Rows)
{
int n = dataGridView1.Rows.Add();
dataGridView1.Rows[n].Cells[0].Value = item["Emp_Name"].ToString();
dataGridView1.Rows[n].Cells[1].Value = item["Emp_Surname"].ToString();
dataGridView1.Rows[n].Cells[2].Value = item["Department"].ToString();
dataGridView1.Rows[n].Cells[3].Value = item["Company"].ToString();
dataGridView1.Rows[n].Cells[4].Value = item["Hostname"].ToString();
if ((bool)item["Wkst_Status"])
{
dataGridView1.Rows[n].Cells[5].Value = "Active";
}
else
{
dataGridView1.Rows[n].Cells[5].Value = "Inactive";
}
dataGridView1.Rows[n].Cells[6].Value = item["Make"].ToString();
dataGridView1.Rows[n].Cells[7].Value = item["Model"].ToString();
dataGridView1.Rows[n].Cells[8].Value = item["SerialNumber"].ToString();
dataGridView1.Rows[n].Cells[9].Value = item["ProductNumber"].ToString();
dataGridView1.Rows[n].Cells[10].Value = item["Purch_Date"].ToString();
dataGridView1.Rows[n].Cells[11].Value = item["WExpiry_Date"].ToString();
dataGridView1.Rows[n].Cells[12].Value = item["Memory"].ToString();
dataGridView1.Rows[n].Cells[13].Value = item["Processor"].ToString();
dataGridView1.Rows[n].Cells[14].Value = item["HDD"].ToString();
dataGridView1.Rows[n].Cells[15].Value = item["OS"].ToString();
}
}
private void label17_Click(object sender, EventArgs e)
{
}
}
}
my SQL table create code
USE [ITInventory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Workstations](
[Emp_Id] [int] IDENTITY(1,1) NOT NULL,
[Emp_Name] [varchar](30) NOT NULL,
[Emp_Surname] [varchar](30) NOT NULL,
[Department] [varchar](50) NOT NULL,
[Company] [varchar](30) NOT NULL,
[Hostname] [nvarchar](20) NOT NULL,
[Wkst_Status] [varchar](15) NOT NULL,
[Make] [varchar](12) NOT NULL,
[Model] [varchar](15) NOT NULL,
[SerialNumber] [nvarchar](30) NOT NULL,
[ProductNumber] [nvarchar](30) NOT NULL,
[Purch_Date] [date] NOT NULL,
[WExpiry_Date] [date] NOT NULL,
[Memory] [nvarchar](6) NOT NULL,
[Processor] [nvarchar](10) NOT NULL,
[HDD] [nvarchar](10) NOT NULL,
[OS] [nvarchar](25) NOT NULL,
[Status_Id] [int] NOT NULL,
CONSTRAINT [PK__Workstat__262359ABF6F5A9AA] PRIMARY KEY CLUSTERED
(
[Emp_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Workstations] WITH CHECK ADD CONSTRAINT [FK__Workstati__Statu__6FE99F9F] FOREIGN KEY([Status_Id])
REFERENCES [dbo].[Status] ([Status_Id])
GO
ALTER TABLE [dbo].[Workstations] CHECK CONSTRAINT [FK__Workstati__Statu__6FE99F9F]
GO
|
|
|
|
|
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
using (SqlConnection con = new SqlConnection(@"Data Source=sh-jasonk\dev;Initial Catalog=ITInventory;Integrated Security=True"))
using (SqlCommand cmd = new SqlCommand(@"INSERT INTO [dbo].[Workstations]
(
[Emp_Name],
[Emp_Surname],
[Department],
[Company],
[Hostname],
[Wkst_Status],
[Make],
[Model],
[SerialNumber],
[ProductNumber],
[Purch_Date],
[WExpiry_Date],
[Memory],
[Processor],
[HDD],
[OS]
)
VALUES
(
@Emp_Name,
@Emp_Surname,
@Department,
@Company,
@Hostname,
@Wkst_Status,
@Make,
@Model,
@SerialNumber,
@ProductNumber,
@Purch_Date,
@WExpiry_Date,
@Memory,
@Processor,
@HDD,
@OS
)", con))
{
cmd.Parameters.AddWithValue("@Emp_Name", txtName.Text);
cmd.Parameters.AddWithValue("@Emp_Surname", txtSurname.Text);
cmd.Parameters.AddWithValue("@Department", comboBox1.Text);
cmd.Parameters.AddWithValue("@Company", comboBox2.Text);
cmd.Parameters.AddWithValue("@Hostname", txtHostName.Text);
cmd.Parameters.AddWithValue("@Wkst_Status", comboBox3.SelectedIndex);
cmd.Parameters.AddWithValue("@Make", txtMake.Text);
cmd.Parameters.AddWithValue("@Model", txtModel.Text);
cmd.Parameters.AddWithValue("@SerialNumber", txtSN.Text);
cmd.Parameters.AddWithValue("@ProductNumber", txtPN.Text);
cmd.Parameters.AddWithValue("@Purch_Date", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@WExpiry_Date", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@Memory", txtMem.Text);
cmd.Parameters.AddWithValue("@Processor", txtProc.Text);
cmd.Parameters.AddWithValue("@HDD", txtHDD.Text);
cmd.Parameters.AddWithValue("@OS", txtOS.Text);
con.Open();
cmd.ExecuteNonQuery();
}
Now it should be obvious what the problem is: you're not specifying a value for the required Status_Id column.
NB: Rather than hard-coding your connection string, look at storing it in the application configuration file instead:
Connection Strings and Configuration Files | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello
I'm using the Ajax ToolKit File Upload
I upload very well the files of Word, Excel, txt, jpg, PNG, XML, zip....
But AutoCAD files type dwg, IPT, stp ….. the Ajax ToolKit File Upload CAN NOT upload …. only mark ERROR when you click the UPLOAD button.
The size of AutoCAD files is small from 150kb to 300kb
Because these types of DWG files cannot be loaded ????
This is my HTML Codigi and this is the Web. Config
<pre>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="ajaxToolkit" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="styles.css" rel="stylesheet" type="text/css" media="screen"/>
</head>
<body>
<form id="form1" runat="server">
<asp:scriptmanager runat="server"></asp:scriptmanager>
<div>
<ajaxToolkit:AjaxFileUpload
ID="AjaxFileUpload1"
runat="server"
OnUploadComplete="AjaxFileUpload1_UploadComplete"
OnUploadCompleteAll="AjaxFileUpload1_UploadCompleteAll"
Mode="Auto"
ChunkSize="4096"
MaxFileSize="21000000" />
</div>
</form>
</body>
</html>
<?xml version="1.0" encoding="UTF-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http:
-->
<configuration>
<system.web>
<customErrors mode="Off" />
<compilation debug="true" targetFramework="4.5">
<assemblies>
<add assembly="System.Design, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
<add assembly="System.Web.Extensions.Design, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
</assemblies>
</compilation>
<httpRuntime maxRequestLength="20480" targetFramework="4.5" />
<httpHandlers>
<add verb="*"
path="AjaxFileUploadHandler.axd"
type="AjaxControlToolkit.AjaxFileUploadHandler, AjaxControlToolkit, Version=4.5.7.725, Culture=neutral, PublicKeyToken=28F01B0E84B6D53E"
/>
</httpHandlers>
</system.web>
<system.webServer>
<validation validateIntegratedModeConfiguration="false" />
<handlers>
<add name="AjaxFileUploadHandler"
verb="*"
path="AjaxFileUploadHandler.axd"
type="AjaxControlToolkit.AjaxFileUploadHandler,AjaxControlToolkit"/>
</handlers>
<defaultDocument>
<files>
<clear />
<add value="Default.aspx" />
<add value="LoginP.aspx" />
<add value="LoginA.aspx" />
</files>
</defaultDocument>
</system.webServer>
</configuration>
|
|
|
|
|
There's nothing in the code you've shown which would restrict the types of file that could be uploaded. Assuming the files are not larger than the maximum file size, the problem is most likely in your code-behind.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
In a vb.net 2012 web form application, there is a page that is only accessed by the administrators of the application.
There are 2 drop down list options for the user to select and one submit button. Basically one drop donw list is the language that
is to be selected and the kind of letter that is to be generated. The letter is a 'template' generic letter that the administrator changes.
After the administrator hits the submit button, the template letter is changed in the database and one dropdown list shows the language that
was selected. The problem is the kind of letter drop list does not stay and show what was selcted. The kind of letter drop down lists displays
what the intitial drop down list looked like initially.
Thus would you show me how to keep is the kind of letter drop down list from changing once the user clicks the submit button?
|
|
|
|
|
It looks like you reloaded the control binding on post back event happen when submit button hits. Check on how you binding the data to these controls on page_load event.
modified 20-Sep-20 21:01pm.
|
|
|
|
|
Hello
I am working my way through Microsoft's WebFormsIdentity template. In the template, the login.aspx page has this:
<pre>Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
RegisterHyperLink.NavigateUrl = "Register"
OpenAuthLogin.ReturnUrl = Request.QueryString("ReturnUrl")
Dim returnUrl = HttpUtility.UrlEncode(Request.QueryString("ReturnUrl"))
If Not [String].IsNullOrEmpty(returnUrl) Then
RegisterHyperLink.NavigateUrl += "?ReturnUrl=" & returnUrl
End If
End Sub
Protected Sub LogIn(sender As Object, e As EventArgs)
If IsValid Then
Dim manager = Context.GetOwinContext().GetUserManager(Of ApplicationUserManager)()
Dim signinManager = Context.GetOwinContext().GetUserManager(Of ApplicationSignInManager)()
Dim result = signinManager.PasswordSignIn(Email.Text, Password.Text, RememberMe.Checked, shouldLockout:=False)
Select Case result
Case SignInStatus.Success
IdentityHelper.RedirectToReturnUrl(Request.QueryString("ReturnUrl"), Response)
Exit Select
Case SignInStatus.LockedOut
Response.Redirect("/Account/Lockout")
Exit Select
Case SignInStatus.RequiresVerification
Response.Redirect(String.Format("/Account/TwoFactorAuthenticationSignIn?ReturnUrl={0}&RememberMe={1}",
Request.QueryString("ReturnUrl"),
RememberMe.Checked),
True)
Exit Select
Case Else
FailureText.Text = "Invalid login attempt"
ErrorMessage.Visible = True
Exit Select
End Select
End If
End Sub
End Class
In that second Protected Sub under Select Case, can I comment out this line
IdentityHelper.RedirectToReturnUrl(Request.QueryString("ReturnUrl"), Response)
to redirect the user instead to my userpage.aspx page, something like
Response.Redirect("Userpage.aspx")
Thanks!
|
|
|
|
|
If you do that, I suspect the authentication cookies won't be set properly.
Instead, just replace the URL you want to redirect the user to:
IdentityHelper.RedirectToReturnUrl("~/Userpage.aspx", Response)
However, this might annoy your users. It's standard practise for the login form to redirect them back to the page they were trying to access in the first place, instead of some other random page.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello Richard
Thank you for your reply and sorry for not getting back to you earlier.
That works a treat. The only reason the user is logging in is to upload files and that is achieved via Userpage.aspx.
Thanks again!
|
|
|
|
|
Hello All,
I have an ASP.Net web application hosted in NLB infrastructure with 2 web servers and 2 app servers. I have 3 web services running in the application server with windows authentication enabled. Other settings as follows
Anonymous authentication is disabled
Basic authentication is disabled
Digest authentication is disabled
ASP.NET Impersonation is disabled.
Windows authentication is enabled
Negotiate is on priority with both Negotiate, NTLM is enabled.
using custom identity : DOmain\UserID2
useAppPoolCredentials set to true
useKernelMode set to TRUE
Registered serviceprincipa1Names for CN-UserID2.CN-Users.DC-XX.DC-XXXX.DC-COM:
HTTP/AppServerName2 : 10443
HTTP/AppServerName1 : 10443
HTTP/AppServerName2 : 443
HTTP/AppServerName1 : 443
As per my observation, user encounter error 401 1 2148074254 when website access a specific webservice(let's say webservice 1) for the first time. When user access the same function again then there is no recurrence of 401 error.
Noticed the Start Mode was set to "On Demand" mode in app pool settings. Tried changing the Start Mode to "Always Running" and Idle Time-Out to "0" but still encountered the same error and the same pattern.
Environment used
Server: Windows Server 2012 R2 standard edition.
IIS Version: 8.5
Extract from IIS log
2019-05-07 06:16:27 xxx.xx.x.xxx POST /SecSvc/SecWebService.asmx - 10443 - XXX.XX.XXX.XXX Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+4.0.30319.42000) - 401 1 2148074254 2906
2019-05-07 06:16:27 xxx.xx.x.xxx POST /SecSvc/SecWebService.asmx - 10443 - XXX.XX.XXX.XXX Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+4.0.30319.42000) - 401 1 2148074254 3453
2019-05-07 06:16:30 xxx.xx.x.xxx POST /SecSvc/SecWebService.asmx - 10443 domain\UserID1 XXX.XX.XXX.XXX Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+4.0.30319.42000) - 200 0 0 3281
modified 14-May-19 3:46am.
|
|
|
|
|
Greetings again.
As you can see from the code below, there are six input parameters and any one of those can be passed to display several rows of data from the database.
There is especially one called call_list_id.
When a value of call_list_id is passed, there are at least 15 records associated with it.
I can confirm this when I run it from SSMS.
However, I will pass same value through my app, only one record is displayed.
What is even more weird is that one record displays as soon as the page loads without even filtering with call_list_id value.
What am I doing wrong?
Many thanks in advance
Dim constr As String = ConfigurationManager.ConnectionStrings("stringst").ConnectionString
Dim conn As SqlConnection = New SqlConnection(constr)
Dim whereclause = ""
Dim Sql As String
Dim s As String = ""
conn.Open()
Dim cmd As New SqlCommand()
Sql = "SELECT phone_number, callerid, call_list_id, startttime,"
Sql += "connecttime, endtime, duration, fullduration,"
Sql += "camapign_id, queue_id, call_type_id, roll_id, cause_code, uuid, box_id, trunk_name, uuid, customer_id "
Sql += "FROM cti.qpcdr "
'Response.Write(Sql)
'Response.End()
whereclause = " uuid = @uuid Or call_list_id=@callerlist Or phone_number=@phone Or startttime=@start Or endtime=@end Or call_type_id=@calltype "
whereclause = whereclause & " ORDER BY endtime DESC"
'Response.Write(whereclause)
'Response.End()
'cmd.Connection = conn
cmd = New SqlCommand(Sql & "WHERE " & whereclause, conn)
cmd.Parameters.AddWithValue("@uuid", uuid.Text)
cmd.Parameters.AddWithValue("@callerlist", call_list_id.Text)
cmd.Parameters.AddWithValue("@phone", phone_number.Text)
cmd.Parameters.AddWithValue("@start", date_start.Text)
cmd.Parameters.AddWithValue("@end", date_end.Text)
cmd.Parameters.AddWithValue("@calltype", call_type.SelectedValue)
'conn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.HasRows() Then
'record exists'
While dr.Read()
lblphonenumber.Text = dr("phone_number").ToString()
lblcallerid.Text = dr("callerid").ToString()
lblcallerlist.Text = dr("call_list_id").ToString()
lblstarttime.Text = dr("startttime").ToString()
lblconnectime.Text = dr("connecttime").ToString()
lblendtime.Text = dr("endtime").ToString()
lblduration.Text = dr("duration").ToString()
lblfullduration.Text = dr("fullduration").ToString()
lblampaignname.Text = dr("camapign_id").ToString()
lblqueuename.Text = dr("queue_id").ToString()
lblcalltype.Text = dr("call_type_id").ToString()
lblemployee.Text = dr("roll_id").ToString()
lblisdn.Text = dr("cause_code").ToString()
lbluuid.Text = dr("uuid").ToString()
lbltelephony.Text = dr("box_id").ToString()
lbltrunkname.Text = dr("trunk_name").ToString()
lbluuid.Text = dr("uuid").ToString()
lblrecording.Text = dr("customer_id").ToString()
End While
End If
dr.Close()
conn.Close()
|
|
|
|
|
samflex wrote:
lblphonenumber.Text = dr("phone_number").ToString()
It may load 15 rows, but each row overwrites the data from the previous row, so only the last row will be displayed.
You need to use a control which is capable of displaying more than one row - for example, the ListView control[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you very much Richard for the prompt response.
From the ListView example though, there is only one parameter which is color:
sp:ControlParameter ControlID="ColorList" Name="Color"
I have several parameters. Do I try listing those parameters like:
sp:ControlParameter ControlID="@uuid" Name="uuid", ControlID="@callerlist" Name="call_list_id"...
|
|
|
|
|
|
Awesome.
Thank you very much for your help as always.
|
|
|
|
|
Or you can use this as a model
Create your model, this is off the top of my head, I forget how to do this in VB
Public class RecordModel
public property PhoneNumber As String
public property CallerId As String
End Class
Then in the database function, call the function to return a list of the model. After you get your list formatted for Linq, then you can fabricate your HTML elements in a for each loop.
Public Shared DatabaseFunction() As List(Of RecordModel)
Dim Results as New List(Of RecordModel)
While dr.Read()
Results.Add(New RecordModel() With {
PhoneNumber = dr("phone_number"),
CallerId = dr("callerid")
}
End While
Return Results
End Function
This will return the results. If the results if any exist
Dim results = DatabaseFunction()
If results.Any() Then
For Each result in results
'Do something
Next
End If
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Oh great.
This seems much easier since all the samples of data source I have seen so far seems very complicated.
I have not done it with more than one parameter before and have not done via binding as the Great Richard suggested.
I have been playing around with Richard's suggestion but I will try this and hopefully, I can get it to work.
Thanks a lot for your help.
|
|
|
|
|
I stopped using binding over a decade ago. When they break you have to fix them.
The internet is flooded with outdated information and tutorials and really needs to be cleaned up.
The example I gave you is home grown, sort of a natural evolution I leaned towards and not really in any tutorials. Notice how simple the code is. Using Jet Brains Ultimate Refactor, I learned that I don't have to fully Dim an object and can use Dim like var in c#, or like var or let in TypeScript. Once you carefully craft a good model, it can reused over and over with ease. Then you can use Linq to populate, manipulate, or spawn more copies of the model.
If you start coding like this, then that will pave the way for you to use MVC, then .Net Core, and Angular/React. But it's all in how you craft your model. And a model can be a single record, or a list or records. Or it can be a record with a list of records inside. Create a model, populate it with data, then use the model. Pass the model around to what ever needs it.
The example is really modern coding pratices taken backwards to web or win forms.
Give it a try, it's worth the investment in time.
See yah around Sam!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Your code seemed really labor intensive. Maybe that's why Richard suggested binding.
Here's a sample to give you an idea of how you can condense and shorten your code. This code has been Jet Brains Ultimate Refactored to be as small as possible. You can feed as many parameters you want. This passes a model into the function, and returns another model as the result.
You just need to change the connection type and string and parameter type for whatever db server technology your using.
Private Shared Function Get_FI_Customer_SAInvoices(
ByVal sFi As FreeInvoice) As List(Of InvoiceIndex)
Dim pResults As New List(Of InvoiceIndex)
Dim dbPath = RegistryShared.Read_HKCU_DataPath()
Dim connString As String = "Provider=" & My.Resources.DBProviderOLE & "; Data Source=" & dbPath & "; Extended Properties=dBASE IV"
Const queryString As String =
"SELECT " &
" h.FINVNO " &
"FROM ARADD01H.dbf h" &
" WHERE h.FCOMPANY = @FCOMPANY " &
" AND h.FCITY = @FCITY " &
" AND h.FZIP = @FZIP " &
"UNION ALL " &
"SELECT " &
" v.FINVNO " &
"FROM ARADD01.dbf v" &
" WHERE v.FCOMPANY = @FCOMPANY " &
" AND v.FCITY = @FCITY " &
" AND v.FZIP = @FZIP "
Using connection As New OleDbConnection(connString)
Using command As New OleDbCommand(queryString, connection)
command.Parameters.Add(New OleDbParameter("@FCOMPANY", OleDbType.VarChar)).Value = sFi.FCUSTOMER_NAME
command.Parameters.Add(New OleDbParameter("@FCITY", OleDbType.VarChar)).Value = sFi.FADDRESS.City
command.Parameters.Add(New OleDbParameter("@FZIP", OleDbType.VarChar)).Value = sFi.FADDRESS.PostalCode
Try
connection.Open()
Using reader As OleDbDataReader = command.ExecuteReader()
While reader.Read()
If Not reader.IsDBNull(0) Then
pResults.Add(New InvoiceIndex() With {
.InvoiceNumber = reader.GetValue(0),
.InvoiceDate = DateTime.Now
})
End If
End While
reader.Close()
End Using
Catch ex As Exception
ErrorLogging.NLog_Exception(ex, "Db_FI_CustomerAdvByDate")
Finally
connection.Close()
End Try
End Using
End Using
Return pResults
End Function
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Hello,
Still having issues with the code from @jkirkerx. It is me, not the code, I am sure.
So, I decided to go the Richard route by switching back to binding to ListView datasource.
However, the code is not giving an error but when enter a vaue say for Caller List and click "Generate Report", nothing happens.
I just flickers for a second.
I know I am missing something critical but have been looking at this now for two days with no success.
I have decided to post some relevant HTML markup and some VB. Hopefuly, you guys can put me out of my misery.
Thank you very much.
<table class="criteria" runat="server">
<tr id="row_0" class="evenRow">
<th>UUID</th>
<td>
<asp:TextBox id="suuid" style="width:150px;" class="form-control" runat="server" />
</td>
</tr>
<tr id="row_1" class="oddRow">
<th>Call List ID</th>
<td>
<asp:TextBox id="caller_list_id" style="width:150px;" class="form-control" runat="server" />
</td>
</tr>
<tr id="row_2" class="evenRow">
<th>Phone Number</th>
<td>
<asp:TextBox id="phonenumber" style="width:150px;" class="form-control" runat="server" />
</td>
</tr>
<tr id="row_3" class="oddRow">
<th>Start Date</th>
<td>
<asp:TextBox id="date_start" style="width:150px;" class="form-control" runat="server" />
</td>
</tr>
<tr id="row_4" class="evenRow">
<th>End Date</th>
<td>
<asp:TextBox id="date_end" style="width:150px;" class="form-control" runat="server" />
</td>
</tr>
<tr class="oddRow">
<th>Call Type</th>
<td>
<asp:DropDownList id="call_type" runat="server">
<asp:ListItem SELECTED="True" value="">All</asp:ListItem>
<asp:ListItem value="0" Text="0 ">Unknown</asp:ListItem>
<asp:ListItem value="1" Text="1">Outbound Progressive</asp:ListItem>
<asp:ListItem value="2" Text="2">Outbound Predictive</asp:ListItem>
<asp:ListItem value="3" Text="4">Inbound</asp:ListItem>
<asp:ListItem value="4" Text="8">Blaster</asp:ListItem>
<asp:ListItem value="5" Text="16">Personal Inbound</asp:ListItem>
<asp:ListItem value="6" Text="32">Nailin</asp:ListItem>
<asp:ListItem value="7" Text="64">External Transfer</asp:ListItem>
<asp:ListItem value="8" Text="128">PBX Outbound</asp:ListItem>
<asp:ListItem value="9" Text="256">PBX Inbound</asp:ListItem>
<asp:ListItem value="10" Text="512">Snoop</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
<div class="menuToolbar" style="padding-bottom: 30px;">
<asp:Button runat="server" id="btnSubmit" Text="Generate Report" OnClick="btnSubmit_Click" />
<asp:Button runat="server" id="btnExport" Text="Export to CSV" OnClick="btnExport_Click" />
</div>
<asp:ListView ID="ProductsListView" runat="server" DataKeyNames="uuid" DataSourceID="SqlDataSource1">
<LayoutTemplate>
<table runat="server" border="0" style="">
<tr>
<th style="border-left: 1px solid #557799" class="reportHeader">
Dialed Phone Number
</th>
<th class="reportHeader">
Caller ID
</th>
<th class="reportHeader">
Call List ID
</th>
<th class="reportHeader">
Start Time
</th>
<th class="reportHeader">
Connect Time
</th>
<th class="reportHeader">
End Time
</th>
<th class="reportHeader">
Duration
</th>
<th class="reportHeader">
Full Duration
</th>
<th class="reportHeader">
Campaign Name
</th>
<th class="reportHeader">
Queue Name
</th>
<th class="reportHeader">
Call Type
</th>
<th class="reportHeader">
Employee
</th>
<th class="reportHeader">
ISDN Cause Code
</th>
<th class="reportHeader">
Disposition
</th>
<th class="reportHeader">
Telephony Server
</th>
<th class="reportHeader">
Trunk Name
</th>
<th class="reportHeader">
UUID
</th>
<th class="reportHeader">
Recording
</th>
<th class="reportHeader">
Details
</th>
</tr>
<tr runat="server" id="itemPlaceholder">
<td colspan="19">
There is no data!
</td>
</tr>
</table>
</LayoutTemplate>
<SelectedItemTemplate>
<table runat="server" border="0" style="">
<tr>
<td><asp:Label ID="lblphonenumber" Text='<%# Eval("phone_number") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblcallerid" Text='<%# Eval("callerid") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblcallerlist" Text='<%# Eval("call_list_id") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblstarttime" Text='<%# Eval("startttime") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblconnectime" Text='<%# Eval("connecttime") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblendtime" Text='<%# Eval("endtime") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblduration" Text='<%# Eval("duration") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblfullduration" Text='<%# Eval("fullduration") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblampaignname" Text='<%# Eval("camapign_id") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblqueuename" Text='<%# Eval("queue_id") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblcalltype" Text='<%# Eval("call_type_id") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblemployee" Text='<%# Eval("roll_id") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblisdn" Text='<%# Eval("cause_code") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lbldisposition" Text='<%# Eval("cause_code") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lbltelephony" Text='<%# Eval("box_id") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lbltrunkname" Text='<%# Eval("trunk_name") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lbluuid" Text='<%# Eval("uuid") %>' runat="server"></asp:Label></td>
<td><asp:Label ID="lblrecording" Text='<%# Eval("customer_id") %>' runat="server"></asp:Label></td>
<td><a href="#"></a></td>
</tr>
</table>
</SelectedItemTemplate>
</asp:ListView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test %>"
SelectCommand="SELECT phone_number, callerid, call_list_id, startttime,connecttime, endtime, duration, fullduration,
camapign_id, queue_id, name, roll_id, cause_code, uuid, box_id, trunk_name, uuid, customer_id FROM cti.qpcdr, cti.call_types
WHERE cti.qpcdr.call_type_id = cti.call_types.id">
<SelectParameters>
<asp:ControlParameter ControlID="suuid" Name="uuid" PropertyName="Text" Type="String" DefaultValue="%" />
<asp:ControlParameter ControlID="caller_list_id" PropertyName="Text" Name="callerlist" />
<asp:ControlParameter ControlID="phonenumber" PropertyName="Text" Name="phone" />
<asp:ControlParameter ControlID="date_start" PropertyName="Text" Name="start" />
<asp:ControlParameter ControlID="date_end" PropertyName="Text" Name="end" />
<asp:ControlParameter ControlID="call_type" PropertyName="SelectedValue" Name="calltype" />
</SelectParameters>
</asp:SqlDataSource>
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
'TODO
End If
End Sub
Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
'ProductsListView.DataSource = SqlDataSource1
ProductsListView.DataBind()
End Sub
Protected Sub btnExport_Click(sender As Object, e As EventArgs)
ProductsListView.DataBind()
End Sub
|
|
|
|
|
Well the code I gave you is concept code, and not actual code.
To perfect my code, you have to do one step at a time.
So the first step would be to write the database code. And confirm that the proper data is returned in the model that you designed.
Once confirmed, you can move to the next step, which is to plug the code into your page code behind and reconfirm that the data model is still intact.
The last step would be to do a small sample in HTML, to confirm that you understand how to transfer the model from the code behind to the asp.net web form.
But this should be the same process as what Richard suggested.
What I do sometimes, is startup a new project or open a test project on a really small scale, and isolate the new code in a fresh environment. Most times it works correctly, which tells me that some other code is the problem.
I totally get the frustration. You don't know how bad I needed help with a project that I just could not figure out, and knew posting here would not help. I wished someone could of done a remote terminal on my project. But using a test project and plugging the code in 1 section at a time solved it for me.
I haven't work with web forms in over 4 years now. I would create a new form on a smaller scale, and work with 1 or 2 columns first and get that working. Then expand a couple columns at a time. It might be a miss typed column name or wrong data type and you got a silent crash.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Ok thank you so much for your encouragement.
Can you please explain this code just a bit mor.
I see that you are passing one param InvoiceIndex to your function.
But I also see three other variables that are part of the WHERE predicate.
I understand the variables are part of the search filters but I am a bit confused by them.
I
|
|
|
|
|
I didn't go back and delete the extra parameters. I thought I might need them to narrow down the customer in case of a duplicate.
I passed a model into the database function, and returned a model out of the database function.
So I made a model called "Invoice", then called a database function to populate the Invoice.
Then passed the "Invoice" model to another database function to get the rest of the invoice data.
I kept everything in models because it was just easier to do and less code to write. Rather than Dim more space, I just used the existing space I already made. I'm just populating the model, so I can pass it to the presentation layer and consume it.
You just seem to be having trouble at an unknown point and need to isolate the problem.
So you can write code to get the data, package it and confirm it.
Then write fake data and pass it to the presentation layer, or your code behind and the web form to confirm that.
And you should see your mistake.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
I am still working on this issue but I have made good progress.
Right now, I am using GridView control which seems to be displaying more than one, several rows as a matter of fact.
My issue now is with embedded query.
There are 6 input parameters and a user can search with one or more at same time.
The query is not results based on input parameter.
For instance, if I select an item from the call_type dropdownList, I expect results based on the selected item from the dropdownList.
The query is not doing that. It is displaying ALL the records.
Same is the case with the rest of the parameters; the filtering is not working and I believe it has to do with the WHERE clauses in the query.
Can you please, please help?
Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
Me.SearchCustomers()
End Sub
Private Sub SearchCustomers()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand()
Dim sql As String = "Select top 100 phone_number, callerid, call_list_id, startttime, "
sql += "connecttime, endtime, duration, fullduration,ca.campName,"
sql += "camapign_id, q.queueName, c.name as call_type, roll_id, (e.first_name +' '+ e.last_name) employee, i.name as cause_code, uuid, box_id, trunk_name, uuid, customer_id "
sql += "From cti.qpcdr qp inner join cti.call_types c on qp.call_type_id = c.id "
sql += "inner join cti.queues q on qp.queue_id = q.queueId "
sql += "inner join cti.campaign ca on qp.camapign_id = ca.campaign_id "
sql += "inner join cti.isdn_cause_code i On qp.cause_code = i.cause_code "
sql += "inner join cti.employee e on qp.employee_id = e.employee_id "
If Not String.IsNullOrEmpty(suuid.Text.Trim()) Or Not String.IsNullOrEmpty(caller_list_id.Text.Trim()) Or Not String.IsNullOrEmpty(phone.Text.Trim()) Or Not String.IsNullOrEmpty(start.Text.Trim()) Or Not String.IsNullOrEmpty(endd.Text.Trim()) Or Not String.IsNullOrEmpty(calltype.Text.Trim()) Then
sql += " WHERE 1 = 1 AND qp.uuid LIKE '%' + @uuid + '%'"
sql += " OR qp.call_list_id LIKE '%' + @callerlist + '%'"
sql += " OR qp.phone_number LIKE '%' + @phone + '%'"
sql += " OR qp.startttime LIKE '%' + @start + '%'"
sql += " OR qp.endtime LIKE '%' + @Endd + '%'"
sql += " OR c.id LIKE '%' + @calltype + '%'"
cmd.Parameters.AddWithValue("@uuid", suuid.Text)
cmd.Parameters.AddWithValue("@callerlist", caller_list_id.Text)
cmd.Parameters.AddWithValue("@phone", phonenumber.Text)
cmd.Parameters.AddWithValue("@start", date_start.Text)
cmd.Parameters.AddWithValue("@Endd", date_end.Text)
cmd.Parameters.AddWithValue("@calltype", call_type.SelectedValue)
End If
cmd.CommandText = sql
cmd.Connection = con
Using sda As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
gvCustomers.DataSource = dt
gvCustomers.DataBind()
End Using
End Using
End Using
End Sub
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
