update values... RSS

• • Reply
  

  natasha_arri...

  Member

  698 Points

  343 Posts

  wat u think?

  Dec 15, 2006 05:51 AM|LINK

   

  old code:

  Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
          Dim sqlcom As SqlClient.SqlCommand = MyConn.CreateCommand()

          MyConn.Open()
          sqlcom.CommandType = CommandType.Text
          sqlcom.CommandText = "update TE_zangyou Set ninka_nen_from = @ninka_nen_from ,ninka_gatsu_from = @ninka_gatsu_from, ninka_hi_from = @ninka_hi_from ,ninka_nen_to = @ninka_nen_to, ninka_gatsu_to = @ninka_gatsu_to, ninka_hi_to = @ninka_hi_to, ninka_ji_from = @ninka_ji_from,ninka_ji_to = @ninka_ji_to,ninka_bun_from = @ninka_bun_from,ninka_bun_to = @ninka_bun_to,ninka_day_name_from = @ninka_day_name_from,ninka_day_name_to= @ninka_day_name_to,ninka_date_kyou= @ninka_date_kyou where syain_No = " + Request.QueryString("syain_No") + " and date_kyou ='" + Request.QueryString("date_kyou") + "' and  time_kyou ='" + Request.QueryString("time_kyou") + "'"

  
          sqlcom.Parameters.AddWithValue("@ninka_nen_from", TextBox1.Text)
          sqlcom.Parameters.AddWithValue("@ninka_gatsu_from", TextBox2.Text)
          sqlcom.Parameters.AddWithValue("@ninka_hi_from", TextBox3.Text)
          sqlcom.Parameters.AddWithValue("@ninka_day_name_from", TextBox5.Text)
          sqlcom.Parameters.AddWithValue("@ninka_ji_from", textbox_from_hr.Text)
          sqlcom.Parameters.AddWithValue("@ninka_bun_from", textbox_from_min.Text)
          sqlcom.Parameters.AddWithValue("@ninka_nen_to", TextBox6.Text)
          sqlcom.Parameters.AddWithValue("@ninka_gatsu_to", TextBox7.Text)
          sqlcom.Parameters.AddWithValue("@ninka_hi_to", TextBox8.Text)
  

  i change my code ....to something like this below but still dont work,... what do u think?

  NEW CODE 

  Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
          Dim sqlcom As SqlClient.SqlCommand = MyConn.CreateCommand()

          MyConn.Open()
          sqlcom.CommandType = CommandType.Text
          sqlcom.CommandText = "update TE_zangyou Set ninka_nen_from = @ninka_nen_from ,ninska_gatsu_from = @ninka_gatsu_from, ninka_hi_from = @ninka_hi_from ,ninka_nen_to = @ninka_nen_to, ninka_gatsu_to = @ninka_gatsu_to, ninka_hi_to = @ninka_hi_to, ninka_ji_from = @ninka_ji_from,ninka_ji_to = @ninka_ji_to,ninka_bun_from = @ninka_bun_from,ninka_bun_to = @ninka_bun_to,ninka_day_name_from = @ninka_day_name_from,ninka_day_name_to= @ninka_day_name_to,ninka_date_kyou= @ninka_date_kyou where syain_No =@syain_No AND date_kyou=@date_kyou AND time_kyou=@time_kyou"

  
          sqlcom.Parameters.AddWithValue("@syain_No", Request.QueryString("syain_No"))
          sqlcom.Parameters.AddWithValue("@date_kyou", Request.QueryString("date_kyou"))
          sqlcom.Parameters.AddWithValue("@time_kyou", Request.QueryString("time_kyou"))

          sqlcom.Parameters.AddWithValue("@ninka_nen_from", TextBox1.Text)
          sqlcom.Parameters.AddWithValue("@ninka_gatsu_from", TextBox2.Text)
          sqlcom.Parameters.AddWithValue("@ninka_hi_from", TextBox3.Text)
          sqlcom.Parameters.AddWithValue("@ninka_day_name_from", TextBox5.Text)
          sqlcom.Parameters.AddWithValue("@ninka_ji_from", textbox_from_hr.Text)
          sqlcom.Parameters.AddWithValue("@ninka_bun_from", textbox_from_min.Text)
          sqlcom.Parameters.AddWithValue("@ninka_nen_to", TextBox6.Text)
          sqlcom.Parameters.AddWithValue("@ninka_gatsu_to", TextBox7.Text)
          sqlcom.Parameters.AddWithValue("@ninka_hi_to", TextBox8.Text)
          sqlcom.Parameters.AddWithValue("@ninka_day_name_to", TextBox9.Text)
          sqlcom.Parameters.AddWithValue("@ninka_ji_to", textbox_to_hr.Text)
          sqlcom.Parameters.AddWithValue("@ninka_bun_to", textbox_to_min.Text)
          Label28.Visible = True
          textbox_approval_date.Visible = True
          textbox_approval_date.Text = Date.Today
          statusbox_approve_mesg.Visible = True
          sqlcom.Parameters.AddWithValue("@ninka_date_kyou", textbox_approval_date.Text)

          sqlcom.ExecuteNonQuery()
          MyConn.Close()
          sqlcom.Dispose()
          MyConn.Dispose()
          MsgBox(" Application Dates was Approved")

   

  パラメータ化クエリ '(@syain_No nvarchar(4),@date_kyou nvarchar(12),@time_kyou nvarch' にはパラメータ @time_kyou が必要ですが、指定されていません。 (It is not specified though parameter @time_kyou is necessary for (@syain_No nvarchar(4), @date_kyou nvarchar(12), and @time_kyou nvarch Ceri of making to the parameter ''. )

  
   

  Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
  
  Exception Details: System.Data.SqlClient.SqlException: パラメータ化クエリ '(@syain_No nvarchar(4),@date_kyou nvarchar(12),@time_kyou nvarch' にはパラメータ @time_kyou が必要ですが、指定されていません。
  
  Source Error:
  
  

  Line 117: sqlcom.Parameters.AddWithValue("@ninka_date_kyou", textbox_approval_date.Text) Line 118: Line 119: sqlcom.ExecuteNonQuery() Line 120: MyConn.Close() Line 121: sqlcom.Dispose()

  
  Source File: C:\Documents and Settings\mspitc5\My Documents\Visual Studio 2005\MSPITC_project\overtime_application_approval_inputt.aspx.vb    Line: 119
  
  Stack Trace:
  
  &&&

  it produces different error....

• • Reply
  

  satish_nagde...

  Contributor

  6572 Points

  1432 Posts

  Re: wat u think?

  Dec 15, 2006 07:31 AM|LINK

  hi grace,

  can you please send me table script and few sample records over here. i am unable to figure out what to do here. right click on table in enterprise manager and under All Tasks option click on generate script. send me that i'll work on.

   thanks,

  satish

• • Reply
  

  natasha_arri...

  Member

  698 Points

  343 Posts

  Re: wat u think?

  Dec 15, 2006 08:06 AM|LINK

  	> TE_zangyou
  primary	syain_No	char(4)	Unchecked
  primary	date_kyou	char(10)Unchecked
  primary	time_kyou	char(5)	Unchecked
  	kinmukeitai_kbn	char(2)	Checked
  	apply_nen_from	char(4)	Checked
  	apply_gatsu_from	char(2)	Checked
  	apply_hi_from	char(2)	Checked
  	apply_day_name_from	char(10)checked
  	apply_ji_from	int	Checked
  	apply_hi_to	char(2)	Checked
  	apply_bun_from	int	Checked
  	apply_nen_to	char(4)	Checked
  	apply_gatsu_to	char(2)	Checked
  	apply_day_name_to	char(10)Checked
  	apply_ji_to	int	Checked
  	apply_bun_to	int	Checked
  	ninka_nen_from	char(4)	Checked
  	ninka_gatsu_from	char(2)	Checked
  	ninka_hi_from	char(2)	Checked
  	ninka_day_name_from	char(10)Checked
  	ninka_ji_ｆrom	int	Checked
  	ninka_bun_ｆrom	int	Checked
  	ninka_nen_to	char(4)	Checked
  	ninka_gatsu_to	char(2)	Checked
  	ninka_hi_to	char(2)	Checked
  	ninka_day_name_to	char(10)Checked
  	ninka_ji_to	int	Checked
  	ninka_bun_to	int	Checked
  	riyuu	char(40)	Checked
  	sagyounaiyou	char(40)	Checked
  	ninka_date_kyou	char(10)	Checked

   

  sample
  	1002	2006/12/12	16:42	c	2007	11	23	Label	9	0	2007	11	24	Label	19	0	2006	11	23	j	9	0	2006	11	24	Ηj	22	0	ewrrg	rtyh	2006/12/14
  	1002	2006/12/13	18:20	x	2007	11	12		9	0	2007	11	14		10	0	2006	11	23	j	9	0	2006	11	24	Ηj	22	0	weqrfwe	rghrt	2006/12/14
  	1003	2006/12/13	18:39		2006	12	12		1	1	2006	12	13		22	11	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	3er	rr	NULL
  	1004	2006/12/13	18:25	c	2006	11	14		10	11	2006	11	15		22	22	2007	11	14		10	11	2007	11	15		22	22	ewwe	wekewew	2006/12/14
  	1004	2006/12/13	18:26		2007	11	14		10	11	2007	11	15		22	22	2007	11	14		10	11	2007	11	15		22	22	ewwe	wekewew	2006/12/14
  	1234	2006/12/13	18:33		2007	10	14		1	11	2007	10	15		22	22	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	333	ww	NULL
  	1234	2006/12/13	18:36	c	2007	12	17		2	22	2007	12	18		22	11	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	ww	ww	NULL
  	2222	2006/12/13	18:34	x	2006	11	19		1	11	2006	11	20		22	11	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	1	NULL

• • Reply
  

  natasha_arri...

  Member

  698 Points

  343 Posts

  Re: update values...

  Dec 18, 2006 11:31 PM|LINK

  thanks....

  but it still got errors...

  if u read my statements above... have u identify what i did or doing worng in my code?

   

  thnx,.,.,.,.

• • Reply
  

  satish_nagde...

  Contributor

  6572 Points

  1432 Posts

  Re: update values...

  Dec 19, 2006 12:34 AM|LINK

  hi grace,

  sorry i'm pretty busy these days due to year ending, could you try following which i think will help you

  sqlcom.Parameters.AddWithValue("@syain_No", '" + Request.QueryString("syain_No") + "'") //adding single quotes to be passed in value
          sqlcom.Parameters.AddWithValue("@date_kyou", '" + Request.QueryString("date_kyou") + "'") // here also
          sqlcom.Parameters.AddWithValue("@time_kyou", Request.QueryString("time_kyou")) // add here also

          sqlcom.Parameters.AddWithValue("@ninka_nen_from", TextBox1.Text)
          sqlcom.Parameters.AddWithValue("@ninka_gatsu_from", TextBox2.Text)
          sqlcom.Parameters.AddWithValue("@ninka_hi_from", TextBox3.Text)
          sqlcom.Parameters.AddWithValue("@ninka_day_name_from", TextBox5.Text)
          sqlcom.Parameters.AddWithValue("@ninka_ji_from", textbox_from_hr.Text)
          sqlcom.Parameters.AddWithValue("@ninka_bun_from", textbox_from_min.Text)
          sqlcom.Parameters.AddWithValue("@ninka_nen_to", TextBox6.Text)
          sqlcom.Parameters.AddWithValue("@ninka_gatsu_to", TextBox7.Text)
          sqlcom.Parameters.AddWithValue("@ninka_hi_to", TextBox8.Text)
          sqlcom.Parameters.AddWithValue("@ninka_day_name_to", TextBox9.Text)
          sqlcom.Parameters.AddWithValue("@ninka_ji_to", textbox_to_hr.Text)
          sqlcom.Parameters.AddWithValue("@ninka_bun_to", textbox_to_min.Text)
          Label28.Visible = True
          textbox_approval_date.Visible = True
          textbox_approval_date.Text = Date.Today
          statusbox_approve_mesg.Visible = True
          sqlcom.Parameters.AddWithValue("@ninka_date_kyou", textbox_approval_date.Text) // and add here also

  thanks and sorry,

  satish.

• • Reply
  

  natasha_arri...

  Member

  698 Points

  343 Posts

  Re: update values...

  Dec 19, 2006 01:00 AM|LINK

  thank you!

  ill try it late! coz i really cant figure it out!

  ill let u know when i got it!

   

  thanks thanks

  and no sorry!

• • Reply
  

  natasha_arri...

  Member

  698 Points

  343 Posts

  Re: update values...

  Dec 20, 2006 05:28 AM|LINK

  satish i tried including that script in my code but still it doesnt work... it doesnt have errors anymore but still no change! not updating....

     Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
          Dim update_ot As SqlClient.SqlCommand = MyConn.CreateCommand()

          MyConn.Open()
          update_ot.CommandType = CommandType.Text
          update_ot.CommandText = "update TE_zangyou Set apply_nen_from = @apply_nen_from ,apply_gatsu_from = @apply_gatsu_from, apply_hi_from = @apply_hi_from ,apply_nen_to = @apply_nen_to, apply_gatsu_to = @apply_gatsu_to, apply_hi_to = @apply_hi_to, apply_ji_from = @apply_ji_from,apply_ji_to = @apply_ji_to,apply_bun_from = @apply_bun_from,apply_bun_to = @apply_bun_to,apply_day_name_from = @apply_day_name_from,apply_day_name_to= @apply_day_name_to where syain_No =@syain_No AND date_kyou=@date_kyou AND time_kyou=@time_kyou"

   

  
          update_ot.Parameters.AddWithValue("@syain_No", "'" + Request.QueryString("syain_No") + "'")

          update_ot.Parameters.AddWithValue("@date_kyou", "'" + Request.QueryString("date_kyou") + "'")

          update_ot.Parameters.AddWithValue("@time_kyou", Request.QueryString("time_kyou"))

          'update_ot.Parameters.AddWithValue("@kinmukeitai_kbn", group_ot.SelectedIndex)
          update_ot.Parameters.AddWithValue("@apply_nen_from", Date_Fr_Yr.SelectedValue)
          update_ot.Parameters.AddWithValue("@apply_gatsu_from", Date_Fr_Month.SelectedValue)
          update_ot.Parameters.AddWithValue("@apply_hi_from", Date_Fr_Day.SelectedValue)
          update_ot.Parameters.AddWithValue("@apply_day_name_from", day_name_fr.Text)
          update_ot.Parameters.AddWithValue("@apply_nen_to", Date_To_Yr.SelectedValue)
          update_ot.Parameters.AddWithValue("@apply_gatsu_to", Date_To_Month.SelectedValue)
          update_ot.Parameters.AddWithValue("@apply_hi_to", Date_To_Day.SelectedValue)
          update_ot.Parameters.AddWithValue("@apply_day_name_to", day_name_to.Text)
          update_ot.Parameters.AddWithValue("@apply_ji_from", Time_Fr_Hr.Text)
          update_ot.Parameters.AddWithValue("@apply_bun_from", Time_Fr_Min.Text)
          update_ot.Parameters.AddWithValue("@apply_ji_to", Time_To_Hr.Text)
          update_ot.Parameters.AddWithValue("@apply_bun_to", Time_To_Min.Text)
          'update_ot.Parameters.AddWithValue("@riyuu", reason_ot.Text)
          'update_ot.Parameters.AddWithValue("@sagyounaiyou", destination_ot.Text)

          update_ot.ExecuteNonQuery()
          update_ot.Dispose()
          update_ot.Parameters.Clear()

• • Reply
  

  satish_nagde...

  Contributor

  6572 Points

  1432 Posts

  Re: update values...

  Dec 20, 2006 05:38 AM|LINK

  aah in that case make sure there is data matching to criteria like passing.

  the possible cause of not updating could be the case say your date field has value '20-dec-2006 11:30:22' -- i'm assuming time is also saved in record

  and from your front end the value being passed might be like '20-DEC-2006 00:00:00' --

  so here if you compare dates the dates wont match hence no update. please check data properly.

   thanks,

  satish.

   

• • Reply
  

  natasha_arri...

  Member

  698 Points

  343 Posts

  hello...

  Jan 09, 2007 05:05 AM|LINK

  Happy new year first :-)

  then help pls?hehehe

  I have this code for updating records...and i ask it you before....but still i dont know why it doesnt work...it seems to me that its ok..but nothing happens....

  it doesnt update my records...

  and if i changed my session to request.requerystring it produce error.... it doesnt read the ":" sign of time and" /" in date.....considering that the data types are only characters.....

  what do u think?

  here is my code for updating..... or approving data

   Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
          Dim sqlcom As SqlClient.SqlCommand = MyConn.CreateCommand()

          MyConn.Open()
          sqlcom.CommandType = CommandType.Text
          sqlcom.CommandText = "update TE_zangyou Set ninka_nen_from = @ninka_nen_from ,ninka_gatsu_from = @ninka_gatsu_from, ninka_hi_from = @ninka_hi_from ,ninka_nen_to = @ninka_nen_to, ninka_gatsu_to = @ninka_gatsu_to, ninka_hi_to = @ninka_hi_to, ninka_ji_from = @ninka_ji_from,ninka_ji_to = @ninka_ji_to,ninka_bun_from = @ninka_bun_from,ninka_bun_to = @ninka_bun_to,ninka_day_name_from = @ninka_day_name_from,ninka_day_name_to= @ninka_day_name_to,ninka_date_kyou= @ninka_date_kyou where syain_No = " + Session("syain_No") + " and date_kyou ='" + Session("date_kyou") + "' and  time_kyou ='" + Session("time_kyou") + "'"

          sqlcom.Parameters.AddWithValue("@ninka_nen_from", TextBox1.Text)
          sqlcom.Parameters.AddWithValue("@ninka_gatsu_from", TextBox2.Text)
          sqlcom.Parameters.AddWithValue("@ninka_hi_from", TextBox3.Text)
          sqlcom.Parameters.AddWithValue("@ninka_day_name_from", TextBox5.Text)
          sqlcom.Parameters.AddWithValue("@ninka_ji_from", textbox_from_hr.Text)
          sqlcom.Parameters.AddWithValue("@ninka_bun_from", textbox_from_min.Text)
          sqlcom.Parameters.AddWithValue("@ninka_nen_to", TextBox6.Text)
          sqlcom.Parameters.AddWithValue("@ninka_gatsu_to", TextBox7.Text)
          sqlcom.Parameters.AddWithValue("@ninka_hi_to", TextBox8.Text)
          sqlcom.Parameters.AddWithValue("@ninka_day_name_to", TextBox9.Text)
          sqlcom.Parameters.AddWithValue("@ninka_ji_to", textbox_to_hr.Text)
          sqlcom.Parameters.AddWithValue("@ninka_bun_to", textbox_to_min.Text)
          Label28.Visible = True
          textbox_approval_date.Visible = True
          textbox_approval_date.Text = Date.Today
          statusbox_approve_mesg.Visible = True
          sqlcom.Parameters.AddWithValue("@ninka_date_kyou", textbox_approval_date.Text)

          sqlcom.ExecuteNonQuery()
          MyConn.Close()
          sqlcom.Dispose()
          MyConn.Dispose()
          MsgBox(" Application Dates was Approved")

          TextBox1.Enabled = False
          TextBox2.Enabled = False
          TextBox3.Enabled = False
          TextBox6.Enabled = False
          TextBox5.Enabled = False
          TextBox7.Enabled = False
          TextBox8.Enabled = False
          TextBox9.Enabled = False
          textbox_from_hr.Enabled = False
          textbox_from_min.Enabled = False
          textbox_to_hr.Enabled = False
          textbox_to_min.Enabled = False
          Reason.Enabled = False
          compensantory.Enabled = False
          Edit1.Visible = False
          Button_save.Visible = False
          Button_approve.Visible = False
          Button_reject.Visible = False
          Button_send.Visible = True

   

      End Sub

• • Reply
  

  natasha_arri...

  Member

  698 Points

  343 Posts

  database...!?help? ......................again?

  Jan 24, 2007 05:18 AM|LINK

  hi satish!

  its been awhile....

  i got new project again similar to my previous project but i never encountered doing it b4.

  hmmmm the scenario is:

  i have a drobdownlist for a office_name and a text box for "date"(2007/11/03). after  choosing a name from a drop down and writing a date in the textbox when i press the button (ok) a datagrid will appear with its data.

  "office_name" field is in table (TM0011) and "year.month.date" is in table (TT0001).

  In the gridview.i need to show the time_in,time_out and the name of the employee.

  "time in" and "time_out" fields are in the table (TT0001) and syain_name field  is in table(TM0001).the only connection they have is the  "syain_id".coz both table have "syain_id" as key.   

   I have two option in my code.. which do u think will most likely work? the inner join or union?

  here is my code:

    Protected Sub Button_date_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_date.Click

          GridView_info.Visible = True

          '////////// option 1 //////////////////
          Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MS_PKG01ConnectionString").ConnectionString)
          MyConn.Open()
          Dim stringQuery As String = "select TT0001.*,TM0001.*,TM0011.* from TT0001 inner join TM0001 on TT0001.syain_id = TM0001.syain_id " '+ Request.requerystring("syain_id")
          Dim sqldataadapter As New SqlClient.SqlDataAdapter(stringQuery, MyConn)

          Dim ds As New DataSet()
          sqldataadapter.Fill(ds, "TT0001")
          GridView_info.DataSourceID = ""
          GridView_info.DataSource = ds
          GridView_info.DataBind()
          MyConn.Close()

          '/////////option 2 ///////////////////
          'Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MS_PKG01ConnectionString").ConnectionString)
          'MyConn.Open()

          'Dim stringQuery As String = " select TT0001.syain_id,TT0001.year,TT0001.month,TT0001.day,TT0001.in_hh,TT0001in_mi,TT0001out_hh,TT0001out_mi From TT0001 Where TT0001.syain_id = TM0001.syain_id and TT0001.syain_id = " + Session("syain_id") + " Union Select TM0001.syain_id,TM0001.syain_name From TM0001 Where TM0001.syain_name = TTM0001.syain_name and TM0001.syain_id = " + Session("syain_id") + " "
          'Dim stringQuery2 As String = "SELECT * from tempo_db"

          'Dim SQLcommand1 As New SqlClient.SqlCommand("Drop table tempo_db", MyConn)
          'SQLcommand1.ExecuteNonQuery()

          'Dim SQLcommand2 As New SqlClient.SqlCommand("Create table tempo_db ( year char(4), month(2), day char(2), in_hh(2),in_mi(2), out_hh(2),out_mi(2) )", MyConn)
          'SQLcommand2.ExecuteNonQuery()

          'Dim sqldataadapter As New SqlClient.SqlDataAdapter(stringQuery, MyConn)
          'Dim ds As New DataSet()
          'Dim foundrow, temprow As DataRow

          'Dim ds2 As New DataSet
          'Dim temp_data_table As New DataTable
          'sqldataadapter.Fill(ds, "TT0001")

          'Dim sqldataadapter2 As New SqlClient.SqlDataAdapter(stringQuery2, MyConn)
          'sqldataadapter2.Fill(ds2, "tempo_db")

          'Dim date_ctr As Integer
          'date_ctr = 1
          'GridView_info.DataSourceID = ""
          'GridView_info.DataSource = ds
          'GridView_info.DataBind()
          'MyConn.Close()

      End Sub

   

  thank you....

  thank you.