|
|
How to import rows from Excel to MS SQL using ASP.net (VB code)?
|
|
|
|
|
|
Greetings again.
Hope this won't be too much of a bother.
I have the following from my gridview:
sp:HyperLinkField DataNavigateUrlFields="filename" headertext="Recording" DataNavigateUrlFormatString="http://Archive/{servername from DB}/play_recording_wav.asp.net?filename='noop_'{0}'.wav" Text="Listen" />
When run, I would like it the url to look like this:
http:
Basically, server1 is value for fieldname called servername, noop_ is hardcoded to filename which is also from the database.
More like this:
c = "archives/"+ '<%# Eval("Servername") %> + "noop_" + Eval("filename") %>' + ".wav"
modified 23-May-19 15:29pm.
|
|
|
|
|
Try:
<asp:HyperLinkField
DataNavigateUrlFields="filename, servername"
DataNavigateUrlFormatString="http://Archive/{1}/play_recording_wav.aspx?filename=noop_{0}.wav"
...
/>
"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 sir.
I really appreciate your help.
|
|
|
|
|
when i saved bookmarks i need to force the user to sign in instead of continuing with the application for safety and security. can you please share the code that i can use.
|
|
|
|
|
Its hard to predict in my knowledge to check if user accessing your application from bookmark link. Also, are you not killing your active sessions? So when user bookmark your application link and comes back after some time/day that session is gone and he has to re-login again? What is your strategy to establish session with user on login?
modified 20-Sep-20 21:01pm.
|
|
|
|
|
Sir
I am using VB.Net 2008 and i want to creat user registration page where i want to send user confirmation OTP via email during the user registration.please guide me with code using vb.net 2008 in developing asp web.
|
|
|
|
|
|
|
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"...
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
